search

vitabaks / postgresql_cluster

PostgreSQL High-Availability Cluster (based on "Patroni" and DCS "etcd" or "consul"). Automating with Ansible.
MIT License
1.29k stars 352 forks source link

PgBackRest: automate "backup-standby" setup #538

Closed vitabaks closed 4 months ago

vitabaks commented 4 months ago

Automated Inclusion of Standby Servers in PgBackRest Configuration.

This feature is triggered when the backup-standby=y option is added to the pgbackrest_conf variable. Standby servers are added sequentially, starting from pg2-host, pg3-host, and so on. In addition, SSH keys are exchanged between database servers.

This is especially true in scenarios where PgBackRest interacts directly with cloud storage, bypassing a dedicated PgBackRest server (when pgbackrest_repo_type has the value "s3", "gcs", or "azure").

pgBackRest creates a standby backup that is identical to a backup performed on the primary. It does this by starting/stopping the backup on the pg-primary host, copying only files that are replicated from the pg-standby host, then copying the remaining few files from the pg-primary host. This means that logs and statistics from the primary database will be included in the backup.

Details about Backup from a Standby: https://pgbackrest.org/user-guide.html#standby-backup

vitabaks commented 4 months ago

Test (backup-standby=y)

Ansible log:

...
PLAY [deploy_pgcluster.yml | Install and configure pgBackRest] *****************

TASK [Gathering Facts] *********************************************************
ok: [10.172.0.22]
ok: [10.172.0.20]
ok: [10.172.0.21]

TASK [Include main variables] **************************************************
ok: [10.172.0.20]
ok: [10.172.0.21]
ok: [10.172.0.22]

TASK [Include OS-specific variables] *******************************************
ok: [10.172.0.20]
ok: [10.172.0.21]
ok: [10.172.0.22]

TASK [pgbackrest : Make sure the gnupg and apt-transport-https packages are present] ***
ok: [10.172.0.21]
ok: [10.172.0.22]
ok: [10.172.0.20]

TASK [pgbackrest : Make sure pgdg apt key is installed] ************************
ok: [10.172.0.20]
ok: [10.172.0.21]
ok: [10.172.0.22]

TASK [pgbackrest : Make sure pgdg repository is installed] *********************
ok: [10.172.0.20]
ok: [10.172.0.21]
ok: [10.172.0.22]

TASK [pgbackrest : Update apt cache] *******************************************
ok: [10.172.0.22]
ok: [10.172.0.20]
ok: [10.172.0.21]

TASK [pgbackrest : Install pgbackrest] *****************************************
changed: [10.172.0.20]
changed: [10.172.0.22]
changed: [10.172.0.21]

TASK [pgbackrest : Ensure spool directory exist] *******************************
ok: [10.172.0.20] => (item=/var/spool/pgbackrest)
ok: [10.172.0.21] => (item=/var/spool/pgbackrest)
ok: [10.172.0.22] => (item=/var/spool/pgbackrest)

TASK [pgbackrest : Ensure config directory exist] ******************************
changed: [10.172.0.20]
changed: [10.172.0.21]
changed: [10.172.0.22]

TASK [pgbackrest : Generate conf file /etc/pgbackrest/pgbackrest.conf] *********
changed: [10.172.0.20]
changed: [10.172.0.22]
changed: [10.172.0.21]

TASK [pgbackrest : Ensure that the openssh-client package is installed] ********
ok: [10.172.0.20]
ok: [10.172.0.22]
ok: [10.172.0.21]

TASK [pgbackrest : ssh_keys | Ensure ssh key are created for "postgres" user on database servers] ***
changed: [10.172.0.21]
changed: [10.172.0.22]
changed: [10.172.0.20]

TASK [pgbackrest : ssh_keys | Get public ssh key from database servers] ********
ok: [10.172.0.22]
ok: [10.172.0.20]
ok: [10.172.0.21]

TASK [pgbackrest : ssh_keys | Add ssh keys in "~postgres/.ssh/authorized_keys" on database servers] ***
changed: [10.172.0.21] => (item=10.172.0.20)
changed: [10.172.0.20] => (item=10.172.0.20)
changed: [10.172.0.22] => (item=10.172.0.20)
changed: [10.172.0.21] => (item=10.172.0.21)
changed: [10.172.0.20] => (item=10.172.0.21)
changed: [10.172.0.22] => (item=10.172.0.21)
changed: [10.172.0.21] => (item=10.172.0.22)
changed: [10.172.0.20] => (item=10.172.0.22)
changed: [10.172.0.22] => (item=10.172.0.22)

TASK [pgbackrest : known_hosts | Get public ssh keys of hosts (ssh-keyscan)] ***
ok: [10.172.0.20] => (item=10.172.0.20)
ok: [10.172.0.21] => (item=10.172.0.20)
ok: [10.172.0.22] => (item=10.172.0.20)
ok: [10.172.0.22] => (item=10.172.0.21)
ok: [10.172.0.20] => (item=10.172.0.21)
ok: [10.172.0.21] => (item=10.172.0.21)
ok: [10.172.0.20] => (item=10.172.0.22)
ok: [10.172.0.21] => (item=10.172.0.22)
ok: [10.172.0.22] => (item=10.172.0.22)

TASK [pgbackrest : known_hosts | add ssh public keys in "~postgres/.ssh/known_hosts" on database servers] ***
changed: [10.172.0.22] => (item=None)
changed: [10.172.0.21] => (item=None)
changed: [10.172.0.20] => (item=None)
changed: [10.172.0.21] => (item=None)
changed: [10.172.0.22] => (item=None)
changed: [10.172.0.20] => (item=None)
changed: [10.172.0.21] => (item=None)
changed: [10.172.0.21]
changed: [10.172.0.22] => (item=None)
changed: [10.172.0.22]
changed: [10.172.0.20] => (item=None)
changed: [10.172.0.20]

TASK [pgbackrest : Make sure that the cron package is installed] ***************
changed: [10.172.0.22]
changed: [10.172.0.20]
changed: [10.172.0.21]

TASK [pgbackrest : Add pgbackrest cron jobs on database server] ****************
changed: [10.172.0.20] => (item={'name': 'pgBackRest: Full Backup', 'file': '/etc/cron.d/pgbackrest-postgres-cluster', 'user': 'postgres', 'minute': '30', 'hour': '6', 'day': '*', 'month': '*', 'weekday': '0', 'job': 'pgbackrest --type=full --stanza=postgres-cluster backup'})
changed: [10.172.0.21] => (item={'name': 'pgBackRest: Full Backup', 'file': '/etc/cron.d/pgbackrest-postgres-cluster', 'user': 'postgres', 'minute': '30', 'hour': '6', 'day': '*', 'month': '*', 'weekday': '0', 'job': 'pgbackrest --type=full --stanza=postgres-cluster backup'})
changed: [10.172.0.22] => (item={'name': 'pgBackRest: Full Backup', 'file': '/etc/cron.d/pgbackrest-postgres-cluster', 'user': 'postgres', 'minute': '30', 'hour': '6', 'day': '*', 'month': '*', 'weekday': '0', 'job': 'pgbackrest --type=full --stanza=postgres-cluster backup'})
changed: [10.172.0.20] => (item={'name': 'pgBackRest: Diff Backup', 'file': '/etc/cron.d/pgbackrest-postgres-cluster', 'user': 'postgres', 'minute': '30', 'hour': '6', 'day': '*', 'month': '*', 'weekday': '1-6', 'job': 'pgbackrest --type=diff --stanza=postgres-cluster backup'})
changed: [10.172.0.21] => (item={'name': 'pgBackRest: Diff Backup', 'file': '/etc/cron.d/pgbackrest-postgres-cluster', 'user': 'postgres', 'minute': '30', 'hour': '6', 'day': '*', 'month': '*', 'weekday': '1-6', 'job': 'pgbackrest --type=diff --stanza=postgres-cluster backup'})
changed: [10.172.0.22] => (item={'name': 'pgBackRest: Diff Backup', 'file': '/etc/cron.d/pgbackrest-postgres-cluster', 'user': 'postgres', 'minute': '30', 'hour': '6', 'day': '*', 'month': '*', 'weekday': '1-6', 'job': 'pgbackrest --type=diff --stanza=postgres-cluster backup'})
...

Result:

root@pgnode01:/# cat /etc/pgbackrest/pgbackrest.conf
[global]
...
backup-standby=y

[postgres-cluster]
process-max=4
log-level-console=info
recovery-option=recovery_target_action=promote
pg1-path=/pgdata/16/main
pg3-host=10.172.0.21
pg3-port=5432
pg3-path=/pgdata/16/main
pg4-host=10.172.0.22
pg4-port=5432
pg4-path=/pgdata/16/main
root@pgnode02:/# cat /etc/pgbackrest/pgbackrest.conf
[global]
...
backup-standby=y

[postgres-cluster]
process-max=4
log-level-console=info
recovery-option=recovery_target_action=promote
pg1-path=/pgdata/16/main
pg2-host=10.172.0.20
pg2-port=5432
pg2-path=/pgdata/16/main
pg4-host=10.172.0.22
pg4-port=5432
pg4-path=/pgdata/16/main
root@pgnode03:/# cat /etc/pgbackrest/pgbackrest.conf
[global]
...
backup-standby=y

[postgres-cluster]
process-max=4
log-level-console=info
recovery-option=recovery_target_action=promote
pg1-path=/pgdata/16/main
pg2-host=10.172.0.20
pg2-port=5432
pg2-path=/pgdata/16/main
pg3-host=10.172.0.21
pg3-port=5432

passed

vitabaks commented 4 months ago

Test 2

make sure that the index is sequential (pg1, pg2, pg3 ...)

root@pgnode01:/# cat /etc/pgbackrest/pgbackrest.conf
[global]
...

[postgres-cluster]
process-max=4
log-level-console=info
recovery-option=recovery_target_action=promote
pg1-path=/pgdata/16/main
pg2-host=10.172.0.21
pg2-port=5432
pg2-path=/pgdata/16/main
pg3-host=10.172.0.22
pg3-port=5432
pg3-path=/pgdata/16/main
root@pgnode02:/# cat /etc/pgbackrest/pgbackrest.conf
[global]
...

[postgres-cluster]
process-max=4
log-level-console=info
recovery-option=recovery_target_action=promote
pg1-path=/pgdata/16/main
pg2-host=10.172.0.20
pg2-port=5432
pg2-path=/pgdata/16/main
pg3-host=10.172.0.22
pg3-port=5432
pg3-path=/pgdata/16/main
root@pgnode02:/# exit
root@pgnode03:/# cat /etc/pgbackrest/pgbackrest.conf
[global]
...

[postgres-cluster]
process-max=4
log-level-console=info
recovery-option=recovery_target_action=promote
pg1-path=/pgdata/16/main
pg2-host=10.172.0.20
pg2-port=5432
pg2-path=/pgdata/16/main
pg3-host=10.172.0.21
pg3-port=5432
pg3-path=/pgdata/16/main
root@pgnode03:/# exit
exit

passed