PostgreSQL: Dump(Backup) and Restore a Database

Backup
pg_dump is the utility i am using for backing up a PostgreSQL database. It makes consistent backups even if the database is being used concurrently. pg_dump does not block other users accessing the database (readers or writers).

Restore
pg_restore is the utility i am using for restoring a PostgreSQL database from an archive created by pg_dump in one of the non-plain-text formats. It will issue the commands necessary to reconstruct the database to the state it was in at the time it was saved. The archive files also allow pg_restore to be selective about what is restored, or even to reorder the items prior to being restored. The archive files are designed to be portable across architectures.

Examples
To dump a database:

$ pg_dump -U username dbname > db.sql

To restore this database:

$ psql -U username -d dbname -f db.sql

To dump a database to a tar file:

$ pg_dump -U username -Ft dbname > dump.tar

To reload this dump into a database:

$ pg_restore -U username -d dbname dump.tar

To get a custom dump file use

pg_dump -U username -Fc dbname > db.dump

While running any of these command, if you get the error
FATAL: Ident authentication failed for user ‘username’“,
Then add the host parameter ‘-h localhost’ :

$ pg_dump -h localhost -U username -Ft dbname > dump.tar
$ pg_restore -h localhost -U username -d dbname dump.tar

There are more options available, see pg_dump and pg_restore.

Hope, it helps! 🙂

Leave a comment

Your email address will not be published. Required fields are marked *

*

This site uses Akismet to reduce spam. Learn how your comment data is processed.