Backups from the Standby Server using pgBackRest in PostgreSQL

August 18, 2021
()

Backups from the Standby Server using pgBackRest in PostgreSQL

 

In the previous blog, we have configured Streaming Replication Cluster using pgBackRest Click here to read more. 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.HostnameIPRole
1test-machine01192.168.114.177Master / Primary Server
2test-machine02192.168.114.176Standby / 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!
Please share your valuable feedback/comments/subscribe and follow us below and don’t forget to click on the bell icon to get the most recent update. Click here to understand more about our pursuit.


Related Articles

, ,


Loading

How useful was this post?

Click on a star to rate it!

Average rating / 5. Vote count:

No votes so far! Be the first to rate this post.

As you found this post useful...

Follow us on social media!

We are sorry that this post was not useful for you!

Let us improve this post!

Tell us how we can improve this post?

Jamsher Khan

🌐

Hello and welcome to DBsGuru,I’m Jamsher Khan working as Senior Oracle DBA based in KSA-Jeddah, I have working experience in Oracle DBA, SQL Server, MySql, PostgreSQL, Linux, Golden Gate, ODA.Thanks for the visits!Share Learn Grow!

Leave a Reply

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