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