Christopher B. Browne's Home Page
cbbrowne@acm.org

2. Backing up PostgreSQL Databases

The primary program used to backup PostgreSQL databases is pg_dump. It extracts databases into either script files or archive files. The script files are in a plain text format, containing the SQL commands needed to reconstruct the database in the state it was as at the instant it was saved. That last point is important; the use of MVCC (Multi Version Concurrency Control) means that the backup is invoked as a single transaction that "sees" the state of the database as all of the transactions that have been COMMITted as at the moment that the backup begins. As a result, this provides the capability to easily do " hot" backups, that is, backups that are performed while the system is still operational. Doing backups this way does not block other processes using the database, and other processes do not block the backup.

These backups can be readily manipulated as text using all the sorts of scripting languages associated with Unix .

Making this even more useful, there are, in addition, options to specify that a specific table be dumped (-t), and to dump just the database schema (-s), omitting the data.

It also supports binary " archive" formats that are based on tar and gzip that add the capability to select via " random access" which objects are to be restored, using the pg_restore command.

In my own experience, the script-based formats have been entirely satisfactory for my purposes, and generally preferable to the " archive" formats. Having the data in text form is vastly more useful in that I can use the whole range of " Unix text tools" to manipulate the data.

There is a third method of backing things up, namely to take a copy of the files and/or filesystem(s) on which the database resides. That would potentially provide faster recovery than any of the pg_dump-based methods, as it would simply involve copying files around, whereas pg_restore or psql involve recreating tables and indices from scratch, which may involve significant work. But there are three caveats:

Google
Contact me at cbbrowne@acm.org