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
Top comments (0)