Backing Up and Restoring Data

Using pg_dump

The pg_dump (which is short for "PostgreSQL dump") application is run from a command line, and creates a list of SQL commands. These commands, executed in the order provided, re-create the database from scratch.

Here is the syntax for pg_dump:

  pg_dump [ options ] dbname

In this syntax, dbname is the name of the database that you want to "dump" SQL instructions for. The available options are similar in format to those of the other database management utilities included with PostgreSQL, such as createdb. The most common flag specified in the options to pg_dump is the -f flag, which specifies the file to store the dumped SQL statements within.

The complete list of pg_dump options follow:

Any system user may run pg_dump by default, but the user with which you connect to PostgreSQL must have SELECT rights for every object in the database being dumped. Example 9-20 demonstrates the use of pg_dump on the booktown database, with the manager PostgreSQL user. The -C flag passed causes the CREATE DATABASE command to be included in the SQL dump as well. This command is not always included, as you may prefer to create the database beforehand with non-standard options.

[jworsley@booktown ~]$ pg_dump -u -C -f booktown.sql booktown
Username: manager
Password:

[jworsley@booktown ~]$ ls -l booktown.sql
-rw-rw-r- -    1 jworsley jworsley    46542 Sep 13 16:42 booktown.sql

Note that since pg_dump provides the standard connection options for specifying a host connection (e.g., the -h, -u and -p flags), it can be used to perform remote backups from any host allowed to make a remote connection (according to the pg_hba.conf file: see Chapter 8 for more on this subject). Example 9-21 shows a user on a remote server specifying a connection to the booktown.commandprompt.com server, in order to create a backup file in compressed format (with the -F c flag) called booktown.sql.tar.gz.

[jworsley@cmd ~]$ pg_dump -u -h booktown.commandprompt.com \
>                         -F c -f booktown.sql.tar.gz booktown
Username: manager
Password:

[jworsley@cmd ~]$ ls -l booktown.sql.tar.gz
-rw-rw-r- -    1 jworsley jworsley   45909 Sep 13 17:12 booktown.sql.tar.gz

If you wish to use large objects in your dumped file, it is necessary that you use either the tar (t) or gzip-compressed (c) format, since the plain-text format cannot include large objects. Otherwise, normal plain-text formatted dumps are suitable for most users.

Creating a dump in tar format can often result in a backup file more than twice the size of its plain-text counterpart, even without large objects. This is because the tar format involves a hierarchy of TOC (table of contents) .dat files which represent information on how to be used by the corresponding pg_restore command; thus, more disk space is taken up by the extra instructions. Since tar is not a compression utility, the gzip-compressed (c) format exists as well to compress the tarred file into gzipped format automatically.

Using pg_dumpall

PostgreSQL supplies a supplementary wrapper command to the pg_dump application called pg_dumpall. The primary use of this application is to allow the entire cluster of PostgreSQL databases on a system to be dumped at once, rather than having to perform a pg_dump for each database, one at a time.

Here is the syntax for pg_dumpall, displayed from the - -help flag:

  pg_dumpall [ -c ] [ -h HOSTNAME ] [ -p PORT ] [ -g ]

The pg_dumpall command accepts the same connection parameters available to pg_dump. The following are the pg_dumpall-specific parameters:

Note that as of PostgreSQL 7.1.x, the use of the pg_dumpall script does have some practical limitations over the normal use of pg_dump. For example, the -u flag cannot be used to provide a different username and password, and the -F flag may not be used to specify a format other than plain text (your backups will be stored as plain text, regardless of chosen format). This means that the -b flag cannot be used with pg_dumpall either, as it requires a format other than plain-text.

While the -f flag can be used to pass a filename through to pg_dump, doing so does not create a complete dump, as pg_dumpall's global data is still sent to stdout. To solve the problem of not having the -f flag available to pg_dumpall, the shell re-direction operator (>) can be used to redirect the output of pg_dumpall to a file.

A simple workaround to the lack of the -u flag is to set the PGUSER environment variable. You can also set the PGPASSWORD environment variable in this fashion if you are connecting to a system which requires password authentication, and you do not wish to provide a password for each database that it connects to and dumps.

Example 9-22 demonstrates a simple bash-shell shorthand for a temporary environment variable (PGUSER) when invoking any PostgreSQL client. While not usually necessary, it can be a handy trick to know for exceptions such as the pg_dumpall script. Example 9-22 uses this technique to create one dump file for all databases.

[jworsley@booktown ~]$ PGUSER=postgres pg_dumpall > all.sql

The first part of the shell statement demonstrated in Example 9-22 sets a temporary environment variable named PGUSER to the value of postgres. This variable is set for the duration of the pg_dumpall command, and expires when the command has finished.

You can also run the pg_dumpall command remotely, though be sure to set any environment variables as needed. These will depend greatly on the remote host configuration in the pg_hba.conf file.

You should use the pg_dumpall command if it is especially inconvenient to backup all your existing databases individually, or if you have any kind of complex user and group system in place. If you are inhibited by the limitations of pg_dumpall for data output (particularly if your database makes use of large objects), the simplest solution is to use pg_dumpall with the -g flag to keep a backup of all user and group data, and to subsequently use pg_dump for each database which needs to be backed up, individually.

Restoring a Database

Data from a SQL dump can be restored to a database in one of two ways. If the dump created by pg_dump as a simple, plain-text file, it may be passed through to psql directly as an input file. Alternatively, if another output format was chosen (e.g., the tar or compressed tar format), the pg_restore command must be used.

A database may either be restored from scratch, to an empty database, or to a non-existent database; how you go about restoring a database depends largely on how it was dumped (e.g., if only data were dumped, or if the commands to create the database were included).

[jworsley@booktown ~]$ psql -U manager -f booktown.sql template1
CREATE DATABASE
You are now connected to database booktown as user postgres.
COMMENT
CREATE
CREATE
CHANGE

[...]

  pg_restore [ options ] [ file ]

[jworsley@cmd ~]$ pg_restore -v -C -O -d template1 booktown.sql.tar
Connecting to database for restore
Creating DATABASE booktown
Connecting to new DB 'booktown' as postgres
Connecting to booktown as postgres
Creating COMMENT DATABASE "booktown"
Creating TABLE inventory

[...]

When to Backup and Restore Data

An important consideration to the use of the pg_dump, pg_dumpall, and pg_restore commands is when to use them, and when not to. Fortunately, in respect to each of these procedures, PostgreSQL is quite accommodating.

Backing Up the Filesystem

While PostgreSQL abstracts the literal data files from its users, all of the data within PostgreSQL databases can be found in normal system files. These files are constantly in a state of flux when the database is running, as not all changes made in PostgreSQL are immediately written to disk. These files are stored within the PostgreSQL directory (e.g., /usr/local/pgsql/data, or whatever path is specified by the PGDATA environment variable).

Rather than creating a set of SQL instructions to re-create a database, you may instead opt to stop the PostgreSQL server (in order to ensure that all changes are synchronized with the hard disk) and create a backup of that area of the filesystem. Typically this is done with the tar utility, and optionally compressed with a compression utility such as gzip.

Example 9-25 demonstrates the backing up of a filesystem in which PostgreSQL keeps its data, as performed by the user who owns the data files (which is the same user that runs PostgreSQL's backend). In this case, the path is /usr/local/pgsql/data, and the system user is postgres.

[postgres@booktown ~]$ cd /usr/local/pgsql
[postgres@booktown pgsql]$ pg_ctl stop
Smart Shutdown request at Fri Sep 14 14:54:15 2001
DEBUG:  shutting down
waiting for postmaster to shut down......DEBUG:  database system is shut down
done
postmaster successfully shut down
[postgres@booktown pgsql]$ tar czf pgsql.bak.tar.gz data/
[postgres@booktown pgsql]$ ls -l *.tar.gz
-rw-rw-r- -    1 postgres postgres 66124795 Sep 14 14:36 pgsql.bak.tar.gz

Notice that the pg_ctl command is called before the tar process, to stop the PostgreSQL backend (if installed, the SysV script may be invoked with the service command to the same end). As stated, this is to ensure that the most recent changes to the database have been synchronized with the hard disk, as well as to verify that none of the data files are modified while backed up.

The primary advantage to backing up your data in this fashion is that you have a literal backup of PostgreSQL's data files. In order to restore a crashed database from this kind of file, it needs to be decompressed in the appropriate location, and for the backend to be re-started. There is no need for negotiation of options, ownership, or potential conflicts between the pg_dump output and restoring it to a live PostgreSQL server through sequential SQL statements.

However, while this method is easier to implement, it presents several limitations. First, the database must be shut down completely to backup or restore the data, eliminating the primary advantage of a hot backup-capable DBMS, which is limited downtime. Further, it is not possible to backup only specific databases, or tables. The entire data directory must be backed up for a complete restoration of the filesystem. This is because there are many files associated with a particular database, and it is not obvious which files correlate to which databases.

Finally, because more than abstract information is represented on disk by a live database, a much greater amount of disk space is required to backup even a compressed copy of the entire data directory.