pgBackRest - Performing Backups on a Standby Cluster

pgBackRest is an open source tool designed for efficiently performing backups and restores for PostgreSQL on up to terabytes of data at a time. Its aim is to be reliable, scalable, and flexible while offering robust performance during the backup process.

Many powerful features are included in pgBackRest, including parallel backup and restore, local or remote operation, full, incremental, and differential backup types, backup rotation, archive expiration, backup integrity, page checksums, backup resume, streaming compression and checksums, delta restore, and much more.

A difficult scenario which may present itself to database engineers is ensuring the high availability of your PostgreSQL cluster, which includes ensuring all backups are performing consistently and well without impacting the primary database host. Occasionally, you may experience a substantial system load on the primary database whenever a pgBackRest backup is running if you have a sizable enough amount of data.

An effective solution to this issue is to enable the option to enable the performance of backups from the replica rather than the primary; the bulk of the impact to I/O then occurs on the replica node and minimizes the load on the primary host. The only connections being made at that point would be from the replica to the primary in order to obtain information and log files.

It is additionally possible to configure more than one replica to work with pgBackRest; in this case, the first running standby found will be chosen and used for the backup process. If one of the standby nodes fail, pgBackRest will automatically find and proceed to run the backup on the next available replica that is configured.

Please note that the ability to define more than one standby was not available until version 1.23 of pgBackRest; in this version, multiple standby support was introduced that allows up to seven replicas to be configured rather than the limit of two from prior to that release. See the release notes here.

Additionally, if you are following this guide with the intent of configuring it for your current cluster and your version of pgBackRest is prior to version 2, please note that any configuration parameters beginning in “pg” will need to be re-referenced as “db”.

Specific documentation from pgBackRest’s official website for configuring the backup from standby option in version 1 can be found here.

In the following guide, we will explore configuring pgBackRest on two separate CentOS 7 servers and enabling the ability to backup PostgreSQL from the replica.

Configure the Virtual Machines

If following this guide exclusively for tutorial purposes, it may be useful to follow the same settings as were used while documenting the exercise.

For this example, two virtual machines were created using VirtualBox. An .iso of the latest release of CentOS 7 was used for installation of the operating system. One is an exact “full clone” of the other, so all settings and configurations as follows are replicated for both machines:

• 12 GB single partition ext4 hard disk
• 2 GB RAM
• 1 CPU

This guide was followed when configuring the two virtual machines to use an internal network for communicating.

NFS Configuration

Both machines in this tutorial are configured with the NFS shared storage system, following the directions found here. The NFS folder configured for this example is located at /pgbackrest-nfs on the primary and /mnt/pgbackrest-nfs on the replica.

Install PostgreSQL and pgBackRest

There are several methods for installing PostgreSQL on a CentOS 7 server that are detailed on the PostgreSQL wiki. For the purposes of this guide, we will install both PostgreSQL 11 and the latest version of pgBackRest using the PGDG Yum repository. At the time of this writing, the pgBackRest installation resulted in version 2.10.

On both the primary and the replica servers, run the following commands:

sudo yum -y install https://download.postgresql.org/pub/repos/yum/11/redhat/rhel-7-x86_64/pgdg-centos11-11-2.noarch.rpm
sudo yum -y install postgresql11-server postgresql11-contrib pgbackrest

Next, on the primary, it will be necessary to initialize a PostgreSQL cluster with the following commands.

sudo /usr/pgsql-11/bin/postgresql-11-setup initdb
sudo systemctl enable postgresql-11
sudo systemctl start postgresql-11

The first command is only necessary to run once, and is responsible for initializing the database in PGDATA. The second will ensure PostgreSQL will start automatically when the operating system is turned on, and the third will start the database at the present time.

To verify the pgBackRest installation, either pgbackrest or pgbackrest version can be called as either the default user or the postgres user.

$ pgbackrest
pgBackRest 2.10 - 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-delete Delete a stanza.
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.

To verify PostgreSQL has installed and started correctly, at this point in time, you can check the version of the PostgreSQL command line tool:

$ sudo -iu postgres psql --version
psql (PostgreSQL) 11.2

Configure Replication from the Primary

At this point, it’s necessary to configure streaming replication between the primary and the replica. If you’re unaware of where your configuration files are, on the primary, you can run the following:

$ sudo -iu postgres psql -U postgres -c 'SHOW config_file'
config_file
----------------------------------------
/var/lib/pgsql/11/data/postgresql.conf
(1 row)

On the primary, edit the postgresql.conf file with root privileges with your preferred text editor. The following parameters will need to be uncommented and set:

listen_addresses = '*'
password_encryption=’scram-sha-256’
archive_mode = on

As an additional note, for a more secure setup, listen_addresses can be set for the IP address of the current host. For the purposes of this example, that IP is 10.10.10.1.

listen_addresses = '10.10.10.1'

While pg_hba.conf is the file responsible for restricting connections, when listen_addresses is set for ‘*’, an attacker would be able to discover the open port on 5432 using nmap and know it exists, thereby possibly opening the server up for an exploit. Setting it to the exact IP address prevents PostgreSQL from listening on an unintended interface, preventing this potential exploit. More information on this specific attack vector and how to avoid it can be found in this blog post.

As an additional note regarding password_encryption - SCRAM-SHA-256 authentication was added in PostgreSQL 10, and is used in this example for the purposes of encouraging secure connections in your cluster. Specifically, this method of authentication prevents password sniffing on untrusted connections and offers support for cryptographically hashing passwords on the server in a secure manner. More detailed information on this authentication method can be found here.

In the major release for PostgreSQL 10, a change was introduced to reduce the configuration edits that were necessary to perform streaming backup and replication (specifically affecting the parameters wal_level, max_wal_senders, max_replication_slots, and hot_standby - these are now all set by default). The release notes regarding this change can be found here.

Because of this, prior to PostgreSQL version 10.0, the following parameters will additionally need to be set in the configuration file.

max_wal_senders=3
wal_level='replica'

We’ll now restart PostgreSQL to allow the changes to postgresql.conf to take effect.

sudo systemctl restart postgresql-11

It is required to restart the cluster here, rather than reload, as all of the particular values changed above require a restart to cause them to take effect. That particular attribute for the values can be checked by running the following commands.

sudo -iu postgres psql

SELECT name,setting,context,source FROM pg_settings WHERE NAME IN ('listen_addresses',’archive_mode’,'password_encryption');
name | setting | context | source
---------------------+---------------+------------+--------------------
listen_addresses | 10.10.10.1 | postmaster | configuration file
archive_mode | on | postmaster | configuration file
password_encryption | scram-sha-256 | user | configuration file

(3 rows)

As you can see in the above output, the context for listen_addresses and archive_mode is set to postmaster. Any setting that has the context of postmaster will require a full restart of the PostgreSQL cluster before it will take effect. Settings that have the context of sighup require only a reload of the database, and the context of user indicates the changes will take place in the same user session. More information on pg_settings can be found in the PostgreSQL documentation.

Next, create a user that will be used uniquely for the purposes of replication:

sudo -iu postgres psql

CREATE USER replicate REPLICATION LOGIN CONNECTION LIMIT 1 ENCRYPTED PASSWORD 'mypassword';
CREATE ROLE

The password can be set to any passphrase of your choosing. Alternatively, if you prefer to keep the passphrase not visible in the command line history:

CREATE ROLE replicate WITH REPLICATION LOGIN;
CREATE ROLE
\password replicate
Enter new password:
Enter it again:

Next, it’s necessary to add the following line to pg_hba.conf on the primary server under “IPv4 Local Connections”. Similarly to before, the location of the pg_hba.conf file can be found in the following manner:

$ sudo -iu postgres psql -U postgres -c 'SHOW hba_file'
hba_file
------------------------------------
/var/lib/pgsql/11/data/pg_hba.conf
(1 row)

The following line permits the replication user to connect through the replica server, with their authentication being verified with the scram-sha-256 authentication method. Make sure to replace the IP address with that of the replica server.

host replication replicate 10.10.10.2/32 scram-sha-256

We are following the secure standard set earlier in this guide of using scram-sha-256 for authentication purposes; however, the different authentication methods available for pg_hba.conf are available for perusal in the PostgreSQL documentation.

Next, reload the cluster to allow the changes made above to take effect:

sudo systemctl reload postgresql-11

Configure pgBackRest on the Primary

Next, we’ll need to initialize pgBackRest on the primary. As good practice, first backup the existing pgbackrest.conf file:

sudo cp /etc/pgbackrest.conf /etc/pgbackrest.conf.backup

Edit pgbackrest.conf with the editor of your choosing and configure it with the following parameters.

[global]
repo1-path=/pgbackrest-nfs
repo1-retention-full=2
log-level-console=info
log-level-file=debug

[demo]
pg1-path=/var/lib/pgsql/11/data

The [global] section defines the location of the backups and the logging settings.

[demo] defines a new stanza which we will create later on, and defines the path.

More information on the above parameters and other options for configuring your pgBackRest configuration file can be found in the pgBackRest Configuration guide.

Then, edit postgresql.conf and uncomment and set the following parameters:

archive_command = 'pgbackrest --stanza=demo archive-push %p'

Reload the PostgreSQL cluster using systemctl to allow the change to take effect.

Next, let’s initialize the pgBackRest stanza, which contains the definitions for the location, archiving options, backup settings, and other similar configurations for the PostgreSQL database cluster. There is generally one stanza defined for each database cluster that needs to have backups. The stanza-create command must be run on the primary host after pgbackrest.conf has been configured.

$ sudo -u postgres pgbackrest --stanza=demo stanza-create
2019-02-11 07:17:10.538 P00 INFO: stanza-create command begin 2.10: --log-level-console=info --log-level-file=off --pg1-path=/var/lib/pgsql/11/data --repo1-path=/pgbackrest-nfs --stanza=demo
2019-02-11 07:17:11.179 P00 INFO: stanza-create command end: completed successfully (642ms)

You may see a warning at the top of the output, similar to the following:

WARN: unable to open log file '/var/log/pgbackrest/demo-stanza-create.log': Permission denied
NOTE: process will continue without log file.

If you see this, check the permissions on the /var/log/pgbackrest folder. You may find you need to change the owner; this should resolve the above error.

chown postgres:postgres /var/log/pgbackrest

Finally, we’ll check the cluster using the pgbackrest check command; this validates that pgBackRest and the archive_command settings are both accurately configured and performing as expected. More information on the pgbackrest check command can be found on the pgBackRest Commands documentation.

$ sudo -iu postgres pgbackrest --stanza=demo check
2019-02-11 07:20:00.548 P00 INFO: check command begin 2.10: --log-level-console=info --log-level-file=debug --pg1-path=/var/lib/pgsql/11/data --repo1-path=/pgbackrest-nfs --stanza=demo
2019-02-11 07:20:03.066 P00 INFO: WAL segment 000000010000000000000001 successfully stored in the archive at '/pgbackrest-nfs/archive/demo/11-1/0000000100000000/000000010000000000000001-64482872a5d9916e751dbcf39e441ac554a97abc.gz'
2019-02-11 07:20:03.067 P00 INFO: check command end: completed successfully (2520ms)

Configure Passwordless SSH

Next, it’s necessary to set up a SSH connection between the hosts to ensure they can communicate properly. Run the following commands on the replica host:

sudo useradd --system --home-dir "/var/lib/pgbackrest" --comment "pgBackRest user" backrest
sudo chmod 700 /var/lib/pgbackrest
sudo chown backrest:backrest /var/lib/pgbackrest
sudo chown backrest:backrest /var/log/pgbackrest

Continuing to apply these commands to the replica server, create the SSH keys for the postgres user and verify the correct SELinux contexts:

sudo -u backrest ssh-keygen -N "" -t rsa -b 4096 -f /var/lib/pgbackrest/.ssh/id_rsa
sudo -u backrest restorecon -R /var/lib/pgbackrest/.ssh

On the primary server, repeat the above actions for the postgres user:

sudo -u postgres ssh-keygen -N "" -t rsa -b 4096 -f /var/lib/pgsql/.ssh/id_rsa
sudo -u postgres restorecon -R /var/lib/pgsql/.ssh

From the primary, copy the public key from the replica (replacing the IP address below with the IP address of your replica):

sudo scp root@10.10.10.2:/var/lib/pgbackrest/.ssh/id_rsa.pub /var/lib/pgsql/.ssh/authorized_keys

And repeat the same for the replica server, replacing the IP address with that of your primary host:

sudo scp root@10.10.10.1:/var/lib/pgsql/.ssh/id_rsa.pub /var/lib/pgbackrest/.ssh/authorized_keys

Afterwards, you can use the following commands (run respectively from the replica server, then the primary server) to test the SSH connection is working as expected, with no password required at login.

sudo -u backrest ssh postgres@10.10.10.1
sudo -u postgres ssh backrest@10.10.10.2

Perform Initial Backup

Take an initial full backup on the primary server that we will later restore from on the replica.

$ sudo -u postgres pgbackrest --stanza=demo --type=full backup
2019-02-11 07:40:20.345 P00 INFO: backup command begin 2.10: --log-level-console=info --log-level-file=debug --pg1-path=/var/lib/pgsql/11/data --repo1-path=/pgbackrest-nfs --repo1-retention-full=2 --stanza=demo --type=full
2019-02-11 07:40:21.400 P00 INFO: execute non-exclusive pg_start_backup() with label "pgBackRest backup started at 2019-02-11 07:40:20": backup begins after the next regular checkpoint completes
2019-02-11 07:40:21.631 P00 INFO: backup start archive = 000000010000000000000003, lsn = 0/3000028
2019-02-11 07:40:24.187 P01 INFO: backup file /var/lib/pgsql/11/data/base/13881/1255 (608KB, 2%) checksum ebc82159c9d12759c60445f1b8715bc492853bbd
[OUTPUT TRUNCATED]
2019-02-11 07:40:42.686 P00 INFO: full backup size = 23.4MB
2019-02-11 07:40:42.686 P00 INFO: execute non-exclusive pg_stop_backup() and wait for all WAL segments to archive
2019-02-11 07:40:42.802 P00 INFO: backup stop archive = 000000010000000000000003, lsn = 0/3000130
2019-02-11 07:40:43.631 P00 INFO: new backup label = 20190211-074020F
2019-02-11 07:40:43.737 P00 INFO: backup command end: completed successfully (23393ms)
2019-02-11 07:40:43.737 P00 INFO: expire command begin
2019-02-11 07:40:43.754 P00 INFO: full backup total < 2 - using oldest full backup for 11-1 archive retention
2019-02-11 07:40:43.761 P00 INFO: expire command end: completed successfully (24ms)

Configure pgBackRest on the Replica

Configure /etc/pgbackrest.conf on the replica with the following content.

[global]
repo1-path=/mnt/pgbackrest-nfs
repo1-retention-full=2
log-level-console=info
log-level-file=debug
delta=y

[demo]
pg1-host=10.10.10.1
pg1-path=/var/lib/pgsql/11/data
recovery-option=standby_mode=on
recovery-option=primary_conninfo=host=10.10.10.1 user=replicate
recovery-option=recovery_target_timeline=latest

Now, initialize the PostgreSQL cluster:

sudo /usr/pgsql-11/bin/postgresql-11-setup initdb

Finally, restore the backup taken from the primary server.

$ sudo -u postgres pgbackrest --stanza=demo restore
2019-02-11 08:03:55.573 P00 INFO: restore command begin 2.10: --delta --log-level-console=info --log-level-file=off --pg1-path=/var/lib/pgsql/11/data --repo1-path=/mnt/pgbackrest-nfs --stanza=demo
2019-02-11 08:03:55.861 P00 INFO: restore backup set 20190211-074020F
2019-02-11 08:03:56.467 P00 INFO: remove invalid files/paths/links from /var/lib/pgsql/11/data
2019-02-11 08:03:56.824 P00 INFO: cleanup removed 3 files
2019-02-11 08:03:57.594 P01 INFO: restore file /var/lib/pgsql/11/data/base/13881/1255 (608KB, 2%) checksum ebc82159c9d12759c60445f1b8715bc492853bbd
2019-02-11 08:03:57.604 P01 INFO: restore file /var/lib/pgsql/11/data/base/13880/1255 (608KB, 5%) checksum ebc82159c9d12759c60445f1b8715bc492853bbd
...
2019-02-11 08:04:13.647 P01 INFO: restore file /var/lib/pgsql/11/data/current_logfiles (30B, 99%) checksum 3e879a8ee40722437310e7d18b134c5397663490
2019-02-11 08:04:14.098 P00 INFO: write /var/lib/pgsql/11/data/recovery.conf
2019-02-11 08:04:14.101 P00 INFO: restore global/pg_control (performed last to ensure aborted restores cannot be started)
2019-02-11 08:04:14.108 P00 INFO: restore command end: completed successfully (18536ms)

Above, we configured pgBackRest to automatically restore using the --delta option; this will enable backing up using checksums rather than timestamps to determine if the files should be copied. Additionally, by default when restoring, pgBackRest will require the database data directory to be cleaned before performing the restore. The delta option enables pgBackRest to automatically determine which files to preserve or restore, and removes files not present in the backup manifest.

Before starting the PostgreSQL cluster, it’s necessary to ensure listen_addresses is set to the correct parameter if it was set to a specific IP address on the primary earlier on. The reason for this is when you restore from a backup, it will also automatically restore all PostgreSQL configuration files from the original host - therefore, when we attempt to start the PostgreSQL server on the replica, it will attempt to assign the wrong IP address.

Next, we’ll enable and start the PostgreSQL service.

sudo systemctl enable postgresql-11
sudo systemctl start postgresql-11

You can verify everything is successfully running by entering the following command:

sudo systemctl status postgresql-11

Now, on the replica, adjust the /etc/pgbackrest.conf file:

[global]
repo1-path=/mnt/pgbackrest-nfs
repo1-retention-full=2
log-level-console=info
log-level-file=debug
backup-standby=y
delta=y

[demo]
pg1-host=10.10.10.1
pg1-path=/var/lib/pgsql/11/data
pg2-path=/var/lib/pgsql/11/data
recovery-option=standby_mode=on
recovery-option=primary_conninfo=host=10.10.10.1 user=replicate
recovery-option=recovery_target_timeline=latest

On the replica, you should now be able to successfully check the full backup that was taken earlier and perform additional backups for the database located on the primary host.

$ sudo -u backrest pgbackrest info
stanza: demo
status: ok
cipher: none

db (current)
wal archive min/max (11-1): 000000010000000000000003/000000010000000000000003

full backup: 20190211-074020F
timestamp start/stop: 2019-02-11 07:40:20 / 2019-02-11 07:40:43
wal start/stop: 000000010000000000000003 / 000000010000000000000003
database size: 23.5MB, backup size: 23.5MB
repository size: 2.8MB, repository backup size: 2.8MB