Backup in PostgreSQL 9

Simon Riggs

October 2010


PostgreSQL 9 Admin Cookbook

PostgreSQL 9 Admin Cookbook

Over 80 recipes to help you run an efficient PostgreSQL 9.0 database

  • Administer and maintain a healthy database
  • Monitor your database ensuring that it performs as quickly as possible
  • Tips for backup and recovery of your database
        Read more about this book      

(For more resources on PostgreSQL, see here.)


Most people admit that backups are essential, though they also devote only a very small amount of time to thinking about the topic.

The first recipe is about understanding and controlling crash recovery. We need to understand what happens if the database server crashes, so we can understand when we might need to recover.

The next recipe is all about planning. That's really the best place to start before you go charging ahead to do backups.

Understanding and controlling crash recovery

Crash recovery is the PostgreSQL subsystem that saves us if the server should crash, or fail as a part of a system crash.

It's good to understand a little about it, and to do what we can to control it in our favor.

How to do it...

If PostgreSQL crashes there will be a message in the server log with severity-level PANIC. PostgreSQL will immediately restart and attempt to recover using the transaction log or Write Ahead Log (WAL).

The WAL consists of a series of files written to the pg_xlog subdirectory of the PostgreSQL data directory. Each change made to the database is recorded first in WAL, hence the name "write-ahead" log. When a transaction commits, the default and safe behavior is to force the WAL records to disk. If PostgreSQL should crash, the WAL will be replayed, which returns the database to the point of the last committed transaction, and thus ensures the durability of any database changes.

Note that the database changes themselves aren't written to disk at transaction commit. Those changes are written to disk sometime later by the "background writer" on a well-tuned server.

Crash recovery replays the WAL, though from what point does it start to recover? Recovery starts from points in the WAL known as "checkpoints". The duration of crash recovery depends upon the number of changes in the transaction log since the last checkpoint. A checkpoint is a known safe starting point for recovery, since at that time we write all currently outstanding database changes to disk. A checkpoint can become a performance bottleneck on busy database servers because of the number of writes required. There are a number of ways of tuning that, though please also understand the effect on crash recovery that those tuning options may cause. Two parameters control the amount of WAL that can be written before the next checkpoint. The first is checkpoint_segments, which controls the number of 16 MB files that will be written before a checkpoint is triggered. The second is time-based, known as checkpoint_timeout, and is the number of seconds until the next checkpoint. A checkpoint is called whenever either of those two limits is reached.

It's tempting to banish checkpoints as much as possible by setting the following parameters:

checkpoint_segments = 1000
checkpoint_timeout = 3600

Though if you do you might give some thought to how long the recovery will be if you do and whether you want that.

Also, you should make sure that the pg_xlog directory is mounted on disks with enough disk space for at least 3 x 16 MB x checkpoint_segments. Put another way, you need at least 32 GB of disk space for checkpoint_segments = 1000. If wal_keep_segments > 0 then the server can also use up to 16MB x (wal_keep_segments + checkpoint_segments).

How it works...

Recovery continues until the end of the transaction log. We are writing this continually, so there is no defined end point; it is literally the last correct record. Each WAL record is individually CRC checked, so we know whether a record is complete and valid before trying to process it. Each record contains a pointer to the previous record, so we can tell that the record forms a valid link in the chain of actions recorded in WAL. As a result of that, recovery always ends with some kind of error reading the next WAL record. That is normal.

Recovery performance can be very fast, though it does depend upon the actions being recovered. The best way to test recovery performance is to setup a standby replication server.

There's more...

It's possible for a problem to be caused replaying the transaction log, and for the database server to fail to start.

Some people's response to this is to use a utility named pg_resetxlog, which removes the current transaction log files and tidies up after that surgery has taken place.

pg_resetxlog destroys data changes and that means data loss. If you do decide to run that utility, make sure you take a backup of the pg_xlog directory first. My advice is to seek immediate assistance rather than do this. You don't know for certain that doing this will fix a problem, though once you've done it, you will have difficulty going backwards.

Planning backups

This section is all about thinking ahead and planning. If you're reading this section before you take a backup, well done.

The key thing to understand is that you should plan your recovery, not your backup. The type of backup you take influences the type of recovery that is possible, so you must give some thought to what you are trying to achieve beforehand.

If you want to plan your recovery, then you need to consider the different types of failures that can occur. What type of recovery do you wish to perform?

You need to consider the following main aspects:

  • Full/Partial database?
  • Everything or just object definitions only?
  • Point In Time Recovery
  • Restore performance

We need to look at the characteristics of the utilities to understand what our backup and recovery options are. It's often beneficial to have multiple types of backup to cover the different types of failure possible.

Your main backup options are

  • logical backup—using pg_dump
  • physical backup—file system backup

pg_dump comes in two main flavors: pg_dump and pg_dumpall. pg_dump has a -F option to produce backups in various file formats. The file format is very important when it comes to restoring from backup, so you need to pay close attention to that.

The following table shows the features available, depending upon the backup technique selected.

Table of Backup/Recovery options:


SQL dump to an archive file pg_dump -F c SQL dump to a script file pg_dump -F p or pg_dumpall Filesystem backup using pg_start_ backup
Backup type Logical Logical Physical
Recover to point in time? No No Yes
Backup all databases? One at a time Yes (pg_dumpall) Yes
All databases backed up at same time? No No Yes
Selective backup? Yes Yes No (Note 3)
Incremental backup? No No Possible (Note 4)
Selective restore? Yes Possible (Note 1) No (Note 5)
DROP TABLE recovery Yes


Possible (Note 6)

DROP TABLESPACE recovery Possible (Note 2) Possible (Note 6) Possible (Note 6)
Compressed backup files? Yes Yes Yes
Backup is multiple files? No No Yes
Parallel backup possible? No No Yes
Parallel restore possible? Yes No Yes
Restore to later release? Yes Yes No
Standalone backup? Yes Yes Yes (Note 7)
Allows DDL during backup No No Yes


How to do it...

  1. If you've generated a script with pg_dump or pg_dumpall and need to restore just a single object, then you're going to need to go deep. You will need to write a Perl script (or similar) to read the file and extract out the parts you want. It's messy and time-consuming, but probably faster than restoring the whole thing to a second server, and then extracting just the parts you need with another pg_dump.
  2. See recipe Recovery of a dropped/damaged tablespace.
  3. Selective backup with physical backup is possible, though will cause later problems when you try to restore.
  4. Selective restore with physical backup isn't possible with currently supplied utilities.
  5. See recipe for Standalone hot physical backup

How it works...

To backup all databases, you may be told you need to use the pg_dumpall utility. I have four reasons why you shouldn't do that, which are as follows:

  • If you use pg_dumpall, then the only output produced is into a script file. Script files can't use the parallel restore feature of pg_restore, so by taking your backup in this way you will be forcing the restore to be slower than it needs to be.
  • pg_dumpall produces dumps of each database, one after another. This means that:
    • pg_dumpall is slower than running multiple pg_dump tasks in parallel, one against each database.
    • The dumps of individual databases are not consistent to a particular point in time. If you start the dump at 04:00 and it ends at 07:00 then we're not sure exactly when the dump relates to—sometime between 0400 and 07:00.
  • Options for pg_dumpall are similar in many ways to pg_dump, though not all of them exist, so some things aren't possible. In summary, pg_dumpall is slower to backup, slow to restore, and gives you less control over the dump. I suggest you don't use it for those reasons. If you have multiple databases, then I suggest you take your backup by doing either.
  • Dump global information for the database server using pg_dumpall -g. Then dump all databases in parallel using a separate pg_dump for each database, taking care to check for errors if they occur. Use the physical database backup technique instead.

Hot logical backup of one database

Logical backup makes a copy of the data in the database by dumping out the contents of each table.

How to do it...

The command to do this is simple and as follows:

pg_dump -F c > dumpfile
pg_dump -F c –f dumpfile

You can also do this through pgAdmin3 as shown in the following screenshot:

Backup in PostgreSQL 9

How it works...

pg_dump produces a single output file. The output file can use the split(1) command to separate the file into multiple pieces, if required.

pg_dump into custom format is lightly compressed by default. Compression can be removed or made more aggressive.

pg_dump runs by executing SQL statements against the database to unload data. When PostgreSQL runs an SQL statement we take a "snapshot" of currently running transactions, which freezes our viewpoint of the database. We can't (yet) share that snapshot across multiple sessions, so we cannot run an exactly consistent pg_dump in parallel in one database, nor across many databases.

The time of the snapshot is the only time we can recover to—we can't recover to a time either before or after that time. Note that the snapshot time is the start of the backup, not the end.

When pg_dump runs, it holds the very lowest kind of lock on the tables being dumped. Those are designed to prevent DDL from running against the tables while the dump takes place. If a dump is run at the point that other DDL are already running, then the dump will sit and wait. If you want to limit the waiting time you can do that by setting the –-lock-wait-timeout option.

pg_dump allows you to make a selective backup of tables. The -t option also allows you to specify views and sequences. There's no way to dump other object types individually using pg_dump. You can use some supplied functions to extract individual snippets of information available at the following website:

pg_dump works against earlier releases of PostgreSQL, so it can be used to migrate data between releases.

pg_dump doesn't generally handle included modules very well. pg_dump isn't aware of additional tables that have been installed as part of an additional package, such as PostGIS or Slony, so it will dump those objects as well. That can cause difficulties if you then try to restore from the backup, as the additional tables may have been created as part of the software installation process in an empty server.

There's more...

What time was the pg_dump taken? The snapshot for a pg_dump is taken at the beginning of a run. The file modification time will tell you when the dump finished. The dump is consistent at the time of the snapshot, so you may want to know that time. If you are making a script dump, you can do a dump verbose as follows:

pg_dump -v

which then adds the time to the top of the script. Custom dumps store the start time as well and that can be accessed using the following:

pg_restore --schema-only -v dumpfile | head | grep Started
-- Started on 2010-06-03 09:05:46 BST

See also

Note that pg_dump does not dump the roles (such as users/groups) and tablespaces. Those two things are only dumped by pg_dumpall; see the next recipes for more detailed descriptions.

        Read more about this book      

(For more resources on PostgreSQL, see here.)

Hot logical backup of all databases

If you have more than one database in your PostgreSQL server, you may want to backup all databases together.

How to do it...

My recommendation is that you do exactly what you did for one database on each database in your cluster.

You can run those individual dumps in parallel if you want to speed things up.

Once complete, dump the global information also, using the following:

pg_dumpall -g

How it works...

To backup all databases, you may be told you need to use the pg_dumpall utility. I have four reasons why you shouldn't do that which are as follows:

  • If you use pg_dumpall, then the only output produced is into a script file. Script files can't use the parallel restore feature of pg_restore, so by taking your backup in this way, you will be forcing the restore to be slower than it needs to be.
  • pg_dumpall produces dumps of each database one after another. This means that
    • pg_dumpall is slower than running multiple pg_dump tasks in parallel, one against each database.
  • The dumps of individual databases are not consistent to a particular point in time. If you start the dump at 04:00 and it ends at 07:00 then we're not sure exactly when the dump relates to—sometime between 0400 and 07:00.
  • Options for pg_dumpall are similar in many ways to pg_dump, though not all of them exist, so some things aren't possible.

Hot logical backup of all tables in a tablespace

Sometimes we may wish to make a dump of tables and data in a tablespace. Unfortunately, there isn't a simple command to do this, so we need to write some reusable scripts.

How to do it...

It is possible for a tablespace to contain objects from more than one database, so run the following query to see which databases from which you need to dump:

SELECT datname
FROM pg_database
WHERE oid IN (
SELECT pg_tablespace_databases(ts.oid)
FROM pg_tablespace ts
WHERE spcname = 'mytablespacename');

The following procedure allows you to dump all tables that reside on one tablespace and within one database only.

Create a file named onets.sql that contains the following SQL, which extracts the list of tables in a tablespace:

SELECT 'pg_dump'
SELECT '-t ' || spcname || '.' || relname
FROM pg_class t JOIN pg_tablespace ts
ON reltablespace = ts.oid AND spcname = :TSNAME
JOIN pg_namespace n ON n.oid = t.relnamespace
WHERE relkind = 'r'
SELECT '-F c > dumpfile'; -- dumpfile is the name of the output file

Execute the query to build the pg_dump script:

psql -t -v TSNAME="'mytablespace'" -f onets.sql > get_my_ts

From the recovered database server, dump the tables in the tablespace, including data and definitions. The output file is named "dumpfile", from last line in the first step.

chmod 755 get_my_ts

How it works...

pg_dump allows you to specify more than one table on the command line, so it's possible to generate a list of tables directly from the database.

We use the named parameter in psql to create a parameterized script, which we then execute to create a dump.

Backup of database object definitions

Sometimes it's useful to get a dump of the object definitions that make up a database. This is useful for comparing what's in the database against the definitions in a data or object-modeling tool. It's also useful to make sure you can recreate objects in exactly the correct schema, tablespace, and database with the correct ownership and permissions.

How to do it...

The basic command to dump the definitions only is to use the following:

pg_dumpall --schema-only > myscriptdump.sql

Which includes all objects, including roles, tablespaces, databases, schemas, tables, indexes, triggers, constraints, views, functions, ownership, and privileges.

If you want to dump PostgreSQL role definitions, you can use the following:

pg_dumpall --roles-only > myroles.sql

If you want to dump PostgreSQL tablespace definitions, you can use the following:

pg_dumpall --tablespaces-only > mytablespaces.sql

Or if you want to dump both roles and tablespaces, then you can use the following:

pg_dumpall --globals-only > myglobals.sql

The output is a human-readable script file that can be re-executed to re-create each of the databases.

There's more...

In PostgreSQL, the word "schema" is also used to describe a set of related database objects similar to a directory, also known as a "namespace". Be careful that you don't confuse what is happening here. The ––schema-only option makes a backup of the "database schema" - the definitions of all objects in the database (and in all namespaces). To make a backup of the data and definitions in just one namespace, use the –s option. Or, to make a backup of only the definitions, in just one namespace, use both –s and ––schema-only together.

Standalone hot physical database backup

Hot physical backup is an important capability for databases.

Physical backup allows us to get a completely consistent view of all database changes at once. Physical backup also allows us to backup even while DDL changes are being executed on the database. Apart from resource constraints, there is no additional overhead or locking with this approach.

Physical backup procedures are slightly more complex than logical backup procedures. So, let's start with a simple procedure to produce a standalone backup.

How to do it...

The following steps assume that a number of environment variables have been set:

  1. $PGDATA is the path to the PostgreSQL data directory, ending with /
  2. $BACKUPNAME is the filename of a backup file
  3. All required PostgreSQL connection parameters have been set

    The initial procedure is steps 1 onwards. If running subsequent backups, start at step 6.

  4. Create a new archive directory, if not already present, as follows:
    cd $PGDATA
    mkdir -p ../ standalone
  5. Set an archive_command. In postgresql.conf you will need to add the following lines and restart the server, or just confirm they are present:
    archive_mode = on
    archive_command = 'test ! ../standalone/archiving_active ||
    cp -i %p ../standalone/archive/%f'
  6. Start archiving as follows:
    mkdir ../standalone/archive
    touch ../standalone/archiving_active
  7. Start the backup as follows:
    psql -c "select pg_start_backup('standalone')"
  8. Base backup—copy the data files (excluding the pg_xlog directory) as follows:
    tar -cv --exclude=$PGDATA/pg_xlog \
    -f ../standalone/$BACKUPNAME $PGDATA
  9. Stop the backup as follows:
    psql -c "select pg_stop_backup(), current_timestamp"
  10. Stop archiving as follows:
    rm ../standalone/archiving_active
  11. Move the files to the archive subdirectory, ready for recovery as follows:
    mv ../standalone/archive/ archive/
  12. Add the archived files to the standalone backup as follows:
    tar -rzf ../standalone/$BACKUPNAME archive/
  13. Write a recovery.conf with which to recover. Note that the archive directory mentioned here must match the location files to which are copied in step 8.
    echo "restore_command = 'cp archive/%f %p'" > recovery.conf
  14. Add the recovery.conf to the archive as follows:
    tar -rf ../standalone/$BACKUPNAME recovery.conf
    echo "recovery_end_command = 'rm -R archive' " >> recovery.conf
  15. Store $BACKUPNAME somewhere safe. A safe place is definitely not on the same server.

The procedure ends with a file named $BACKUPNAME in the standalone directory. So, you need to remember to copy it somewhere safe. The file contains everything that you need to recover, including a recovery parameter file.

How it works...

The backup produced by the preceding procedure allows you to restore only to a single point in time. That point is the time of the pg_stop_backup() function.

Physical backup takes a copy of all files in the database (step 8, the "base backup"). That alone is not sufficient as a backup, and you need the other steps as well. A simple copy of the database produces a time-inconsistent copy of the database files. To make the backup timeconsistent, we need to add to it all the changes that took place from the start to the end of the backup. That's why we have steps 7 and 9 to bracket our backup step.

The changes made are put into the standalone/archive directory as a set of archived transaction log/write-ahead log (WAL) files. Step 6 creates the archive directory. Steps 1, 2, 3, and 5 set the parameters that copy the files to the archive. Changing archive_mode requires us to restart the database server, so there is a trick to avoid that. These parameters are conditional upon the existence of a file named archiving_active, whose presence enables or disables the archiving process. Note this choice of parameters is configurable, so PostgreSQL doesn't always need to work this way.

Steps 6 and 10 enable and disable archiving, so that we only store copies of the WAL files created during the period of the backup. So, steps 1 to 5 are setup, and steps 6 to 10 are where the backup happens. Steps 11 onwards are gift wrapping, so that the backup script ends with everything in one neat file.

Step 11 moves the archived files under the data directory, a more convenient location from which to restore. Step 12 appends the WAL files to the backup file, so it is just one file.

Steps 3-14 add a recovery.conf file with its parameters setup so that there are no manual steps when we recover from this backup.

The key to understanding this is that we need both the base backup and the appropriate archived WAL files to allow us to recover. Without both of those things, we have nothing. Most of the steps are designed to ensure that we really will have the appropriate WAL files in all cases.

See also

It's common to use continuous archiving when using the physical backup technique, because that allows you to recover to any point in time, should you need that.

Hot physical backup & Continuous Archiving

This recipe describes how to set up a hot physical backup with a continuous archiving mechanism. The purpose of the continuous archiving is to allow us to recover to any point in time from the time of the backup to the time onwards.

Getting ready

This recipe builds upon the previous recipe to take a Standalone hot physical backup. You should read that first before following this recipe.

You need to decide a few things, which are as follows:

  • Where will you store the WAL files (known as the "archive")?
  • How will you send WAL files to the archive?
  • Where will you store your base backups?
  • How will you take my base backups?
  • How many backups will you keep? What is your policy for maintaining the archive?

These are hard questions to answer immediately. So, we give a practical example as a way of explaining how this works, and then let the user decide how they would like it to operate.

How to do it...

The rest of this recipe assumes the following answers to the key questions:

  • Archive is a directory on a remote server named $OTHERNODE
  • We send WAL files to the archive using scp
  • Base backups are also stored on $OTHERNODE
  • Base backups are made using rsync
  • We'll keep two backups, overwriting alternate backups as we take new ones and backups are taken weekly on Sundays

The following steps assume that a number of environment variables have been set:

  • $PGDATA is the path to the PostgreSQL data directory, ending with /
  • $OTHERNODE is the name of the remote server
  • $BACKUPNAME is either b1/ or b2/, and we alternate this each time we take a backup. Two backups is the minimum; you may wish to use more copies.
  • All required PostgreSQL connection parameters have been set

The procedure is as follows:

  1. Create the archive and backup directories on a backup server.
  2. Set an archive_command. In postgresql.conf, you will need to add the following lines and restart the server, or just confirm they are present.
    archive_mode = on
    archive_command = 'scp %p $OTHERNODE:/archive/%f'
  3. Start the backup as follows:
    psql -c "select pg_start_backup('my backup')"
  4. Copy the data files (excluding the pg_xlog directory) as follows:
    rsync -cva --inplace --exclude=*pg_xlog* \
  5. Stop the backup as follows:
    psql -c "select pg_stop_backup(), current_timestamp"
  6. It's also good practice to put a README.backup file into the data directory prior to the backup, so that it forms part of the set of files that make up the base backup. This should say something intelligent about the location of the archive, including any identification numbers, names, and so on.

Notice that we don't put a recovery.conf into the backup this time. That's because we're assuming we want flexibility at the time of recovery, rather than a gift-wrapped solution. The reason for that is we don't know when or where or how we will be recovering, nor do we need to make a decision on that yet.

How it works...

The key point here is that we must have both the base backup and the archive in order to recover. Where you put them is completely up to you. You can use any file system backup technology and/or file system backup management system to do this.

Many backup management systems have claimed that they have a PostgreSQL interface/ plugin, though this most often means they support logical backup. However, there's no need for them to officially support PostgreSQL; there isn't any "Runs on PostgreSQL" badge or certification required. If you can copy files, then you can run the preceding processes to keep your database safe.

The preceding procedure uses a simple secure file copy, though it could also use rsync. If the network or backup server goes down, then the command will begin to fail. When the archive_command fails, it will repeatedly retry until it succeeds. PostgreSQL does not remove WAL files from pg_xlog until the WAL files have been successfully archived, so the end result is that your pg_xlog directory fills up. It's a good idea to have an archive_command that reacts better to that condition, though that is left as an improvement for the sysadmin. Typical action is to make that an emergency call out so we can resolve the problem manually. Automatic resolution is difficult to get right as this condition is one for which it is hard to test.

When continuously archiving, we will generate a considerable number of WAL files. If archive_timeout is set to 30 seconds, we will generate a minimum of 2*60*24 = 2880 files per day, each 16 MB in size, so a total volume of 46 GB per day (minimum). With a reasonable transaction rate, a database server might generate 100 GB of archive data per day, so you should use that as a rough figure for calculations before you have better measurements. Of course the rate could be much higher, with rates of 1 TB per day or higher being possible. Clearly we would only want to store WAL files that are useful for backup, so when we decide we no longer wish to keep a backup we will also want to remove files from the archive. In each base backup you will find a file called backup_label. The earliest WAL file required by a physical backup is the filename mentioned on the first line of the backup_label file. We can use a contrib module called pg_archivecleanup to remove any WAL files earlier than the earliest file.

The size of the WAL archive is clearly something we would want to compress. Ordinary compression is only reasonably effective. As is typically the case, a domain-specific compression tool is usually better at compressing archives. pg_lesslog is available at the following website to do this.


In this article we covered the following recipes:

  • Understanding and controlling crash recovery
  • Planning backups
  • Hot logical backup of one database
  • Hot logical backup of all databases
  • Hot logical backup of all tables in a tablespace
  • Backup of database object definitions
  • Standalone hot physical database backup
  • Hot physical backup & Continuous Archiving

In the next article, Recovery in PostgreSQL 9, we will look at the various recovery methods.

Further resources on this subject:

You've been reading an excerpt of:

PostgreSQL 9 Admin Cookbook

Explore Title