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