Protecting your data with Patroni and pgBackRest

Transcript

1. Protecting your data with Patroni and pgBackRest NaLUG, Napoli Federico Campoli PostgreSQL Europe 4th May 2024 Federico Campoli (PostgreSQL Europe) Protecting your data with Patroni and pgBackRest 1 / 63
2. Table of contents 1 Greetings, programs! 2 The grid 3 The light cycle maze 4 End of line 5 I fight for the users Federico Campoli (PostgreSQL Europe) Protecting your data with Patroni and pgBackRest 2 / 63
3. Greetings, programs! Copyright Walt Disney LTD Image source https://liveforfilms.wordpress.com/2009/07/09/greetings-program-tron-2-synopsis/ Federico Campoli (PostgreSQL Europe) Protecting your data with Patroni and pgBackRest Greetings, programs! 3 / 63
4. PostgreSQL https://www.postgresql.org/ Enteprise class RDBMS ACID compliant HA and DR With one tiny little catch... No built in mechanism for automating backups or failover Federico Campoli (PostgreSQL Europe) Protecting your data with Patroni and pgBackRest Greetings, programs! 4 / 63
5. Some PostgreSQL auto failover options B.Y.O.T, shell script, manually operated, Cthulhu summoning... Automated with third party tools repmgr pg auto failover PostgreSQL Automatic Failover (PAF) Patroni Federico Campoli (PostgreSQL Europe) Protecting your data with Patroni and pgBackRest Greetings, programs! 5 / 63
6. Patroni https://github.com/zalando/patroni Patroni is an auto failover system developed in python by Zalando. The tool relies on a distributed consensus store (DCS) to maintain the cluster status. Patroni is available in the pgdg official repository. Federico Campoli (PostgreSQL Europe) Protecting your data with Patroni and pgBackRest Greetings, programs! 6 / 63
7. Patroni Developed in Python Supports for DCS etcd,consul,zookeper Support for python RAFT (requires pysyncobj module) (Deprecated) Federico Campoli (PostgreSQL Europe) Protecting your data with Patroni and pgBackRest Greetings, programs! 7 / 63
8. Patroni Automated boostrap and replica setup Automated failover/switchover Centralised configuration for PostgreSQL stored in DCS Very resilient to split brain HAProxy for connection routed via api check Federico Campoli (PostgreSQL Europe) Protecting your data with Patroni and pgBackRest Greetings, programs! 8 / 63
9. Caveats Everything is managed by Patroni The documentation may be unclear (but the community is very helpful) The client works only in interactive mode In order to automate the api call you’ll need to build your api call (e.g. curl, ansible uri module...) Federico Campoli (PostgreSQL Europe) Protecting your data with Patroni and pgBackRest Greetings, programs! 9 / 63
10. Some PostgreSQL backup options logical with pg dump physical with tools like pg basebackup barman WAL-E/WAL-G pgBackRest Federico Campoli (PostgreSQL Europe) Protecting your data with Patroni and pgBackRest Greetings, programs! 10 / 63
11. pgBackRest https://pgbackrest.org/ pgBackRest is a simple and reliable solution for automatic the backups. pgBackRest is a community driven project. Federico Campoli (PostgreSQL Europe) Protecting your data with Patroni and pgBackRest Greetings, programs! 11 / 63
12. pgBackRest Physical backup tool Implements HA/DR Differential,incremental and full backup Parallel jobs configurable Can backup from the standby servers Async WAL push and pull Federico Campoli (PostgreSQL Europe) Protecting your data with Patroni and pgBackRest Greetings, programs! 12 / 63
13. pgBackRest Developed initially in perl now fully migrated to C ini style configuration Available in deb/yum pgdg repositories Cloud backup options GCP/S3/Azure Multiple repositories configurable Self contained backup repository Federico Campoli (PostgreSQL Europe) Protecting your data with Patroni and pgBackRest Greetings, programs! 13 / 63
14. Caveats Configuring remote backups via SSH may be complex Beware of when configuring archive-push-queue-max. There is the risk of wal files not being archived if the limit is reached. Federico Campoli (PostgreSQL Europe) Protecting your data with Patroni and pgBackRest Greetings, programs! 14 / 63
15. The rule of thumb RTFM Federico Campoli (PostgreSQL Europe) Protecting your data with Patroni and pgBackRest Greetings, programs! 15 / 63
16. Advanced pgBackRest For advanced topics on pgBackRest please check Stefan Fercot’s Talk Unleash the Power within pgBackRest https://www.youtube.com/watch?v=eyda4r6T3Ek Federico Campoli (PostgreSQL Europe) Protecting your data with Patroni and pgBackRest Greetings, programs! 16 / 63
17. The grid Copyright Walt Disney LTD Image source https://siftingthroughpatterns.wordpress.com/2012/05/12/why-kevin-flynn-is-the-true-villain-behind-tron-legacy/ Federico Campoli (PostgreSQL Europe) Protecting your data with Patroni and pgBackRest The grid 17 / 63
18. Patroni super biased configuration Federico Campoli (PostgreSQL Europe) Protecting your data with Patroni and pgBackRest The grid 18 / 63
19. Setup For our example we’ll use Rocky Linux 8 on GCP and a GCP bucket patroni-0 patroni-1 patroni-2 Federico Campoli (PostgreSQL Europe) Protecting your data with Patroni and pgBackRest The grid 19 / 63
20. Configure and install the software Deploy the machines with opentofu Install etcd on the nodes Configure and start etcd Install PostgreSQL 14 and patroni on the nodes Configure patroni Start patroni Profit Federico Campoli (PostgreSQL Europe) Protecting your data with Patroni and pgBackRest The grid 20 / 63
21. opentofu for nodes resource " google_compute_instance " " patroni_instance " { name = "${var. node_prefix }${count.index }" machine_type = "e2 -small" tags = [" patroni "] count = var. patroni_node_count labels = { patroni -node = count.index , } boot_disk { initialize_params { image = "rocky -linux -cloud/rocky -linux -8" } } network_interface { network = google_compute_network . vpc_network .name access_config { } } metadata = { ssh -keys = "${var.ssh_user }:${file ("${var.HOME }/${var.ssh_key }. pub ")}" } } Federico Campoli (PostgreSQL Europe) Protecting your data with Patroni and pgBackRest The grid 21 / 63
22. opentofu for firewall resource " google_compute_firewall " " firewall_ssh " { name = "firewall -ssh" network = google_compute_network . vpc_network .name allow { protocol = "tcp" ports = ["22"] } source_ranges = ["0.0.0.0/0"] target_tags = [" patroni "] } resource " google_compute_firewall " " firewall_internal " { name = "firewall -internal" network = google_compute_network . vpc_network .name allow { protocol = "icmp" } allow { protocol = "tcp" ports = ["22" ,"5432" ,"6432" ,"8008" ,"2379" ,"2380"] } source_tags = [" patroni "] } Federico Campoli (PostgreSQL Europe) Protecting your data with Patroni and pgBackRest The grid 22 / 63
23. etcd setup Rocky Linux 8 doesn’t have etcd in repository. We need to download the archive from github and configure it. Example configuration file for patroni-0 #[Member] ETCD_DATA_DIR="/var/lib/etcd/data" ETCD_LISTEN_PEER_URLS="http://10.164.0.3:2380" ETCD_LISTEN_CLIENT_URLS="http://10.164.0.3:2379" ETCD_NAME="patroni-0" #must be unique within the etcd cluster #[Clustering] ETCD_INITIAL_ADVERTISE_PEER_URLS="http://10.164.0.3:2380" ETCD_ADVERTISE_CLIENT_URLS="http://10.164.0.3:2379" ETCD_INITIAL_CLUSTER="patroni-0=http://10.164.0.3:2380,patroni-1=http://10.164.0.2:2380,patroni-2 =http://10.164.0.4:2380" Federico Campoli (PostgreSQL Europe) Protecting your data with Patroni and pgBackRest The grid 23 / 63
24. etcd setup Systemd unit file [Unit] Description=Etcd Server After=network.target After=network-online.target Wants=network-online.target [Service] Type=notify WorkingDirectory=/var/lib/etcd EnvironmentFile=-/etc/etcd_config.env User=etcd # set GOMAXPROCS to number of processors ExecStart=/bin/bash -c "GOMAXPROCS=$(nproc) /usr/local/bin/etcd " Restart=on-failure LimitNOFILE=65536 [Install] WantedBy=multi-user.target Federico Campoli (PostgreSQL Europe) Protecting your data with Patroni and pgBackRest The grid 24 / 63
25. etcd setup On each node start etcd sudo systemctl enable etcd sudo systemctl start etcd [ansible@patroni -0 ~]$ etcdctl --endpoints "http ://10.164.0.3:2379" --cluster=true endpoint health http ://10.164.0.3:2379 is healthy: successfully committed proposal: took = 3.468686 ms http ://10.164.0.2:2379 is healthy: successfully committed proposal: took = 3.946757 ms http ://10.164.0.4:2379 is healthy: successfully committed proposal: took = 14.749053 ms Federico Campoli (PostgreSQL Europe) Protecting your data with Patroni and pgBackRest The grid 25 / 63
26. postgresql install Install the PostgreSQL repository and PostgreSQL 14 # Install the repository RPM: sudo dnf install -y https :// download. postgresql.org/pub/repos/yum/reporpms/EL -8- x86_64/pgdg -redhat -repo -latest.noarch.rpm # Disable the built -in PostgreSQL module: sudo dnf -qy module disable postgresql #Install PostgreSQL sudo dnf install -y postgresql14 postgresql14 -contrib postgresql14 -server Federico Campoli (PostgreSQL Europe) Protecting your data with Patroni and pgBackRest The grid 26 / 63
27. patroni install Install the epel-release first then patroni and patroni-etcd sudo dnf install -y epel -release sudo dnf install -y patroni patroni -etcd Federico Campoli (PostgreSQL Europe) Protecting your data with Patroni and pgBackRest The grid 27 / 63
28. Configure patroni dcs section I On the patroni machines create the directory /etc/patroni Then add a new configuration file into the directory named patroni.yml scope: f l y n n namespace: / t h e _ g r i d / name: p a t r o n i - 0 log: dir: / v a r / l o g / p a t r o n i restapi: listen: 1 0 . 1 6 4 . 0 . 3 : 8 0 0 8 connect_address: 1 0 . 1 6 4 . 0 . 3 : 8 0 0 8 etcd3: hosts: 1 0 . 1 6 4 . 0 . 3 : 2 3 7 9 , 1 0 . 1 6 4 . 0 . 2 : 2 3 7 9 , 1 0 . 1 6 4 . 0 . 4 : 2 3 7 9 bootstrap: dcs: ttl: 1 0 loop_wait: 1 0 retry_timeout: 1 0 Federico Campoli (PostgreSQL Europe) Protecting your data with Patroni and pgBackRest The grid 28 / 63
29. Configure patroni dcs section II maximum_lag_on_failover: 1 0 4 8 5 7 6 postgresql: use_pg_rewind: t r u e use_slots: f a l s e parameters: wal_level: ’ r e p l i c a ’ archive_mode: ’ o n ’ archive_command: ’ / b i n / t r u e ’ unix_socket_directories: ’ / v a r / r u n / p o s t g r e s q l / . ’ max_connections: " 1 0 0 " shared_buffers: " 2 5 6 M B " work_mem: " 8 M B " maintenance_work_mem: " 1 6 M B " password_encryption: " s c r a m - s h a - 2 5 6 " method: i n i t d b # some desired options for ’initdb ’ initdb: # N o t e : I t n e e d s t o b e a l i s t ( s o m e o p t i o n s n e e d v a l u e s , o t h e r s a r e s w i t c h e s ) - encoding: U T F 8 Federico Campoli (PostgreSQL Europe) Protecting your data with Patroni and pgBackRest The grid 29 / 63
30. Configure patroni dcs section III - data -checksums pg_hba: # A d d f o l l o w i n g l i n e s t o p g _ h b a . c o n f a f t e r r u n n i n g ’ i n i t d b ’ - host replication replicator 0.0.0.0/0 scram -sha -256 - host all all 0.0.0.0/0 scram -sha -256 # Some additional users users which needs to be created after initializing new cluster users: Federico Campoli (PostgreSQL Europe) Protecting your data with Patroni and pgBackRest The grid 30 / 63
31. Configure patroni postgresql section I postgresql: listen: " * : 5 4 3 2 " connect_address: p a t r o n i - 0 : 5 4 3 2 data_dir: / v a r / l i b / p g s q l / d a t a / p o s t g r e s q l 1 4 bin_dir: / u s r / p g s q l - 1 4 / b i n / # config_dir: pgpass: / t m p / p g p a s s 0 pg_hba: - local all all peer - host all all 127.0.0.1/32 scram -sha -256 - host all all :: 1 / 1 2 8 s c r a m - s h a - 2 5 6 - host replication replicator :: 1 / 1 2 8 s c r a m - s h a - 2 5 6 - host replication replicator 0.0.0.0/0 scram -sha -256 - host all all 0.0.0.0/0 scram -sha -256 authentication: replication: username: r e p l i c a t o r password: S E C R E T superuser: username: p o s t g r e s Federico Campoli (PostgreSQL Europe) Protecting your data with Patroni and pgBackRest The grid 31 / 63
32. Configure patroni postgresql section II password: S U P E R _ S E C R E T rewind: # H a s n o e f f e c t o n p o s t g r e s 1 0 a n d l o w e r username: r e w i n d _ u s e r password: V E R Y _ S E C R E T parameters: wal_level: ’ r e p l i c a ’ archive_mode: ’ o n ’ unix_socket_directories: ’ / v a r / r u n / p o s t g r e s q l / . ’ max_connections: " 1 0 0 " shared_buffers: " 2 5 6 M B " work_mem: " 8 M B " maintenance_work_mem: " 1 6 M B " password_encryption: " s c r a m - s h a - 2 5 6 " unix_socket_directories: ’ / v a r / r u n / p o s t g r e s q l / . ’ tags: nofailover: f a l s e noloadbalance: f a l s e clonefrom: f a l s e nosync: f a l s e Federico Campoli (PostgreSQL Europe) Protecting your data with Patroni and pgBackRest The grid 32 / 63
33. Create the systemd service file I In /etc/systemd/system create the file patroni.service [Unit] Description=Runners to orchestrate a high-availability PostgreSQL After=syslog.target network.target [Service] Type=simple User=postgres Group=postgres # Read in configuration file if it exists, otherwise proceed EnvironmentFile=-/etc/patroni_env.conf WorkingDirectory=/var/lib/pgsql # Start the patroni process ExecStart=/bin/patroni /etc/patroni/patroni.yml Federico Campoli (PostgreSQL Europe) Protecting your data with Patroni and pgBackRest The grid 33 / 63
34. Create the systemd service file II # Send HUP to reload from patroni.yml ExecReload=/bin/kill -s HUP $MAINPID # only kill the patroni process, not it’s children, so it will gracefully stop postgres KillMode=process # Give a reasonable amount of time for the server to start up/shut down TimeoutSec=30 # Do not restart the service if it crashes, we want to manually inspect database on failure Restart=no [Install] WantedBy=multi-user.target Federico Campoli (PostgreSQL Europe) Protecting your data with Patroni and pgBackRest The grid 34 / 63
35. Enable and start the patroni service On the patroni machines sudo systemctl daemon -reload sudo systemctl enable patroni sudo systemctl start patroni Federico Campoli (PostgreSQL Europe) Protecting your data with Patroni and pgBackRest The grid 35 / 63
36. Patroni bootstrap [postgres@patroni -0 ~]$ patronictl -c /etc/patroni/patroni.yml list + Cluster: flynn (7073348425059730447) -----+----+-----------+ | Member | Host | Role | State | TL | Lag in MB | +-----------+-----------+---------+---------+----+-----------+ | patroni -0 | patroni -0 | Leader | running | 1 | | +-----------+-----------+---------+---------+----+-----------+ Federico Campoli (PostgreSQL Europe) Protecting your data with Patroni and pgBackRest The grid 36 / 63
37. Patroni cloning [postgres@patroni -0 ~]$ patronictl -c /etc/patroni/patroni.yml list + Cluster: flynn (7073348425059730447) -----+----+-----------+ | Member | Host | Role | State | TL | Lag in MB | +-----------+-----------+---------+---------+----+-----------+ | patroni -0 | patroni -0 | Leader | running | 1 | | | patroni -1 | patroni -1 | Replica | running | 1 | 0 | | patroni -2 | patroni -2 | Replica | running | 1 | 0 | +-----------+-----------+---------+---------+----+-----------+ Federico Campoli (PostgreSQL Europe) Protecting your data with Patroni and pgBackRest The grid 37 / 63
38. The light cycle maze Copyright Walt Disney LTD Image source https://geektyrant.com/news/the-history-of-the-tron-lightcycle-infographic Federico Campoli (PostgreSQL Europe) Protecting your data with Patroni and pgBackRest The light cycle maze 38 / 63
39. Patroni and pgBackRest Federico Campoli (PostgreSQL Europe) Protecting your data with Patroni and pgBackRest The light cycle maze 39 / 63
40. Configure and install the software Install pgBackRest on the patroni servers Create the GCP bucket Create a new service account grant access to the GCP bucket Generate and save the service account’s access key in json format Configure pgBackRest for using the GCP bucket Configure patroni to use pgBackRest Run the pgBackRest first backup Profit Federico Campoli (PostgreSQL Europe) Protecting your data with Patroni and pgBackRest The light cycle maze 40 / 63
41. Configure pgbackrest I On the patroni machines edit the file /etc/pgbackrest.conf [global] repo1-type=gcs repo1-path=/repo repo1-gcs-bucket=pg_backup-grongo-571 repo1-gcs-key=/etc/gcp/gcp_key.json repo1-retention-full=1 log-level-console=info log-level-file=info backup-standby=y [flynn] # No pg1-host setting for the local instance pg1-path=/var/lib/pgsql/data/postgresql14 pg1-port=5432 pg1-user=postgres pg2-path=/var/lib/pgsql/data/postgresql14 pg2-port=5432 pg2-user=postgres Federico Campoli (PostgreSQL Europe) Protecting your data with Patroni and pgBackRest The light cycle maze 41 / 63
42. Configure pgbackrest II pg2-host=patroni-1 pg3-path=/var/lib/pgsql/data/postgresql14 pg3-port=5432 pg3-user=postgres pg3-host=patroni-2 log-path=/var/log/patroni/ Federico Campoli (PostgreSQL Europe) Protecting your data with Patroni and pgBackRest The light cycle maze 42 / 63
43. Optional, configure .pgpass If the local login is made with password authentication it may be necessary to configure the .pgpass file in the PostgreSQL data directory. patroni-0:5432:*:postgres:SECRET patroni-0:5432:*:replicator:SUPER_SECRET patroni-0:5432:*:rewind_user:VERY_SECRET patroni-1:5432:*:postgres:SECRET patroni-1:5432:*:replicator:SUPER_SECRET patroni-1:5432:*:rewind_user:VERY_SECRET patroni-2:5432:*:postgres:SECRET patroni-2:5432:*:replicator:SUPER_SECRET patroni-2:5432:*:rewind_user:VERY_SECRET Federico Campoli (PostgreSQL Europe) Protecting your data with Patroni and pgBackRest The light cycle maze 43 / 63
44. Save the gcp-key.json Create the directory /etc/gcp and save into it the file gcp key.json downloaded from the GCP console. The directory and file must be accessible by the user running pgbackrest. { "type": "service_account", "project_id": "XXXXXXXXXXXXX", "private_key_id": "XXXXXXXXXXXXX", "private_key": "-----BEGIN PRIVATE KEY----- XXXXXXXXXXXXXXXXXXXX -----END PRIVATE KEY----- ", "client_email": "XXXXXXXXXXXXX", "client_id": "XXXXXXXXXXXXX", "auth_uri": "https://accounts.google.com/o/oauth2/auth", "token_uri": "https://oauth2.googleapis.com/token", "auth_provider_x509_cert_url": "https://www.googleapis.com/oauth2/v1/certs", "client_x509_cert_url": "https://www.googleapis.com/robot/v1/metadata/x509/XXXXXXXXXXXXXXX" } Federico Campoli (PostgreSQL Europe) Protecting your data with Patroni and pgBackRest The light cycle maze 44 / 63
45. Configure patroni for using pgbackrest patronictl -c /etc/patroni/patroni.yml edit -config ## add the following lines under the parameters key postgresql : parameters : archive_command : pgbackrest --stanza=flynn archive -push %p After saving the data in the DCS patroni should reload the nodes in order to apply the new settings. Federico Campoli (PostgreSQL Europe) Protecting your data with Patroni and pgBackRest The light cycle maze 45 / 63
46. Run the first backup Create the stanza and then run the backup [postgres@patroni -0 ~]$ pgbackrest --stanza=flynn stanza -create 2022 -03 -10 06:06:59.161 P00 INFO: stanza -create command begin 2.38: --exec -id =65347 - c2df9b6c --log -level -console=info --log -level -file=info --log -path =/ var/ log/patroni/ --pg2 -host=patroni -1 --pg3 -host=patroni -2 --pg1 -path =/ var/lib/pgsql/ data/ postgresql14 --pg2 -path =/ var/lib/pgsql/data/ postgresql14 --pg3 -path =/ var/lib /pgsql/data/ postgresql14 --pg1 -port =5432 --pg2 -port =5432 --pg3 -port =5432 --pg1 - user=postgres --pg2 -user=postgres --pg3 -user=postgres --repo1 -gcs -bucket= pg_backup -grongo -571 --repo1 -gcs -key=<redacted > --repo1 -path =/ repo --repo1 -type= gcs --stanza=flynn 2022 -03 -10 06:07:01.783 P00 INFO: stanza -create for stanza ’flynn ’ on repo1 2022 -03 -10 06:07:02.826 P00 INFO: stanza -create command end: completed successfully (3666 ms) Federico Campoli (PostgreSQL Europe) Protecting your data with Patroni and pgBackRest The light cycle maze 46 / 63
47. Run the first backup Create the stanza and then run the backup [postgres@patroni -0 ~]$ pgbackrest --stanza=flynn backup 2022 -03 -10 06:08:09.226 P00 INFO: backup command begin 2.38: --backup -standby -- exec -id =65457 -0 f1e88ce --log -level -console=info --log -level -file=info --log -path =/ var/log/patroni/ --pg2 -host=patroni -1 --pg3 -host=patroni -2 --pg1 -path =/ var/lib/ pgsql/data/ postgresql14 --pg2 -path =/ var/lib/pgsql/data/ postgresql14 --pg3 -path =/ var/lib/pgsql/data/ postgresql14 --pg1 -port =5432 --pg2 -port =5432 --pg3 -port =5432 --pg1 -user=postgres --pg2 -user=postgres --pg3 -user=postgres --repo1 -gcs -bucket= pg_backup -grongo -571 --repo1 -gcs -key=<redacted > --repo1 -path =/ repo --repo1 - retention -full =1 --repo1 -type=gcs --stanza=flynn WARN: no prior backup exists , incr backup has been changed to full . . . 2022 -03 -10 06:09:07.491 P00 INFO: expire command begin 2.38: --exec -id =65457 -0 f1e88ce --log -level -console=info --log -level -file=info --log -path =/ var/log/ patroni/ --repo1 -gcs -bucket=pg_backup -grongo -571 --repo1 -gcs -key=<redacted > -- repo1 -path =/ repo --repo1 -retention -full =1 --repo1 -type=gcs --stanza=flynn 2022 -03 -10 06:09:08.217 P00 INFO: repo1: 14-1 remove archive , start = 000000010000000000000004 , stop = 000000010000000000000004 2022 -03 -10 06:09:08.356 P00 INFO: expire command end: completed successfully (865 ms) Federico Campoli (PostgreSQL Europe) Protecting your data with Patroni and pgBackRest The light cycle maze 47 / 63
48. Check the backup status Check the backup status [postgres@patroni -0 ~]$ pgbackrest info stanza: flynn status: ok cipher: none db (current) wal archive min/max (14): 000000010000000000000005/000000010000000000000005 full backup: 20220310 -060811F timestamp start/stop: 2022 -03 -10 06:08:11 / 2022 -03 -10 06:09:06 wal start/stop: 000000010000000000000005 / 000000010000000000000005 database size: 25.2MB , database backup size: 25.2 MB repo1: backup set size: 3.2MB , backup size: 3.2 MB Federico Campoli (PostgreSQL Europe) Protecting your data with Patroni and pgBackRest The light cycle maze 48 / 63
49. End of line Copyright Walt Disney LTD Image source https://tron.fandom.com/wiki/Master Control Program Federico Campoli (PostgreSQL Europe) Protecting your data with Patroni and pgBackRest End of line 49 / 63
50. Wisdom An ancient Italian proverb tells: Il backup ` e quella cosa che andava fatta prima. Translation: the backup is something to do before (the disaster strikes). Federico Campoli (PostgreSQL Europe) Protecting your data with Patroni and pgBackRest End of line 50 / 63
51. Disaster recovery Install and configure etcd Install and configure pgBackRest Install PostgreSQL and patroni Configure patroni to bootstrap and clone from pgBackRest Start patroni Profit Federico Campoli (PostgreSQL Europe) Protecting your data with Patroni and pgBackRest End of line 51 / 63
52. Check the pgbackrest can access the repository Check the pgbackrest can access the repository [postgres@patroni -0 ~]$ pgbackrest info stanza: flynn status: ok cipher: none db (current) wal archive min/max (14): 000000010000000000000005/000000010000000000000005 full backup: 20220310 -060811F timestamp start/stop: 2022 -03 -10 06:08:11 / 2022 -03 -10 06:09:06 wal start/stop: 000000010000000000000005 / 000000010000000000000005 database size: 25.2MB , database backup size: 25.2 MB repo1: backup set size: 3.2MB , backup size: 3.2 MB Federico Campoli (PostgreSQL Europe) Protecting your data with Patroni and pgBackRest End of line 52 / 63
53. Configure the custom bootstrap script for patroni Create the file /etc/patroni/boot pgbackrest.sh #!/ usr/bin/env bash while getopts ": -:" optchar; do [[ "${optchar }" == "-" ]] || continue case "${OPTARG }" in datadir =* ) DATA_DIR=${OPTARG #*=} ;; scope =* ) SCOPE=${OPTARG #*=} ;; esac done /usr/bin/pgbackrest --stanza=$SCOPE --link -all restore Make the file executable Federico Campoli (PostgreSQL Europe) Protecting your data with Patroni and pgBackRest End of line 53 / 63
54. Configure the patroni boostrap section bootstrap: ..... postgresql: use_pg_rewind: t r u e use_slots: f a l s e parameters: wal_level: ’ r e p l i c a ’ archive_mode: ’ o n ’ archive_command: ’ p g b a c k r e s t - - s t a n z a = f l y n n a r c h i v e - p u s h % p ’ unix_socket_directories: ’ / v a r / r u n / p o s t g r e s q l / . ’ recovery_conf: recovery_target_timeline: l a t e s t restore_command: / u s r / b i n / p g b a c k r e s t - - s t a n z a = f l y n n a r c h i v e - g e t % f " % p " method: p g b a c k r e s t pgbackrest: command: / e t c / p a t r o n i / b o o t _ p g b a c k r e s t . s h keep_existing_recovery_conf: F a l s e recovery_conf: recovery_target_timeline: l a t e s t restore_command: / u s r / b i n / p g b a c k r e s t - - s t a n z a = f l y n n a r c h i v e - g e t % f " % p " Federico Campoli (PostgreSQL Europe) Protecting your data with Patroni and pgBackRest End of line 54 / 63
55. Add pgbackrest for creating replicas postgresql: create_replica_methods: - pgbackrest pgbackrest: command: / u s r / b i n / p g b a c k r e s t - - s t a n z a = f l y n n r e s t o r e - - d e l t a - - l i n k - a l l keep_data: T r u e no_params: T r u e recovery_conf: recovery_target_timeline: l a t e s t restore_command: / u s r / b i n / p g b a c k r e s t - - s t a n z a = f l y n n a r c h i v e - g e t % f " % p " parameters: wal_level: ’ r e p l i c a ’ archive_mode: ’ o n ’ archive_command: ’ p g b a c k r e s t - - s t a n z a = f l y n n a r c h i v e - p u s h % p ’ unix_socket_directories: ’ / v a r / r u n / p o s t g r e s q l / . ’ ..... Federico Campoli (PostgreSQL Europe) Protecting your data with Patroni and pgBackRest End of line 55 / 63
56. Create the empty PGDATA on the patroni machines mkdir -p /var/lib/pgsql/data/ postgresql14 chown postgres:postgres /var/lib/pgsql/data/ postgresql14 chmod 0700 /var/lib/pgsql/data/ postgresql14 Federico Campoli (PostgreSQL Europe) Protecting your data with Patroni and pgBackRest End of line 56 / 63
57. Start patroni and wait for the bootstrap to complete sudo systemctl start patroni patronictl -c /etc/patroni/patroni.yml list + Cluster: flynn ( initializing ) ---+---------------------------------+----+-----------+ | Member | Host | Role | State | TL | Lag in MB | +-----------+-----------+---------+---------------------------------+----+-----------+ | patroni -0 | patroni -0 | Replica | running custom bootstrap script | | unknown | +-----------+-----------+---------+---------------------------------+----+-----------+ patronictl -c /etc/patroni/patroni.yml list + Cluster: flynn (6981439838068958622) -----+----+-----------+ | Member | Host | Role | State | TL | Lag in MB | +-----------+-----------+--------+---------+----+-----------+ | patroni -0 | patroni -0 | Leader | running | 2 | | +-----------+-----------+--------+---------+----+-----------+ Federico Campoli (PostgreSQL Europe) Protecting your data with Patroni and pgBackRest End of line 57 / 63
58. Start patroni on the other nodes sudo systemctl start patroni patronictl -c /etc/patroni/patroni.yml list + Cluster: flynn (6981439838068958622) ---------------+----+-----------+ | Member | Host | Role | State | TL | Lag in MB | +-----------+-----------+---------+------------------+----+-----------+ | patroni -0 | patroni -0 | Leader | running | 2 | | | patroni -1 | patroni -1 | Replica | creating replica | | unknown | | patroni -2 | patroni -2 | Replica | creating replica | | unknown | +-----------+-----------+---------+------------------+----+-----------+ patronictl -c /etc/patroni/patroni.yml list + Cluster: flynn (6981439838068958622) ------+----+-----------+ | Member | Host | Role | State | TL | Lag in MB | +-----------+-----------+---------+---------+----+-----------+ | patroni -0 | patroni -0 | Leader | running | 2 | | | patroni -1 | patroni -1 | Replica | running | 2 | 0 | | patroni -2 | patroni -2 | Replica | running | 2 | 0 | +-----------+-----------+---------+---------+----+-----------+ Federico Campoli (PostgreSQL Europe) Protecting your data with Patroni and pgBackRest End of line 58 / 63
59. I fight for the users Copyright Walt Disney LTD Image source https://disney.fandom.com/wiki/Tron (character) Federico Campoli (PostgreSQL Europe) Protecting your data with Patroni and pgBackRest I fight for the users 59 / 63
60. Wrap up The demo repository for this example is available on github. https://github.com/the4thdoctor/patroni-pgbackrest-demo Federico Campoli (PostgreSQL Europe) Protecting your data with Patroni and pgBackRest I fight for the users 60 / 63
61. Wrap up Patroni and pgBackRest are amazing Patroni requires the DBA to change their point of view Patroni doesn’t implement the DR but pgBackRest does it! This example is missing a lot of pieces (security, connection routing...) Using tools like Puppet or Ansible is a very,very,very,very,very good idea Always RTFM! Federico Campoli (PostgreSQL Europe) Protecting your data with Patroni and pgBackRest I fight for the users 61 / 63
62. Thank you for listening! Any questions? Copyright by dan232323 http://dan232323.deviantart.com/art/Pinkie-Pie-Thats-All-Folks-454693000 Federico Campoli (PostgreSQL Europe) Protecting your data with Patroni and pgBackRest I fight for the users 62 / 63
63. Protecting your data with Patroni and pgBackRest NaLUG, Napoli Federico Campoli PostgreSQL Europe 4th May 2024 Federico Campoli (PostgreSQL Europe) Protecting your data with Patroni and pgBackRest I fight for the users 63 / 63