pgBackRest – Running Backup from a Standby Server

In our previous blog post, we discussed how to set up pgBackrest on a single standalone PostgreSQL Instance. When you run backups on a Primary Instance, we would be utilizing the computing power of the primary instance that takes the WRITES traffic. For this reason, one may choose to shift the backup job from primary to standby. These days, It is quite common to have a High Availability cluster with 1 Primary and one or more Standby hosts. Considering this fact, we could also run backups from a standby server and offload the overhead from primary to a standby server, during backups. In this article, we will see the steps involved in running backup from a standby server using pgBackRest.

Pre-requisites

Let us see the prerequisites to make from the backups from standby work seamlessly.

  1. It is important to have Streaming Replication setup between the Primary and Standby PostgreSQL Host.
    (This procedure is not applicable for a Subscriber (considered as a replica) of a Publisher replicating using logical replication).
  2. pgBackrest must be installed in Primary, Standby, and also Backup Server (if applicable). If you have not installed pgBackrest, refer to our previous blog post where we have explained how to install pgBackrest.
Setup demonstrated in this Article

In this article, we have included a setup that includes the following 3 servers -

  1. pg1 (Primary Server)
  2. pg2 (Standby Server)
  3. backupserver (Backup repository Server)

When we don't have a Backup Server but have a shared NFS filesystem to store backups, the steps performed on the backupserver can be performed on the standby server (pg2). This means that the NFS filesystem is mounted on both Primary and Standby servers.

pgBackRest Running Backup from a Standby Server
Steps to configure backups from a standby server using pgBackRest

Step 1 : Create the required directories on the Primary and Standby Servers for pgBackRest.

sudo mkdir -p /var/log/pgbackrest
sudo chown -R postgres:postgres /var/log/pgbackrest
sudo chmod -R 700 /var/log/pgbackrest
sudo mkdir -p /etc/pgbackrest
sudo chown postgres:postgres -R /etc/pgbackrest
sudo chmod -R 755 /etc/pgbackrest
sudo mkdir /var/spool/pgbackrest
sudo chown -R postgres:postgres /var/spool/pgbackrest
sudo chmod -R 755 /var/spool/pgbackrest
Contact Us for PostgreSQL Consulting

Step 2 : Enable trusted ssh authentication (password-less ssh) between all the three servers. 

pgbackrest@backupserver:~$ ssh-keygen -t rsa -b 2048
Generating public/private rsa key pair.
....
....
postgres@pg1:~$ ssh-keygen -t rsa -b 2048
Generating public/private rsa key pair.
....
....
postgres@pg1:~$ ssh-keygen -t rsa -b 2048
Generating public/private rsa key pair.
....
....
pgbackrest@backupserver:~$ ssh-copy-id postgres@pg1
pgbackrest@backupserver:~$ ssh-copy-id postgres@pg2
postgres@pg1:~$ ssh-copy-id pgbackrest@backupserver
postgres@pg2:~$ ssh-copy-id pgbackrest@backupserver
postgres@pg1:~$ ssh-copy-id postgres@pg2
postgres@pg2:~$ ssh-copy-id postgres@pg1

Note: In a production database, we could expect either an Automatic failover or  a manual switchover/failover. In such a case, a standby server can always become a Primary. So the following pgBackrest configuration files will be created on both pg1 and pg2 servers to make it easy to switch upon a failover.

Step 3 : Create the following pgBackRest configuration file on both PostgreSQL servers. This is only used for the purpose of Archiving from Primary.

$ vi /etc/pgbackrest/backrest_primary.conf

 

[global]
# Path where backups and archive are stored in repository
repo1-path=/backupdir
repo1-host=backupserver ip address or name
# Full backup retention count
repo1-retention-full=4
# Level for console logging [default=warn]
log-level-console=info
# Level for file logging [default=info]
log-level-file=info
# Max processes to use for compress/transfer
process-max=8
#Enabling parallel archiving instead of sequential
archive-async=y
#Creates a list of to be archived file list in the below location
spool-path=/var/spool/pgbackrest
#Username by which it connects to repository server
repo1-host-user=pgbackrest
[backupstanza]
pg1-path=/var/lib/pgsql/13/data
pg1-port=5432

Step 4 : Create the following pgBackRest configuration file on both PostgreSQL servers. This configuration file is only used by restore_command on the standby to catch up with replication.

$ vi /etc/pgbackrest/standby_restore_command.conf

 

[global]
# Path where backups and archive are stored
repo1-path=/backupdir/pg2
repo1-host=backupserver
# Level for console logging [default=warn]
log-level-console=info
# Level for file logging [default=info]
log-level-file=info
#Creates a list of to be archived file list in the below location
spool-path=/var/spool/pgBackrest
#Username by which it connects to repository server
repo1-host-user=pgbackrest

[backupstanza]
#data directory of PostgreSQL server
pg1-path=/var/lib/pgsql/13/data
#Port Number in which postgresql is running
pg1-port=5432

Step 5 : Set archive_command and restore_command to following settings on Primary and Standby servers.

postgres@pg1:~$ psql -c "ALTER SYSTEM SET archive_command TO 'pgbackrest --config=/etc/pgbackrest/backrest_primary.conf --stanza=backupstanza archive-push %p'"
ALTER SYSTEM

 

postgres@pg1:~$ psql -c "ALTER SYSTEM SET restore_command TO 'pgbackrest --config=/etc/pgbackrest/standby_restore_command.conf --stanza=backupstanza archive-get %f "%p"'"
ALTER SYSTEM

postgres@pg2:~$ psql -c "ALTER SYSTEM SET archive_command TO 'pgbackrest --config=/etc/pgbackrest/backrest_primary.conf --stanza=backupstanza archive-push %p'"
ALTER SYSTEM

postgres@pg2:~$ psql -c "ALTER SYSTEM SET restore_command TO 'pgbackrest --config=/etc/pgbackrest/standby_restore_command.conf --stanza=backupstanza archive-get %f "%p"'"
ALTER SYSTEM

postgres@pg1:~$ psql -c "select pg_reload_conf()"
pg_reload_conf 
----------------
 t
(1 row)

postgres@pg2:~$ psql -c "select pg_reload_conf()"
 pg_reload_conf 
----------------
 t
(1 row)

Step 6 : Create a New repository location for backups on the Backup Server. If there is no backup server but only the common mount on Standby, then, perform these steps on the Standby server.

sudo mkdir -p /backupdir
sudo chown -R pgbackrest:pgbackrest /backupdir
sudo chmod -R 700 /backupdir

Step 7 : Create a Stanza for the Primary server on the Backup Server. This command must be executed on the primary instance. 

$ pgbackrest --config=/etc/pgbackrest/backrest_primary.conf --stanza=backupstanza stanza-create

Output looks like following - 

postgres@pg1:~$ pgbackrest --config=/etc/pgbackrest/backrest_primary.conf --stanza=backupstanza stanza-create

 

2021-10-10 23:11:44.742 P00   INFO: stanza-create command begin 2.35: --config=/etc/pgbackrest/backrest_primary.conf --exec-id=91412-8d6c4c1b --log-level-console=info --log-level-file=info --pg1-path=/var/lib/postgresql/13/main --pg1-port=5432 --repo1-host=192.168.131.33 --repo1-host-user=postgres --repo1-path=/backupdir --stanza=backupstanza

2021-10-10 23:11:45.145 P00   INFO: stanza-create for stanza 'backupstanza' on repo1
2021-10-10 23:11:45.565 P00   INFO: stanza-create command end: completed successfully (824ms)

Step 8 : Validate configuration from the Primary

$ pgbackrest --config=/etc/pgbackrest/backrest_primary.conf --stanza=backupstanza check

Output appears as following

postgres@localhost:~$ pgbackrest --config=/etc/pgbackrest/backrest_primary.conf --stanza=backupstanza check

 

2021-10-10 23:17:56.466 P00   INFO: check command begin 2.35: --config=/etc/pgbackrest/backrest_primary.conf --exec-id=91597-4a03d0d8 --log-level-console=info --log-level-file=info --pg1-path=/var/lib/postgresql/13/main --pg1-port=5432 --repo1-host=192.168.131.33 --repo1-host-user=postgres --repo1-path=/backupdir --stanza=backupstanza

2021-10-10 23:17:56.871 P00   INFO: check repo1 configuration (primary)
2021-10-10 23:17:57.347 P00   INFO: check repo1 archive for WAL (primary)
2021-10-10 23:17:58.055 P00   INFO: WAL segment 00000003000000020000003C successfully archived to '/backupdir/archive/backupstanza/13-1/0000000300000002/00000003000000020000003C-b3c07345c0e0186255aae9a63f83f9692b83cf35.gz' on repo1
2021-10-10 23:17:58.155 P00   INFO: check command end: completed successfully (1689ms)

Step 9 : Create the following configuration file in the Backup server. This file will be used for performing backups from the standby server. 

Please Note : If there is no backup server, but you have an NFS file system mounted on both pg servers, you will be running backups from the Standby server. In this case, you need to remove the parameter: pg2-host=pg2 from the following configuration file. This is because, the backup will run locally and pgBackRest does not expect the pg2-host parameter when it is initiated from a standby host.

In case you don’t have a separate backup server, we can schedule a cronjob in both the pg server which will check the status of the PostgreSQL server (select pg_is_in_recovery) which is running locally, and then trigger the pgBackrest command. 

$ vi /etc/pgbackrest/backrest_standby.conf

 

[global]
# Path where backups and archive are stored in repository
repo1-path=/backupdir
# Full backup retention count
repo1-retention-full=4
# Level for console logging [default=warn]
log-level-console=info
# Level for file logging [default=info]
log-level-file=info
# Backup from the standby cluster
backup-standby=y
# Force a checkpoint to start backup quickly
start-fast=y
# Max parallel processes to use for compress/transfer while running a backup
process-max=8
# Copy WAL segments needed for consistency to the backup
archive-copy=y
#Username by which it connects to repository server
repo1-host-user=pgbackrest

[backupstanza]
pg1-path=/var/lib/pgsql/13/data
pg1-host=pg1
pg1-port=5432
pg1-user=postgres
pg2-path=/var/lib/pgsql/13/data
pg2-host=pg2
pg2-port=5432
pg2-user=postgres

Step 10 : Run backup from the backup server using the following command as an example.

As the configuration file contains the parameter backup-standby=y, it will run a backup of Globals from Primary and the actual data directory from the Standby.

$ pgbackrest --config=/etc/pgbackrest/backrest_standby.conf --stanza=backupstanza --type=full --archive-timeout=2000 --compress backup

Output from the above command - 

pgbackrest@backupserver:~$ pgbackrest --config=/etc/pgbackrest/backrest_standby.conf --stanza=backupstanza --type=full --archive-timeout=2000 --compress backup

 

2021-10-10 23:35:05.003 P00   INFO: backup command begin 2.35: --archive-copy --archive-timeout=2000 --backup-standby --compress --config=/etc/pgbackrest/backrest_standby.conf --exec-id=1881235-bea4562f --log-level-console=info --log-level-file=info --pg1-host=pg1 --pg2-host=pg2 --pg1-path=/var/lib/postgresql/13/main --pg2-path=/var/lib/postgresql/13/main --pg1-port=5432 --pg2-port=5432 --pg1-user=postgres --pg2-user=postgres --process-max=8 --repo1-path=/backupdir --repo1-retention-full=1 --stanza=montymobile --start-fast --type=full

2021-10-10 23:35:07.269 P00   INFO: execute non-exclusive pg_start_backup(): backup begins after the requested immediate checkpoint completes
2021-10-10 23:35:07.675 P00   INFO: backup start archive = 000000030000000200000044, lsn = 2/44000028
2021-10-10 23:35:07.675 P00   INFO: wait for replay on the standby to reach 2/44000028
2021-10-10 23:35:07.984 P00   INFO: replay on the standby reached 2/44000028
2021-10-10 23:35:17.091 P00   INFO: execute non-exclusive pg_stop_backup() and wait for all WAL segments to archive
2021-10-10 23:35:17.296 P00   INFO: backup stop archive = 000000030000000200000044, lsn = 2/44000138
2021-10-10 23:35:17.298 P00   INFO: check archive for segment(s) 000000030000000200000044:000000030000000200000044
2021-10-10 23:35:17.833 P00   INFO: new backup label = 20211010-233507F
2021-10-10 23:35:17.893 P00   INFO: full backup size = 30.8MB, file total = 1231
2021-10-10 23:35:17.894 P00   INFO: backup command end: completed successfully (12892ms)
2021-10-10 23:35:17.894 P00   INFO: expire command begin 2.35: --config=/etc/pgbackrest/backrest_standby.conf --exec-id=1881235-bea4562f --log-level-console=info --log-level-file=info --repo1-path=/backupdir --repo1-retention-full=1 --stanza=backupstanza
2021-10-10 23:35:17.894 P00   INFO: repo1: expire full backup 20211010-232942F
2021-10-10 23:35:17.897 P00   INFO: repo1: remove expired backup 20211010-232942F
2021-10-10 23:35:17.937 P00   INFO: repo1: 13-1 remove archive, start = 000000030000000200000041, stop = 000000030000000200000043
2021-10-10 23:35:17.937 P00   INFO: expire command end: completed successfully (43ms)

Step 11 : Validate the backup using the below command.

$ pgbackrest --config=/etc/pgbackrest/backrest_standby.conf --stanza= backupstanza info

Output Should look Similar to Below

pgbackrest@backupserver:~$ pgbackrest --config=/etc/pgbackrest/backrest_standby.conf --stanza=backupstanza info

 

stanza: backupstanza
    status: ok
    cipher: none
    db (current)
        wal archive min/max (13): 000000030000000200000044/000000030000000200000044
        full backup: 20211010-233507F
            timestamp start/stop: 2021-10-10 23:35:07 / 2021-10-10 23:35:17
            wal start/stop: 000000030000000200000044 / 000000030000000200000044
            database size: 46.8MB, database backup size: 46.8MB
            repo1: backup set size: 3.8MB, backup size: 3.8MB

Conclusion :

This article gives us detailed instructions on how to take a Backup using pgBackRest from a standby server in PostgreSQL. In our previous articles, we discussed about how to perform a backup and restore or point-in-time-recovery using pgBackRest. Subscribe to our newsletters for more updates about the latest PostgreSQL features. If you are looking for any services around Migrations to PostgreSQL or Consulting services for PostgreSQL, please fill the following form or Contact Us by sending an email to sales@migops.com

Leave a Comment

Your email address will not be published. Required fields are marked *

Scroll to Top