How to Migrate PostgreSQL Databases Beta

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 PostgreSQL databases from other cloud providers (such as AWS, Azure, and self-hosted databases) or from inside DigitalOcean to other 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.

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.

Prerequisites

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

Enable Logical Replication

Most cloud database providers have logical replication enabled by default, but if you are migrating a database from an on-premises server, you need to verify that logical replication is enabled on the database before migration. If your database is not set up for logical replication, the migration feature will not work. You can tell if your database is not set up for logical replication if your schemas migrate but your data does not during the migration.

You can enable logical replication on your database by editing the postgresql.conf file of the PostgreSQL installation.

To enable logical replication on the source database, open postgresql.conf in a text editor. It is located at /etc/postgresql/<YOUR-POSTGRESQL-VERSION-NUMBER>/main/postgresql.conf (C:/Program Files/PostgreSQL/<YOUR-POSTGRESQL-VERSION-NUMBER>/data/postgresql.conf on Windows).

In postgresql.conf, uncomment the parameter wal_level and set it to the following:

wal_level = logical

Save the changes to the file and then restart PostgreSQL:

sudo service postgresql stop
sudo service postgresql start

To check to see if logical replication has been enabled, run the following command from the PostgreSQL terminal:

SHOW wal_level;

It should return:

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

Reference Source Database’s Credentials

Before migrating an existing database, you need the database’s following credentials:

• Publicly accessible - Ensure that the database’s hostname or IP address is accessible from the public internet. If you’re migrating a cluster that resides at DigitalOcean, you can locate your database’s public connection information under its Connection Details in the control panel. If you’re database resides at another provider, reference their documentation for further information.
• 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 should have sufficient permissions to access the data you want to migrate.
• Password - The password used to connect to the database.

If you’re migrating a database that resides at DigitalOcean, you can locate your database’s public connection information and credentials under its Connection Details in the control panel. If your database resides at another provider, reference their documentation for further 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, add the trusted sources back to the database.

If your source database resides at DigitalOcean, repeat the process of removing all trusted sources from the source database cluster.

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

Migrate a PostgreSQL Database

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.

Once the migration begins, some features on both databases become unavailable. 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.