{"id":47,"date":"2023-02-25T16:17:39","date_gmt":"2023-02-25T15:17:39","guid":{"rendered":"https:\/\/tivuli-it.com\/?p=47"},"modified":"2023-02-25T16:47:31","modified_gmt":"2023-02-25T15:47:31","slug":"simple-pgbackrest-integration-in-to-a-patroni-cluster","status":"publish","type":"post","link":"https:\/\/tivuli-it.com\/index.php\/2023\/02\/25\/simple-pgbackrest-integration-in-to-a-patroni-cluster\/","title":{"rendered":"Simple pgbackrest integration in to a Patroni cluster"},"content":{"rendered":"\n

We will need a backup virtual machine (backupvm) with a big \/backup folder and an extra \/var\/log\/pgbackrest folder for logs. Pgbackrest on this machine will run as pgbackrest user\/group.
On the database servers (pg01 and pg02) we will also need a \/var\/log\/pgbackrest folder but on this machine it will be owned by postgres user. We will also need a spool folder for asynchronous log archiving on both database servers. Since we already have everything in \/app from patroni installation I will put spool in the \/app\/pgsql\/pgbackrest-spool folder (also owned by postgres).<\/p>\n\n\n\n

\"\"
System Architecture<\/figcaption><\/figure>\n\n\n\n

On backup machine:<\/p>\n\n\n\n

$ sudo mkdir \/var\/log\/pgbackrest \n$ sudo chown pgbackrest.pgbackrest \/var\/log\/pgbackrest\n$ sudo chmod 750 \/var\/log\/pgbackrest<\/code><\/pre>\n\n\n\n
Install pgbackrest on all three machines<\/p>\n\n\n\n
$ sudo yum install pgbackrest<\/code><\/pre>\n\n\n\n
On all DB servers:<\/p>\n\n\n\n
$ sudo mkdir \/var\/log\/pgbackrest \n$ sudo chown postgres.postgres \/var\/log\/pgbackrest\n$ sudo chmod 750 \/var\/log\/pgbackrest\n$ sudo mkdir -p \/app\/pgsql\/pgbackrest-spool\n$ sudo chown postgres.postgres \/app\/pgsql\/pgbackrest-spool\n$ sudo chmod 750 \/app\/pgsql\/pgbackrest-spool<\/code><\/pre>\n\n\n\n
Edit configuration file on all three servers:<\/p>\n\n\n\n
$ sudoedit \/etc\/pgbackrest.conf<\/code><\/pre>\n\n\n\n
——->backupvm<———–<\/p>\n\n\n\n
[pg0102]\npg1-host=pg01\npg1-path=\/app\/pgsql\/13\/data\npg2-host=pg02\npg2-path=\/app\/pgsql\/13\/data\n\n[global]\nrepo1-path=\/backup\/pgbackrest\nrepo1-retention-full-type=count\nrepo1-retention-full=2\nrepo1-bundle=y\nstart-fast=y\nprocess-max=4\nbackup-standby=y\nlog-path=\/var\/log\/pgbackrest<\/code><\/pre>\n\n\n\n
——-> pg01<———–<\/p>\n\n\n\n
[pg0102]\npg1-path=\/app\/pgsql\/13\/data\n\n[global]\nlog-level-file=detail\nlog-path=\/var\/log\/pgbackrest\nrepo1-host=backupvm\nrepo1-host-user=pgbackrest\narchive-async=y\nspool-path=\/app\/pgsql\/pgbackrest-spool\narchive-timeout=180\nprocess-max=8\n\n[global:archive-get]\nprocess-max=4\n\n[global:archive-push]\nprocess-max=8\ncompress-type=bz2<\/code><\/pre>\n\n\n\n
——-> pg02<———–<\/p>\n\n\n\n
[pg0102]\npg1-path=\/app\/pgsql\/13\/data\n\n[global]\nlog-level-file=detail\nlog-path=\/var\/log\/pgbackrest\nrepo1-host=backupvm\nrepo1-host-user=pgbackrest\narchive-async=y\nspool-path=\/app\/pgsql\/pgbackrest-spool\narchive-timeouot=180\nprocess-max=8\n\n[global:archive-get]\nprocess-max=4\n\n[global:archive-push]\nprocess-max=8\ncompress-type=bz2<\/code><\/pre>\n\n\n\n
Create repository on backup host:<\/p>\n\n\n\n
[pgbackrest@backupvm ~]$ pgbackrest --stanza=pg0102 --log-level-console=info stanza-create<\/strong>\n2022-12-06 14:31:22.919 P00   INFO: stanza-create command begin 2.41: --exec-id=191188-303eb2c7 --log-level-console=info --log-path=\/var\/log\/pgbackrest --pg1-host=pg01 --pg2-host=pg02 --pg1-path=\/app\/pgsql\/13\/data --pg2-path=\/app\/pgsql\/13\/data --pg1-socket-path=\/app\/pgsql\/13\/data --pg2-socket-path=\/app\/pgsql\/13\/data --repo1-path=\/backup\/pgbackrest --stanza=pg0102\n2022-12-06 14:31:25.989 P00   INFO: stanza-create for stanza 'pg0102' on repo1\n2022-12-06 14:31:26.209 P00   INFO: stanza-create command end: completed successfully (3292ms)<\/code><\/pre>\n\n\n\n
Adjust patroni config on DB hosts:<\/p>\n\n\n\n
$ patronictl -c \/etc\/patroni.yml edit-config<\/strong>\n## adjust the following lines\npostgresql:\n  parameters:\n    archive_command: pgbackrest --stanza=pg0102 archive-push \"%p\"\n    archive_mode: \"on\"<\/strong>\n\n$ patronictl -c \/etc\/patroni.yml restart postgres<\/strong><\/code><\/pre>\n\n\n\n
Check that the archiving system is working on master database host:<\/p>\n\n\n\n
$ pgbackrest --stanza=pg0102 --log-level-console=info check<\/strong>\n2022-12-06 14:58:06.653 P00   INFO: check command begin 2.41: --exec-id=871670-c772d656 --log-level-console=info --log-level-file=detail --log-path=\/var\/log\/pgbackrest --pg1-path=\/app\/pgsql\/13\/data --pg1-socket-path=\/app\/pgsql\/13\/data --repo1-host=backupvm --repo1-host-user=pgbackrest --stanza=pg0102\n2022-12-06 14:58:07.263 P00   INFO: check repo1 configuration (primary)\n2022-12-06 14:58:08.034 P00   INFO: check repo1 archive for WAL (primary)\n2022-12-06 14:58:08.750 P00   INFO: WAL segment 00000018000265A6000000D3 successfully archived to '\/backup\/pgbackrest\/archive\/pg0102\/13-1\/00000018000265A6\/00000018000265A6000000D3-ea3d3593d1daf572ddc695944e62c9abe1bcad13.gz' on repo1\n2022-12-06 14:58:08.851 P00   INFO: check command end: completed successfully (2199ms)<\/code><\/pre>\n\n\n\n
From now on your master database will keep its WAL files in pg_wal folder until replica applies them but then they will be pushed\/archived to the backup server freeing up space in your pg_wal directory. This will make your \/backup folder on backupvm grow until you do enough full backups for the expiry process to clean them up (expiry goes after each full backup).<\/p>\n\n\n\n
Do a backup on backup host as pgbackrest user to have the base for restoring:<\/p>\n\n\n\n
[pgbackrest@backupvm ~]$ pgbackrest --stanza=pg0102 --log-level-console=info backup --type=full<\/strong><\/code><\/pre>\n\n\n\n
Check info on backups on any DB host as postgres user:<\/p>\n\n\n\n
$ pgbackrest --stanza=pg0102 --log-level-console=info info<\/strong>\nstanza: pg0102\n    status: ok\n    cipher: none\n\n    db (current)\n        wal archive min\/max (13): 00000018000265A6000000CF\/00000018000265A6000000FF\n\n        full backup: 20221206-150150F\n            timestamp start\/stop: 2022-12-06 15:01:50 \/ 2022-12-06 17:56:34\n            wal start\/stop: 00000018000265A6000000D5 \/ 00000018000265A6000000D5\n            database size: 787.6GB, database backup size: 787.6GB\n            repo1: backup set size: 28.2GB, backup size: 28.2GB\n\n        incr backup: 20221206-150150F_20221207-085800I\n            timestamp start\/stop: 2022-12-07 08:58:00 \/ 2022-12-07 09:06:54\n            wal start\/stop: 00000018000265A6000000EC \/ 00000018000265A6000000EC\n            database size: 787.6GB, database backup size: 51.3GB\n            repo1: backup set size: 28.2GB, backup size: 664.5MB\n            backup reference list: 20221206-150150F<\/code><\/pre>\n\n\n\n
While the backup is going this info command will display current percentage of the backup process under status filed.<\/p>\n\n\n\n
Update patroni again for recovery.conf section on database server so that replica can pull WAL files from backup if it needs them for recovery:<\/p>\n\n\n\n
$ patronictl -c \/etc\/patroni.yml edit-config<\/strong>\n## adjust the following lines\nloop_wait: 10\nmaximum_lag_on_failover: 1048576\npostgresql:\n  parameters:\n    archive_command: pgbackrest --stanza=pg0102 archive-push \"%p\"\n    archive_mode: 'on'\n  recovery_conf:\n    recovery_target_timeline: latest\n    restore_command: pgbackrest --stanza=pg0102 archive-get %f \"%p\"<\/strong>\n  use_slots: true\nretry_timeout: 10\nttl: 30\n\n$ patronictl -c \/etc\/patroni.yml reload postgres<\/strong><\/code><\/pre>\n\n\n\n
To use pgBackRest for creating (or re-initializing) replicas, we need to adjust the Patroni configuration file.<\/p>\n\n\n\n
On all your nodes, in \/etc\/patroni.yml, find the following part (example only):<\/p>\n\n\n\n
postgresql:\n  listen: \"0.0.0.0:5432\"\n  connect_address: \"$MY_IP:5432\"\n  data_dir: \/var\/lib\/pgsql\/13\/data\n  bin_dir: \/usr\/pgsql-13\/bin\n  pgpass: \/tmp\/pgpass0\n  authentication:\n    replication:\n      username: replicator\n      password: confidential\n    superuser:\n      username: postgres\n      password: my-super-password\n    rewind:\n      username: rewind_user\n      password: rewind_password\n  parameters:\n    unix_socket_directories: '\/var\/run\/postgresql,\/tmp'<\/code><\/pre>\n\n\n\n
and add:<\/p>\n\n\n\n
  create_replica_methods:\n    - pgbackrest\n    - basebackup\n  pgbackrest:\n    command: pgbackrest --stanza=pg0102 restore --delta --type=none\n    keep_data: True\n    no_params: True\n  basebackup:\n    checkpoint: 'fast'<\/code><\/pre>\n\n\n\n
Don\u2019t forget to reload the configuration:<\/p>\n\n\n\n
$ sudo su - postgres\n$ ps -ef | grep patroni\n...\n$ kill -HUP <patroni_PID><\/code><\/pre>\n\n\n\n
And now if you do a patronictl -c \/etc\/patroni.yml reinit postgres pg02 it will use a backup from backup server and WAL files that were archived to the backup server to reinitialize it (doing it much faster since it uses parallel processes).<\/p>\n\n\n\n
Don’t forget to schedule regular full backups of databases on your backupvm using cron or similar tool.<\/p>\n","protected":false},"excerpt":{"rendered":"
Implementing pgBackRest backup of Patroni cluster.<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[11],"tags":[13,4,12,5],"class_list":["post-47","post","type-post","status-publish","format-standard","hentry","category-article","tag-backup","tag-patroni","tag-pgbackrest","tag-postgresql"],"_links":{"self":[{"href":"https:\/\/tivuli-it.com\/index.php\/wp-json\/wp\/v2\/posts\/47","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/tivuli-it.com\/index.php\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/tivuli-it.com\/index.php\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/tivuli-it.com\/index.php\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/tivuli-it.com\/index.php\/wp-json\/wp\/v2\/comments?post=47"}],"version-history":[{"count":3,"href":"https:\/\/tivuli-it.com\/index.php\/wp-json\/wp\/v2\/posts\/47\/revisions"}],"predecessor-version":[{"id":56,"href":"https:\/\/tivuli-it.com\/index.php\/wp-json\/wp\/v2\/posts\/47\/revisions\/56"}],"wp:attachment":[{"href":"https:\/\/tivuli-it.com\/index.php\/wp-json\/wp\/v2\/media?parent=47"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/tivuli-it.com\/index.php\/wp-json\/wp\/v2\/categories?post=47"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/tivuli-it.com\/index.php\/wp-json\/wp\/v2\/tags?post=47"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}