How to Import PostgreSQL Databases into DigitalOcean Managed Databases with pg_dump

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.

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. A 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 > 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 database, you need to connect with psql and use the connection URI for the database you want to add the existing data to.

If you want to use the default database and default user, you can use the public network connection string from the cluster’s Overview page. If you want to import with a different user or to a different database, you need to edit the connection URI.

The connection URI is in the following format:


Once you have the connection URI for the database and user you want to use, the command to import the dump file looks like this:

pg_restore -d 'your_connection_URI' --jobs 4 use_your_dump_file.pgsql

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.

If the database you’re importing used 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.

Learn more in PostgreSQL’s documentation, Restoring the Dump.

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.