To import a PostgreSQL database you will need:
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 -Fc 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 -Fc use_your_dbname > dbdump.pgsql
See the PostgreSQL SQL Dump documentation for more detail about
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.
To import the database, you’ll first need to connect with psql.
You can use the public network connection string supplied on the cluster’s overview page with pg_restore exactly as it is to connect to the default database as the default user. 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.
To import with a different user or into a different database, you’ll need to edit the public 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:
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.
--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
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.
See the wiki PostgreSQL wiki article Introduction to VACUUM, ANALYZE, EXPLAIN, and COUNT for more about the