Backup & Recovery - PostgreSQL 9 Administration - page 279

F Check that all file permissions and ownerships are correct and links are valid. That
should already be the case if you are using the postgres userid everywhere,
which is recommended.
F Remove any files in pg_xlog/ . If you've been following my recipes, you'll be able to
skip this step, because we never backed them up in the first place.
F Copy in any latest WAL files from a running server, if any.
F Add in a recovery.conf and set its file permissions correctly also.
F Start the server.
The only part that requires some thought and checking is which parameters you select for the
recovery.conf . There's only one that matters here, and that is the restore_command .
restore_command tells us how to restore archived WAL files. It needs to be the command
that will be executed to bring back WAL files from the archive.
If you are forward-thinking, there'll be a README.backup file for you to read to find out how
to set the restore_command . If not, then presumably you've got the location of the WAL files
you've been saving written down somewhere.
Say, for example, that your files are being saved to a directory named /backups/pg/
servername/archive , owned by the postgres user.
On a remote server named backup1 , we would then write this all on one line of the
recovery.conf as follows:
restore_command = 'scp backup1:/backups/pg/servername/archive/%f %p'
How it works...
PostgreSQL is designed to require very minimal information to perform a recovery. We try hard
to wrap all the details up for you.
F Logical recovery: Logical recovery executes SQL to re-create the database objects. If
performance is an issue, look at the recipe on recovery performance.
F Physical recovery: Physical recovery re-applies data changes at the block level so
tends to be much faster than logical recovery. Physical recovery requires both a base
backup and a set of archived WAL files.
There is a file named backup_label in the data directory of the base backup. This tells us to
retrieve a .backup file from the archive that contains the start and stop WAL locations of the
base backup. Recovery then starts to apply changes from the starting WAL location, and must
proceed as far as the stop address for the backup to be valid.
After recovery completes, the recovery.conf file is renamed to recovery.done to prevent
the server from re-entering recovery.