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