We will use the same PostgreSQL cluster to show How we can take a backup from Standby Cluster using pgBackRest.
Below are the high-level steps which we will follow to setup it.
1. Edit file /etc/pgbackrest.conf file on Standby Node.
2. Perform a backup from Standby.
Below are setup details and the same will be used in this demonstration.
| Sr No. | Hostname | IP | Role |
| 1 | test-machine01 | 192.168.114.177 | Master / Primary Server |
| 2 | test-machine02 | 192.168.114.176 | Standby / Secondary Server |
Step 1. Edit file /etc/pgbackrest.conf file on Standby Node: pgBackRest can perform backups on a standby server instead of the primary. Both the primary and secondary databases configuration are required. pg1 represents Primary Node and pg2 represents Standby node. You need to set up a trusted SSH communication between the hosts.
[root@test-machine02 ~]# cat /etc/pgbackrest.conf
[global]
repo-path=/u01/psql_backup
retention-full=1
process-max=2
log-level-console=info
log-level-file=debug
start-fast=y
backup-standby=y
delta=y
[mystanza1]
pg1-host=test-machine01
pg1-path=/var/lib/pgsql/13/data
pg2-path=/var/lib/pgsql/13/data
recovery-option=primary_conninfo=host=test-machine01 user=repluser
[root@test-machine02 ~]#
Step 2. Perform a backup from Standby Node: Perform a backup from Standby Node
[postgres@test-machine02]
[postgres@test-machine02] pgbackrest --stanza=mystanza1 --type=full backup
2021-08-15 14:48:46.805 P00 INFO: backup command begin 2.34: --backup-standby --delta --exec-id=82984-c9ffb56c --log-level-console=info --log-level-file=debug --pg1-host=test-machine01 --pg1-path=/var/lib/pgsql/13/data --pg2-path=/var/lib/pgsql/13/data --process-max=2 --repo1-path=/u01/psql_backup --repo1-retention-full=1 --stanza=mystanza1 --start-fast --type=full
2021-08-15 14:48:48.405 P00 INFO: execute non-exclusive pg_start_backup(): backup begins after the requested immediate checkpoint completes
2021-08-15 14:48:48.973 P00 INFO: backup start archive = 000000010000000000000011, lsn = 0/11000028
2021-08-15 14:48:48.973 P00 INFO: wait for replay on the standby to reach 0/11000028
2021-08-15 14:48:49.279 P00 INFO: replay on the standby reached 0/11000028
2021-08-15 14:48:50.471 P03 INFO: backup file /var/lib/pgsql/13/data/pg_tblspc/16385/PG_13_202007201/16391/17812 (1.2MB, 1%) checksum 58139aa1b96186a5938b14e72c779fffd63208e7
2021-08-15 14:48:50.516 P02 INFO: backup file /var/lib/pgsql/13/data/pg_tblspc/16384/PG_13_202007201/16388/17168 (1.2MB, 2%) checksum 139844d06b0f49814e4b874475d3e07946100963
2021-08-15 14:48:50.574 P03 INFO: backup file /var/lib/pgsql/13/data/pg_tblspc/16385/PG_13_202007201/16389/17490 (1.2MB, 3%) checksum 532661cbd01824a1ac53fc2c6a9865e9ed5fda86
2021-08-15 14:48:50.607 P02 INFO: backup file /var/lib/pgsql/13/data/pg_tblspc/16384/PG_13_202007201/16386/16524 (1.2MB, 4%) checksum 7cdcb845c29ad9ac9d8c291d25998a594260132b
2021-08-15 14:48:50.676 P03 INFO: backup file /var/lib/pgsql/13/data/pg_tblspc/16385/PG_13_202007201/16387/16846 (1.2MB, 5%) checksum fd64808e179edd42f85668a214c64dd29d3002f6
2021-08-15 14:48:50.685 P02 INFO: backup file /var/lib/pgsql/13/data/pg_tblspc/16384/PG_13_202007201/16388/17162 (864KB, 6%) checksum 9045868508ad2e3244dec460afa32a4476945daf
2021-08-15 14:48:50.729 P03 INFO: backup file /var/lib/pgsql/13/data/pg_tblspc/16385/PG_13_202007201/16391/17806 (864KB, 6%) checksum 81e0bc936c611797a8c11102584bb39e31d3f3a0
2021-08-15 14:49:04.250 P02 INFO: backup file /var/lib/pgsql/13/data/base/1/14037 (0B, 100%)
2021-08-15 14:49:04.352 P03 INFO: backup file /var/lib/pgsql/13/data/base/1/14032 (0B, 100%)
2021-08-15 14:49:04.473 P02 INFO: backup file /var/lib/pgsql/13/data/base/1/14027 (0B, 100%)
2021-08-15 14:49:04.501 P00 INFO: full backup size = 108.7MB
2021-08-15 14:49:04.501 P00 INFO: execute non-exclusive pg_stop_backup() and wait for all WAL segments to archive
2021-08-15 14:49:04.710 P00 INFO: backup stop archive = 000000010000000000000011, lsn = 0/11000138
2021-08-15 14:49:04.718 P00 INFO: check archive for segment(s) 000000010000000000000011:000000010000000000000011
2021-08-15 14:49:04.856 P00 INFO: new backup label = 20210815-144848F
2021-08-15 14:49:04.932 P00 INFO: backup command end: completed successfully (18128ms)
2021-08-15 14:49:04.932 P00 INFO: expire command begin 2.34: --exec-id=82984-c9ffb56c --log-level-console=info --log-level-file=debug --repo1-path=/u01/psql_backup --repo1-retention-full=1 --stanza=mystanza1
2021-08-15 14:49:04.937 P00 INFO: repo1: expire full backup 20210815-122425F
2021-08-15 14:49:04.943 P00 INFO: repo1: remove expired backup 20210815-122425F
2021-08-15 14:49:07.031 P00 INFO: repo1: 13-1 remove archive, start = 00000001000000000000000C, stop = 000000010000000000000010
2021-08-15 14:49:07.031 P00 INFO: expire command end: completed successfully (2099ms)
[postgres@test-machine02]
[postgres@test-machine02]
[postgres@test-machine02]pgbackrest --stanza=mystanza1 --type=incr backup
2021-08-15 14:51:04.043 P00 INFO: backup command begin 2.34: --backup-standby --delta --exec-id=83111-b1cd8f92 --log-level-console=info --log-level-file=debug --pg1-host=test-machine01 --pg1-path=/var/lib/pgsql/13/data --pg2-path=/var/lib/pgsql/13/data --process-max=2 --repo1-path=/u01/psql_backup --repo1-retention-full=1 --stanza=mystanza1 --start-fast --type=incr
2021-08-15 14:51:05.657 P00 INFO: last backup label = 20210815-144848F, version = 2.34
2021-08-15 14:51:05.657 P00 INFO: execute non-exclusive pg_start_backup(): backup begins after the requested immediate checkpoint completes
2021-08-15 14:51:06.192 P00 INFO: backup start archive = 000000010000000000000013, lsn = 0/13000028
2021-08-15 14:51:06.192 P00 INFO: wait for replay on the standby to reach 0/13000028
2021-08-15 14:51:06.499 P00 INFO: replay on the standby reached 0/13000028
2021-08-15 14:51:07.532 P01 INFO: backup file test-machine01:/var/lib/pgsql/13/data/log/postgresql-Sun.log (1.7MB, 63%) checksum 063073d9953fe02b1d67d8620acd6250aa3839a4
2021-08-15 14:51:07.580 P01 INFO: backup file test-machine01:/var/lib/pgsql/13/data/global/pg_control (8KB, 65%) checksum c42e02e1205048f913c61ae18deba4c693223f39
2021-08-15 14:51:08.647 P00 INFO: incr backup size = 108.7MB
2021-08-15 14:51:08.647 P00 INFO: execute non-exclusive pg_stop_backup() and wait for all WAL segments to archive
2021-08-15 14:51:08.860 P00 INFO: backup stop archive = 000000010000000000000013, lsn = 0/13000100
2021-08-15 14:51:08.869 P00 INFO: check archive for segment(s) 000000010000000000000013:000000010000000000000013
2021-08-15 14:51:09.027 P00 INFO: new backup label = 20210815-144848F_20210815-145105I
2021-08-15 14:51:09.108 P00 INFO: backup command end: completed successfully (5066ms)
2021-08-15 14:51:09.108 P00 INFO: expire command begin 2.34: --exec-id=83111-b1cd8f92 --log-level-console=info --log-level-file=debug --repo1-path=/u01/psql_backup --repo1-retention-full=1 --stanza=mystanza1
2021-08-15 14:51:09.116 P00 INFO: repo1: 13-1 no archive to remove
2021-08-15 14:51:09.116 P00 INFO: expire command end: completed successfully (8ms)
[postgres@test-machine02]
[postgres@test-machine02]
[postgres@test-machine02]pgbackrest --stanza=mystanza1 info
stanza: mystanza1
status: ok
cipher: none
db (current)
wal archive min/max (13): 000000010000000000000011/000000010000000000000013
full backup: 20210815-144848F
timestamp start/stop: 2021-08-15 14:48:48 / 2021-08-15 14:49:04
wal start/stop: 000000010000000000000011 / 000000010000000000000011
database size: 108.7MB, database backup size: 108.7MB
repo1: backup set size: 16.7MB, backup size: 16.7MB
incr backup: 20210815-144848F_20210815-145105I
timestamp start/stop: 2021-08-15 14:51:05 / 2021-08-15 14:51:08
wal start/stop: 000000010000000000000013 / 000000010000000000000013
database size: 108.7MB, database backup size: 1.7MB
repo1: backup set size: 16.7MB, backup size: 68.6KB
backup reference list: 20210815-144848F
[postgres@test-machine02]This document is just for learning purpose and always validate in the LAB environment first before applying in the LIVE environment.
Hope so you like this article!