How to Import PostgreSQL Databases with pg_dump

To import a PostgreSQL database you will need:

  1. A DigitalOcean PostgreSQL Database Cluster
  2. A database on that cluster. You can use the default or create one specifically for the import
  3. An export of the existing database

Export an Existing Database

If you have command-line access to your current database server, you can use the pg_dump command directly, supplying the administrative username and password. The -P flag will prompt you for the user’s password:

pg_dump -U use_your_admin_username use_your_dbname -P > dbdump.pgsql

If you’re using a remote database, you need to specify the IP address or domain name:

pg_dump -h use_your_host -U use_your_username use_your_dbname > dbdump.pgsql

See the PostgreSQL SQL Dump documentation for more detail about pg_dump.

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 database, you’ll first need to connect with psql.

Using the Default Database and User

You can use this connection string with pg_restore exactly as it is. Substitute it in the command below and be sure to specify the name of your dump file as well:

pg_restore -d 'use_your_connnectionURI' --jobs 4 use_your_dump_file

See the PostgreSQL documentation for more detail.

Using a Database or User You Created

To import with a different user or into a different database, you’ll need to edit the connection string.

Visit the cluster’s Users and Databases page to retrieve the password for users other than doadmin. Substitute the username, password, and database name as appropriate. Their location in the connection string is highlighted in ALL CAPS below. Substitute the parts to match the target database or user as needed. The rest of the string should stay the same:

postgres://USERNAME:PASSWORD@DATABASENAME-do-user-1234567-0.db.ondigitalocean.com:25060/defaultdb?sslmode=require

When you’ve edited the connection string, use the pg_restore command to begin the import. Be sure to specify the name of your dump file as well.

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

The --jobs 4 flag tells PostgreSQL to use 4 CPU cores for this import so it takes less time. If you have a different number of available CPU cores, you can adjust that number to use more or fewer cores.

pg_restore -d 'use_your_edited_connnection_URI' --jobs 4 use_your_dump_file

Final Steps

Once the import is complete, you can change your application’s connection information the connect to new database, updating the username and password as needed.

Finally, we recommend running the PostgreSQL-specific ANALYZE command with the psql command-line tool to generate statistical database information so the query planner can optimize the execution plan.

newdb=> ANALYZE;

See the wiki PostgreSQL wiki article Introduction to VACUUM, ANALYZE, EXPLAIN, and COUNT for more about the ANALYZE command.