How to Import PostgreSQL Databases into DigitalOcean Managed Databases with pg_dump

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 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 importing a dump. For instructions on using continuous migration, see our how-to on using continuous migration with PostgreSQL databases.

Prerequisites

If you have a PostgreSQL database that you want to import into DigitalOcean Managed Databases, you need the following:

1. An export of the existing database, which you can get using pg_dump or other utilities.

2. A PostgreSQL database cluster created in your DigitalOcean account.

3. An existing database in the database cluster to import your data into. You can use the default database or create a new database.

Export an Existing Database

One method of exporting data from an existing PostgreSQL database is using pg_dump, a PostgreSQL database backup utility. pg_dumpall is a similar utility meant for PostgreSQL database clusters.

To use pg_dump, you need specify the connection details (like admin username and database) and redirect the output of the command to save the database dump. The command will look like this:

pg_dump -h <your_host> -U <your_username> -p 25060 -Fc <your_database> > <path/to/your_dump_file.pgsql>

The components of the command are:

• The -h flag to specify the IP address or hostname, if using a remote database.

• The -U flag to specify the admin user on the existing database.

• The -p flag to specify a connecting port. Our managed databases require connecting to port 25060.

• The -Fc flags to create the dump file in the custom format, compatible with pg_restore.

• The name of the database to dump.

• The redirection to save the database dump to a file called your_dump_file.pgsql.

Learn more in PostgreSQL’s SQL Dump documentation.

The time to export increases with the size of the database, so a large database will take some time. When the export is complete, you’ll be returned to the command prompt or notified by the client you used.

Import a Database

To import the new source database, ensure that you can connect to your target database with psql. Then, you need to find the connection URI for the target database you want to add the existing data into.

If you want to import to the default target database with the default user, you can use the public network connection string from the cluster’s Overview page, under Connection Details and in the drop-down menu.

If you want to import to a different target database or with a different user, select your desired specifications using the User and Database/Pool drop-down menus below.

Click the blue, highlighted show-password string to reveal your password, then copy the URI.

Once you have the connection URI for the source database and user you want to use, note whether your source database is in custom format or is a text format dump, and then follow the applicable steps below. We recommend exporting dumps in custom format for its compression and ability to restore tables selectively.

Import Data in Custom Format

To import a source database in custom format, use the pg_restore command:

pg_restore -d <your_connection_URI> --jobs 4 <path/to/your_dump_file.pgsql>

The components of the command are:

• The -d flag to specify the database name.
• Your connection URI.
• The --jobs flag to specify the number of concurrent threads to run the import. A higher number accelerates the process, but requires more CPUs.
• The number of threads to run.
• The path to your local source database file.

If the database you’re importing has multiple users, you can add the --no-owner flag to avoid permissions errors. Even without this command, the import will complete, but you may see a number of error messages.

Reference PostgreSQL’s documentation for more information about its Backup and Restore functions.

Import a Text Format Dump

To import a regular text format dump, use the following command:

psql -d <your_connection_URI> < <path/to/your_dump_file.pgsql>

The components of the command are:

• The -d flag to specify the database name.
• Your connection URI.
• The less-than symbol (<) to input the following file to your target database.
• The path to your local source database file.

Reference PostgreSQL’s documentation for more information about its Backup and Restore functions.

After Importing

Once the import is complete, you can update the connection information in any applications using the database to use the new database cluster.

We also recommend running the PostgreSQL-specific ANALYZE command to generate statistical database information. This helps the query planner optimize the execution plan, which increases the speed that the database executes SQL queries. Learn more in the PostgreSQL wiki introduction to VACUUM, ANALYZE, EXPLAIN, and COUNT.