SQL: pg_dump Command Builder
The pg_dump command associated with PostgreSQL is used to 'extract a PostgreSQL database into a script file or other archive file'. The form below will generate a command based on the options selected in the table.
Generating a pg_dump command
Enter the name of the database you want to dump and then complete or select the relevant options. The command you want will appear in the box below.
pg_dump command
The command to use will appear here.
There are a number of additional options that aren't included here. For information on them you can visit the link under References below. This form is powered by JavaScript so you can download run it locally as well as on The Art of Web.
Restoring the database from a dump file
This really is quite simple. For example, if the name of the dump file is db.dump and the database dbname the following command will execute that file in that database:
psql -f db.dump dbname
Note: Don't forget to include any username and password options as required to connect to the database.
For experienced command-line users who want to 'cut out the middle man' when copying schemas or data between databases the following might also be useful:
pg_dump <options> db1 | psql -f /dev/stdin db2
This causes the output of the pg_dump command on db1 to be executed immediately in db2. Again, this should only be used if you know what you're doing (and have a backup in case something goes wrong).
When using pg_dump for backup/archive purposes I find the following command the most useful:
pg_dump -oOxc <dbname> | gzip > <dbname>.dump.gz
This creates a gzip file containing all tables and text data (including OIDs).
