How to Migrate PostgreSQL Databases to DigitalOcean using Continuous Migration

You can migrate existing non-DigitalOcean PostgreSQL databases to a DigitalOcean Managed Databases cluster. There are two methods of migration:

• Continuous migration establishes a connection with an existing database and replicates its contents to the new database cluster using logical replication, including any changes being written to the database during the migration, until there is no more data to replicate or you manually stop the replication.

  We recommend this strategy when you want to keep the source database operational while transferring data to the target database. You cannot use continuous migration to move an existing DigitalOcean Managed Databases cluster from one DigitalOcean team to another.

• Importing a dump, which is a point-in-time snapshot of the database. Any data written to your source database after initiating the dump does not transfer over to the target database.

  You must import a dump to migrate an existing DigitalOcean Managed Databases cluster from one DigitalOcean team to another. We also recommend this strategy if you do not have superuser permissions on the source database.

This article covers continuous migration. For instructions on importing a dump, see our how-to on importing PostgreSQL databases with pg_dump.

Prerequisites

To migrate an existing non-DigitalOcean PostgreSQL database into a DigitalOcean Managed Databases cluster, you need to:

1. Get the source database’s credentials.
2. Check the PostgreSQL versions of both databases.
3. Have superuser permissions on the source database.
4. Update networking to make the databases accessible to each other.

Get Source Database’s Credentials

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.

Check PostgreSQL Versions

The source database’s PostgreSQL version must not be newer than the target cluster’s version. This can result in an error that causes migration to fail.

If the target DigitalOcean Managed Databases cluster is on an older version, upgrade it.

Have Superuser Permissions

To use continuous migration, you need superuser permissions on the source database.

\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 | {}

Update Networking

To use continuous migration, the source database’s hostname or IP address must be accessible from the public internet, and any firewalls protecting the databases must allow the databases to connect to each other.

On the target DigitalOcean database cluster, remove any trusted sources from the database cluster to leave the database open to all incoming connections. To keep your databases secure after migration, add the trusted sources back to the database. Public connection information for DigitalOcean databases are in the database’s connection details in the control panel.

For the source database outside of DigitalOcean, you may need to confirm its accessibility to the public internet and update or temporarily disable any firewalls. Refer to your database provider’s documentation for information.

Prepare the Source Database for Migration

Once the prerequisites are satisfied, you need to prepare the source database itself for migration by:

1. Allowing remote connections.
2. Enabling logical replication.
3. Setting the maximum replication slots equal to or greater than the number of databases in the PostgreSQL server.
4. Restarting your PostgreSQL server.

Migration using the control panel migrates all databases, and therefore requires replication permissions on all databases.

Migration using the control panel requires that you grant replication permissions to all databases. However, you can exclude databases in the PostgreSQL migration window or by changing the parameter ignore_dbs using the DigitalOcean API. In this case, only databases selected with the parameter need replication permissions.

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;

CREATE EXTENSION aiven_extras CASCADE;

CREATE PUBLICATION pub_source_tables
FOR TABLE test_table,test_table_2,test_table_3
WITH (publish=‘insert,update,delete’);

pg_dump --schema-only --no-publications \
SRC_CONN_URI              \
-t test_table -t test_table_2 -t test_table_3 > origin-database-schema.sql

CREATE EXTENSION aiven_extras CASCADE;

\i origin-database-schema.sql

SELECT * FROM
aiven_extras.pg_create_subscription(
 ‘dest_subscription’,
 ‘host=SRC_HOST password=SRC_PASSWORD port=SRC_PORT dbname=SRC_DATABASE user=SRC_USER’,
 ‘pub_source_tables’,
 ‘dest_slot’,
 TRUE,
 TRUE);

SELECT subdbid, subname, subowner, subenabled, subslotname
FROM aiven_extras.pg_list_all_subscriptions();

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

Initiate the Migration

Using the Control Panel

To migrate a PostgreSQL database from the DigitalOcean Control Panel, in the left menu, click Databases, 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 select a connection type between public host, public connection string, or private host. Enter the source database’s credentials, the source database’s credentials, any databases you want to exclude, and your credentials. Then, 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.

Using the API

curl -X PUT \
-H "Content-Type: application/json" \
-H "Authorization: Bearer $DIGITALOCEAN_TOKEN" \
-d '{"source":{"host":"source-do-user-6607903-0.b.db.ondigitalocean.com","dbname":"defaultdb","port":25060,"username":"doadmin","password":"paakjnfe10rsrsmf"},"disable_ssl":false,"ignore_dbs":["db0","db1"]}' \
"https://api.digitalocean.com/v2/databases/9cc10173-e9ea-4176-9dbc-a4cee4c4ff30/online-migration"

import os
from pydo import Client

client = Client(token=os.environ.get("DIGITALOCEAN_TOKEN"))

req = {
  "source": {
    "host": "source-do-user-6607903-0.b.db.ondigitalocean.com",
    "dbname": "defaultdb",
    "port": 25060,
    "username": "doadmin",
    "password": "paakjnfe10rsrsmf"
  },
  "disable_ssl": False
  "ignore_dbs": ["db0","db1"]
}

update_resp = client.databases.update_online_migration(database_cluster_uuid="a7a8bas", body=req)

During Migration

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.

Migrations automatically stop after two weeks. We do not recommend leaving migrations ongoing in order to keep two database clusters in sync. Instead, we recommend adding a read-only node to your cluster.