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


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.


pg_dump -U username -f backup.sql database_name


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.

Leave a Reply

Your email address will not be published.