PostgreSQL: pg_dump and pg_restore

Export

pg_dump is a utility for creating consistent database dumps. The format for pg_dump is:

pg_dump -h localhost -U dbuser -W stagingdb -F t > ~/db_dumps/staging_dump.tar

Where the arguments:

• h : specifies the host
• U: specifies the user
• W: forces to input the password
• stagingdb is the database name
• F: selects the format of the dump. It can be either: p — plain sql, c — outputs into custom format(zipped by default) d- dumps into directory format(not zipped) t — export will be in tar format.

Restore

If the exported format is plain SQL, then it can be restored using psql command.

psql -h localhost -U dbuser -d stagingdb -f staging_dumb.sql
Or, you can restore the tar dump using pg_restore

Or, you can restore the tar dump using pg_restore

pg_restore -hlocalhost -U db_user -d dump staging_dump.tar