Backup & Restore

Backup

Use pg_dump and pg_dumpall if possible in CLI to back up a database.

pg_dump --format=plain --file=output.sql my_database

Note that pg_restore can't restore plain formats, in this case use --format=custom

If admin privileges are needed:

sudo -u postgres pg_dump --format=plain --file=output.sql my_database

Dumping globals and/or clusters:

pg_dumpall --globals-only

Always test the backup file by restoring it to a separate database.

Use pg_restore to restore a backup file. Note pg_restore can only restore custom format backup files, not plain ones.

pg_restore --format=custom --dbname=my_database output.sql

If admin privileges are needed:

sudo -u postgres pg_restore --format=custom --dbname=my_database output.sql

If admin privileges are needed but sudo is not available:

pg_restore -U postgres --format=custom --dbname=my_database output.sql

If importing into a database owned by another user, don't emit owner statements:

pg_restore --format=custom --dbname=my_database --no-owner output.sql

Set it to a different owner than what was dumped:

pg_restore --format=custom --dbname=my_database --no-owner --role=my_owner output.sql

Use psql to restore plain SQL backup files:

psql my_database < output.sql