PGBackRest Configuration from Standby Database

pgbackrest is the faster backup tools and the completed feature for backup. I choose pgbackrest because barman cannot handle in high wal generated. My Database have 22.000 until 40.000 wal per hours (1 wal = 1mb). Here I have 3 servers. 10.10.10.1 - pg1 as master postgresql 10.10.10.2 - pg2 as standby postgresql 10.10.10.3 - pg3 as backup server since my master databases very busy I will backup the database from standby. here is my configuration. master database - /etc/pgbackrest.conf [global] repo1-host=10.10.10.3 repo1-host-user=postgres repo1-path=/pgbackuprest start-fast=y log-level-console=info log-level-file=detail compress-level=1 process-max=4 compress-type=lz4 archive-async=y spool-path=/archspool archive-push-queue-max=5GiB [mycluster] pg1-path=/data pg1-port=5432 standby database - /etc/pgbackrest.conf [global] repo1-host=10.10.10.3 repo1-host-user=postgres repo1-path=/pgbackuprest start-fast=y log-level-console=info log-level-file=detail compress-level=1 process-max=12 archive-async=y spool-path=/arc_temp archive-get-queue-max=5GiB compress-type=lz4 [mypgback] pg1-path=/data pg1-port=5432 Don't forget to change in master and standby to push the archive postgres=# show archive_command ; archive_command ----------------------------------------------- pgbackrest --stanza=mypgback archive-push %p (1 row) this is for restore wal postgres=# show restore_command ; restore_command --------------------------------------------------- pgbackrest --stanza=mypgback archive-get %f "%p" (1 row) pg3 - /etc/pgbackrest.conf [global] repo1-path=/pgbackuprest repo1-retention-full=1 start-fast=y log-level-console=info log-level-file=detail process-max=12 archive-timeout=1h compress-level=1 backup-standby=y log-path=/pgbackuprest/log compress-type=lz4 [mypgback] pg1-host=10.10.10.1 pg1-host-user=postgres pg1-port=5432 pg1-path=/data pg2-host=10.10.10.2 pg2-host-user=postgres pg2-port=5432 pg2-path=/data Set the pgbackrest on crontab 5 17 * * 6 /usr/bin/pgbackrest --stanza=mypgback backup --type=full 5 18 * * 0-5 /usr/bin/pgbackrest --stanza=mypgback backup --type=incr Here is the result from pgbackrest backup pgbackest info stanza: mypgback status: ok cipher: none db (current) wal archive min/max (12): 000000090002877600000046/00000009000289F100000097 full backup: 20241210-180000F timestamp start/stop: 2024-12-10 18:00:55+08 / 2025-03-10 22:09:09+08 wal start/stop: 000000090002877600000046 / 00000009000287E10000000B database size: 21821.5GB, database backup size: 21821.5GB repo1: backup set size: 3395.6GB, backup size: 3395.6GB as we can see from the result my database size is 21821.5GB but the back size 33395.6GB

Mar 11, 2025 - 00:01

PGBackRest Configuration from Standby Database

pgbackrest is the faster backup tools and the completed feature for backup. I choose pgbackrest because barman cannot handle in high wal generated.
My Database have 22.000 until 40.000 wal per hours (1 wal = 1mb).
Here I have 3 servers.

10.10.10.1 - pg1 as master postgresql
10.10.10.2 - pg2 as standby postgresql
10.10.10.3 - pg3 as backup server

since my master databases very busy I will backup the database from standby.

here is my configuration.

master database - /etc/pgbackrest.conf

[global]
repo1-host=10.10.10.3
repo1-host-user=postgres
repo1-path=/pgbackuprest
start-fast=y
log-level-console=info
log-level-file=detail
compress-level=1
process-max=4
compress-type=lz4
archive-async=y
spool-path=/archspool
archive-push-queue-max=5GiB

[mycluster]
pg1-path=/data
pg1-port=5432

standby database - /etc/pgbackrest.conf

[global]
repo1-host=10.10.10.3
repo1-host-user=postgres
repo1-path=/pgbackuprest
start-fast=y
log-level-console=info
log-level-file=detail
compress-level=1
process-max=12
archive-async=y
spool-path=/arc_temp
archive-get-queue-max=5GiB
compress-type=lz4

[mypgback]
pg1-path=/data
pg1-port=5432

Don't forget to change in master and standby to push the archive

postgres=# show archive_command ;
                archive_command
-----------------------------------------------
 pgbackrest --stanza=mypgback archive-push %p
(1 row)

this is for restore wal

postgres=# show restore_command ;
                  restore_command
---------------------------------------------------
 pgbackrest --stanza=mypgback archive-get %f "%p"
(1 row)

pg3 - /etc/pgbackrest.conf

[global]
repo1-path=/pgbackuprest
repo1-retention-full=1
start-fast=y
log-level-console=info
log-level-file=detail
process-max=12
archive-timeout=1h
compress-level=1
backup-standby=y
log-path=/pgbackuprest/log
compress-type=lz4

[mypgback]
pg1-host=10.10.10.1
pg1-host-user=postgres
pg1-port=5432
pg1-path=/data
pg2-host=10.10.10.2
pg2-host-user=postgres
pg2-port=5432
pg2-path=/data

Set the pgbackrest on crontab

5 17 * * 6 /usr/bin/pgbackrest --stanza=mypgback backup --type=full
5 18 * * 0-5 /usr/bin/pgbackrest --stanza=mypgback backup --type=incr

Here is the result from pgbackrest backup
pgbackest info

stanza: mypgback 
    status: ok
    cipher: none

    db (current)
        wal archive min/max (12): 000000090002877600000046/00000009000289F100000097

        full backup: 20241210-180000F
            timestamp start/stop: 2024-12-10 18:00:55+08 / 2025-03-10 22:09:09+08
            wal start/stop: 000000090002877600000046 / 00000009000287E10000000B
            database size: 21821.5GB, database backup size: 21821.5GB
            repo1: backup set size: 3395.6GB, backup size: 3395.6GB

as we can see from the result my database size is 21821.5GB but the back size 33395.6GB