Chapter 24. Backup and Restore
archive_command = ’test ! -f /mnt/server/archivedir/%f && cp %p /mnt/server/archivedir/%f’
archive_command = ’copy "%p" "C:\\server\\archivedir\\%f"’
which will copy archivable WAL segments to the directory
.(This is an
example, not arecommendation, andmightnot workonallplatforms.) After the
have been replaced, the actual command executed might look like this:
test ! -f /mnt/server/archivedir/00000001000000A900000065 && cp pg_xlog/00000001000000A900000065 /mnt/server/archivedir/00000001000000A900000065
Asimilar command will be generated for each new ﬁle to be archived.
The archive command will be executed under the ownership of the same user that the PostgreSQL
server is running as. Since the series of WAL ﬁles being archived contains effectively everything
in your database, you will want to be sure that the archived data is protected from prying eyes; for
example, archive into a directory that does not have group or world read access.
Itis important that the archive commandreturn zero exitstatus if and onlyif itsucceeds. Upongetting
azeroresult, PostgreSQL will assume thatthe ﬁle has been successfully archived, and willremove or
recycle it. However, a nonzero status tells PostgreSQL that the ﬁle was not archived; it will try again
periodically until it succeeds.
The archive command should generally be designed to refuse to overwrite any pre-existing archive
ﬁle. This is animportant safetyfeaturetopreserve the integrityof your archiveincase of administrator
error (such as sending the output of two different servers to the same archive directory).
It is advisable to test your proposed archive command to ensure that it indeed does not overwrite an
existing ﬁle, and that it returns nonzero status in this case. The example command above for Unix
ensures this by including a separate
step. On some Unix platforms,
has switches such as
thatcanbe used to dothe same thingless verbosely, butyou should not relyonthesewithout verifying
that the right exit status is returned. (In particular, GNU
will return status zero when
and the target ﬁle alreadyexists, which is not the desired behavior.)
While designingyour archivingsetup, consider what will happenif the archive command fails repeat-
edlybecause some aspect requires operator interventionor thearchive runs outof space. For example,
this could occur if you write to tape without an autochanger; when the tape ﬁlls, nothing further can
be archived until the tape is swapped. You should ensure that any error condition or request to a hu-
man operator is reported appropriately so that the situation can be resolved reasonably quickly. The
directory will continue to ﬁll with WAL segment ﬁles until the situation is resolved. (If
the ﬁle system containing
ﬁlls up, PostgreSQL will do a PANIC shutdown. No committed
transactions will be lost, but the database will remain ofﬂine until you free some space.)
The speed of the archiving command is unimportant as long as it can keep up with the average rate
at which your server generates WAL data. Normal operation continues even if the archiving process
falls a little behind. If archiving falls signiﬁcantly behind, this will increase the amount of data that
would be lost in the event of a disaster. It will also mean that the
directory will contain
large numbers of not-yet-archived segment ﬁles, which could eventually exceed available disk space.
You are advised to monitor the archiving process to ensure that it is working as you intend.
In writing your archive command, you should assume that the ﬁle names to be archived can be up
to 64 characters long and can contain any combination of ASCII letters, digits, and dots. It is not
necessary to preserve the original relative path (
)but it is necessary to preserve the ﬁle name (
Note that although WAL archiving will allow you to restore any modiﬁcations made to the data
in your PostgreSQL database, it will not restore changes made to conﬁguration ﬁles (that is,
), since those are edited manually rather
than through SQL operations. You might wish to keep the conﬁguration ﬁles in a location that will
Chapter 24. Backup and Restore
be backed up by your regular ﬁle system backup procedures. See Section 18.2 for how to relocate the
The archive command is only invoked on completed WAL segments. Hence, if your server generates
only little WAL trafﬁc(or has slack periods whereit does so), there could be a long delay between the
completion of a transaction and its safe recording in archive storage. To put a limit on how old unar-
chived data can be, you can set archive_timeout to force the server to switch to a new WAL segment
ﬁle at least that often. Note thatarchived ﬁles thatarearchivedearly due toa forcedswitchare still the
same length as completely full ﬁles. Itis therefore unwise to seta very short
will bloat your archive storage.
settings of a minute or so are usually reasonable.
Also, you can force a segment switch manually with
if you want to ensure that
ajust-ﬁnished transaction is archived as soon as possible. Other utility functions related to WAL
management are listed in Table 9-65.
some SQL commands are optimized to avoid WAL logging, as de-
scribed in Section 14.4.7. If archiving or streaming replication were turned on during execution of
one of these statements, WAL would not contain enough information for archive recovery. (Crash
recovery is unaffected.) For this reason,
can only be changed at server start. However,
can be changed with a conﬁguration ﬁle reload. If you wish to temporarily stop
archiving, one way to do it is to set
tothe empty string (
). This will cause WAL
ﬁles to accumulate in
until a working
24.3.2. Making a Base Backup
The easiest way to perform a base backup is to use the pg_basebackup tool. It can create a base
backup either as regular ﬁles or as a tar archive. If more ﬂexibility than pg_basebackup can provide
is required, you canalso make a base backup using the low level API (see Section 24.3.3).
It is notnecessary tobe concernedabout the amount of time it takes to make a base backup. However,
if you normally run the server with
disabled, you might notice a drop in per-
formance while the backup runs since
is effectively forced on during backup
To make use of the backup, you will need to keep all the WAL segment ﬁles generated during
and after the ﬁle system backup. To aid you in doing this, the base backup process creates a
backup history ﬁle that is immediately stored into the WAL archive area. This ﬁle is named after
the ﬁrst WAL segment ﬁle that you need for the ﬁle system backup. For example, if the starting
WAL ﬁle is
the backup history ﬁle will be named something like
. (The second part of the ﬁle name stands
for an exact position within the WAL ﬁle, and can ordinarily be ignored.) Once you have safely
archived the ﬁle system backup and the WAL segment ﬁles used during the backup (as speciﬁed in
the backup history ﬁle), all archived WAL segments with names numerically less are no longer
needed to recover the ﬁle system backup and can be deleted. However, you should consider keeping
several backupsets to be absolutely certain that you can recover your data.
The backup history ﬁle is just a small textﬁle. It contains the label string you gaveto pg_basebackup,
as well as the starting and ending times and WAL segments of the backup. If you used the label to
identify the associated dump ﬁle, then the archived history ﬁle is enough to tell you which dump ﬁle
Since you have to keep around all the archived WAL ﬁles 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 ﬁles. You should also consider how long you are prepared to spend recovering, if
Chapter 24. Backup and Restore
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.
24.3.3. Making a Base Backup Using the Low Level API
The procedure for making a base backup using the low level APIs contains a few more steps than the
pg_basebackup method, but is relatively simple. It is very important that these steps are executed in
sequence, and that the success of a step is veriﬁed before proceeding to the next step.
1. Ensure that WAL archiving is enabled and working.
2. Connect to the database as a superuser and issue the command:
is any string you want touse to uniquely identify this backupoperation. (One good
practice is touse the full path where you intend to put the backup dump ﬁle.)
creates a backup labelﬁle, called
,inthecluster directory withinformation about
your backup, including the start time and label string. The ﬁle is critical to the integrity of the
backup, should you need to restore from it.
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
can take a long time to ﬁnish. This is because it performs a
checkpoint, and the I/O required for the checkpoint will be spread out over a signiﬁcant period
of time, by default half your inter-checkpoint interval (see the conﬁguration parameter check-
point_completion_target). This is usually what you want, because it minimizes the impact on
query processing. If you want to start the backup as soon as possible, use:
SELECT pg_start_backup(’label’, true);
This forces the checkpoint to be done as quickly as possible.
3. Perform the backup, using any convenient ﬁle-system-backup tool such as tar or cpio (not
pg_dump or pg_dumpall). It is neither necessary nor desirable to stop normal operation of the
database while you do this.
4. Again connect to the database as a superuser, and issue the command:
This terminates the backup mode and performs an automatic switch to the next WAL segment.
The reason for the switch is to arrange for the last WAL segment ﬁle written during the backup
interval to be ready to archive.
5. Once the WAL segment ﬁles active during the backup are archived, you are done. The ﬁle iden-
’s result is the last segment that is required to form a complete set of
backup ﬁles. If
does not return until the last seg-
ment has been archived. Archiving of these ﬁles happens automatically since you have already
.In most cases this happens quickly, but you are advisedto mon-
itor your archive system to ensure there are no delays. If the archive process has fallen behind
because of failures of the archive command, it will keep retrying until the archive succeeds and
the backup is complete. If you wish to place a time limit on the execution of
set an appropriate
Chapter 24. Backup and Restore
Some ﬁle system backup tools emit warnings or errors if the ﬁles theyaretryingtocopy change while
the copy proceeds. When taking a base backup of an active database, this situation is normal and
not an error. However, 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 ﬁles”,
and you can write a driver script to accept this exit code as a non-error case. Also, some versions
of GNU tar return an error code indistinguishable from a fatal error if a ﬁle was truncated while
tar was copying it. Fortunately, GNU tar versions 1.16 and later exit with 1 if a ﬁle was changed
during the backup, and 2 for other errors. With GNU tar version 1.23 and later, you can use the warn-
to hide the related
Be certain that your backup dump includes all of the ﬁles under the database cluster directory (e.g.,
). If you are usingtablespaces thatdonot reside underneath this directory,
be carefultoinclude them aswell(andbesure thatyour backupdumparchives symbolic linksas links,
otherwise the restore will corrupt your tablespaces).
You can, however, omit from the backup dump the ﬁles within the cluster’s
This slight adjustment is worthwhile because it reduces the risk of mistakes when restoring. This
is easy to arrange if
is a symbolic link pointing to someplace outside the cluster direc-
tory, which is a common setup anyway for performance reasons. You might also want to exclude
,which record information about the running postmaster,
not about the postmaster which will eventually use this backup. (These ﬁles can confuse pg_ctl.)
Itis oftena goodideatoalso omit from the backup dump the ﬁles within the cluster’s
directory, sothatreplication slots thatexistonthemaster donotbecomepart of the backup. Otherwise,
the subsequentuse of the backup to createa standbymayresult in indeﬁnite retentionof WAL ﬁles on
the standby, and possibly bloat on the master if hot standby feedback is enabled, because the clients
that are using those replication slots will still be connecting to and updating the slots on the master,
not the standby. Even if the backup is only intended for use in creating a new master, copying the
replicationslots isn’t expectedto be particularly useful, since the contents of those slots willlikely be
badly out of date by the time the new master comes on line.
It’s also worth noting that the
function makes a ﬁle named
the database cluster directory, which is removed by
.This ﬁle will of course be
archived as a part of your backup dump ﬁle. The backup label ﬁle includes the label string you gave
,as wellas thetime at which
was run, and the name of the
starting WAL ﬁle. In case of confusion it is therefore possible to look inside a backup dump ﬁle and
determine exactly which backup session the dump ﬁle came from. However, this ﬁle is not merely
for your information; its presence and contents are critical to the proper operation of the system’s
It is also possible to make a backup dump while the server is stopped. In this case, you obviously
,and 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 ﬁles go.
It is generally better to follow the continuous archiving procedure above.
24.3.4. Recovering Using a Continuous Archive Backup
Okay, the worst has happened and you need to recover from your backup. Here is the procedure:
1. Stop the server, if it’s running.
2. If you have the space to do so, copy the whole cluster data directory and any tablespaces to a
temporary location in case you need them later. Note that this precaution will require that you
Chapter 24. Backup and Restore
have enough free spaceonyour system tohold twocopies of your existing database. If youdonot
have enough space, you should at least save the contents of the cluster’s
as it might contain logs which were not archived before the system went down.
3. Remove all existing ﬁles and subdirectories under the cluster data directory and under the root
directories of any tablespaces you are using.
4. Restore the database ﬁles from your ﬁle system backup. Be sure that they are restored with the
right ownership (the database system user, not
!) and with the right permissions. If you
are using tablespaces, you should verify that the symbolic links in
5. Remove any ﬁles presentin
;these came from the ﬁle system backup and are therefore
probably obsolete rather than current. If youdidn’t archive
at all, then recreate itwith
proper permissions, being careful to ensure that you re-establish it as a symbolic link if you had
it set up that way before.
6. If you have unarchived WAL segment ﬁles that you saved in step 2, copy them into
(It is bestto copy them, not move them, so you still have the unmodiﬁed ﬁles if a problem occurs
and you have to start over.)
7. Create a recovery command ﬁle
in the cluster data directory (see Chapter 26).
You might also want to temporarily modify
to prevent ordinary users from con-
necting until you are sure the recovery was successful.
8. Start the server. The server will go into recovery mode and proceed to read through the archived
WAL ﬁles it needs. Should the recovery be terminated because of an external error, the server
can simply be restarted and it will continue recovery. Upon completion of the recovery process,
the server will rename
(toprevent 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 desired state. If not,
return to step 1. If all is well, allow your users to connect by restoring
The key partof allthis is toset upa recovery conﬁguration ﬁle thatdescribes howyou wantto recover
and howfar the recovery should run. You can use
(normally located in the
directory) as a prototype. The one thing that you absolutely must specify in
,which tells PostgreSQL how to retrieve archived WAL
ﬁle segments. Like the
,this is a shell command string. It can contain
is replacedby the name of the desired log ﬁle, and
,whichis replacedby the path name to copy the
logﬁle to. (Thepath nameis relativetothecurrent working directory, i.e., thecluster’s datadirectory.)
if you need to embed an actual
character in the command. The simplest useful command
is something like:
restore_command = ’cp /mnt/server/archivedir/%f %p’
which willcopypreviously archived WAL segments from thedirectory
Of course, you can use something much more complicated, perhaps even a shell script that requests
the operator to mount an appropriate tape.
It is important that the command return nonzero exit status on failure. The command will be called
requesting ﬁles that are not present in the archive; it must return nonzero when so asked. This is
not an error condition. An exception is that if the command was terminated by a signal (other than
SIGTERM, which is used as part of a database server shutdown) or an error by the shell (such as
command not found), then recovery will abort and the server will not start up.
Chapter 24. Backup and Restore
Notall of the requested ﬁles will be WAL segment ﬁles; you shouldalso expectrequests for ﬁles with
.Also be aware that the base name of the
path will be different
;do not expect them to be interchangeable.
WAL segments that cannot be found in the archive will be sought in
;this allows use of
recent un-archived segments. However, segments that are available from the archive will be used in
preference to ﬁles in
Normally, recovery will proceed through all available WAL segments, thereby restoring the database
to the current point in time (or as close as possible given the available WAL segments). Therefore,
a normal recovery will end with a “ﬁle not found” message, the exact text of the error message
depending upon your choice of
.You may also see an error message at the start
of recovery for a ﬁle named something like
.This is also normal and does not
indicate a problem in simple recovery situations; see Section 24.3.5 for discussion.
If you want to recover to some previous point in time (say, right before the junior DBA dropped
your main transaction table), just specify the required stopping point in
specify the stop point, known as the “recovery target”, either by date/time, named restore point or
by completion of a speciﬁc transaction ID. As of this writing only the date/time and named restore
point options are very usable, since there are no tools to help you identify with any accuracy which
transaction ID to use.
Note: The stop point must be after the ending time of the base backup, i.e., the end time of
.You cannot use a base backup to recover to a time when that backup was in
progress. (To recover to such a time, you must go back to your previous base backup and roll
forward from there.)
If recovery ﬁnds corrupted WAL data, recovery will halt at that point and the server will not start. In
such a case the recovery process could be re-run from the beginning, specifying a “recovery target”
before the point of corruptionsothat recoverycan complete normally. If recovery fails for an external
reason, such as a system crash or if the WAL archive has become inaccessible, then the recovery can
simply be restarted and it will restart almost from where it failed. Recovery restart works much like
checkpointing in normal operation: the server periodically forces all its state to disk, and then updates
ﬁle to indicate that the already-processed WAL data need not be scanned again.
The ability to restore the database to a previous point in time creates some complexities that are akin
to science-ﬁction stories about time travel and parallel universes. For example, in the original history
of thedatabase, suppose you droppeda critical tableat 5:15PM on Tuesdayevening, butdidn’t realize
your mistake until Wednesday noon. Unfazed, you get out your backup, restore to the point-in-time
5:14PM Tuesday evening, and are up and running. In this history of the database universe, you never
dropped the table. But suppose you later realize this wasn’t such a great idea, and would like toreturn
to sometime Wednesday morningin the original history. Youwon’t be able to if, while your database
was up-and-running, it overwrote some of the WAL segment ﬁles that led up to the time you now
wish you could get back to. Thus, to avoid this, you need to distinguish the series of WAL records
generated after you’ve done a point-in-time recovery from those that were generated in the original
To dealwith this problem, PostgreSQL has a notion of timelines. Whenever an archive recovery com-
pletes, a new timeline is created to identify the series of WAL records generated after that recovery.
The timeline ID number is part of WAL segment ﬁle names so a new timeline does not overwrite the
Documents you may be interested
Documents you may be interested