Backup and Restore {#backup}
==================

backup

As with everything that contains valuable data, PostgreSQL databases
should be backed up regularly. While the procedure is essentially
simple, it is important to have a clear understanding of the underlying
techniques and assumptions.

There are three fundamentally different approaches to backing up
PostgreSQL data:

-   SQL dump

-   File system level backup

-   Continuous archiving

Each has its own strengths and weaknesses; each is discussed in turn in
the following sections.

SQL Dump {#backup-dump}
========

The idea behind this dump method is to generate a file with SQL commands
that, when fed back to the server, will recreate the database in the
same state as it was at the time of the dump. PostgreSQL provides the
utility program [???](#app-pgdump) for this purpose. The basic usage of
this command is: pg\_dump dbname \> dumpfile As you see, pg\_dump writes
its result to the standard output. We will see below how this can be
useful. While the above command creates a text file, pg\_dump can create
files in other formats that allow for parallelism and more fine-grained
control of object restoration.

pg\_dump is a regular PostgreSQL client application (albeit a
particularly clever one). This means that you can perform this backup
procedure from any remote host that has access to the database. But
remember that pg\_dump does not operate with special permissions. In
particular, it must have read access to all tables that you want to back
up, so in order to back up the entire database you almost always have to
run it as a database superuser. (If you do not have sufficient
privileges to back up the entire database, you can still back up
portions of the database to which you do have access using options such
as `-n schema` or `-t table`.)

To specify which database server pg\_dump should contact, use the
command line options `-h
   host` and `-p port`. The default host is the local host or whatever
your PGHOST environment variable specifies. Similarly, the default port
is indicated by the PGPORT environment variable or, failing that, by the
compiled-in default. (Conveniently, the server will normally have the
same compiled-in default.)

Like any other PostgreSQL client application, pg\_dump will by default
connect with the database user name that is equal to the current
operating system user name. To override this, either specify the `-U`
option or set the environment variable PGUSER. Remember that pg\_dump
connections are subject to the normal client authentication mechanisms
(which are described in [???](#client-authentication)).

An important advantage of pg\_dump over the other backup methods
described later is that pg\_dump\'s output can generally be re-loaded
into newer versions of PostgreSQL, whereas file-level backups and
continuous archiving are both extremely server-version-specific.
pg\_dump is also the only method that will work when transferring a
database to a different machine architecture, such as going from a
32-bit to a 64-bit server.

Dumps created by pg\_dump are internally consistent, meaning, the dump
represents a snapshot of the database at the time pg\_dump began
running. pg\_dump does not block other operations on the database while
it is working. (Exceptions are those operations that need to operate
with an exclusive lock, such as most forms of `ALTER TABLE`.)

Restoring the Dump {#backup-dump-restore}
------------------

Text files created by pg\_dump are intended to be read in by the psql
program. The general command form to restore a dump is psql dbname \<
dumpfile where dumpfile is the file output by the pg\_dump command. The
database dbname will not be created by this command, so you must create
it yourself from `template0` before executing psql (e.g., with
`createdb -T template0 dbname`). psql supports options similar to
pg\_dump for specifying the database server to connect to and the user
name to use. See the [???](#app-psql) reference page for more
information. Non-text file dumps are restored using the
[???](#app-pgrestore) utility.

Before restoring an SQL dump, all the users who own objects or were
granted permissions on objects in the dumped database must already
exist. If they do not, the restore will fail to recreate the objects
with the original ownership and/or permissions. (Sometimes this is what
you want, but usually it is not.)

By default, the psql script will continue to execute after an SQL error
is encountered. You might wish to run psql with the `ON_ERROR_STOP`
variable set to alter that behavior and have psql exit with an exit
status of 3 if an SQL error occurs:

    psql --set ON_ERROR_STOP=on dbname < dumpfile

Either way, you will only have a partially restored database.
Alternatively, you can specify that the whole dump should be restored as
a single transaction, so the restore is either fully completed or fully
rolled back. This mode can be specified by passing the `-1` or
`--single-transaction` command-line options to psql. When using this
mode, be aware that even a minor error can rollback a restore that has
already run for many hours. However, that might still be preferable to
manually cleaning up a complex database after a partially restored dump.

The ability of pg\_dump and psql to write to or read from pipes makes it
possible to dump a database directly from one server to another, for
example:

    pg_dump -h host1 dbname | psql -h host2 dbname

> **Important**
>
> The dumps produced by pg\_dump are relative to `template0`. This means
> that any languages, procedures, etc. added via `template1` will also
> be dumped by pg\_dump. As a result, when restoring, if you are using a
> customized `template1`, you must create the empty database from
> `template0`, as in the example above.

After restoring a backup, it is wise to run [`ANALYZE`](#sql-analyze) on
each database so the query optimizer has useful statistics; see
[???](#vacuum-for-statistics) and [???](#autovacuum) for more
information. For more advice on how to load large amounts of data into
PostgreSQL efficiently, refer to [???](#populate).

Using pg\_dumpall {#backup-dump-all}
-----------------

pg\_dump dumps only a single database at a time, and it does not dump
information about roles or tablespaces (because those are cluster-wide
rather than per-database). To support convenient dumping of the entire
contents of a database cluster, the [???](#app-pg-dumpall) program is
provided. pg\_dumpall backs up each database in a given cluster, and
also preserves cluster-wide data such as role and tablespace
definitions. The basic usage of this command is: pg\_dumpall \> dumpfile
The resulting dump can be restored with psql: psql -f dumpfile postgres
(Actually, you can specify any existing database name to start from, but
if you are loading into an empty cluster then `postgres` should usually
be used.) It is always necessary to have database superuser access when
restoring a pg\_dumpall dump, as that is required to restore the role
and tablespace information. If you use tablespaces, make sure that the
tablespace paths in the dump are appropriate for the new installation.

pg\_dumpall works by emitting commands to re-create roles, tablespaces,
and empty databases, then invoking pg\_dump for each database. This
means that while each database will be internally consistent, the
snapshots of different databases are not synchronized.

Cluster-wide data can be dumped alone using the pg\_dumpall
`--globals-only` option. This is necessary to fully backup the cluster
if running the pg\_dump command on individual databases.

Handling Large Databases {#backup-dump-large}
------------------------

Some operating systems have maximum file size limits that cause problems
when creating large pg\_dump output files. Fortunately, pg\_dump can
write to the standard output, so you can use standard Unix tools to work
around this potential problem. There are several possible methods:

**Use compressed dumps..**

You can use your favorite compression program, for example gzip:

    pg_dump dbname | gzip > filename.gz

Reload with:

    gunzip -c filename.gz | psql dbname

or:

    cat filename.gz | gunzip | psql dbname

**Use `split`..**

The `split` command allows you to split the output into smaller files
that are acceptable in size to the underlying file system. For example,
to make 2 gigabyte chunks:

    pg_dump dbname | split -b 2G - filename

Reload with:

    cat filename* | psql dbname

If using GNU split, it is possible to use it and gzip together:

    pg_dump dbname | split -b 2G --filter='gzip > $FILE.gz'

It can be restored using `zcat`.

**Use pg\_dump\'s custom dump format..**

If PostgreSQL was built on a system with the zlib compression library
installed, the custom dump format will compress data as it writes it to
the output file. This will produce dump file sizes similar to using
`gzip`, but it has the added advantage that tables can be restored
selectively. The following command dumps a database using the custom
dump format:

    pg_dump -Fc dbname > filename

A custom-format dump is not a script for psql, but instead must be
restored with pg\_restore, for example:

    pg_restore -d dbname filename

See the [???](#app-pgdump) and [???](#app-pgrestore) reference pages for
details.

For very large databases, you might need to combine `split` with one of
the other two approaches.

**Use pg\_dump\'s parallel dump feature..**

To speed up the dump of a large database, you can use pg\_dump\'s
parallel mode. This will dump multiple tables at the same time. You can
control the degree of parallelism with the `-j` parameter. Parallel
dumps are only supported for the \"directory\" archive format.

    pg_dump -j num -F d -f out.dir dbname

You can use `pg_restore -j` to restore a dump in parallel. This will
work for any archive of either the \"custom\" or the \"directory\"
archive mode, whether or not it has been created with `pg_dump -j`.

File System Level Backup {#backup-file}
========================

An alternative backup strategy is to directly copy the files that
PostgreSQL uses to store the data in the database;
[???](#creating-cluster) explains where these files are located. You can
use whatever method you prefer for doing file system backups; for
example:

    tar -cf backup.tar /usr/local/pgsql/data

There are two restrictions, however, which make this method impractical,
or at least inferior to the pg\_dump method:

1.  The database server *must* be shut down in order to get a usable
    backup. Half-way measures such as disallowing all connections will
    *not* work (in part because `tar` and similar tools do not take an
    atomic snapshot of the state of the file system, but also because of
    internal buffering within the server). Information about stopping
    the server can be found in [???](#server-shutdown). Needless to say,
    you also need to shut down the server before restoring the data.

2.  If you have dug into the details of the file system layout of the
    database, you might be tempted to try to back up or restore only
    certain individual tables or databases from their respective files
    or directories. This will *not* work because the information
    contained in these files is not usable without the commit log files,
    `pg_xact/*`, which contain the commit status of all transactions. A
    table file is only usable with this information. Of course it is
    also impossible to restore only a table and the associated `pg_xact`
    data because that would render all other tables in the database
    cluster useless. So file system backups only work for complete
    backup and restoration of an entire database cluster.

An alternative file-system backup approach is to make a "consistent
snapshot" of the data directory, if the file system supports that
functionality (and you are willing to trust that it is implemented
correctly). The typical procedure is to make a "frozen snapshot" of the
volume containing the database, then copy the whole data directory (not
just parts, see above) from the snapshot to a backup device, then
release the frozen snapshot. This will work even while the database
server is running. However, a backup created in this way saves the
database files in a state as if the database server was not properly
shut down; therefore, when you start the database server on the
backed-up data, it will think the previous server instance crashed and
will replay the WAL log. This is not a problem; just be aware of it (and
be sure to include the WAL files in your backup). You can perform a
`CHECKPOINT` before taking the snapshot to reduce recovery time.

If your database is spread across multiple file systems, there might not
be any way to obtain exactly-simultaneous frozen snapshots of all the
volumes. For example, if your data files and WAL log are on different
disks, or if tablespaces are on different file systems, it might not be
possible to use snapshot backup because the snapshots *must* be
simultaneous. Read your file system documentation very carefully before
trusting the consistent-snapshot technique in such situations.

If simultaneous snapshots are not possible, one option is to shut down
the database server long enough to establish all the frozen snapshots.
Another option is to perform a continuous archiving base backup ([Making
a Base Backup](#backup-base-backup)) because such backups are immune to
file system changes during the backup. This requires enabling continuous
archiving just during the backup process; restore is done using
continuous archive recovery ([Recovering Using a Continuous Archive
Backup](#backup-pitr-recovery)).

Another option is to use rsync to perform a file system backup. This is
done by first running rsync while the database server is running, then
shutting down the database server long enough to do an
`rsync --checksum`. (`--checksum` is necessary because `rsync` only has
file modification-time granularity of one second.) The second rsync will
be quicker than the first, because it has relatively little data to
transfer, and the end result will be consistent because the server was
down. This method allows a file system backup to be performed with
minimal downtime.

Note that a file system backup will typically be larger than an SQL
dump. (pg\_dump does not need to dump the contents of indexes for
example, just the commands to recreate them.) However, taking a file
system backup might be faster.

Continuous Archiving and Point-in-Time Recovery (PITR) {#continuous-archiving}
======================================================

continuous archiving

point-in-time recovery

PITR

At all times, PostgreSQL maintains a write ahead log (WAL) in the
`pg_wal/` subdirectory of the cluster\'s data directory. The log records
every change made to the database\'s data files. This log exists
primarily for crash-safety purposes: if the system crashes, the database
can be restored to consistency by "replaying" the log entries made since
the last checkpoint. However, the existence of the log makes it possible
to use a third strategy for backing up databases: we can combine a
file-system-level backup with backup of the WAL files. If recovery is
needed, we restore the file system backup and then replay from the
backed-up WAL files to bring the system to a current state. This
approach is more complex to administer than either of the previous
approaches, but it has some significant benefits:

-   We do not need a perfectly consistent file system backup as the
    starting point. Any internal inconsistency in the backup will be
    corrected by log replay (this is not significantly different from
    what happens during crash recovery). So we do not need a file system
    snapshot capability, just tar or a similar archiving tool.

-   Since we can combine an indefinitely long sequence of WAL files for
    replay, continuous backup can be achieved simply by continuing to
    archive the WAL files. This is particularly valuable for large
    databases, where it might not be convenient to take a full backup
    frequently.

-   It is not necessary to replay the WAL entries all the way to the
    end. We could stop the replay at any point and have a consistent
    snapshot of the database as it was at that time. Thus, this
    technique supports point-in-time recovery: it is possible to restore
    the database to its state at any time since your base backup was
    taken.

-   If we continuously feed the series of WAL files to another machine
    that has been loaded with the same base backup file, we have a warm
    standby system: at any point we can bring up the second machine and
    it will have a nearly-current copy of the database.

> **Note**
>
> pg\_dump and pg\_dumpall do not produce file-system-level backups and
> cannot be used as part of a continuous-archiving solution. Such dumps
> are *logical* and do not contain enough information to be used by WAL
> replay.

As with the plain file-system-backup technique, this method can only
support restoration of an entire database cluster, not a subset. Also,
it requires a lot of archival storage: the base backup might be bulky,
and a busy system will generate many megabytes of WAL traffic that have
to be archived. Still, it is the preferred backup technique in many
situations where high reliability is needed.

To recover successfully using continuous archiving (also called "online
backup" by many database vendors), you need a continuous sequence of
archived WAL files that extends back at least as far as the start time
of your backup. So to get started, you should set up and test your
procedure for archiving WAL files *before* you take your first base
backup. Accordingly, we first discuss the mechanics of archiving WAL
files.

Setting Up WAL Archiving {#backup-archiving-wal}
------------------------

In an abstract sense, a running PostgreSQL system produces an
indefinitely long sequence of WAL records. The system physically divides
this sequence into WAL segment files, which are normally 16MB apiece
(although the segment size can be altered during initdb). The segment
files are given numeric names that reflect their position in the
abstract WAL sequence. When not using WAL archiving, the system normally
creates just a few segment files and then "recycles" them by renaming
no-longer-needed segment files to higher segment numbers. It\'s assumed
that segment files whose contents precede the last checkpoint are no
longer of interest and can be recycled.

When archiving WAL data, we need to capture the contents of each segment
file once it is filled, and save that data somewhere before the segment
file is recycled for reuse. Depending on the application and the
available hardware, there could be many different ways of "saving the
data somewhere": we could copy the segment files to an NFS-mounted
directory on another machine, write them onto a tape drive (ensuring
that you have a way of identifying the original name of each file), or
batch them together and burn them onto CDs, or something else entirely.
To provide the database administrator with flexibility, PostgreSQL tries
not to make any assumptions about how the archiving will be done.
Instead, PostgreSQL lets the administrator specify an archive library to
be executed to copy a completed segment file to wherever it needs to go.
This could be as simple as a shell command that uses `cp`, or it could
invoke a complex C function MDASH it\'s all up to you.

To enable WAL archiving, set the [???](#guc-wal-level) configuration
parameter to `replica` or higher, [???](#guc-archive-mode) to `on`, and
specify the library to use in the [???](#guc-archive-library)
configuration parameter. In practice these settings will always be
placed in the `postgresql.conf` file. One simple way to archive is to
set `archive_library` to an empty string and to specify a shell command
in [???](#guc-archive-command). In `archive_command`, `%p` is replaced
by the path name of the file to archive, while `%f` is replaced by only
the file name. (The path name is relative to the current working
directory, i.e., the cluster\'s data directory.) Use `%%` if you need to
embed an actual `%` character in the command. The simplest useful
command is something like:

    archive_command = 'test ! -f /mnt/server/archivedir/%f && cp %p /mnt/server/archivedir/%f'  # Unix
    archive_command = 'copy "%p" "C:\\server\\archivedir\\%f"'  # Windows

which will copy archivable WAL segments to the directory
`/mnt/server/archivedir`. (This is an example, not a recommendation, and
might not work on all platforms.) After the `%p` and `%f` parameters
have been replaced, the actual command executed might look like this:

    test ! -f /mnt/server/archivedir/00000001000000A900000065 && cp pg_wal/00000001000000A900000065 /mnt/server/archivedir/00000001000000A900000065

A similar command will be generated for each new file to be archived.

Another way to archive is to use a custom archive module as the
`archive_library`. Since such modules are written in `C`, creating your
own may require considerably more effort than writing a shell command.
However, archive modules can be more performant than archiving via
shell, and they will have access to many useful server resources. For
more information about archive modules, see [???](#archive-modules).

The archive library will be executed under the ownership of the same
user that the PostgreSQL server is running as. Since the series of WAL
files 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.

It is important that the archive function return `true` if and only if
it succeeds. If `true` is returned, PostgreSQL will assume that the file
has been successfully archived, and will remove or recycle it. However,
a return value of `false` tells PostgreSQL that the file was not
archived; it will try again periodically until it succeeds. If you are
archiving via a shell command, the appropriate return values can be
achieved by returning `0` if the command succeeds and a nonzero value if
it fails.

If the archive function emits an `ERROR` or `FATAL`, the archiver
process aborts and gets restarted by the postmaster. If you are
archiving via shell command, FATAL is emitted if the command is
terminated by a signal (other than SIGTERM that is used as part of a
server shutdown) or an error by the shell with an exit status greater
than 125 (such as command not found). In such cases, the failure is not
reported in [???](#pg-stat-archiver-view).

The archive library should generally be designed to refuse to overwrite
any pre-existing archive file. This is an important safety feature to
preserve the integrity of your archive in case 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 library to ensure that it
indeed does not overwrite an existing file, *and that it returns `false`
in this case*. The example command above for Unix ensures this by
including a separate `test` step. On some Unix platforms, `cp` has
switches such as `-i` that can be used to do the same thing less
verbosely, but you should not rely on these without verifying that the
right exit status is returned. (In particular, GNU `cp` will return
status zero when `-i` is used and the target file already exists, which
is *not* the desired behavior.)

While designing your archiving setup, consider what will happen if the
archive library fails repeatedly because some aspect requires operator
intervention or the archive runs out of space. For example, this could
occur if you write to tape without an autochanger; when the tape fills,
nothing further can be archived until the tape is swapped. You should
ensure that any error condition or request to a human operator is
reported appropriately so that the situation can be resolved reasonably
quickly. The `pg_wal/` directory will continue to fill with WAL segment
files until the situation is resolved. (If the file system containing
`pg_wal/` fills up, PostgreSQL will do a PANIC shutdown. No committed
transactions will be lost, but the database will remain offline until
you free some space.)

The speed of the archive library 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 significantly behind, this will increase the amount
of data that would be lost in the event of a disaster. It will also mean
that the `pg_wal/` directory will contain large numbers of
not-yet-archived segment files, 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 library, you should assume that the file 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
file name.

Note that although WAL archiving will allow you to restore any
modifications made to the data in your PostgreSQL database, it will not
restore changes made to configuration files (that is, `postgresql.conf`,
`pg_hba.conf` and `pg_ident.conf`), since those are edited manually
rather than through SQL operations. You might wish to keep the
configuration files in a location that will be backed up by your regular
file system backup procedures. See [???](#runtime-config-file-locations)
for how to relocate the configuration files.

The archive function is only invoked on completed WAL segments. Hence,
if your server generates only little WAL traffic (or has slack periods
where it 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 unarchived data can be, you can set
[???](#guc-archive-timeout) to force the server to switch to a new WAL
segment file at least that often. Note that archived files that are
archived early due to a forced switch are still the same length as
completely full files. It is therefore unwise to set a very short
`archive_timeout` MDASH it will bloat your archive storage.
`archive_timeout` settings of a minute or so are usually reasonable.

Also, you can force a segment switch manually with `pg_switch_wal` if
you want to ensure that a just-finished transaction is archived as soon
as possible. Other utility functions related to WAL management are
listed in [???](#functions-admin-backup-table).

When `wal_level` is `minimal` some SQL commands are optimized to avoid
WAL logging, as described in [???](#populate-pitr). 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, `wal_level`
can only be changed at server start. However, `archive_library` can be
changed with a configuration file reload. If you are archiving via shell
and wish to temporarily stop archiving, one way to do it is to set
`archive_command` to the empty string (`''`). This will cause WAL files
to accumulate in `pg_wal/` until a working `archive_command` is
re-established.

Making a Base Backup {#backup-base-backup}
--------------------

The easiest way to perform a base backup is to use the
[???](#app-pgbasebackup) tool. It can create a base backup either as
regular files or as a tar archive. If more flexibility than
[???](#app-pgbasebackup) can provide is required, you can also make a
base backup using the low level API (see [Making a Base Backup Using the
Low Level API](#backup-lowlevel-base-backup)).

It is not necessary to be concerned about the amount of time it takes to
make a base backup. However, if you normally run the server with
`full_page_writes` disabled, you might notice a drop in performance
while the backup runs since `full_page_writes` is effectively forced on
during backup mode.

To make use of the backup, you will need to keep all the WAL segment
files generated during and after the file system backup. To aid you in
doing this, the base backup process 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 for the file system
backup. For example, if the starting WAL file is
`0000000100001234000055CD` the backup history file will be named
something like `0000000100001234000055CD.007C9330.backup`. (The second
part of 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 can 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 [???](#app-pgbasebackup), as well as the starting and
ending times and WAL segments of the backup. If you used the label to
identify the associated dump file, then the archived history file is
enough to tell you which dump file to restore.

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 MDASH 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.

Making a Base Backup Using the Low Level API {#backup-lowlevel-base-backup}
--------------------------------------------

The procedure for making a base backup using the low level APIs contains
a few more steps than the [???](#app-pgbasebackup) method, but is
relatively simple. It is very important that these steps are executed in
sequence, and that the success of a step is verified before proceeding
to the next step.

Multiple backups are able to be run concurrently (both those started
using this backup API and those started using [???](#app-pgbasebackup)).

1.  Ensure that WAL archiving is enabled and working.

2.  Connect to the server (it does not matter which database) as a user
    with rights to run pg\_backup\_start (superuser, or a user who has
    been granted EXECUTE on the function) and issue the command:

        SELECT pg_backup_start(label => 'label', fast => false);

    where `label` is any string you want to use to uniquely identify
    this backup operation. The connection calling `pg_backup_start` must
    be maintained until the end of the backup, or the backup will be
    automatically aborted.

    Online backups are always started at the beginning of a checkpoint.
    By default, `pg_backup_start` will wait for the next regularly
    scheduled checkpoint to complete, which may take a long time (see
    the configuration parameters [???](#guc-checkpoint-timeout) and
    [???](#guc-checkpoint-completion-target)). This is usually
    preferable as it minimizes the impact on the running system. If you
    want to start the backup as soon as possible, pass `true` as the
    second parameter to `pg_backup_start` and it will request an
    immediate checkpoint, which will finish as fast as possible using as
    much I/O as possible.

3.  Perform the backup, using any convenient file-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. See [Backing Up the Data
    Directory](#backup-lowlevel-base-backup-data) for things to consider
    during this backup.

4.  In the same connection as before, issue the command:

        SELECT * FROM pg_backup_stop(wait_for_archive => true);

    This terminates backup mode. On a primary, it also performs an
    automatic switch to the next WAL segment. On a standby, it is not
    possible to automatically switch WAL segments, so you may wish to
    run `pg_switch_wal` on the primary to perform a manual switch. The
    reason for the switch is to arrange for the last WAL segment file
    written during the backup interval to be ready to archive.

    `pg_backup_stop` will return one row with three values. The second
    of these fields should be written to a file named `backup_label` in
    the root directory of the backup. The third field should be written
    to a file named `tablespace_map` unless the field is empty. These
    files are vital to the backup working and must be written byte for
    byte without modification, which may require opening the file in
    binary mode.

5.  Once the WAL segment files active during the backup are archived,
    you are done. The file identified by `pg_backup_stop`\'s first
    return value is the last segment that is required to form a complete
    set of backup files. On a primary, if `archive_mode` is enabled and
    the `wait_for_archive` parameter is `true`, `pg_backup_stop` does
    not return until the last segment has been archived. On a standby,
    `archive_mode` must be `always` in order for `pg_backup_stop` to
    wait. Archiving of these files happens automatically since you have
    already configured `archive_library`. In most cases this happens
    quickly, but you are advised to monitor your archive system to
    ensure there are no delays. If the archive process has fallen behind
    because of failures of the archive library, 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 `pg_backup_stop`, set an
    appropriate `statement_timeout` value, but make note that if
    `pg_backup_stop` terminates because of this your backup may not be
    valid.

    If the backup process monitors and ensures that all WAL segment
    files required for the backup are successfully archived then the
    `wait_for_archive` parameter (which defaults to true) can be set to
    false to have `pg_backup_stop` return as soon as the stop backup
    record is written to the WAL. By default, `pg_backup_stop` will wait
    until all WAL has been archived, which can take some time. This
    option must be used with caution: if WAL archiving is not monitored
    correctly then the backup might not include all of the WAL files and
    will therefore be incomplete and not able to be restored.

### Backing Up the Data Directory {#backup-lowlevel-base-backup-data}

Some file system backup tools emit warnings or errors if the files they
are trying to copy 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 files", 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 file was truncated while tar was copying it. Fortunately, GNU
tar versions 1.16 and later exit with 1 if a file was changed during the
backup, and 2 for other errors. With GNU tar version 1.23 and later, you
can use the warning options `--warning=no-file-changed
    --warning=no-file-removed` to hide the related warning messages.

Be certain that your backup includes all of the files under 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 archives symbolic
links as links, otherwise the restore will corrupt your tablespaces).

You should, however, omit from the backup the files within the
cluster\'s `pg_wal/` subdirectory. This slight adjustment is worthwhile
because it reduces the risk of mistakes when restoring. This is easy to
arrange if `pg_wal/` is a symbolic link pointing to someplace outside
the cluster directory, which is a common setup anyway for performance
reasons. You might also want to exclude `postmaster.pid` and
`postmaster.opts`, which record information about the running
postmaster, not about the postmaster which will eventually use this
backup. (These files can confuse pg\_ctl.)

It is often a good idea to also omit from the backup the files within
the cluster\'s `pg_replslot/` directory, so that replication slots that
exist on the primary do not become part of the backup. Otherwise, the
subsequent use of the backup to create a standby may result in
indefinite retention of WAL files on the standby, and possibly bloat on
the primary 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 primary, not the standby. Even if the backup
is only intended for use in creating a new primary, copying the
replication slots isn\'t expected to be particularly useful, since the
contents of those slots will likely be badly out of date by the time the
new primary comes on line.

The contents of the directories `pg_dynshmem/`, `pg_notify/`,
`pg_serial/`, `pg_snapshots/`, `pg_stat_tmp/`, and `pg_subtrans/` (but
not the directories themselves) can be omitted from the backup as they
will be initialized on postmaster startup.

Any file or directory beginning with `pgsql_tmp` can be omitted from the
backup. These files are removed on postmaster start and the directories
will be recreated as needed.

`pg_internal.init` files can be omitted from the backup whenever a file
of that name is found. These files contain relation cache data that is
always rebuilt when recovering.

The backup label file includes the label string you gave to
`pg_backup_start`, as well as the time at which `pg_backup_start` was
run, and the name of the starting WAL file. In case of confusion it is
therefore possible to look inside a backup file and determine exactly
which backup session the dump file came from. The tablespace map file
includes the symbolic link names as they exist in the directory
`pg_tblspc/` and the full path of each symbolic link. These files are
not merely for your information; their presence and contents are
critical to the proper operation of the system\'s recovery process.

It is also possible to make a backup while the server is stopped. In
this case, you obviously cannot use `pg_backup_start` or
`pg_backup_stop`, and you will therefore be left to your own devices to
keep track of which backup is which and how far back the associated WAL
files go. It is generally better to follow the continuous archiving
procedure above.

Recovering Using a Continuous Archive Backup {#backup-pitr-recovery}
--------------------------------------------

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
    have 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_wal` subdirectory, as
    it might 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 right ownership (the database system
    user, not `root`!) and with the right permissions. If you are using
    tablespaces, you should verify that the symbolic links in
    `pg_tblspc/` were correctly restored.

5.  Remove any files present in `pg_wal/`; these came from the file
    system backup and are therefore probably obsolete rather than
    current. If you didn\'t archive `pg_wal/` at all, then recreate it
    with 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 files that you saved in step 2,
    copy them into `pg_wal/`. (It is best to copy them, not move them,
    so you still have the unmodified files if a problem occurs and you
    have to start over.)

7.  Set recovery configuration settings in `postgresql.conf` (see
    [???](#runtime-config-wal-archive-recovery)) and create a file
    `recovery.signal` in the cluster data directory. You might also want
    to temporarily modify `pg_hba.conf` to prevent ordinary users from
    connecting 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 files 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 remove `recovery.signal` (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 desired state. If not, return to step 1. If all is well, allow
    your users to connect by restoring `pg_hba.conf` to normal.

The key part of all this is to set up a recovery configuration that
describes how you want to recover and how far the recovery should run.
The one thing that you absolutely must specify is the `restore_command`,
which tells PostgreSQL how to retrieve archived WAL file segments. Like
the `archive_command`, this is a shell command string. It can contain
`%f`, which is replaced by the name of the desired log file, and `%p`,
which is replaced by the path name to copy the log file to. (The path
name is relative to the current working directory, i.e., the cluster\'s
data directory.) Write `%%` 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 will copy previously archived WAL segments from the directory
`/mnt/server/archivedir`. 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 files 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.

Not all of the requested files will be WAL segment files; you should
also expect requests for files with a suffix of `.history`. Also be
aware that the base name of the `%p` path will be different from `%f`;
do not expect them to be interchangeable.

WAL segments that cannot be found in the archive will be sought in
`pg_wal/`; this allows use of recent un-archived segments. However,
segments that are available from the archive will be used in preference
to files in `pg_wal/`.

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 "file not found" message, the exact text of the
error message depending upon your choice of `restore_command`. You may
also see an error message at the start of recovery for a file named
something like `00000001.history`. This is also normal and does not
indicate a problem in simple recovery situations; see
[Timelines](#backup-timelines) 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](#runtime-config-wal-recovery-target). You can
specify the stop point, known as the "recovery target", either by
date/time, named restore point or by completion of a specific
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 `pg_backup_stop`. 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 finds 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 corruption so that recovery can 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 the
`pg_control` file to indicate that the already-processed WAL data need
not be scanned again.

Timelines {#backup-timelines}
---------

timelines

The ability to restore the database to a previous point in time creates
some complexities that are akin to science-fiction stories about time
travel and parallel universes. For example, in the original history of
the database, suppose you dropped a critical table at 5:15PM on Tuesday
evening, but didn\'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 to return to sometime
Wednesday morning in the original history. You won\'t be able to if,
while your database was up-and-running, it overwrote some of the WAL
segment files 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 database history.

To deal with this problem, PostgreSQL has a notion of timelines.
Whenever an archive recovery completes, 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 file names so a new timeline
does not overwrite the WAL data generated by previous timelines. It is
in fact possible to archive many different timelines. While that might
seem like a useless feature, it\'s often a lifesaver. Consider the
situation where you aren\'t quite sure what point-in-time to recover to,
and so have to do several point-in-time recoveries by trial and error
until you find the best place to branch off from the old history.
Without timelines this process would soon generate an unmanageable mess.
With timelines, you can recover to *any* prior state, including states
in timeline branches that you abandoned earlier.

Every time a new timeline is created, PostgreSQL creates a "timeline
history" file that shows which timeline it branched off from and when.
These history files are necessary to allow the system to pick the right
WAL segment files when recovering from an archive that contains multiple
timelines. Therefore, they are archived into the WAL archive area just
like WAL segment files. The history files are just small text files, so
it\'s cheap and appropriate to keep them around indefinitely (unlike the
segment files which are large). You can, if you like, add comments to a
history file to record your own notes about how and why this particular
timeline was created. Such comments will be especially valuable when you
have a thicket of different timelines as a result of experimentation.

The default behavior of recovery is to recover to the latest timeline
found in the archive. If you wish to recover to the timeline that was
current when the base backup was taken or into a specific child timeline
(that is, you want to return to some state that was itself generated
after a recovery attempt), you need to specify `current` or the target
timeline ID in [???](#guc-recovery-target-timeline). You cannot recover
into timelines that branched off earlier than the base backup.

Tips and Examples {#backup-tips}
-----------------

Some tips for configuring continuous archiving are given here.

### Standalone Hot Backups {#backup-standalone}

It is possible to use PostgreSQL\'s backup facilities to produce
standalone hot backups. These are backups that cannot be used for
point-in-time recovery, yet are typically much faster to backup and
restore than pg\_dump dumps. (They are also much larger than pg\_dump
dumps, so in some cases the speed advantage might be negated.)

As with base backups, the easiest way to produce a standalone hot backup
is to use the [???](#app-pgbasebackup) tool. If you include the `-X`
parameter when calling it, all the write-ahead log required to use the
backup will be included in the backup automatically, and no special
action is required to restore the backup.

### Compressed Archive Logs

If archive storage size is a concern, you can use gzip to compress the
archive files:

    archive_command = 'gzip < %p > /mnt/server/archivedir/%f.gz'

You will then need to use gunzip during recovery:

    restore_command = 'gunzip < /mnt/server/archivedir/%f.gz > %p'

### `archive_command` Scripts {#backup-scripts}

Many people choose to use scripts to define their `archive_command`, so
that their `postgresql.conf` entry looks very simple:

    archive_command = 'local_backup_script.sh "%p" "%f"'

Using a separate script file is advisable any time you want to use more
than a single command in the archiving process. This allows all
complexity to be managed within the script, which can be written in a
popular scripting language such as bash or perl.

Examples of requirements that might be solved within a script include:

-   Copying data to secure off-site data storage

-   Batching WAL files so that they are transferred every three hours,
    rather than one at a time

-   Interfacing with other backup and recovery software

-   Interfacing with monitoring software to report errors

> **Tip**
>
> When using an `archive_command` script, it\'s desirable to enable
> [???](#guc-logging-collector). Any messages written to stderr from the
> script will then appear in the database server log, allowing complex
> configurations to be diagnosed easily if they fail.

Caveats {#continuous-archiving-caveats}
-------

At this writing, there are several limitations of the continuous
archiving technique. These will probably be fixed in future releases:

-   If a [`CREATE DATABASE`](#sql-createdatabase) command is executed
    while a base backup is being taken, and then the template database
    that the `CREATE DATABASE` copied is modified while the base backup
    is still in progress, it is possible that recovery will cause those
    modifications to be propagated into the created database as well.
    This is of course undesirable. To avoid this risk, it is best not to
    modify any template databases while taking a base backup.

-   [`CREATE TABLESPACE`](#sql-createtablespace) commands are WAL-logged
    with the literal absolute path, and will therefore be replayed as
    tablespace creations with the same absolute path. This might be
    undesirable if the log is being replayed on a different machine. It
    can be dangerous even if the log is being replayed on the same
    machine, but into a new data directory: the replay will still
    overwrite the contents of the original tablespace. To avoid
    potential gotchas of this sort, the best practice is to take a new
    base backup after creating or dropping tablespaces.

It should also be noted that the default WAL format is fairly bulky
since it includes many disk page snapshots. These page snapshots are
designed to support crash recovery, since we might need to fix
partially-written disk pages. Depending on your system hardware and
software, the risk of partial writes might be small enough to ignore, in
which case you can significantly reduce the total volume of archived
logs by turning off page snapshots using the
[???](#guc-full-page-writes) parameter. (Read the notes and warnings in
[???](#wal) before you do so.) Turning off page snapshots does not
prevent use of the logs for PITR operations. An area for future
development is to compress archived WAL data by removing unnecessary
page copies even when `full_page_writes` is on. In the meantime,
administrators might wish to reduce the number of page snapshots
included in WAL by increasing the checkpoint interval parameters as much
as feasible.
