How to Migrate PostgreSQL Databases

Validated on 25 Jan 2023 • Last edited on 31 Jan 2024 pitr | port

PostgreSQL is an open source, object-relational database built for 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 managed database clusters on DigitalOcean to other managed database clusters on DigitalOcean using continuous migration. For example, you cannot migrate a managed database cluster from one DigitalOcean account to another. However, you can migrate with a dump.

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.

How to Verify Whether You Have superuser Permissions

To verify whether you have superuser permissions, use the \du command from the PostgreSQL (psql) terminal:

\du

The command line returns a table of the database’s roles (usernames), their respective attributes (permissions), and the groups they belong to:

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

If your role name does not have the Superuser attribute, contact your system admin and request that they grant it to you.

Warning
Using pg_dump on a connection pool causes errors when pool_mode is set to transaction. We recommend you use pg_dump directly against your database cluster, bypassing its connection pool(s).

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.

Warning
Before migrating a database, ensure that the source’s PostgreSQL version is not newer than the target cluster’s. This can result in an error that causes migration to fail.

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
Tip
Migration via the control panel requires that you grant replication permissions to all databases. However, the DigitalOcean API supports the parameter ignore_dbs, which allows you to only migrate certain databases instead of all of them. In this case, only databases selected with the parameter need replication permissions.

Migrate a Database Using the API

How to Migrate a Database Using the DigitalOcean API

  1. Create a personal access token and save it for use with the API.

  2. Send a PUT request to https://api.digitalocean.com/v2/databases/{database_cluster_uuid}/online-migration

    cURL

    Using cURL:

                    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"

    Python

                    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)

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.

Action menu with Set Up Migration highlighted

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.

PostgreSQL migration with credentials

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

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

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.
Control Panel Blog Pricing Careers Terms Privacy Status API Docs Tutorials Support