How to import and export a PostgreSQL database

We always recommend to dump and import through the command line, if possible. This documentation will explain how to do this securely.

PostgreSQL export

For most databases (only a couple of gigabytes big), you can dump the database with this command:

pg_dump -U DBUsername DatabaseName > DBNAME.sql

For larger databases (tens of gigabytes or bigger), compress the database to conserve disk space and speed up transfer:

pg_dump -U DBUsername DatabaseName | gzip -3 -v > test.gz

pg_dump -U DBUsername -Fc DatabaseName > DBNAME.dump

Transferring the database

Use scp to transfer the file:

scp FileName user@HostnameOrIP:Path/To/Folder

Importing the database

psql -U DBUsername DatabaseName < dbname

Or, if compressed:

gunzip -c dbname.gz | psql -U DBUsername DatabaseName

pg_restore -U DBUsername -d DatabaseName DBNAME.dump

Extra tips

Nohup

Use nohup to ensure the dump or import continues if the connection is lost.

Screen

Use screen to allow session sharing or recovery:

• Create a session:

  screen -S <session_name>

• Disconnect (keep running):

  Ctrl + A, then D

• Reattach or take over session:

  screen -dr <session_name>

• List sessions:

  screen -ls

SSH key

Avoid password prompts by setting up SSH key authentication.

Related

• Configure PostgreSQL
• Create and manage PostgreSQL users
• Backups and restore
• SSH access
• Database problems