Backup and Restore

As everything that contains valuable data, PostgreSQL DBs should be backed up regularly.

DB backups allow DBs to be restored if a disk drive fails, a table is accidentally dropped, or a DB file is accidentally deleted.

The idea behind the SQL-dump method is to generate a text file with SQL commands that, when fed back to the server, will recreate the DB in the same state as it was at the time of the dump.

Dump Individual DBs with pg_dump

PostgreSQL provides the utility program pg_dump for dumping individual DBs: pg_dump dbname > outfile pg_dump writes its results to the standard output.

pg_dump is a regular PostgreSQL client application.
This means that you can do this backup procedure from any remote host that has access to the DB.
pg_dump does not operate with special permissions. You must have read access to all tables that you want to back up.

Large objects (blobs) are not dumped by default,

Restoring the dump

The text files created by pg_dump are intended to be read in by the psql program: psql dbname < infile The DB dbname will not be created by this command, you must create it yourself before.
psql and pg_dump support options for controlling the DB server location and the user names.

Example:

Dump the workshop DB in the file "workshop.dump": $> pg_dump workshop >workshop.dump Look at the file "workshop.dump": $> more workshop.dump Create a new DB for restoring: $> createdb dump_test Restore the workshop DB into the dump_test DB: $> psql dump_test < workshop.dump Connect to the dump_test DB: $> psql dump_test

The ability of pg_dump and psql to write to or read from pipes makes it possible to dump a DB directly from one server to another, for example:

pg_dump -h host1 dbname | psql -h host2 dbname

Tricks for large DBs:

Compressed dumps:

pg_dump dbname | gzip > filename.gz Reload with createdb dbname gunzip -c filename.gz | psql dbname

split allows you to split the output into pieces that are acceptable in size to the underlying file system. For example, to make chunks of 1 megabyte:

pg_dump dbname | split -b 1m - filename Reload with createdb dbname cat filename* | psql dbname

Dumping a Complete Cluster with pg_dumpall

pg_dumpall is a utility for dumping all PostgreSQL DBs of a cluster into one file. It also dumps global objects that are common to all DBs: pg_dumpall > outfile The resulting dumps can be restored with psql.

Example "dump and reload all DBs":

$ pg_dumpall >cluster.dump Reload the DBs (into an empty cluster space as the server will not accept duplicate DB name) with: $ psql -f cluster.dump template1

File system level backup

An alternative backup strategy is to directly copy the files that PostgreSQL uses to store the data in the database cluster with whatever method you prefer for doing file system backups, for example: tar -cf backup.tar /home/fred/databases/postgresql/data The database server must be shut down before.

It will not work to restore only certain individual tables or databases from their respective files or directories, because the information contained in these files must be combined with the commit log files pg_clog/*, which contain the commit status of all transactions.

The file system backup will likely be larger than an SQL dump, because a pg_dump does not need to dump the contents of indexes for example, just the commands to recreate them.