Table of Contents
1
Introduction
This user guide is intended to be followed sequentially from beginning to end — each section depends on the last. For example, the Backup section relies on setup that is performed in the Quick Start section. Once pgBackRest is up and running then skipping around is possible but following the user guide in order is recommended the first time through.
Although the examples are targeted at Debian/Ubuntu and PostgreSQL 9.4, it should be fairly easy to apply this guide to any Unix distribution and PostgreSQL version. Note that only 64-bit distributions are currently supported due to 64-bit operations in the Perl code. The only OS-specific commands are those to create, start, stop, and drop PostgreSQL clusters. The pgBackRest commands will be the same on any Unix system though the locations to install Perl libraries and executables may vary.

Configuration information and documentation for PostgreSQL can be found in the PostgreSQL Manual.
A somewhat novel approach is taken to documentation in this user guide. Each command is run on a virtual machine when the documentation is built from the XML source. This means you can have a high confidence that the commands work correctly in the order presented. Output is captured and displayed below the command when appropriate. If the output is not included it is because it was deemed not relevant or was considered a distraction from the narrative.
All commands are intended to be run as an unprivileged user that has sudo privileges for both the root and postgres users. It's also possible to run the commands directly as their respective users without modification and in that case the sudo commands can be stripped off.
2
Concepts
The following concepts are defined as they are relevant to pgBackRest, PostgreSQL, and this user guide.
2.1
Backup
A backup is a consistent copy of a database cluster that can be restored to recover from a hardware failure, to perform Point-In-Time Recovery, or to bring up a new standby.
Full Backup: pgBackRest copies the entire contents of the database cluster to the backup server. The first backup of the database cluster is always a Full Backup. pgBackRest is always able to restore a full backup directly. The full backup does not depend on any files outside of the full backup for consistency.
Differential Backup: pgBackRest copies only those database cluster files that have changed since the last full backup. pgBackRest restores a differential backup by copying all of the files in the chosen differential backup and the appropriate unchanged files from the previous full backup. The advantage of a differential backup is that it requires less disk space than a full backup, however, the differential backup and the full backup must both be valid to restore the differential backup.
Incremental Backup: pgBackRest copies only those database cluster files that have changed since the last backup (which can be another incremental backup, a differential backup, or a full backup). As an incremental backup only includes those files changed since the prior backup, they are generally much smaller than full or differential backups. As with the differential backup, the incremental backup depends on other backups to be valid to restore the incremental backup. Since the incremental backup includes only those files since the last backup, all prior incremental backups back to the prior differential, the prior differential backup, and the prior full backup must all be valid to perform a restore of the incremental backup. If no differential backup exists then all prior incremental backups back to the prior full backup, which must exist, and the full backup itself must be valid to restore the incremental backup.
2.2
Restore
A restore is the act of copying a backup to a system where it will be started as a live database cluster. A restore requires the backup files and one or more WAL segments in order to work correctly.
2.3
Write Ahead Log (WAL)
WAL is the mechanism that PostgreSQL uses to ensure that no committed changes are lost. Transactions are written sequentially to the WAL and a transaction is considered to be committed when those writes are flushed to disk. Afterwards, a background process writes the changes into the main database cluster files (also known as the heap). In the event of a crash, the WAL is replayed to make the database consistent.
WAL is conceptually infinite but in practice is broken up into individual 16MB files called segments. WAL segments follow the naming convention 0000000100000A1E000000FE where the first 8 hexadecimal digits represent the timeline and the next 16 digits are the logical sequence number (LSN).
3
Installation
A new host named db-primary is created to contain the demo cluster and run pgBackRest examples.
If pgBackRest has been installed before it's best to be sure that no prior copies of it are still installed. Depending on how old the version of pgBackRest is it may have been installed in a few different locations. The following commands will remove all prior versions of pgBackRest.
db-primary Remove prior pgBackRest installations
sudo rm -f /usr/bin/pgbackrest
sudo rm -f /usr/bin/pg_backrest
sudo rm -rf /usr/lib/perl5/BackRest
sudo rm -rf /usr/share/perl5/BackRest
sudo rm -rf /usr/lib/perl5/pgBackRest
sudo rm -rf /usr/share/perl5/pgBackRest
pgBackRest is written in Perl which is included with Debian/Ubuntu by default. Some additional modules must also be installed but they are available as standard packages.
db-primary Install required Perl packages
sudo apt-get install libdbd-pg-perl libio-socket-ssl-perl libxml-libxml-perl
Debian/Ubuntu packages for pgBackRest are available at apt.postgresql.org. If they are not provided for your distribution/version it is easy to download the source and install manually.
db-primary Download version 1.24 of pgBackRest
sudo wget -q -O - \
       https://github.com/pgbackrest/pgbackrest/archive/release/1.24.tar.gz | \
       sudo tar zx -C /root
db-primary Install pgBackRest
sudo cp -r /root/pgbackrest-release-1.24/lib/pgBackRest \
       /usr/share/perl5
sudo find /usr/share/perl5/pgBackRest -type f -exec chmod 644 {} +
sudo find /usr/share/perl5/pgBackRest -type d -exec chmod 755 {} +
sudo cp /root/pgbackrest-release-1.24/bin/pgbackrest /usr/bin/pgbackrest
sudo chmod 755 /usr/bin/pgbackrest
sudo mkdir -m 770 /var/log/pgbackrest
sudo chown postgres:postgres /var/log/pgbackrest
sudo touch /etc/pgbackrest.conf
sudo chmod 640 /etc/pgbackrest.conf
sudo chown postgres:postgres /etc/pgbackrest.conf
pgBackRest should now be properly installed but it is best to check. If any dependencies were missed then you will get an error when running pgBackRest from the command line.
db-primary Make sure the installation worked
sudo -u postgres pgbackrest
pgBackRest 1.24 - General help

Usage:
    pgbackrest [options] [command]

Commands:
    archive-get     Get a WAL segment from the archive.
    archive-push    Push a WAL segment to the archive.
    backup          Backup a database cluster.
    check           Check the configuration.
    expire          Expire backups that exceed retention.
    help            Get help.
    info            Retrieve information about backups.
    restore         Restore a database cluster.
    stanza-create   Create the required stanza data.
    stanza-upgrade  Upgrade a stanza.
    start           Allow pgBackRest processes to run.
    stop            Stop pgBackRest processes from running.
    version         Get version.

Use 'pgbackrest help [command]' for more information.
4
Quick Start
The Quick Start section will cover basic configuration of pgBackRest and PostgreSQL and introduce the backup, restore, and info commands.
4.1
Setup Demo Cluster
Creating the demo cluster is optional but is strongly recommended, especially for new users, since the example commands in the user guide reference the demo cluster; the examples assume the demo cluster is running on the default port (i.e. 5432). The cluster will not be started until a later section because there is still some configuration to do.
db-primary Create the demo cluster
sudo -u postgres /usr/lib/postgresql/9.4/bin/initdb \
       -D /var/lib/postgresql/9.4/demo -k -A peer
sudo pg_createcluster 9.4 demo
Configuring already existing cluster (configuration: /etc/postgresql/9.4/demo, data: /var/lib/postgresql/9.4/demo, owner: 5000:5000)
Ver Cluster Port Status Owner    Data directory               Log file
9.4 demo    5432 down   postgres /var/lib/postgresql/9.4/demo /var/log/postgresql/postgresql-9.4-demo.log
By default PostgreSQL will only accept local connections. The examples in this guide will require connections from other servers so listen_addresses is configured to listen on all interfaces. This may not be appropriate for secure installations.
db-primary:/etc/postgresql/9.4/demo/postgresql.conf Set listen_addresses
listen_addresses = '*'
For demonstration purposes the log_line_prefix setting will be minimally configured. This keeps the log output as brief as possible to better illustrate important information.
db-primary:/etc/postgresql/9.4/demo/postgresql.conf Set log_line_prefix
listen_addresses = '*'
log_line_prefix = ''
4.2
Configure Cluster Stanza
A stanza is the configuration for a PostgreSQL database cluster that defines where it is located, how it will be backed up, archiving options, etc. Most db servers will only have one Postgres database cluster and therefore one stanza, whereas backup servers will have a stanza for every database cluster that needs to be backed up.

It is tempting to name the stanza after the primary cluster but a better name describes the databases contained in the cluster. Because the stanza name will be used for the primary and all replicas it is more appropriate to choose a name that describes the actual function of the cluster, such as app or dw, rather than the local cluster name, such as main or prod.
The name 'demo' describes the purpose of this cluster accurately so that will also make a good stanza name.
pgBackRest needs to know where the base data directory for the PostgreSQL cluster is located. The path can be requested from PostgreSQL directly but in a recovery scenario the PostgreSQL process will not be available. During backups the value supplied to pgBackRest will be compared against the path that PostgreSQL is running on and they must be equal or the backup will return an error. Make sure that db-path is exactly equal to data_directory in postgresql.conf.
By default Debian/Ubuntu stores clusters in /var/lib/postgresql/[version]/[cluster] so it is easy to determine the correct path for the data directory.
When creating the /etc/pgbackrest.conf file, the database owner (usually postgres) must be granted read privileges.
db-primary:/etc/pgbackrest.conf Configure the PostgreSQL cluster data directory
[demo]
db-path=/var/lib/postgresql/9.4/demo
pgBackRest configuration files follow the Windows INI convention. Sections are denoted by text in brackets and key/value pairs are contained in each section. Lines beginning with # are ignored and can be used as comments.
4.3
Create the Repository
The repository is where pgBackRest stores backup and archives WAL segments.

If you are new to backup then it will be difficult to estimate in advance how much space you'll need. The best thing to do is take some backups then record the size of different types of backups (full/incr/diff) and measure the amount of WAL generated per day. This will give you a general idea of how much space you'll need, though of course requirements will likely change over time as your database evolves.
For this demonstration the repository will be stored on the same host as the PostgreSQL server. This is the simplest configuration and is useful in cases where traditional backup software is employed to backup the database host.
db-primary Create the pgBackRest repository
sudo mkdir /var/lib/pgbackrest
sudo chmod 750 /var/lib/pgbackrest
sudo chown postgres:postgres /var/lib/pgbackrest
The repository path must be configured so pgBackRest knows where to find it.
db-primary:/etc/pgbackrest.conf Configure the pgBackRest repository path
[demo]
db-path=/var/lib/postgresql/9.4/demo

[global]
repo-path=/var/lib/pgbackrest
4.4
Configure Archiving
Backing up a running PostgreSQL cluster requires WAL archiving to be enabled. Note that at least one WAL segment will be created during the backup process even if no explicit writes are made to the cluster.
db-primary:/etc/postgresql/9.4/demo/postgresql.conf Configure archive settings
archive_command = 'pgbackrest --stanza=demo archive-push %p'
archive_mode = on
listen_addresses = '*'
log_line_prefix = ''
max_wal_senders = 3
wal_level = hot_standby
The wal_level setting must be set to archive at a minimum but hot_standby and logical also work fine for backups. Setting wal_level to hot_standy and increasing max_wal_senders is a good idea even if you do not currently run a hot standby as this will allow them to be added later without restarting the primary cluster.
The PostgreSQL cluster must be restarted after making these changes and before performing a backup.
db-primary Restart the demo cluster
sudo pg_ctlcluster 9.4 demo restart
When archiving a WAL segment is expected to take more than 60 seconds (the default) then the archive-timeout option should be increased.
4.5
Configure Retention
pgBackRest expires backups based on retention options.
db-primary:/etc/pgbackrest.conf Configure retention to 2 full backups
[demo]
db-path=/var/lib/postgresql/9.4/demo

[global]
repo-path=/var/lib/pgbackrest
retention-full=2
More information about retention can be found in the Retention section.
4.6
Create the Stanza
The stanza-create command must be run on the host where the repository is located to initialize the stanza. It is recommended that the check command be run after stanza-create to ensure archiving and backups are properly configured.
db-primary Create the stanza and check the configuration
sudo -u postgres pgbackrest --stanza=demo --log-level-console=info stanza-create
P00   INFO: stanza-create command begin 1.24: --db1-path=/var/lib/postgresql/9.4/demo --log-level-console=info --no-log-timestamp --repo-path=/var/lib/pgbackrest --stanza=demo
P00   INFO: stanza-create command end: completed successfully
4.7
Check the Configuration
The check command validates that pgBackRest and the archive_command setting are configured correctly for archiving and backups. It detects misconfigurations, particularly in archiving, that result in incomplete backups because required WAL segments did not reach the archive. The command can be run on the database or the backup host. The command may also be run on the standby host, however, since pg_switch_xlog()/pg_switch_wal() cannot be performed on the standby, the command will only test the repository configuration.

Note that pg_create_restore_point('pgBackRest Archive Check') and pg_switch_xlog()/pg_switch_wal() are called to force PostgreSQL to archive a WAL segment. Restore points are only supported in PostgreSQL >= 9.1 so for older versions the check command may fail if there has been no write activity since the last log rotation, therefore it is recommended that activity be generated by the user if there have been no writes since the last WAL switch before running the check command.
db-primary Check the configuration
sudo -u postgres pgbackrest --stanza=demo --log-level-console=info check
P00   INFO: check command begin 1.24: --db1-path=/var/lib/postgresql/9.4/demo --log-level-console=info --log-level-stderr=off --no-log-timestamp --repo-path=/var/lib/pgbackrest --stanza=demo
P00   INFO: WAL segment 000000010000000000000001 successfully stored in the archive at '/var/lib/pgbackrest/archive/demo/9.4-1/0000000100000000/000000010000000000000001-54c49dc8193b3e3bacead5fd3e519383d774edb6.gz'
P00   INFO: check command end: completed successfully
4.8
Perform a Backup
To perform a backup of the PostgreSQL cluster run pgBackRest with the backup command.
db-primary Backup the demo cluster
sudo -u postgres pgbackrest --stanza=demo \
       --log-level-console=info backup
P00   INFO: backup command begin 1.24: --db1-path=/var/lib/postgresql/9.4/demo --log-level-console=info --log-level-stderr=off --no-log-timestamp --repo-path=/var/lib/pgbackrest --retention-full=2 --stanza=demo
P00   WARN: no prior backup exists, incr backup has been changed to full
P00   INFO: execute exclusive pg_start_backup() with label "pgBackRest backup started at 2017-09-28 22:56:08": backup begins after the next regular checkpoint completes
P00   INFO: backup start archive = 000000010000000000000002, lsn = 0/2000028
       [filtered 754 lines of output]
P01   INFO: backup file /var/lib/postgresql/9.4/demo/base/1/11895 (0B, 100%)
P01   INFO: backup file /var/lib/postgresql/9.4/demo/base/1/11885 (0B, 100%)
P00   INFO: full backup size = 19.2MB
P00   INFO: execute exclusive pg_stop_backup() and wait for all WAL segments to archive
P00   INFO: backup stop archive = 000000010000000000000002, lsn = 0/20000F0
       [filtered 4 lines of output]
By default pgBackRest will attempt to perform an incremental backup. However, an incremental backup must be based on a full backup and since no full backup existed pgBackRest ran a full backup instead.
The type option can be used to specify a full or differential backup.
db-primary Differential backup of the demo cluster
sudo -u postgres pgbackrest --stanza=demo --type=diff \
       --log-level-console=info backup
       [filtered 4 lines of output]
P01   INFO: backup file /var/lib/postgresql/9.4/demo/global/pg_control (8KB, 97%) checksum 780487f8a1a001ee40192bec4c7c8bc2f2d7c21a
P01   INFO: backup file /var/lib/postgresql/9.4/demo/backup_label (236B, 100%) checksum 372012b196a890f28d401a5723dd92bbe4735146
P00   INFO: diff backup size = 8.2KB
P00   INFO: execute exclusive pg_stop_backup() and wait for all WAL segments to archive
P00   INFO: backup stop archive = 000000010000000000000003, lsn = 0/3000128
       [filtered 4 lines of output]
This time there was no warning because a full backup already existed. While incremental backups can be based on a full or differential backup, differential backups must be based on a full backup. A full backup can be performed by running the backup command with --type=full.
More information about the backup command can be found in the Backup section.
4.9
Schedule a Backup
Backups can be scheduled with utilities such as cron.
In the following example, two cron jobs are configured to run; full backups are scheduled for 6:30 AM every Sunday with differential backups scheduled for 6:30 AM Monday through Saturday. If this crontab is installed for the first time mid-week, then pgBackRest will run a full backup the first time the differential job is executed, followed the next day by a differential backup.
#m h   dom mon dow   command
30 06  *   *   0     pgbackrest --type=full --stanza=demo backup
30 06  *   *   1-6   pgbackrest --type=diff --stanza=demo backup
Once backups are scheduled it's important to configure retention so backups are expired on a regular schedule, see Retention.
4.10
Backup Information
Use the info command to get information about backups.
db-primary Get info for the demo cluster
sudo -u postgres pgbackrest info
stanza: demo
    status: ok

    db (current)
        wal archive min/max (9.4-1): 000000010000000000000002 / 000000010000000000000003
        full backup: 20170928-225608F
            timestamp start/stop: 2017-09-28 22:56:08 / 2017-09-28 22:56:13
            wal start/stop: 000000010000000000000002 / 000000010000000000000002
            database size: 19.2MB, backup size: 19.2MB
            repository size: 2.2MB, repository backup size: 2.2MB
        diff backup: 20170928-225608F_20170928-225614D
            timestamp start/stop: 2017-09-28 22:56:14 / 2017-09-28 22:56:17
            wal start/stop: 000000010000000000000003 / 000000010000000000000003
            database size: 19.2MB, backup size: 8.2KB
            repository size: 2.2MB, repository backup size: 345B
            backup reference list: 20170928-225608F
Each stanza has a separate section and it is possible to limit output to a single stanza with the --stanza option. The stanza 'status' gives a brief indication of the stanza's health. If this is 'ok' then pgBackRest is functioning normally. The 'wal archive min/max' shows the minimum and maximum WAL currently stored in the archive. Note that there may be gaps due to archive retention policies or other reasons.
The backups are displayed oldest to newest. The oldest backup will always be a full backup (indicated by an F at the end of the label) but the newest backup can be full, differential (ends with D), or incremental (ends with I).
The 'timestamp start/stop' defines the time period when the backup ran. The 'timestamp stop' can be used to determine the backup to use when performing Point-In-Time Recovery. More information about Point-In-Time Recovery can be found in the Point-In-Time Recovery section.
The 'wal start/stop' defines the WAL range that is required to make the database consistent when restoring. The backup command will ensure that this WAL range is in the archive before completing.
The 'database size' is the full uncompressed size of the database while 'backup size' is the amount of data actually backed up (these will be the same for full backups). The 'repository size' includes all the files from this backup and any referenced backups that are required to restore the database while 'repository backup size' includes only the files in this backup (these will also be the same for full backups). Repository sizes reflect compressed file sizes if compression is enabled in pgBackRest or the filesystem.
The 'backup reference list' contains the additional backups that are required to restore this backup.
4.11
Restore a Backup
Backups can protect you from a number of disaster scenarios, the most common of which are hardware failure and data corruption. The easiest way to simulate data corruption is to remove an important PostgreSQL cluster file.
db-primary Stop the demo cluster and delete the pg_control file
sudo pg_ctlcluster 9.4 demo stop
sudo -u postgres rm /var/lib/postgresql/9.4/demo/global/pg_control
Starting the cluster without this important file will result in an error.
db-primary Attempt to start the corrupted demo cluster
sudo pg_ctlcluster 9.4 demo start
The PostgreSQL server failed to start. Please check the log output:
postgres: could not find the database system
Expected to find it in the directory "/var/lib/postgresql/9.4/demo",
but could not open file "/var/lib/postgresql/9.4/demo/global/pg_control": No such file or directory
To restore a backup of the PostgreSQL cluster run pgBackRest with the restore command. The cluster needs to be stopped (in this case it is already stopped) and all files must be removed from the PostgreSQL data directory.
db-primary Remove old files from demo cluster
sudo -u postgres find /var/lib/postgresql/9.4/demo -mindepth 1 -delete
db-primary Restore the demo cluster and start PostgreSQL
sudo -u postgres pgbackrest --stanza=demo restore
sudo pg_ctlcluster 9.4 demo start
This time the cluster started successfully since the restore replaced the missing pg_control file.
More information about the restore command can be found in the Restore section.
5
Backup
The Backup section introduces additional backup command features.
5.1
Fast Start Option
By default pgBackRest will wait for the next regularly scheduled checkpoint before starting a backup. Depending on the checkpoint_timeout and checkpoint_segments settings in PostgreSQL it may be quite some time before a checkpoint completes and the backup can begin.
db-primary Incremental backup of the demo cluster with the regularly scheduled checkpoint
sudo -u postgres pgbackrest --stanza=demo --type=incr \
       --log-level-console=info backup
P00   INFO: backup command begin 1.24: --db1-path=/var/lib/postgresql/9.4/demo --log-level-console=info --log-level-stderr=off --no-log-timestamp --repo-path=/var/lib/pgbackrest --retention-full=2 --stanza=demo --type=incr
P00   INFO: last backup label = 20170928-225608F_20170928-225614D, version = 1.24
P00   INFO: execute exclusive pg_start_backup() with label "pgBackRest backup started at 2017-09-28 22:56:28": backup begins after the next regular checkpoint completes
P00   INFO: backup start archive = 000000020000000000000005, lsn = 0/5000028
P01   INFO: backup file /var/lib/postgresql/9.4/demo/pg_multixact/offsets/0000 (8KB, 33%) checksum 0631457264ff7f8d5fb1edc2c0211992a67c73e6
       [filtered 10 lines of output]
When --start-fast is passed on the command-line or start-fast=y is set in /etc/pgbackrest.conf an immediate checkpoint is requested and the backup will start more quickly. This is convenient for testing and for ad-hoc backups. For instance, if a backup is being taken at the beginning of a release window it makes no sense to wait for a checkpoint. Since regularly scheduled backups generally only happen once per day it is unlikely that enabling the start-fast in /etc/pgbackrest.conf will negatively affect performance, however for high-volume transactional systems you may want to pass --start-fast on the command-line instead. Alternately, it is possible to override the setting in the configuration file by passing --no-start-fast on the command-line.
db-primary:/etc/pgbackrest.conf Enable the start-fast option
[demo]
db-path=/var/lib/postgresql/9.4/demo

[global]
repo-path=/var/lib/pgbackrest
retention-full=2
start-fast=y
db-primary Incremental backup of the demo cluster with an immediate checkpoint
sudo -u postgres pgbackrest --stanza=demo --type=incr \
       --log-level-console=info backup
P00   INFO: backup command begin 1.24: --db1-path=/var/lib/postgresql/9.4/demo --log-level-console=info --log-level-stderr=off --no-log-timestamp --repo-path=/var/lib/pgbackrest --retention-full=2 --stanza=demo --start-fast --type=incr
P00   INFO: last backup label = 20170928-225608F_20170928-225628I, version = 1.24
P00   INFO: execute exclusive pg_start_backup() with label "pgBackRest backup started at 2017-09-28 22:56:33": backup begins after the requested immediate checkpoint completes
P00   INFO: backup start archive = 000000020000000000000006, lsn = 0/6000028
P01   INFO: backup file /var/lib/postgresql/9.4/demo/global/pg_control (8KB, 97%) checksum 2f6a5eadc88a61fc27443f302ccdcd9a70690fd6
       [filtered 8 lines of output]
5.2
Automatic Stop Option
Sometimes pgBackRest will exit unexpectedly and the backup in progress on the PostgreSQL cluster will not be properly stopped. pgBackRest exits as quickly as possible when an error occurs so that the cause can be reported accurately and is not masked by another problem that might happen during a more extensive cleanup.
Here an error is intentionally caused by removing repository permissions.
db-primary Revoke write privileges in the pgBackRest repository and attempt a backup
sudo chmod 550 /var/lib/pgbackrest/backup/demo/
sudo -u postgres pgbackrest --stanza=demo --type=incr \
       --log-level-console=info backup
       [filtered 2 lines of output]
P00   INFO: execute exclusive pg_start_backup() with label "pgBackRest backup started at 2017-09-28 22:56:37": backup begins after the requested immediate checkpoint completes
P00   INFO: backup start archive = 000000020000000000000007, lsn = 0/7000028
P00  ERROR: [047]: unable to create path '/var/lib/pgbackrest/backup/demo/20170928-225608F_20170928-225637I': Permission denied
P00   INFO: backup command end: aborted with exception [047]
Even when the permissions are fixed pgBackRest will still be unable to perform a backup because the PostgreSQL cluster is stuck in backup mode.
db-primary Restore write privileges in the pgBackRest repository and attempt a backup
sudo chmod 750 /var/lib/pgbackrest/backup/demo/
sudo -u postgres pgbackrest --stanza=demo --type=incr \
       --log-level-console=info backup
P00   INFO: backup command begin 1.24: --db1-path=/var/lib/postgresql/9.4/demo --log-level-console=info --log-level-stderr=off --no-log-timestamp --repo-path=/var/lib/pgbackrest --retention-full=2 --stanza=demo --start-fast --type=incr
P00   INFO: last backup label = 20170928-225608F_20170928-225633I, version = 1.24
P00   INFO: execute exclusive pg_start_backup() with label "pgBackRest backup started at 2017-09-28 22:56:39": backup begins after the requested immediate checkpoint completes
P00  ERROR: [057]: ERROR:  a backup is already in progress
            HINT:  Run pg_stop_backup() and try again.:
            select to_char(current_timestamp, 'YYYY-MM-DD HH24:MI:SS.US TZ'), pg_xlogfile_name(lsn), lsn::text from pg_start_backup('pgBackRest backup started at 2017-09-28 22:56:39', true) as lsn
Enabling the stop-auto option allows pgBackRest to stop the current backup if it detects that no other pgBackRest backup process is running.
db-primary:/etc/pgbackrest.conf Enable the stop-auto option
[demo]
db-path=/var/lib/postgresql/9.4/demo

[global]
repo-path=/var/lib/pgbackrest
retention-full=2
start-fast=y
stop-auto=y
Now pgBackRest will stop the old backup and start a new one so the process completes successfully.
db-primary Perform an incremental backup
sudo -u postgres pgbackrest --stanza=demo --type=incr \
       --log-level-console=info backup
P00   INFO: backup command begin 1.24: --db1-path=/var/lib/postgresql/9.4/demo --log-level-console=info --log-level-stderr=off --no-log-timestamp --repo-path=/var/lib/pgbackrest --retention-full=2 --stanza=demo --start-fast --stop-auto --type=incr
P00   INFO: last backup label = 20170928-225608F_20170928-225633I, version = 1.24
P00   WARN: the cluster is already in backup mode but no pgBackRest backup process is running. pg_stop_backup() will be called so a new backup can be started.
P00   INFO: execute exclusive pg_stop_backup() and wait for all WAL segments to archive
P00   INFO: execute exclusive pg_start_backup() with label "pgBackRest backup started at 2017-09-28 22:56:41": backup begins after the requested immediate checkpoint completes
P00   INFO: backup start archive = 000000020000000000000008, lsn = 0/8000028
P01   INFO: backup file /var/lib/postgresql/9.4/demo/global/pg_control (8KB, 97%) checksum e83ec411bacabe3684d79a7ffe98d2b18243b4be
       [filtered 8 lines of output]
Although useful this feature may not be appropriate when another third-party backup solution is being used to take online backups as pgBackRest will not recognize that the other software is running and may terminate a backup started by that software. However, it would be unusual to run more than one third-party backup solution at the same time so this is not likely to be a problem.
Note that pg_dump and pg_base_backup do not take online backups so are not affected. It is safe to run them in conjunction with pgBackRest.
5.3
Archive Timeout
During an online backup, pgBackRest waits for WAL segments that are required to make the backup consistent to be archived. This wait time is governed by the archive-timeout option which defaults to 60 seconds. If archiving an individual segment is known to take longer, then this option should be increased.
6
Retention
Generally it is best to retain as many backups as possible to provide a greater window for Point-in-Time Recovery, but practical concerns such as disk space must also be considered. Retention options remove older backups once they are no longer needed.
6.1
Full Backup Retention
Set retention-full to the number of full backups required. New backups must be completed before expiration will occur — that means if retention-full=2 then there will be three full backups stored before the oldest one is expired.
db-primary:/etc/pgbackrest.conf Configure retention-full
[demo]
db-path=/var/lib/postgresql/9.4/demo

[global]
repo-path=/var/lib/pgbackrest
retention-full=2
start-fast=y
stop-auto=y
Backup retention-full=2 but currently there is only one full backup so the next full backup to run will not expire any full backups.
db-primary Perform a full backup
sudo -u postgres pgbackrest --stanza=demo --type=full \
       --log-level-console=detail backup
       [filtered 763 lines of output]
P00   INFO: backup command end: completed successfully
P00   INFO: expire command begin 1.24: --log-level-console=detail --log-level-stderr=off --no-log-timestamp --repo-path=/var/lib/pgbackrest --retention-archive=2 --retention-full=2 --stanza=demo
P00 DETAIL: archive retention on backup 20170928-225608F, archiveId = 9.4-1, start = 000000010000000000000002
P00 DETAIL: no archive to remove, archiveId = 9.4-1
P00   INFO: expire command end: completed successfully
Archive is expired because WAL segments were generated before the oldest backup. These are not useful for recovery — only WAL segments generated after a backup can be used to recover that backup.
db-primary Perform a full backup
sudo -u postgres pgbackrest --stanza=demo --type=full \
       --log-level-console=info backup
       [filtered 763 lines of output]
P00   INFO: backup command end: completed successfully
P00   INFO: expire command begin 1.24: --log-level-console=info --log-level-stderr=off --no-log-timestamp --repo-path=/var/lib/pgbackrest --retention-archive=2 --retention-full=2 --stanza=demo
P00   INFO: expire full backup set: 20170928-225608F, 20170928-225608F_20170928-225614D, 20170928-225608F_20170928-225628I, 20170928-225608F_20170928-225633I, 20170928-225608F_20170928-225641I
P00   INFO: remove expired backup 20170928-225608F_20170928-225641I
P00   INFO: remove expired backup 20170928-225608F_20170928-225633I
       [filtered 3 lines of output]
The 20170928-225608F full backup is expired and archive retention is based on the 20170928-225646F which is now the oldest full backup.
6.2
Differential Backup Retention
Set retention-diff to the number of differential backups required. Differentials only rely on the prior full backup so it is possible to create a rolling set of differentials for the last day or more. This allows quick restores to recent points-in-time but reduces overall space consumption.
db-primary:/etc/pgbackrest.conf Configure retention-diff
[demo]
db-path=/var/lib/postgresql/9.4/demo

[global]
repo-path=/var/lib/pgbackrest
retention-diff=1
retention-full=2
start-fast=y
stop-auto=y
Backup retention-diff=1 so two differentials will need to be performed before one is expired. An incremental backup is added to demonstrate incremental expiration. Incremental backups cannot be expired independently — they are always expired with their related full or differential backup.
db-primary Perform differential and incremental backups
sudo -u postgres pgbackrest --stanza=demo --type=diff backup
sudo -u postgres pgbackrest --stanza=demo --type=incr backup
Now performing a differential backup will expire the previous differential and incremental backups leaving only one differential backup.
db-primary Perform a differential backup
sudo -u postgres pgbackrest --stanza=demo --type=diff \
       --log-level-console=info backup
       [filtered 10 lines of output]
P00   INFO: backup command end: completed successfully
P00   INFO: expire command begin 1.24: --log-level-console=info --log-level-stderr=off --no-log-timestamp --repo-path=/var/lib/pgbackrest --retention-archive=2 --retention-diff=1 --retention-full=2 --stanza=demo
P00   INFO: expire diff backup set: 20170928-225652F_20170928-225700D, 20170928-225652F_20170928-225704I
P00   INFO: remove expired backup 20170928-225652F_20170928-225704I
P00   INFO: remove expired backup 20170928-225652F_20170928-225700D
6.3
Archive Retention
Although pgBackRest automatically removes archived WAL segments when expiring backups (the default expires WAL for full backups based on the retention-full option), it may be useful to expire archive more aggressively to save disk space. Note that full backups are treated as differential backups for the purpose of differential archive retention.
Expiring archive will never remove WAL segments that are required to make a backup consistent. However, since Point-in-Time-Recovery (PITR) only works on a continuous WAL stream, care should be taken when aggressively expiring archive outside of the normal backup expiration process.
db-primary:/etc/pgbackrest.conf Configure retention-diff
[demo]
db-path=/var/lib/postgresql/9.4/demo

[global]
repo-path=/var/lib/pgbackrest
retention-diff=2
retention-full=2
start-fast=y
stop-auto=y
db-primary Perform differential backup
sudo -u postgres pgbackrest --stanza=demo --type=diff \
       --log-level-console=info backup
       [filtered 7 lines of output]
P00   INFO: execute exclusive pg_stop_backup() and wait for all WAL segments to archive
P00   INFO: backup stop archive = 000000020000000000000011, lsn = 0/110000F0
P00   INFO: new backup label = 20170928-225652F_20170928-225713D
P00   INFO: backup command end: completed successfully
P00   INFO: expire command begin 1.24: --log-level-console=info --log-level-stderr=off --no-log-timestamp --repo-path=/var/lib/pgbackrest --retention-archive=2 --retention-diff=2 --retention-full=2 --stanza=demo
db-primary Expire archive
sudo -u postgres pgbackrest --stanza=demo --log-level-console=detail \
       --retention-archive-type=diff --retention-archive=1 expire
P00   INFO: expire command begin 1.24: --log-level-console=detail --log-level-stderr=off --no-log-timestamp --repo-path=/var/lib/pgbackrest --retention-archive=1 --retention-archive-type=diff --retention-diff=2 --retention-full=2 --stanza=demo
P00 DETAIL: archive retention on backup 20170928-225646F, archiveId = 9.4-1, start = 000000020000000000000009, stop = 000000020000000000000009
P00 DETAIL: archive retention on backup 20170928-225652F, archiveId = 9.4-1, start = 00000002000000000000000A, stop = 00000002000000000000000A
P00 DETAIL: archive retention on backup 20170928-225652F_20170928-225708D, archiveId = 9.4-1, start = 00000002000000000000000D, stop = 00000002000000000000000D
P00 DETAIL: archive retention on backup 20170928-225652F_20170928-225713D, archiveId = 9.4-1, start = 000000020000000000000011
P00 DETAIL: remove archive: archiveId = 9.4-1, start = 00000002000000000000000B, stop = 00000002000000000000000C
P00 DETAIL: remove archive: archiveId = 9.4-1, start = 00000002000000000000000E, stop = 000000020000000000000010
P00   INFO: expire command end: completed successfully
The 20170928-225652F_20170928-225708D differential backup has archived WAL segments that must be retained to make the older backups consistent even though they cannot be played any further forward with PITR. WAL segments generated after 20170928-225652F_20170928-225708D but before 20170928-225652F_20170928-225713D are removed. WAL segments generated after the new backup 20170928-225652F_20170928-225713D remain and can be used for PITR.
Since full backups are considered differential backups for the purpose of differential archive retention, if a full backup is now performed with the same settings, only the archive for that full backup is retained for PITR.
7
Restore
The Restore section introduces additional restore command features.
7.1
Delta Option
Restore a Backup in Quick Start required the database cluster directory to be cleaned before the restore could be performed. The delta option allows pgBackRest to automatically determine which files in the database cluster directory can be preserved and which ones need to be restored from the backup — it also removes files not present in the backup manifest so it will dispose of divergent changes. This is accomplished by calculating a SHA-1 cryptographic hash for each file in the database cluster directory. If the SHA-1 hash does not match the hash stored in the backup then that file will be restored. This operation is very efficient when combined with the process-max option. Since the PostgreSQL server is shut down during the restore, a larger number of processes can be used than might be desirable during a backup when the PostgreSQL server is running.
db-primary Stop the demo cluster, perform delta restore
sudo pg_ctlcluster 9.4 demo stop
sudo -u postgres pgbackrest --stanza=demo --delta \
       --log-level-console=detail restore
       [filtered 692 lines of output]
P01 DETAIL: restore file /var/lib/postgresql/9.4/demo/base/12134/PG_VERSION - exists and matches backup (4B, 99%) checksum 8dbabb96e032b8d9f1993c0e4b9141e71ade01a1
P01 DETAIL: restore file /var/lib/postgresql/9.4/demo/base/1/PG_VERSION - exists and matches backup (4B, 99%) checksum 8dbabb96e032b8d9f1993c0e4b9141e71ade01a1
P01 DETAIL: restore file /var/lib/postgresql/9.4/demo/PG_VERSION - exists and matches backup (4B, 100%) checksum 8dbabb96e032b8d9f1993c0e4b9141e71ade01a1
P01 DETAIL: restore file /var/lib/postgresql/9.4/demo/global/12086 - exists and is zero size (0B, 100%)
P01 DETAIL: restore file /var/lib/postgresql/9.4/demo/global/12038 - exists and is zero size (0B, 100%)
       [filtered 83 lines of output]
P01 DETAIL: restore file /var/lib/postgresql/9.4/demo/base/1/11885 - exists and is zero size (0B, 100%)
P00   INFO: write /var/lib/postgresql/9.4/demo/recovery.conf
P00   INFO: restore global/pg_control (performed last to ensure aborted restores cannot be started)
P00   INFO: restore command end: completed successfully
db-primary Restart PostgreSQL
sudo pg_ctlcluster 9.4 demo start
7.2
Restore Selected Databases
There may be cases where it is desirable to selectively restore specific databases from a cluster backup. This could be done for performance reasons or to move selected databases to a machine that does not have enough space to restore the entire cluster backup.
To demonstrate this feature two databases are created: test1 and test2. A fresh backup is run so pgBackRest is aware of the new databases.
db-primary Create two test databases and perform a backup
sudo -u postgres psql -c "create database test1;"
CREATE DATABASE
sudo -u postgres psql -c "create database test2;"
CREATE DATABASE
sudo -u postgres pgbackrest --stanza=demo --type=incr backup
Each test database will be seeded with tables and data to demonstrate that recovery works with selective restore.
db-primary Create a test table in each database
sudo -u postgres psql -c "create table test1_table (id int); \
       insert into test1_table (id) values (1);" test1
INSERT 0 1
sudo -u postgres psql -c "create table test2_table (id int); \
       insert into test2_table (id) values (2);" test2
INSERT 0 1
One of the main reasons to use selective restore is to save space. The size of the test1 database is shown here so it can be compared with the disk utilization after a selective restore.
db-primary Show space used by test1 database
sudo -u postgres du -sh /var/lib/postgresql/9.4/demo/base/16384
6.4M	/var/lib/postgresql/9.4/demo/base/16384
Stop the cluster and restore only the test2 database. Built-in databases (template0, template1, and postgres) are always restored.
db-primary Restore from last backup including only the test2 database
sudo pg_ctlcluster 9.4 demo stop
sudo -u postgres pgbackrest --stanza=demo --delta \
       --db-include=test2 restore
sudo pg_ctlcluster 9.4 demo start
Once recovery is complete the test2 database will contain all previously created tables and data.
db-primary Demonstrate that the test2 database was recovered
sudo -u postgres psql -c "select * from test2_table;" test2
 id 
----
  2
(1 row)
The test1 database, despite successful recovery, is not accessible. This is because the entire database was restored as sparse, zeroed files. PostgreSQL can successfully apply WAL on the zeroed files but the database as a whole will not be valid because key files contain no data. This is purposeful to prevent the database from being accidentally used when it might contain partial data that was applied during WAL replay.
db-primary Attempting to connect to the test1 database will produce an error
sudo -u postgres psql -c "select * from test1_table;" test1
psql: FATAL:  relation mapping file "base/16384/pg_filenode.map" contains invalid data
Since the test1 database is restored with sparse, zeroed files it will only require as much space as the amount of WAL that is written during recovery. While the amount of WAL generated during a backup and applied during recovery can be significant it will generally be a small fraction of the total database size, especially for large databases where this feature is most likely to be useful.
It is clear that the test1 database uses far less disk space during the selective restore than it would have if the entire database had been restored.
db-primary Show space used by test1 database after recovery
sudo -u postgres du -sh /var/lib/postgresql/9.4/demo/base/16384
152K	/var/lib/postgresql/9.4/demo/base/16384
At this point the only action that can be taken on the invalid test1 database is drop database. pgBackRest does not automatically drop the database since this cannot be done until recovery is complete and the cluster is accessible.
db-primary Drop the test1 database
sudo -u postgres psql -c "drop database test1;"
DROP DATABASE
Now that the invalid test1 database has been dropped only the test2 and built-in databases remain.
db-primary List remaining databases
sudo -u postgres psql -c "select oid, datname from pg_database order by oid;"
  oid  |  datname  
-------+-----------
     1 | template1
 12134 | template0
 12139 | postgres
 16385 | test2
(4 rows)
8
Point-in-Time Recovery
Restore a Backup in Quick Start performed default recovery, which is to play all the way to the end of the WAL stream. In the case of a hardware failure this is usually the best choice but for data corruption scenarios (whether machine or human in origin) Point-in-Time Recovery (PITR) is often more appropriate.
Point-in-Time Recovery (PITR) allows the WAL to be played from the last backup to a specified time, transaction id, or recovery point. For common recovery scenarios time-based recovery is arguably the most useful. A typical recovery scenario is to restore a table that was accidentally dropped or data that was accidentally deleted. Recovering a dropped table is more dramatic so that's the example given here but deleted data would be recovered in exactly the same way.
db-primary Backup the demo cluster and create a table with very important data
sudo -u postgres pgbackrest --stanza=demo --type=diff backup
sudo -u postgres psql -c "begin; \
       create table important_table (message text); \
       insert into important_table values ('Important Data'); \
       commit; \
       select * from important_table;"
    message     
----------------
 Important Data
(1 row)
It is important to represent the time as reckoned by PostgreSQL and to include timezone offsets. This reduces the possibility of unintended timezone conversions and an unexpected recovery result.
db-primary Get the time from PostgreSQL
sudo -u postgres psql -Atc "select current_timestamp"
2017-09-28 22:57:47.520063+00
Now that the time has been recorded the table is dropped. In practice finding the exact time that the table was dropped is a lot harder than in this example. It may not be possible to find the exact time, but some forensic work should be able to get you close.
db-primary Drop the important table
sudo -u postgres psql -c "begin; \
       drop table important_table; \
       commit; \
       select * from important_table;"
ERROR:  relation "important_table" does not exist
LINE 1: ...le important_table;     commit;     select * from important_...
                                                             ^
Now the restore can be performed with time-based recovery to bring back the missing table.
db-primary Stop PostgreSQL, restore the demo cluster to 2017-09-28 22:57:47.520063+00, and display recovery.conf
sudo pg_ctlcluster 9.4 demo stop
sudo -u postgres pgbackrest --stanza=demo --delta \
       --type=time "--target=2017-09-28 22:57:47.520063+00" restore
sudo -u postgres cat /var/lib/postgresql/9.4/demo/recovery.conf
restore_command = '/usr/bin/pgbackrest --stanza=demo archive-get %f "%p"'
recovery_target_time = '2017-09-28 22:57:47.520063+00'
The recovery.conf file has been automatically generated by pgBackRest so PostgreSQL can be started immediately. Once PostgreSQL has finished recovery the table will exist again and can be queried.
db-primary Start PostgreSQL and check that the important table exists
sudo pg_ctlcluster 9.4 demo start
sudo -u postgres psql -c "select * from important_table"
    message     
----------------
 Important Data
(1 row)
The PostgreSQL log also contains valuable information. It will indicate the time and transaction where the recovery stopped and also give the time of the last transaction to be applied.
db-primary Examine the PostgreSQL log output
sudo -u postgres cat /var/log/postgresql/postgresql-9.4-demo.log
LOG:  database system was interrupted; last known up at 2017-09-28 22:57:42 UTC
LOG:  starting point-in-time recovery to 2017-09-28 22:57:47.520063+00
LOG:  restored log file "00000004.history" from archive
LOG:  restored log file "000000040000000000000016" from archive
       [filtered 2 lines of output]
LOG:  incomplete startup packet
LOG:  restored log file "000000040000000000000017" from archive
LOG:  recovery stopping before commit of transaction 686, time 2017-09-28 22:57:47.631866+00
LOG:  redo done at 0/170157F0
LOG:  last completed transaction was at log time 2017-09-28 22:57:47.41566+00
LOG:  selected new timeline ID: 5
LOG:  restored log file "00000004.history" from archive
       [filtered 4 lines of output]
This example was rigged to give the correct result. If a backup after the required time is chosen then PostgreSQL will not be able to recover the lost table. PostgreSQL can only play forward, not backward. To demonstrate this the important table must be dropped (again).
db-primary Drop the important table (again)
sudo -u postgres psql -c "begin; \
       drop table important_table; \
       commit; \
       select * from important_table;"
ERROR:  relation "important_table" does not exist
LINE 1: ...le important_table;     commit;     select * from important_...
                                                             ^
Now take a new backup and attempt recovery from the new backup.
db-primary Perform a backup then attempt recovery from that backup
sudo -u postgres pgbackrest --stanza=demo --type=incr backup
sudo pg_ctlcluster 9.4 demo stop
sudo -u postgres pgbackrest --stanza=demo --delta \
       --type=time "--target=2017-09-28 22:57:47.520063+00" restore
sudo pg_ctlcluster 9.4 demo start
sudo -u postgres psql -c "select * from important_table"
ERROR:  relation "important_table" does not exist
LINE 1: select * from important_table
                      ^
Looking at the log output it's not obvious that recovery failed to restore the table. The key is to look for the presence of the recovery stopping before... and last completed transaction... log messages. If they are not present then the recovery to the specified point-in-time was not successful.
db-primary Examine the PostgreSQL log output to discover the recovery was not successful
sudo -u postgres cat /var/log/postgresql/postgresql-9.4-demo.log
LOG:  database system was interrupted; last known up at 2017-09-28 22:57:57 UTC
LOG:  starting point-in-time recovery to 2017-09-28 22:57:47.520063+00
LOG:  restored log file "00000005.history" from archive
LOG:  restored log file "000000050000000000000018" from archive
LOG:  redo starts at 0/18000028
LOG:  consistent recovery state reached at 0/180000F0
FATAL:  the database system is starting up
LOG:  redo done at 0/180000F0
       [filtered 10 lines of output]
Using an earlier backup will allow PostgreSQL to play forward to the correct time. The info command can be used to find the next to last backup.
db-primary Get backup info for the demo cluster
sudo -u postgres pgbackrest info
stanza: demo
    status: ok

    db (current)
        wal archive min/max (9.4-1): 000000020000000000000009 / 000000050000000000000018

        full backup: 20170928-225646F
            timestamp start/stop: 2017-09-28 22:56:46 / 2017-09-28 22:56:51
            wal start/stop: 000000020000000000000009 / 000000020000000000000009
            database size: 19.2MB, backup size: 19.2MB
            repository size: 2.2MB, repository backup size: 2.2MB

        full backup: 20170928-225652F
            timestamp start/stop: 2017-09-28 22:56:52 / 2017-09-28 22:56:58
            wal start/stop: 00000002000000000000000A / 00000002000000000000000A
            database size: 19.2MB, backup size: 19.2MB
            repository size: 2.2MB, repository backup size: 2.2MB

        diff backup: 20170928-225652F_20170928-225713D
            timestamp start/stop: 2017-09-28 22:57:13 / 2017-09-28 22:57:16
            wal start/stop: 000000020000000000000011 / 000000020000000000000011
            database size: 19.2MB, backup size: 8.2KB
            repository size: 2.2MB, repository backup size: 348B
            backup reference list: 20170928-225652F

        incr backup: 20170928-225652F_20170928-225726I
            timestamp start/stop: 2017-09-28 22:57:26 / 2017-09-28 22:57:30
            wal start/stop: 000000030000000000000013 / 000000030000000000000013
            database size: 31.7MB, backup size: 12.5MB
            repository size: 3.7MB, repository backup size: 1.5MB
            backup reference list: 20170928-225652F
        diff backup: 20170928-225652F_20170928-225741D
            timestamp start/stop: 2017-09-28 22:57:41 / 2017-09-28 22:57:46
            wal start/stop: 000000040000000000000016 / 000000040000000000000016
            database size: 25.5MB, backup size: 6.3MB
            repository size: 3MB, repository backup size: 765.2KB
            backup reference list: 20170928-225652F

        incr backup: 20170928-225652F_20170928-225756I
            timestamp start/stop: 2017-09-28 22:57:56 / 2017-09-28 22:57:59
            wal start/stop: 000000050000000000000018 / 000000050000000000000018
            database size: 25.5MB, backup size: 1.7MB
            repository size: 2.9MB, repository backup size: 200.3KB
            backup reference list: 20170928-225652F, 20170928-225652F_20170928-225741D
The default behavior for restore is to use the last backup but an earlier backup can be specified with the --set option.
db-primary Stop PostgreSQL, restore from the selected backup, and start PostgreSQL
sudo pg_ctlcluster 9.4 demo stop
sudo -u postgres pgbackrest --stanza=demo --delta \
       --type=time "--target=2017-09-28 22:57:47.520063+00" \
       --set=20170928-225652F_20170928-225741D restore
sudo pg_ctlcluster 9.4 demo start
sudo -u postgres psql -c "select * from important_table"
    message     
----------------
 Important Data
(1 row)
Now the the log output will contain the expected recovery stopping before... and last completed transaction... messages showing that the recovery was successful.
db-primary Examine the PostgreSQL log output for log messages indicating success
sudo -u postgres cat /var/log/postgresql/postgresql-9.4-demo.log
LOG:  database system was interrupted; last known up at 2017-09-28 22:57:42 UTC
LOG:  starting point-in-time recovery to 2017-09-28 22:57:47.520063+00
LOG:  restored log file "00000004.history" from archive
LOG:  restored log file "000000040000000000000016" from archive
       [filtered 2 lines of output]
LOG:  incomplete startup packet
LOG:  restored log file "000000040000000000000017" from archive
LOG:  recovery stopping before commit of transaction 686, time 2017-09-28 22:57:47.631866+00
LOG:  redo done at 0/170157F0
LOG:  last completed transaction was at log time 2017-09-28 22:57:47.41566+00
LOG:  restored log file "00000005.history" from archive
LOG:  restored log file "00000006.history" from archive
       [filtered 7 lines of output]
9
S3 Support
pgBackRest supports storing repositories in Amazon S3.
db-primary:/etc/pgbackrest.conf Configure S3
[demo]
db-path=/var/lib/postgresql/9.4/demo

[global]
process-max=4
repo-path=/
repo-s3-bucket=demo-bucket
repo-s3-endpoint=s3.amazonaws.com
repo-s3-key=accessKey1
repo-s3-key-secret=verySecretKey1
repo-s3-region=us-east-1
repo-type=s3
retention-diff=2
retention-full=2
start-fast=y
stop-auto=y
Commands are run exactly as if the repository were stored on a local disk.
db-primary Create the stanza
sudo -u postgres pgbackrest --stanza=demo --log-level-console=info stanza-create
P00   INFO: stanza-create command begin 1.24: --db1-path=/var/lib/postgresql/9.4/demo --log-level-console=info --no-log-timestamp --repo-path=/ --repo-s3-bucket=demo-bucket --repo-s3-endpoint=s3.amazonaws.com --repo-s3-region=us-east-1 --no-repo-s3-verify-ssl --repo-type=s3 --stanza=demo
P00   INFO: stanza-create command end: completed successfully
File creation time in S3 is relatively slow so commands benefit by increasing process-max to parallelize file creation.
db-primary Backup the demo cluster
sudo -u postgres pgbackrest --stanza=demo \
       --log-level-console=info backup
P00   INFO: backup command begin 1.24: --db1-path=/var/lib/postgresql/9.4/demo --log-level-console=info --log-level-stderr=off --no-log-timestamp --process-max=4 --repo-path=/ --repo-s3-bucket=demo-bucket --repo-s3-endpoint=s3.amazonaws.com --repo-s3-region=us-east-1 --no-repo-s3-verify-ssl --repo-type=s3 --retention-diff=2 --retention-full=2 --stanza=demo --start-fast --stop-auto
P00   WARN: no prior backup exists, incr backup has been changed to full
P00   INFO: execute exclusive pg_start_backup() with label "pgBackRest backup started at 2017-09-28 22:58:21": backup begins after the requested immediate checkpoint completes
P00   INFO: backup start archive = 000000070000000000000018, lsn = 0/18000028
       [filtered 995 lines of output]
P04   INFO: backup file /var/lib/postgresql/9.4/demo/base/1/11905 (0B, 100%)
P03   INFO: backup file /var/lib/postgresql/9.4/demo/base/1/11885 (0B, 100%)
P00   INFO: full backup size = 25.5MB
P00   INFO: execute exclusive pg_stop_backup() and wait for all WAL segments to archive
P00   INFO: backup stop archive = 000000070000000000000018, lsn = 0/18000128
       [filtered 4 lines of output]
10
Dedicated Backup Host
The configuration described in Quickstart is suitable for simple installations but for enterprise configurations it is more typical to have a dedicated backup host. This separates the backups and WAL archive from the database server so database host failures have less impact. It is still a good idea to employ traditional backup software to backup the backup host.
10.1
Installation
A new host named backup is created to store the cluster backups.
The backrest user is created to own the pgBackRest repository. Any user can own the repository but it is best not to use postgres (if it exists) to avoid confusion.
backup Create backrest
sudo adduser --disabled-password --gecos "" backrest
pgBackRest is written in Perl which is included with Debian/Ubuntu by default. Some additional modules must also be installed but they are available as standard packages.
backup Install required Perl packages
sudo apt-get install libdbd-pg-perl libio-socket-ssl-perl libxml-libxml-perl
Debian/Ubuntu packages for pgBackRest are available at apt.postgresql.org. If they are not provided for your distribution/version it is easy to download the source and install manually.
backup Download version 1.24 of pgBackRest
sudo wget -q -O - \
       https://github.com/pgbackrest/pgbackrest/archive/release/1.24.tar.gz | \
       sudo tar zx -C /root
backup Install pgBackRest
sudo cp -r /root/pgbackrest-release-1.24/lib/pgBackRest \
       /usr/share/perl5
sudo find /usr/share/perl5/pgBackRest -type f -exec chmod 644 {} +
sudo find /usr/share/perl5/pgBackRest -type d -exec chmod 755 {} +
sudo cp /root/pgbackrest-release-1.24/bin/pgbackrest /usr/bin/pgbackrest
sudo chmod 755 /usr/bin/pgbackrest
sudo mkdir -m 770 /var/log/pgbackrest
sudo chown backrest:backrest /var/log/pgbackrest
sudo touch /etc/pgbackrest.conf
sudo chmod 640 /etc/pgbackrest.conf
sudo chown backrest:backrest /etc/pgbackrest.conf
backup Create the pgBackRest repository
sudo mkdir /var/lib/pgbackrest
sudo chmod 750 /var/lib/pgbackrest
sudo chown backrest:backrest /var/lib/pgbackrest
10.2
Setup Trusted SSH
pgBackRest requires trusted (no password) SSH to enable communication between the hosts.
backup Create backup host key pair
sudo -u backrest mkdir -m 750 /home/backrest/.ssh
sudo -u backrest ssh-keygen -f /home/backrest/.ssh/id_rsa -t rsa -b 4096 -N ""
db-primary Create db-primary host key pair
sudo -u postgres mkdir -m 750 -p /home/postgres/.ssh
sudo -u postgres ssh-keygen -f /home/postgres/.ssh/id_rsa -t rsa -b 4096 -N ""
Exchange keys between backup and db-primary.
backup Copy db-primary public key to backup
sudo ssh root@db-primary cat /home/postgres/.ssh/id_rsa.pub | \
       sudo -u backrest tee -a /home/backrest/.ssh/authorized_keys
db-primary Copy backup public key to db-primary
sudo ssh root@backup cat /home/backrest/.ssh/id_rsa.pub | \
       sudo -u postgres tee -a /home/postgres/.ssh/authorized_keys
Test that connections can be made from backup to db-primary and vice versa.
backup Test connection from backup to db-primary
sudo -u backrest ssh postgres@db-primary
db-primary Test connection from db-primary to backup
sudo -u postgres ssh backrest@backup
10.3
Configuration
The backup host must be configured with the db-primary host/user and database path. The primary will be configured as db1 to allow a standby to be added later.
backup:/etc/pgbackrest.conf Configure db1-host/db1-user and db1-path
[demo]
db1-host=db-primary
db1-path=/var/lib/postgresql/9.4/demo
db1-user=postgres

[global]
repo-path=/var/lib/pgbackrest
retention-full=2
start-fast=y
The database host must be configured with the backup host/user. The default for the backup-user option is backrest. If the postgres user does restores on the backup host it is best not to also allow the postgres user to perform backups. However, the postgres user can read the repository directly if it is in the same group as the backrest user.
db-primary:/etc/pgbackrest.conf Configure backup-host/backup-user
[demo]
db-path=/var/lib/postgresql/9.4/demo

[global]
backup-host=backup
backup-user=backrest
log-level-file=detail
The repository directory will also be removed from the database host. It will not be used anymore so leaving it around may be confusing later on.
db-primary Remove repository now that it will be located on the backup host server
sudo find /var/lib/pgbackrest -delete
Commands are run the same as on a single host configuration except that some commands such as backup and expire are run from the backup host instead of the database host.
Create the stanza in the new repository.
backup Create the stanza
sudo -u backrest pgbackrest --stanza=demo stanza-create
Check that the configuration is correct on both the database and backup hosts. More information about the check command can be found in Check the Configuration.
db-primary Check the configuration
sudo -u postgres pgbackrest --stanza=demo check
backup Check the configuration
sudo -u backrest pgbackrest --stanza=demo check
10.4
Perform a Backup
To perform a backup of the PostgreSQL cluster run pgBackRest with the backup command on the backup host.
backup Backup the demo cluster
sudo -u backrest pgbackrest --stanza=demo backup
P00   WARN: no prior backup exists, incr backup has been changed to full
Since a new repository was created on the backup host the warning about the incremental backup changing to a full backup was emitted.
10.5
Restore a Backup
To perform a restore of the PostgreSQL cluster run pgBackRest with the restore command on the database host.
db-primary Stop the demo cluster, restore, and restart PostgreSQL
sudo pg_ctlcluster 9.4 demo stop
sudo -u postgres pgbackrest --stanza=demo --delta restore
sudo pg_ctlcluster 9.4 demo start
A new backup must be performed due to the timeline switch.
backup Backup the demo cluster
sudo -u backrest pgbackrest --stanza=demo backup
10.6
Asynchronous Archiving
The archive-async option offloads WAL archiving to a separate process (or processes) to improve throughput. It works by looking ahead to see which WAL segments are ready to be archived beyond the request that PostgreSQL is currently making via the archive_command. WAL segments are transferred to the archive directly from the pg_xlog/pg_wal directory and success is only returned by the archive_command when the WAL segment has been safely stored in the archive.
The spool directory is created to hold the current status of WAL archiving. Status files written into the spool directory are typically zero length and should consume a minimal amount of space (a few MB at most) and very little IO. All the information in this directory can be recreated so it is not necessary to preserve the spool directory if the cluster is moved to new hardware.
NOTE: In the original implementation of asynchronous archiving, WAL segments were copied to the spool directory before compression and transfer. The new implementation copies WAL directly from the pg_xlog directory. If asynchronous archiving was utilized in v1.12 or prior, read the v1.13 release notes carefully before upgrading.
db-primary Create the spool directory
sudo mkdir -m 750 /var/spool/pgbackrest
sudo chown postgres:postgres /var/spool/pgbackrest
The spool path must be configured and asynchronous archiving enabled. Asynchronous archiving automatically confers some benefit by reducing the number of ssh connections made to the backup server, but setting process-max can drastically improve performance. Be sure not to set process-max so high that it affects normal database operations.
db-primary:/etc/pgbackrest.conf Configure the spool path and asynchronous archiving
[demo]
db-path=/var/lib/postgresql/9.4/demo

[global]
archive-async=y
backup-host=backup
backup-user=backrest
log-level-file=detail
spool-path=/var/spool/pgbackrest

[global:archive-push]
process-max=2
The archive-async.log file can be used to monitor the activity of the asynchronous process. A good way to test this is to quickly push a number of WAL segments.
db-primary Test parallel asynchronous archiving
sudo -u postgres psql -c " \
       select pg_create_restore_point('test async push'); select pg_switch_xlog(); \
       select pg_create_restore_point('test async push'); select pg_switch_xlog(); \
       select pg_create_restore_point('test async push'); select pg_switch_xlog(); \
       select pg_create_restore_point('test async push'); select pg_switch_xlog(); \
       select pg_create_restore_point('test async push'); select pg_switch_xlog();"
sudo -u postgres pgbackrest --stanza=demo --log-level-console=info check
P00   INFO: check command begin 1.24: --backup-host=backup --backup-user=backrest --db1-path=/var/lib/postgresql/9.4/demo --log-level-console=info --log-level-file=detail --log-level-stderr=off --no-log-timestamp --stanza=demo
P00   INFO: WAL segment 000000080000000000000024 successfully stored in the archive at '/var/lib/pgbackrest/archive/demo/9.4-1/0000000800000000/000000080000000000000024-19a9afb8a52d1c72367544bec1ebfeef23679128.gz'
P00   INFO: check command end: completed successfully
Now the log file will contain parallel, asynchronous activity.
db-primary Check results in the log
sudo -u postgres cat /var/log/pgbackrest/demo-archive-async.log
-------------------PROCESS START-------------------
P00   INFO: archive-push command begin 1.24: --archive-async --backup-host=backup --backup-user=backrest --db1-path=/var/lib/postgresql/9.4/demo --log-level-file=detail --log-level-stderr=off --no-log-timestamp --process-max=2 --spool-path=/var/spool/pgbackrest --stanza=demo
P00   INFO: push 3 WAL file(s) to archive: 00000008000000000000001F...000000080000000000000021
P02 DETAIL: pushed WAL file 000000080000000000000020 to archive
P01 DETAIL: pushed WAL file 00000008000000000000001F to archive
P02 DETAIL: pushed WAL file 000000080000000000000021 to archive
P00   INFO: archive-push command end: completed successfully

-------------------PROCESS START-------------------
P00   INFO: archive-push command begin 1.24: --archive-async --backup-host=backup --backup-user=backrest --db1-path=/var/lib/postgresql/9.4/demo --log-level-file=detail --log-level-stderr=off --no-log-timestamp --process-max=2 --spool-path=/var/spool/pgbackrest --stanza=demo
P00   INFO: push 3 WAL file(s) to archive: 000000080000000000000022...000000080000000000000024
P01 DETAIL: pushed WAL file 000000080000000000000022 to archive
P02 DETAIL: pushed WAL file 000000080000000000000023 to archive
P01 DETAIL: pushed WAL file 000000080000000000000024 to archive
P00   INFO: archive-push command end: completed successfully
11
Parallel Backup / Restore
pgBackRest offers parallel processing to improve performance of compression and transfer. The number of processes to be used for this feature is set using the --process-max option.
backup Check the number of CPUs
sudo lscpu
Architecture:          x86_64
CPU op-mode(s):        32-bit, 64-bit
Byte Order:            Little Endian
CPU(s):                8
On-line CPU(s) list:   0-7
Thread(s) per core:    1
       [filtered 17 lines of output]
It is usually best not to use more than 25% of the available CPUs for the backup command. Backups don't have to run that fast as long as they are performed regularly and the backup process should not impact database performance, if at all possible.
The restore command can and should use all available CPUs because during a restore the PostgreSQL cluster is shut down and there is generally no other important work being done on the host. If the host contains multiple clusters then that should be considered when setting restore parallelism.
backup Perform a backup with single process
sudo -u backrest pgbackrest --stanza=demo --type=full backup
backup:/etc/pgbackrest.conf Configure pgBackRest to use multiple backup processes
[demo]
db1-host=db-primary
db1-path=/var/lib/postgresql/9.4/demo
db1-user=postgres

[global]
process-max=3
repo-path=/var/lib/pgbackrest
retention-full=2
start-fast=y
backup Perform a backup with multiple processes
sudo -u backrest pgbackrest --stanza=demo --type=full backup
backup Get backup info for the demo cluster
sudo -u backrest pgbackrest info
stanza: demo
    status: ok

    db (current)
        wal archive min/max (9.4-1): 000000080000000000000025 / 000000080000000000000026

        full backup: 20170928-225931F
            timestamp start/stop: 2017-09-28 22:59:31 / 2017-09-28 22:59:40
            wal start/stop: 000000080000000000000025 / 000000080000000000000025
            database size: 25.5MB, backup size: 25.5MB
            repository size: 3MB, repository backup size: 3MB

        full backup: 20170928-225941F
            timestamp start/stop: 2017-09-28 22:59:41 / 2017-09-28 22:59:48
            wal start/stop: 000000080000000000000026 / 000000080000000000000026
            database size: 25.5MB, backup size: 25.5MB
            repository size: 3MB, repository backup size: 3MB
The performance of the last backup should be improved by using multiple processes. For very small backups the difference may not be very apparent, but as the size of the database increases so will time savings.
12
Starting and Stopping
Sometimes it is useful to prevent pgBackRest from running on a system. For example, when failing over from a primary to a standby it's best to prevent pgBackRest from running on the old primary in case PostgreSQL gets restarted or can't be completely killed. This will also prevent pgBackRest from running on cron.
db-primary Stop the pgBackRest services
sudo -u postgres pgbackrest stop
New pgBackRest processes will no longer run.
backup Attempt a backup
sudo -u backrest pgbackrest --stanza=demo backup
P00  ERROR: [062]: process 'db-primary remote' terminated unexpectedly [062]: stop file exists for all stanzas
            
Specify the --force option to terminate any pgBackRest process that are currently running. If pgBackRest is already stopped then stopping again will generate a warning.
db-primary Stop the pgBackRest services again
sudo -u postgres pgbackrest stop
P00   WARN: stop file already exists for all stanzas
Start pgBackRest processes again with the start command.
db-primary Start the pgBackRest services
sudo -u postgres pgbackrest start
It is also possible to stop pgBackRest for a single stanza.
db-primary Stop pgBackRest services for the demo stanza
sudo -u postgres pgbackrest --stanza=demo stop
New pgBackRest processes for the specified stanza will no longer run.
backup Attempt a backup
sudo -u backrest pgbackrest --stanza=demo backup
P00  ERROR: [062]: process 'db-primary remote' terminated unexpectedly [062]: stop file exists for stanza demo
            
The stanza must also be specified when starting the pgBackRest processes for a single stanza.
db-primary Start the pgBackRest services for the demo stanza
sudo -u postgres pgbackrest --stanza=demo start
13
Replication
Replication allows multiple copies of a PostgreSQL cluster (called standbys) to be created from a single primary. The standbys are useful for balancing reads and to provide redundancy in case the primary host fails.
13.1
Installation
A new host named db-standby is created to run the standby.
pgBackRest is written in Perl which is included with Debian/Ubuntu by default. Some additional modules must also be installed but they are available as standard packages.
db-standby Install required Perl packages
sudo apt-get install libdbd-pg-perl libio-socket-ssl-perl libxml-libxml-perl
Debian/Ubuntu packages for pgBackRest are available at apt.postgresql.org. If they are not provided for your distribution/version it is easy to download the source and install manually.
db-standby Download version 1.24 of pgBackRest
sudo wget -q -O - \
       https://github.com/pgbackrest/pgbackrest/archive/release/1.24.tar.gz | \
       sudo tar zx -C /root
db-standby Install pgBackRest
sudo cp -r /root/pgbackrest-release-1.24/lib/pgBackRest \
       /usr/share/perl5
sudo find /usr/share/perl5/pgBackRest -type f -exec chmod 644 {} +
sudo find /usr/share/perl5/pgBackRest -type d -exec chmod 755 {} +
sudo cp /root/pgbackrest-release-1.24/bin/pgbackrest /usr/bin/pgbackrest
sudo chmod 755 /usr/bin/pgbackrest
sudo mkdir -m 770 /var/log/pgbackrest
sudo chown postgres:postgres /var/log/pgbackrest
sudo touch /etc/pgbackrest.conf
sudo chmod 640 /etc/pgbackrest.conf
sudo chown postgres:postgres /etc/pgbackrest.conf
The demo cluster must be created even though it will be overwritten later.
db-standby Create demo cluster
sudo pg_createcluster 9.4 demo
13.2
Setup Trusted SSH
pgBackRest requires trusted (no password) SSH to enable communication between the hosts.
db-standby Create db-standby host key pair
sudo -u postgres mkdir -m 750 -p /home/postgres/.ssh
sudo -u postgres ssh-keygen -f /home/postgres/.ssh/id_rsa -t rsa -b 4096 -N ""
Exchange keys between backup and db-standby.
backup Copy db-standby public key to backup
sudo ssh root@db-standby cat /home/postgres/.ssh/id_rsa.pub | \
       sudo -u backrest tee -a /home/backrest/.ssh/authorized_keys
db-standby Copy backup public key to db-standby
sudo ssh root@backup cat /home/backrest/.ssh/id_rsa.pub | \
       sudo -u postgres tee -a /home/postgres/.ssh/authorized_keys
Test that connections can be made from backup to db-standby and vice versa.
backup Test connection from backup to db-standby
sudo -u backrest ssh postgres@db-standby
db-standby Test connection from db-standby to backup
sudo -u postgres ssh backrest@backup
13.3
Hot Standby
A hot standby performs replication using the WAL archive and allows read-only queries.
pgBackRest configuration is very similar to db-primary except that the standby_mode setting will be enabled to keep the cluster in recovery mode when the end of the WAL stream has been reached.
db-standby:/etc/pgbackrest.conf Configure pgBackRest on the standby
[demo]
db-path=/var/lib/postgresql/9.4/demo
recovery-option=standby_mode=on

[global]
backup-host=backup
Now the standby can be created with the restore command.
db-standby Restore the demo standby cluster
sudo -u postgres pgbackrest --stanza=demo --delta restore
sudo -u postgres cat /var/lib/postgresql/9.4/demo/recovery.conf
standby_mode = 'on'
restore_command = '/usr/bin/pgbackrest --stanza=demo archive-get %f "%p"'
Note that the standby_mode setting has been written into the recovery.conf file. Configuring recovery settings in pgBackRest means that the recovery.conf file does not need to be stored elsewhere since it will be properly recreated with each restore. The --type=preserve option can be used with the restore to leave the existing recovery.conf file in place if that behavior is preferred.
The hot_standby setting must be enabled before starting PostgreSQL to allow read-only connections on db-standby. Otherwise, connection attempts will be refused.
db-standby:/etc/postgresql/9.4/demo/postgresql.conf Enable hot_standby
hot_standby = on
log_filename = 'postgresql.log'
log_line_prefix = ''
db-standby Start PostgreSQL
sudo pg_ctlcluster 9.4 demo start
The PostgreSQL log gives valuable information about the recovery. Note especially that the cluster has entered standby mode and is ready to accept read-only connections.
db-standby Examine the PostgreSQL log output for log messages indicating success
sudo -u postgres cat /var/log/postgresql/postgresql-9.4-demo.log
LOG:  could not bind IPv6 socket: Cannot assign requested address
HINT:  Is another postmaster already running on port 5432? If not, wait a few seconds and retry.
LOG:  database system was interrupted; last known up at 2017-09-28 22:59:42 UTC
LOG:  entering standby mode
LOG:  restored log file "00000008.history" from archive
LOG:  incomplete startup packet
LOG:  restored log file "000000080000000000000026" from archive
LOG:  redo starts at 0/26000028
LOG:  consistent recovery state reached at 0/260000F0
LOG:  database system is ready to accept read only connections
An easy way to test that replication is properly configured is to create a table on db-primary.
db-primary Create a new table on the primary
sudo -u postgres psql -c " \
       begin; \
       create table replicated_table (message text); \
       insert into replicated_table values ('Important Data'); \
       commit; \
       select * from replicated_table";
    message     
----------------
 Important Data
(1 row)
And then query the same table on db-standby.
db-standby Query new table on the standby
sudo -u postgres psql -c "select * from replicated_table;"
ERROR:  relation "replicated_table" does not exist
LINE 1: select * from replicated_table;
                      ^
So, what went wrong? Since PostgreSQL is pulling WAL segments from the archive to perform replication, changes won't be seen on the standby until the WAL segment that contains those changes is pushed from db-primary.
This can be done manually by calling pg_switch_xlog() which pushes the current WAL segment to the archive (a new WAL segment is created to contain further changes).
db-primary Call pg_switch_xlog()
sudo -u postgres psql -c "select *, current_timestamp from pg_switch_xlog()";
 pg_switch_xlog |              now              
----------------+-------------------------------
 0/270199E0     | 2017-09-28 23:00:11.890735+00
(1 row)
Now after a short delay the table will appear on db-standby.
db-standby Now the new table exists on the standby (may require a few retries)
sudo -u postgres psql -c " \
       select *, current_timestamp from replicated_table"
    message     |              now              
----------------+-------------------------------
 Important Data | 2017-09-28 23:00:14.299461+00
(1 row)
Check the standby configuration for access to the repository.
db-standby Check the configuration
sudo -u postgres pgbackrest --stanza=demo --log-level-console=info check
P00   INFO: check command begin 1.24: --backup-host=backup --db1-path=/var/lib/postgresql/9.4/demo --log-level-console=info --log-level-stderr=off --no-log-timestamp --stanza=demo
P00   INFO: switch xlog cannot be performed on the standby, all other checks passed successfully
P00   INFO: check command end: completed successfully
13.4
Streaming Replication
Instead of relying solely on the WAL archive, streaming replication makes a direct connection to the primary and applies changes as soon as they are made on the primary. This results in much less lag between the primary and standby.
Streaming replication requires a user with the replication privilege.
db-primary Create replication user
sudo -u postgres psql -c " \
       create user replicator password 'jw8s0F4' replication";
CREATE ROLE
The pg_hba.conf file must be updated to allow the standby to connect as the replication user. Be sure to replace the IP address below with the actual IP address of your db-primary. A reload will be required after modifying the pg_hba.conf file.
db-primary Create pg_hba.conf entry for replication user
sudo -u postgres sh -c 'echo \
       "host    replication     replicator      172.17.0.5/32           md5" \
       >> /etc/postgresql/9.4/demo/pg_hba.conf'
sudo pg_ctlcluster 9.4 demo reload
The standby needs to know how to contact the primary so the primary_conninfo setting will be configured in pgBackRest.
db-standby:/etc/pgbackrest.conf Set primary_conninfo
[demo]
db-path=/var/lib/postgresql/9.4/demo
recovery-option=standby_mode=on
recovery-option=primary_conninfo=host=172.17.0.3 port=5432 user=replicator

[global]
backup-host=backup
It is possible to configure a password in the primary_conninfo setting but using a .pgpass file is more flexible and secure.
db-standby Configure the replication password in the .pgpass file.
sudo -u postgres sh -c 'echo \
       "172.17.0.3:*:replication:replicator:jw8s0F4" \
       >> /home/postgres/.pgpass'
sudo -u postgres chmod 600 /home/postgres/.pgpass
Now the standby can be created with the restore command.
db-standby Stop PostgreSQL and restore the demo standby cluster
sudo pg_ctlcluster 9.4 demo stop
sudo -u postgres pgbackrest --stanza=demo --delta restore
sudo -u postgres cat /var/lib/postgresql/9.4/demo/recovery.conf
primary_conninfo = 'host=172.17.0.3 port=5432 user=replicator'
standby_mode = 'on'
restore_command = '/usr/bin/pgbackrest --stanza=demo archive-get %f "%p"'
db-standby Start PostgreSQL
sudo pg_ctlcluster 9.4 demo start
The PostgreSQL log will confirm that streaming replication has started.
db-standby Examine the PostgreSQL log output for log messages indicating success
sudo -u postgres cat /var/log/postgresql/postgresql-9.4-demo.log
       [filtered 9 lines of output]
LOG:  database system is ready to accept read only connections
LOG:  restored log file "000000080000000000000027" from archive
LOG:  started streaming WAL from primary at 0/28000000 on timeline 8
Now when a table is created on db-primary it will appear on db-standby quickly and without the need to call pg_switch_xlog().
db-primary Create a new table on the primary
sudo -u postgres psql -c " \
       begin; \
       create table stream_table (message text); \
       insert into stream_table values ('Important Data'); \
       commit; \
       select *, current_timestamp from stream_table";
    message     |              now              
----------------+-------------------------------
 Important Data | 2017-09-28 23:00:24.565875+00
(1 row)
db-standby Query table on the standby
sudo -u postgres psql -c " \
       select *, current_timestamp from stream_table"
    message     |              now              
----------------+-------------------------------
 Important Data | 2017-09-28 23:00:24.729957+00
(1 row)
14
Backup from a Standby
pgBackRest can perform backups on a standby instead of the primary. Standby backups require the db-standby host to be configured and the backup-standby option enabled. If more than one standby is configured then the first running standby found will be used for the backup.
backup:/etc/pgbackrest.conf Configure db2-host/db2-user and db2-path
[demo]
db1-host=db-primary
db1-path=/var/lib/postgresql/9.4/demo
db1-user=postgres
db2-host=db-standby
db2-path=/var/lib/postgresql/9.4/demo
db2-user=postgres

[global]
backup-standby=y
process-max=3
repo-path=/var/lib/pgbackrest
retention-full=2
start-fast=y
Both the primary and standby databases are required to perform the backup, though the vast majority of the files will be copied from the standby to reduce load on the primary. The database hosts can be configured in any order. pgBackRest will automatically determine which is the primary and which is the standby.
backup Backup the demo cluster from db-standby
sudo -u backrest pgbackrest --stanza=demo --log-level-console=detail backup
       [filtered 2 lines of output]
P00   INFO: execute exclusive pg_start_backup() with label "pgBackRest backup started at 2017-09-28 23:00:25": backup begins after the requested immediate checkpoint completes
P00   INFO: backup start archive = 000000080000000000000029, lsn = 0/29000028
P00   INFO: wait for replay on the standby to reach 0/29000028
P00   INFO: replay on the standby reached 0/290000C8
P04   INFO: backup file db-standby:/var/lib/postgresql/9.4/demo/base/12139/12009 (240KB, 12%) checksum 4db01860354afc569a7351d85865687ef1211685
P02   INFO: backup file db-standby:/var/lib/postgresql/9.4/demo/base/12139/12007 (392KB, 32%) checksum 3746d4ee7afd008dfa98fb71e004b99274528a55
       [filtered 11 lines of output]
P02   INFO: backup file db-standby:/var/lib/postgresql/9.4/demo/base/12139/11900_fsm (24KB, 91%) checksum 283ee200135d14180c2f6d72967b3b3e36e8d494
P03   INFO: backup file db-standby:/var/lib/postgresql/9.4/demo/base/12139/11879 (32KB, 92%) checksum 99c644a60e59f79ee9a1ba4c90ae19e97fae2dfb
P01   INFO: backup file db-primary:/var/lib/postgresql/9.4/demo/global/pg_control (8KB, 93%) checksum 4839b31ba046f4a2f7cddd24959389a73d68ac04
P04   INFO: backup file db-standby:/var/lib/postgresql/9.4/demo/global/11884 (16KB, 94%) checksum 35235f8b5c0c02c5fede6e62360b24ec6d6a1478
P02   INFO: backup file db-standby:/var/lib/postgresql/9.4/demo/global/11883 (16KB, 95%) checksum ad318c3591bbd49e7b7bb888b23584ef6afa7dd0
P03   INFO: backup file db-standby:/var/lib/postgresql/9.4/demo/base/12139/11928 (16KB, 95%) checksum 393237a241bd3df306966151f759a937612dc0ba
P01   INFO: backup file db-primary:/var/lib/postgresql/9.4/demo/backup_label (238B, 95%) checksum 8c89fd251e38e41256a4be2b4a079b4191b54080
P02   INFO: backup file db-standby:/var/lib/postgresql/9.4/demo/pg_clog/0000 (8KB, 96%) checksum b36e351f7d4325a9b2f538bbfa5d97100c50f288
P03   INFO: backup file db-standby:/var/lib/postgresql/9.4/demo/global/11881_vm (8KB, 96%) checksum df7f4ef33268234e4c671f7810ba2fb59339e4a4
       [filtered 17 lines of output]
This incremental backup shows that most of the files are copied from the db-standby host and only a few are copied from the db-primary host.
pgBackRest creates a standby backup that is identical to a backup performed on the primary. It does this by starting/stopping the backup on the db-primary host, copying only files that are replicated from the db-standby host, then copying the remaining few files from the db-primary host. This means that logs and statistics from the primary database will be included in the backup.
15
Upgrading PostgreSQL
Immediately after upgrading PostgreSQL to a newer major version, the db-path for all pgBackRest configurations must be set to the new database location and the stanza-upgrade run on the backup host. If the database is offline use the --no-online option.
The following instructions are not meant to be a comprehensive guide for upgrading PostgreSQL, rather they will outline the general process for upgrading a primary and standby with the intent of demonstrating the steps required to reconfigure pgBackRest. It is recommended that a backup be taken prior to upgrading.
db-primary Install new PostgreSQL version
sudo apt-get install postgresql-9.5
Create the new cluster. If the PostgreSQL install creates a default cluster, then remove it to avoid confusion.
db-primary Drop default cluster and create the new demo cluster
sudo pg_dropcluster 9.5 main
sudo -u postgres /usr/lib/postgresql/9.5/bin/initdb \
       -D /var/lib/postgresql/9.5/demo -k -A peer
sudo pg_createcluster 9.5 demo
Configuring already existing cluster (configuration: /etc/postgresql/9.5/demo, data: /var/lib/postgresql/9.5/demo, owner: 5000:5000)
Ver Cluster Port Status Owner    Data directory               Log file
9.5 demo    5433 down   postgres /var/lib/postgresql/9.5/demo /var/log/postgresql/postgresql-9.5-demo.log
Stop the old cluster on the standby since it will be restored from the newly upgraded cluster to ensure the database system id is identical on both the primary and standby.
db-standby Stop old cluster and drop default cluster if created
sudo pg_ctlcluster 9.4 demo stop
sudo apt-get install postgresql-9.5
sudo pg_dropcluster 9.5 main
Stop the old cluster on the primary and perform the upgrade.
db-primary Stop old cluster and perform the upgrade
sudo pg_ctlcluster 9.4 demo stop
sudo -u postgres sh -c 'cd /var/lib/postgresql && \
       /usr/lib/postgresql/9.5/bin/pg_upgrade \
       --old-bindir=/usr/lib/postgresql/9.4/bin \
       --new-bindir=/usr/lib/postgresql/9.5/bin \
       --old-datadir=/var/lib/postgresql/9.4/demo \
       --new-datadir=/var/lib/postgresql/9.5/demo \
       --old-options=" -c config_file=/etc/postgresql/9.4/demo/postgresql.conf" \
       --new-options=" -c config_file=/etc/postgresql/9.5/demo/postgresql.conf"'
       [filtered 68 lines of output]
Creating script to delete old cluster                       ok
Upgrade Complete
----------------
Optimizer statistics are not transferred by pg_upgrade so,
       [filtered 4 lines of output]
Configure the new cluster settings and port.
db-primary:/etc/postgresql/9.5/demo/postgresql.conf Configure PostgreSQL
archive_command = 'pgbackrest --stanza=demo archive-push %p'
archive_mode = on
listen_addresses = '*'
log_line_prefix = ''
max_wal_senders = 3
port = 5432
wal_level = hot_standby
Update the pgBackRest configuration on all systems to point to the new cluster.
db-primary:/etc/pgbackrest.conf Upgrade the db-path
[demo]
db-path=/var/lib/postgresql/9.5/demo

[global]
archive-async=y
backup-host=backup
backup-user=backrest
log-level-file=detail
spool-path=/var/spool/pgbackrest

[global:archive-push]
process-max=2
db-standby:/etc/pgbackrest.conf Upgrade the db-path
[demo]
db-path=/var/lib/postgresql/9.5/demo
recovery-option=standby_mode=on
recovery-option=primary_conninfo=host=172.17.0.3 port=5432 user=replicator

[global]
backup-host=backup
backup:/etc/pgbackrest.conf Upgrade db1-path and db2-path, disable backup from standby
[demo]
db1-host=db-primary
db1-path=/var/lib/postgresql/9.5/demo
db1-user=postgres
db2-host=db-standby
db2-path=/var/lib/postgresql/9.5/demo
db2-user=postgres

[global]
backup-standby=n
process-max=3
repo-path=/var/lib/pgbackrest
retention-full=2
start-fast=y
db-primary Copy hba configuration
sudo cp /etc/postgresql/9.4/demo/pg_hba.conf \
       /etc/postgresql/9.5/demo/pg_hba.conf
Before starting the new cluster, the stanza-upgrade command must be run on the server where the pgBackRest repository is located.
backup Upgrade the stanza
sudo -u backrest pgbackrest --stanza=demo --no-online \
       --log-level-console=info stanza-upgrade
P00   INFO: stanza-upgrade command begin 1.24: --no-backup-standby --db1-host=db-primary --db1-path=/var/lib/postgresql/9.5/demo --db1-user=postgres --db2-host=db-standby --db2-path=/var/lib/postgresql/9.5/demo --db2-user=postgres --log-level-console=info --no-log-timestamp --no-online --repo-path=/var/lib/pgbackrest --stanza=demo
P00   INFO: stanza-upgrade command end: completed successfully
Start the new cluster and confirm it is successfully installed.
db-primary Start new cluster
sudo pg_ctlcluster 9.5 demo start
Test configuration using the check command. The warning on the backup host regarding the standby being down is expected and can be ignored.
db-primary Check configuration
sudo -u postgres pg_lsclusters
Ver Cluster Port Status Owner    Data directory               Log file
9.4 demo    5432 down   postgres /var/lib/postgresql/9.4/demo /var/log/postgresql/postgresql-9.4-demo.log
9.5 demo    5432 online postgres /var/lib/postgresql/9.5/demo /var/log/postgresql/postgresql-9.5-demo.log
sudo -u postgres pgbackrest --stanza=demo check
backup Check configuration
sudo -u backrest pgbackrest --stanza=demo check
P00   WARN: [056]: raised on 'db-standby remote' host: could not connect to server: No such file or directory
            	Is the server running locally and accepting
            	connections on Unix domain socket "/var/run/postgresql/.s.PGSQL.5432"?
Remove the old cluster.
db-primary Remove old cluster
sudo pg_dropcluster 9.4 demo
Run a full backup on the new cluster and then restore the standby from the backup. The backup type will automatically be changed to full if incr or diff is requested.
backup Run a full backup
sudo -u backrest pgbackrest --stanza=demo --type=full backup
Install the new PostgreSQL binaries on the standby and create the cluster.
db-standby Remove old cluster and initialize new one
sudo pg_dropcluster 9.4 demo
sudo -u postgres /usr/lib/postgresql/9.5/bin/initdb \
       -D /var/lib/postgresql/9.5/demo -k -A peer
sudo pg_createcluster 9.5 demo
Configuring already existing cluster (configuration: /etc/postgresql/9.5/demo, data: /var/lib/postgresql/9.5/demo, owner: 5000:5000)
Ver Cluster Port Status Owner    Data directory               Log file
9.5 demo    5432 down   postgres /var/lib/postgresql/9.5/demo /var/log/postgresql/postgresql-9.5-demo.log
db-standby Restore the demo standby cluster
sudo -u postgres pgbackrest --stanza=demo --delta restore
db-standby:/etc/postgresql/9.5/demo/postgresql.conf Configure PostgreSQL
hot_standby = on
db-standby Start PostgreSQL
sudo pg_ctlcluster 9.5 demo start
Backup from standby can be enabled now that the standby is restored.
backup:/etc/pgbackrest.conf Reenable backup from standby
[demo]
db1-host=db-primary
db1-path=/var/lib/postgresql/9.5/demo
db1-user=postgres
db2-host=db-standby
db2-path=/var/lib/postgresql/9.5/demo
db2-user=postgres

[global]
backup-standby=y
process-max=3
repo-path=/var/lib/pgbackrest
retention-full=2
start-fast=y