DEV Community

dimas
dimas

Posted on

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.

  1. 10.10.10.1 - pg1 as master postgresql
  2. 10.10.10.2 - pg2 as standby postgresql
  3. 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

Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode

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)
Enter fullscreen mode Exit fullscreen mode

this is for restore wal

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


Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode

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


Enter fullscreen mode Exit fullscreen mode

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

Hostinger image

Get n8n VPS hosting 3x cheaper than a cloud solution

Get fast, easy, secure n8n VPS hosting from $4.99/mo at Hostinger. Automate any workflow using a pre-installed n8n application and no-code customization.

Start now

Top comments (0)

AWS GenAI LIVE image

Real challenges. Real solutions. Real talk.

From technical discussions to philosophical debates, AWS and AWS Partners examine the impact and evolution of gen AI.

Learn more

👋 Kindness is contagious

Please leave a ❤️ or a friendly comment on this post if you found it helpful!

Okay