Restoring a binary backup (Simple)

Please refer to the Getting a basic export (Simple) recipe to bootstrap our sample database. Then, we want a very simple backup to restore. We should stop the database to avoid any extra work using the following commands:

$> pg_ctl -D /db/pgdata stop -m fast
$> tar -C /db/pgdata --exclude=pg_xlog -cz \
  -f /db/backup/backup.tar.gz .
$> tar -C /db/pgdata/pg_xlog --exclude=archive_status \
  --exclude=archive_status -czf /db/backup/xlog.tar.gz .

Once these files are safely stored, erase our sample database using the following command:

$> rm -Rf /db/pgdata

Restoring a binary backup is generally easy if we run a few commands as the postgres user, as shown in the following list:

We cheat a little to create a simple restore backup that will require using a recovery.conf file. All recovery is controlled through this file, so it is important to know how it works.

Afterwards, we simply create the basic directory for the database as it existed before, and extract the backup file there. When a database is in recovery, it copies old transaction logs and processes them, ensuring the database properly reflects all the necessary data.

The real trick here is the recovery.conf file, which plays a central role in almost all binary recovery. We used one of the simplest settings to copy transaction logs to their required destination directory, and before this is all over, we will learn much more. For now, know that in the restore_command line, %f is the name of a file PostgreSQL needs, and %p is where the file should reside.

This means we could use any equivalent Unix command instead of our simple copy. For instance, we could use rsync to copy the file from a remote storage location instead.

Restoring a simple backup is effectively just extraction and recovery. We don't even need a bootstrap like with a SQL restore. However, there is still a little more to know.

restore_command = 'rsync -a postgres@backup_server::archive/%f "%p"'