Section 21.2. Backup and Recovery

21.2. Backup and Recovery

PostgreSQL offers three methods for backup and recovery: pg_dump, pg_dumpall, and point-in-time recovery using the select pg_start_backup and select pg_stop_backup commands. These tools are designed for very different purposes, as the following table shows.

pg_dump	pg_dumpall	pg_start_backup,pg_stop_backup
Supports hot backups	Supports hot backups	Supports hot backups
Backs up a single database, table, or schema	Backs up all databases in a cluster	pg_start_backup prepares the entire cluster to be backed up. Actual backup contents are up to your script.
Backups usable by pg_restore	Backups not usable by pg_restore	Backups not usable by pg_restore; they are usable with the point-in-time recovery process
Backups can be in text, tar, or custom binary format	Backups are in text format	Backups are actual copies of the datafiles and are in whatever format you chose to back up with
Text backups usable by psql for restore	All backups usable by psql for restore	Not usable with psql
Cannot back up global system tables, including users and groups	Backs up global system tables with every backup. Can specify to back up just global tables with the g option.	Backs up the entire cluster
Can only recover database to when you last ran a pg_dump	Can only recover database to when you last ran a pg_dumpall	Can recover database up to the point of failure if you have all write ahead logs
WAL archiving need not be enabled	WAL archiving need not be enabled	WAL archiving must be enabled

The binary format that pg_dump can produce is similar to a dump database from Sybase and SQL Server. The text format produced by pg_dump or pg_dumpall is similar to what is produced by mysqldump from MySQL. pg_dump can dump a single database, where pg_dumpall can dump all databases including the system tables. Point-in-time recovery is most like Oracle's begin/end backup commands: the commands simply prepare the database to be backed up, rather than creating the backup.

The reason that you cannot perform a point-in-time recovery with pg_dump or psql is that these commands actually create a new write ahead log as part of the recovery process. Therefore, they cannot read a write ahead log from a previous iteration of the database.

All methods can be used to safely back up and recover PostgreSQL live. Adding or modifying records/tuples during a backup does not corrupt the backup. Changes to the physical structure of the database (such as adding columns, adding tables, and dropping tables) causes problems during a restore if they are allowed to run during a backup, so these activities are automatically blocked while a database backup is running.

21.2.1. Using pg_dump with pg_restore

pg_dump can be used with pg_restore or psql. This section covers how to use it with pg_restore.

21.2.1.1. Backing up with pg_dump

pg_dump supports two binary formats that are compatible with pg_restore, tar, and custom. The tar format does not allow reordering and/or exclusion of schema elements during restore. The custom archive format allows reordering and/or exclusion of the data load and schema elements, and this format is compressed by default. The default output format of pg_dump is text, which is not compatible with pg_restore; therefore, when running pg_dump, you must specify either the Ft option for the tar format or the Fc option for the custom format, if you wish to use the backup with pg_restore.

If you're running a version prior to 8.1, you must specify the b option to pg_dump and dump in a text format in order to include large objects in the backup. Large objects are included by default in versions 8.1 and later.

Finally, you must specify the name of the database to back up as the last argument to pg_dump. Only pg_dumpall automatically backs up all databases within a cluster.

Putting this together, the following command performs a dump of the test database, using the custom archive format (-Fc), backing up BLOB data (-b), and compressing it as much as possible (-Z9):

$ pg_dump -Fc -Z9 -b test > db.dmp

21.2.1.2. Restoring with pg_restore

pg_restore is typically used to restore an entire PostgreSQL database in the case of failure, but there are options to restore only selected portions of a database. Let's cover the basic restore first. To restore a database using the dump created with the previous example, issue the following command. It tells pg_restore to restore the test database from the db.dmp dump file. Compression and backup format are automatically detected, and BLOB data is included if it was in the backup. This command assumes that the test database is already created and ready to be restored:

$ pg_restored test db.dmp

If the database that you wish to restore has not been created, you can ask pg_restore to create it for you:

$ pg_restoreC d test db.dmp

You also might want to restore just one table or trigger. In this case, you can specify that table or trigger via an option on the command line. This command restores just the special table of the test database:

$ pg_restore test t special db.dmp

This command restores just the specialtrigger trigger of the test database:

$ pg_restore test T specialtrigger db.dmp

If you want to restore several database objects, but not all of them, you can create a list of everything in the dump, edit the list, then tell pg_restore to restore just what's in the list. First, create a list of what's in the dump using the following command:

$ pg_restore -l db.dump > dump.list

This produces a file that looks something like this:

; ; Archive created at Sun Jul 02 22:28:36 2006 ;     dbname: test ;     TOC Entries: 50 ;     Compression: 9 ;     Dump Version: 1.4-0 ;     Format: CUSTOM ; ; ; Selected TOC Entries: ; 2; 145344 TABLE species postgres 3; 145344 ACL species 4; 145359 TABLE nt_header postgres 5; 145359 ACL nt_header 6; 145402 TABLE species_records postgres 7; 145402 ACL species_records 8; 145416 TABLE ss_old postgres 9; 145416 ACL ss_old 10; 145433 TABLE map_resolutions postgres 11; 145433 ACL map_resolutions 12; 145443 TABLE hs_old postgres 13; 145443 ACL hs_old

You can comment out lines by preceding them with a semicolon, then tell the pg_restore command to restore the tables that it finds in the dump listing. You can also reorder objects by changing the order in which they appear in the output.

$ pg_restore -L dump.list db.dump

21.2.2. Using pg_dump with psql

The default backup format for pg_dump is text. Therefore, if you issue a pg_dump command without specifying a backup format, you create a text file that contains the commands necessary to rebuild the database you backed up. You can also, of course, specify the text output format on the command line.

$ pg_dump -Fp -b test > db.sql

This command creates a text file containing all the commands necessary to rebuild the database. Because the b option is specified, the text also contains BLOB data converted to text. (This is not necessary in versions 8.1 and higher.) The text format does not support compression; however, you can compress the data yourself by running it through gzip or a similar command:

$ pg_dump -Fp -b test | gzip c > db.sql.gz

To restore from this backup, pass it to the psql command:

$ psql -d test -f db.sql

If you compressed it, you need to uncompress it before passing it to psql:

$ gzip dc db.sql.gz | psql -d test

Obviously, you can edit the SQL file, deleting any tables or other objects that you don't want to restore.

21.2.3. Using pg_dumpall with psql

pg_dump backs up all databases within a cluster, and the only backup format for pg_dumpall is also text. The most common use of pg_dumpall is to create a single database backup of every database in a cluster, using the following command:

$ pg_dumpall > all-databases.sql

This command creates a text file containing all the commands necessary to rebuild every database in the cluster, including large object data. The text format does not support compression but you can compress the data yourself by running it through gzip or a similar command:

$ pg_dumpall |gzip c > all-databases.sql.gz 

If you're using pg_dump or the point-in-time recovery method to back up your databases, you can use pg_dumpall to back up just the global objects, such as users and groups. This is done by specifying the g option to the pg_dumpall command:

$ pg_dumpall g > global-objects.sql

You can also compress this backup as well:

$ pg_dumpall g | gzip c > global-objects.sql.gz

This command creates a text file containing all the commands necessary to rebuild just the system tables for a cluster.

To restore from either of these backups, pass the backup file to the psql command:

$ psql -f all-databases.sql $ psql -f global-objects.sql

If you compressed these backups, you need to uncompress them before passing them to psql:

$ gzip dc all-databases.sql.gz | psql $ gzip dc global-objects.sql.gz |psql

Obviously, you can edit the SQL file, deleting any tables or other objects that you don't want to restore.