PostgreSQL: Dump(Backup) and Restore a Database

On August 21, 2012, in DB, PostgreSql, by Dipin Krishna

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).

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.

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! 🙂

Tagged with:  

Leave a Reply