psql vs pg_restore from backup

Dumps can be output in script or archive file formats.

Script dumps are plain-text files containing the SQL commands required to reconstruct the database to the state it was in at the time it was saved. To restore from such a script, feed it to psql. Script files can be used to reconstruct the database even on other machines and other architectures; with some modifications even on other SQL database products.

The alternative archive file formats must be used with pg_restore(1) to rebuild the database. They allow pg_restore to be selective about what is restored, or even to reorder the items prior to being restored. The archive file formats are designed to be portable across architectures.

pg_dump/pg_restore

  pg_dump -U username -f backup.dump database_name -Fc 

switch -F specify format of backup file:

• c will use custom PostgreSQL format which is compressed and results in smallest backup file size
• d for directory where each file is one table
• t for TAR archive (bigger than custom format)
• -h/--host Specifies the host name of the machine on which the server is running
• -W/--password Force pg_dump to prompt for a password before connecting to a database

restore backup:

psql -c "create database database_name;"   

pg_restore -d database_name -U username backup.dump

pg_dump/psql

In case that you didn’t specify the argument -F default plain text SQL format was used (or with -F p). Then you can’t use pg_restore. You can import data with psql.

backup:

pg_dump -U username -f backup.sql database_name

restore:

psql -d database_name -f backup.sql
psql -d database_name < backup.sql

Previously, attributes of the database itself, such as database-level GRANT/REVOKE permissions and ALTER DATABASE SET variable settings, were only dumped by pg_dumpall.

Now pg_dump –create and pg_restore –create will restore these database properties in addition to the objects within the database.

pg_dumpall -g now only dumps role- and tablespace-related attributes.

pg_dumpall‘s complete output (without -g) is unchanged.