|PostgreSQL Reference Manual - Volume 3 - Server Administration Guide|
by The PostgreSQL Global Development Group
Paperback (6"x9"), 204 pages
RRP £13.95 ($24.95)
Sales of this book support the PostgreSQL project! Get a printed copy>>>
10.3.2 Making a Base Backup
The procedure for making a base backup is relatively simple:
- Ensure that WAL archiving is enabled and working.
Connect to the database as a superuser, and issue the command
labelis any string you want to use to uniquely identify this backup operation. (One good practice is to use the full path where you intend to put the backup dump file.)
pg_start_backupcreates a backup label file, called ‘backup_label’, in the cluster directory with information about your backup. It does not matter which database within the cluster you connect to to issue this command. You can ignore the result returned by the function; but if it reports an error, deal with that before proceeding.
- Perform the backup, using any convenient file-system-backup tool such as tar or cpio. It is neither necessary nor desirable to stop normal operation of the database while you do this.
Again connect to the database as a superuser, and issue the command
SELECT pg_stop_backup();This terminates the backup mode and performs an automatic switch to the next WAL segment. The reason for the switch is to arrange that the last WAL segment file written during the backup interval is immediately ready to archive.
Once the WAL segment files used during the backup are archived, you are
done. The file identified by
pg_stop_backup's result is the last segment that needs to be archived to complete the backup. Archival of these files will happen automatically, since you have already configured
archive_command. In many cases, this happens fairly quickly, but you are advised to monitor your archival system to ensure this has taken place so that you can be certain you have a complete backup.
Some backup tools that you might wish to use emit warnings or errors if the files they are trying to copy change while the copy proceeds. This situation is normal, and not an error, when taking a base backup of an active database; so you need to ensure that you can distinguish complaints of this sort from real errors. For example, some versions of rsync return a separate exit code for “vanished source files”, and you can write a driver script to accept this exit code as a non-error case. Also, some versions of GNU tar consider it an error if a file is changed while tar is copying it. There does not seem to be any very convenient way to distinguish this error from other types of errors, other than manual inspection of tar's messages. GNU tar is therefore not the best tool for making base backups.
It is not necessary to be very concerned about the amount of time elapsed
pg_start_backup and the start of the actual backup,
nor between the end of the backup and
few minutes' delay won't hurt anything. (However, if you normally run the
full_page_writes disabled, you may notice a drop
in performance between
effectively forced on during backup mode.) You must ensure that these
steps are carried out in sequence without any possible
overlap, or you will invalidate the backup.
Be certain that your backup dump includes all of the files underneath the database cluster directory (e.g., ‘/usr/local/pgsql/data’). If you are using tablespaces that do not reside underneath this directory, be careful to include them as well (and be sure that your backup dump archives symbolic links as links, otherwise the restore will mess up your tablespaces).
You may, however, omit from the backup dump the files within the ‘pg_xlog/’ subdirectory of the cluster directory. This slight complication is worthwhile because it reduces the risk of mistakes when restoring. This is easy to arrange if ‘pg_xlog/’ is a symbolic link pointing to someplace outside the cluster directory, which is a common setup anyway for performance reasons.
To make use of the backup, you will need to keep around all the WAL
segment files generated during and after the file system backup.
To aid you in doing this, the
creates a backup history file that is immediately
stored into the WAL archive area. This file is named after the first
WAL segment file that you need to have to make use of the backup.
For example, if the starting WAL file is
0000000100001234000055CD the backup history file will be
named something like
0000000100001234000055CD.007C9330.backup. (The second
number in the file name stands for an exact position within the WAL
file, and can ordinarily be ignored.) Once you have safely archived
the file system backup and the WAL segment files used during the
backup (as specified in the backup history file), all archived WAL
segments with names numerically less are no longer needed to recover
the file system backup and may be deleted. However, you should
consider keeping several backup sets to be absolutely certain that
you can recover your data.
The backup history file is just a small text file. It contains the
label string you gave to
pg_start_backup, as well as
the starting and ending times and WAL segments of the backup.
If you used the label to identify where the associated dump file is kept,
then the archived history file is enough to tell you which dump file to
restore, should you need to do so.
Since you have to keep around all the archived WAL files back to your last base backup, the interval between base backups should usually be chosen based on how much storage you want to expend on archived WAL files. You should also consider how long you are prepared to spend recovering, if recovery should be necessary--the system will have to replay all those WAL segments, and that could take awhile if it has been a long time since the last base backup.
It's also worth noting that the
makes a file named ‘backup_label’ in the database cluster
directory, which is then removed again by
This file will of course be archived as a part of your backup dump file.
The backup label file includes the label string you gave to
pg_start_backup, as well as the time at which
pg_start_backup was run, and the name of the starting WAL
file. In case of confusion it will
therefore be possible to look inside a backup dump file and determine
exactly which backup session the dump file came from.
It is also possible to make a backup dump while the server is
stopped. In this case, you obviously cannot use
you will therefore be left to your own devices to keep track of which
backup dump is which and how far back the associated WAL files go.
It is generally better to follow the continuous archiving procedure above.
|ISBN 0954612043||PostgreSQL Reference Manual - Volume 3 - Server Administration Guide||See the print edition|