How to Backup a PostgreSQL Database

PostgreSQL provides the utility program pg_dump to generate a text file with SQL commands that, when fed back to the server, will recreate the database in the same state as it was at the time of the dump.

pg_dump dbname > outfile

pg_dump can be used from the Linux console.

For example, if you want to backup a database called ‘my_database’ you run, from the console:

pg_dump my_database > my_database.sql

When you need to restore a file created this way, you can use the following command:

psql my_database < my_database.sql

The database my_database will not be created by this command, so you must create it yourself from before executing psql.

Compressing Backups

To create a backup compressed by gzip, you can use a pipe | to send the pg_dump outfile to gzip:

pg_dump dbname | gzip > filename.gz

To restore a backup created this way, use this command:

gunzip -c filename.gz | psql dbname