How to Migrate PostgreSQL Databases

PostgreSQL is an open source, object-relational database built with a focus on extensibility, data integrity, and speed. Its concurrency support makes it fully ACID-compliant, and it supports dynamic loading and catalog-driven operations to let users customize its data types, functions, and more.

You can migrate existing on-premise or cloud hosted PostgreSQL databases to clusters in your DigitalOcean account. Migrating a database establishes a connection with an existing database and replicates its contents to the new database cluster. If the existing database is continuously being written to, the migration process will continue until there is no more data to replicate or you manually stop the migration.

We do not currently support migrating databases from clusters inside of DigitalOcean to other clusters inside of DigitalOcean.

Logical Replication and Migration Strategies

The online migration feature uses logical replication to migrate data from one database to another. Logical replication continuously streams the replication line-by-line, including any changes being written to the database during the migration, until the replication is stopped.

Depending on your database’s workload, you may need to develop a strategy on how best to cutover to your new database after the migration is complete.

Continuous Migration vs. Dump

There are two methods of migration: continuous migration and a dump. Continuous migration requires superuser permissions and keeps the source database operational while transferring data to the target database. This tutorial provides instructions for continuous migration. A dump does not require superuser permissions and is a point-in-time snapshot, meaning that any data written to your source database after initiating the dump will not transfer over to the target database. For this reason, migrating via a dump requires that the database be offline or in maintenance mode.

\du

Role name |                      Attributes                            |                 Member of                         
----------+------------------------------------------------------------+-----------------------------------------
_dodb     | Superuser, Replication                                     | {}
example   | Create role, Create DB, Replication, Bypass RLS            | {pg_read_all_stats,pg_stat_scan_tables,pg_signal_backend}
postgres  | Superuser, Create role, Create DB, Replication, Bypass RLS | {}

We recommend continuous migration; however, if you do not have superuser permissions, you can migrate your database with a dump if you have the following permissions instead:

• Connect
• Select on all tables in the database
• Select on all the sequences in the database

To initiate the dump, ensure your database is offline or in maintenance mode, then skip to the final step.

Warning

A dump is a point-in-time snapshot, meaning that any data written to your source database after initiating the dump will not transfer over to the target database. If you choose this method, ensure your database is offline or in maintenance mode.

Prerequisites

To migrate an existing database to a DigitalOcean database cluster, you need to ensure logical replication is enabled on the source database, have the source database’s connection credentials, and disable or update any firewalls between the databases.

Have Superuser Permissions

To follow our recommended process for preparing a database for migration and to migrate a database, you need superuser permissions on the source database.

Make Database Publicly Accessible

To migrate a database, the source database’s hostname or IP address must be accessible from the public internet. Public connection information for DigitalOcean databases are in the database’s Connection Details in the control panel. For other providers, reference their documentation for more information.

Reference Source Database’s Credentials

Before migrating an existing database, you need the following information about the source database:

• Hostname or connection string: The public hostname, connection string, or IP address used to connect to the database.
• Port: The port used to connect to the database. DigitalOcean clusters connect on port 25061 by default.
• Username: The username used to connect to the database. The username needs the superuser permission to access the data you want to migrate.
• Password: The password used to connect to the database.

Reference your database provider’s documentation for details on how to locate this information.

Update or Disable Firewalls

To migrate an existing database, you also need to update or temporarily disable any firewalls protecting the databases to allow the databases to connect to each other.

To do this on the target DigitalOcean database, remove any trusted sources from the database cluster. Removing all trusted sources leaves the database open to all incoming connections. To keep your databases secure after migration, make sure to add the trusted sources back to the database.

For the source database outside of DigitalOcean, you may need to update or temporarily disable any firewalls protecting the database before attempting migration. Please refer to your database provider’s documentation to see how to do this.

Prepare the Source Database for Migration

Once the source database is accessible from the public internet, prepare the source database itself for migration by:

• Allowing remote connections.
• Changing your IPv4 local connection to 0.0.0.0/0.
• Enabling logical replication.
• Setting the maximum replication slots equal to or greater than the number of databases in the PostgreSQL server.
• Restarting your PostgreSQL server.

Allow Remote Connections

First, verify that your database is allowing all remote connections. This is determined by your database’s listen_addresses variable, which allows all remote connections when its value is set to *. To check its current value, run the following query in the PostgreSQL (psql) terminal:

SHOW listen_addresses;

If enabled, the command line returns:

listen_addresses
-----------
*
(1 row)

If the output is different, allow remote connections in your database by running the following query:

ALTER SYSTEM SET listen_addresses = '*';

You also need to change your local IPv4 connection to allow all incoming IPs. To do this, find the configuration file pg_hba.conf with the following query:

SHOW hba_file;

Open pg_hba.conf in your text editor of choice, such as nano:

nano pg_hba.conf

Under IPv4 local connections, find and replace the IP address with 0.0.0.0/0, which allows all IPv4 addresses:

    
        
            
# TYPE  DATABASE        USER            ADDRESS                 METHOD

# IPv4 local connections:
host    all             all             0.0.0.0/0               md5
# IPv6 local connections:
host    all             all             ::/0                    md5

        
    

For a full description of the configuration file’s syntax, see the official documentation.

Enable Logical Replication

Most cloud database providers have logical replication enabled by default. If you are migrating a database from an on-premises server, logical replication may not be enabled. If your database is not set up for logical replication, the migration process will not work because the database can only move your schemas, not your data.

To verify that logical replication has been enabled, run the following query in the PostgreSQL (psql) terminal:

SHOW wal_level;

If enabled, the command line returns:

wal_level
-----------
logical
(1 row)

If the output is different, enable logical replication in your database by setting wal_level to logical:

ALTER SYSTEM SET wal_level = logical;

Change Max Replication Slots

After enabling logical replication, you need to verify that your database’s max_replication_slots value is equal to or greater than the number of databases you have in your PostgreSQL server. To check your current value, run the following query in the PostgreSQL (psql) terminal:

SHOW max_replication_slots;

The command line returns:

max_replication_slots
-----------
<number of slots, e.g. 8>
(1 row)

If <number of slots> is smaller than the number of databases in your PostgreSQL server, adjust it by running the following query, where use_your_number is the number of databases in your server:

ALTER SYSTEM SET max_replication_slots = use_your_number;

Restart the Server

To make your changes in this section take effect, restart your PostgreSQL server:

sudo service postgresql stop
sudo service postgresql start

Migrate a Database Using the API

Migrate a Database Cluster Using the Control Panel

To migrate a PostgreSQL database from the DigitalOcean Control Panel, click Databases and then select the database you want to migrate to from your list of databases.

From the database’s Overview page, click the Actions button and then select Set Up Migration.

In the PostgreSQL migration window, click Continue, then enter the source database’s credentials. Once you have entered the source database’s credentials, click Start Migration. A migration status banner opens at the top of the Overview page and your target database’s data begins to transfer.

You can stop the migration at any time by clicking the Stop Migration button in the migration status banner. If you stop migration, the database retains any migrated data.

Warning

During migration, you can still write to the target database, but avoid the following actions because they may result in conflicts and replication issues:

• Do not write to any tables on the target database that the migration is already editing.
• Do not manually alter the source database’s replication configuration, change wal_level, or reduce max_replication_slots.
• Do not make changes to either database that could prevent the source and target database from connecting with each other. This includes modifying the source database’s listen address and updating or enabling firewalls/trusted sources on either database.