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
-F specify format of backup file:
cwill use custom PostgreSQL format which is compressed and results in smallest backup file size
dfor directory where each file is one table
tfor TAR archive (bigger than custom format)
--hostSpecifies the host name of the machine on which the server is running
pg_dumpto prompt for a password before connecting to a database
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
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.