PostgreSQL backup and restore¶

Backing up users¶

The pg_dump tool operates on one database (or schema or table as required) at a time. However, information about users is not stored in any one database. To back up your user information, use the pg_dumpall tool, with the --globals-only flag.

pg_dumpall --globals-only --port 54321

You can also use pg_dumpall in its default mode to back up an entire database cluster, but be aware that you will end up backing up the PostGIS function signatures, so the dump will have to be restored against an identical software installation, and it can’t be used as part of an upgrade process.

How online backup works¶

Rather than continually write to the main data tables, PostgreSQL stores changes initially in write-ahead logs (WAL). These logs are a complete record of all changes made to a database. Online backup involves taking a copy of the database main data table, then taking a copy of each WAL that is generated from that point onwards.

Online backup

When it is time to recover to a new database, the system starts on the main data copy, then replays all the WAL file entries into the database. The end result is a restored database in the same state as the original at the time when the last WAL was received.

As WAL files are being written anyway, and transferring copies to an archive server is computationally cheap, online backup is an effective and efficient means of maintaining an up-to-date backup of a system without having to rely on regular full or partial basic backups.

Archiving the WAL files¶

The first thing to do when setting up online backup is to create an archiving method. PostgreSQL archiving methods are flexible and easy to configure as the PostgreSQL server simply calls a script specified in the archive_command configuration parameter. That means archiving can be as simple as copying the file to a network-mounted drive, and as complex as encrypting and emailing the files to the remote archive.

Any process you can automate through scripting you can use to archive the files. To enable archiving, edit the postgresql.conf file, and turn on WAL archiving:

wal_level = archive
archive_mode = on

And then setting the archive_command to copy our archive files to a safe location (changing the destination paths as appropriate):

# Unix
archive_command = 'test ! -f /archivedir/%f && cp %p /archivedir/%f'

# Windows
archive_command = 'copy "%p" "C:\\archivedir\\%f"'

The UNIX command includes an initial test to ensure that the files aren’t already there so they won’t be over-written unintentionally. It is also important that the command returns a non-zero status if the copy process fails.

Once the changes are made, re-start PostgreSQL to activate archiving.

Taking the base backup¶

Once the archiving process has been enabled, take a base backup. This will provide the benchmark state of the database.

1. Put the database into backup mode.

   SELECT pg_start_backup('/archivedir/basebackup.tgz');
   

   This doesn’t do anything to alter the operation of queries or data updates, it simply forces a checkpoint and writes a label file indicating when the backup was taken. For the label, using the path to the backup file is a good practice, as it helps you track where the backup was stored.

2. Copy the database to an archive location:

   # Unix
   tar cvfz /archivedir/basebackup.tgz ${PGDATA}
   

1. Finally, issue the following command to tell the database the backup process is complete.

   SELECT pg_stop_backup();
   

Restoring from the archive¶

The following steps are taking from the PostgreSQL manual on continuous archiving and point-in-time recovery.

1. Stop the PostgreSQL server if it’s running.
2. If you have available space, copy the whole cluster data directory and any tablespaces to a temporary location. This precautionary step requires enough free space on your system to hold two copies of your existing database. If you do not have enough space, you should at least save the contents of the cluster’s pg_xlog subdirectory, as it may contain logs which were not archived before the system went down.
3. Remove all existing files and subdirectories under the cluster data directory and under the root directories of any tablespaces you are using.
4. Restore the database files from your file system backup. Be sure that they are restored with the correct ownership (the database system user, not the system root or administrator user) and with the correct permissions. If you are using tablespaces, you should verify that the symbolic links in the pg_tblspc folder were correctly restored.
5. Remove any files present in the pg_xlog folder (these came from the file system backup and are therefore probably obsolete rather than current). If you didn’t archive pg_xlog at all, then recreate it with the correct permissions. If you were using a symbolic link before, ensure you re-create the link to replicate the configuration.
6. If you have unarchived WAL segment files you saved in step 2, copy them into pg_xlog. It is preferable to copy them rather than move them, so you still have the unmodified files if problems occur.
7. Create a recovery command file recovery.conf in the cluster data directory. You may also want to temporarily modify he pg_hba.conf file to prevent users from connecting to the database until you are sure the recovery was successful.
8. Start the PostgeSQL server. The server will go into recovery mode and read through the archived WAL files it needs. If the recovery be interrupted for any reason, the server can simply be restarted and it will continue the recovery process. On completion of the process, the server will rename recovery.conf to recovery.done (to prevent accidentally re-entering recovery mode later) and then commence normal database operations.
9. Inspect the contents of the database to ensure you have recovered to the required state. If all is as expected, restore the pg_hba.conf to enable database connections. If the database is not in the required state, repeat the restore process.