Protecting your data with Patroni and pgBackRest

Patroni is a solid and reliable HA tool. Pgbackrest is the perfect choice to have a proper DR solution in place. The talk will walk the audience through the configuration of patroni coupled with pgBackrRest. It will be shown how to bootstrap a new patroni cluster by using an existing pgBackrRest repository and how to use patroni patroni to create and initialize new replicas without the need of a running primary. The talk will also explain how to perform a disaster recovery by using a pgBackrRest repository saved in cloud.

Transcript

1. Protecting your data with Patroni and pgBackRest Incontro Devops Italia 2022 Federico Campoli Somewhere in the time vortex Federico Campoli Protecting your data with Patroni and pgBackRest Somewhere in the time vortex 1 / 66
2. Few words about the speaker Born in 1972 Passionate about IT since 1982 Joined the Oracle DBA secret society in 2004 In love with PostgreSQL since 2006 PostgreSQL tattoo on the right shoulder Freelance devops and data engineer Federico Campoli Protecting your data with Patroni and pgBackRest Somewhere in the time vortex 2 / 66
3. Getting in touch Blog: https://pgdba.org Twitter: @4thdoctor scarf Github: https://github.com/the4thdoctor Linkedin: https://www.linkedin.com/in/federicocampoli/ Youtube: https://www.youtube.com/c/FedericoCampoli Federico Campoli Protecting your data with Patroni and pgBackRest Somewhere in the time vortex 3 / 66
4. 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 Protecting your data with Patroni and pgBackRest Somewhere in the time vortex 4 / 66
5. Greetings, programs! Copyright Walt Disney LTD Image source https://liveforfilms.wordpress.com/2009/07/09/greetings-program-tron-2-synopsis/ Federico Campoli Protecting your data with Patroni and pgBackRest Somewhere in the time vortex 5 / 66
6. PostgreSQL https://www.postgresql.org/ Enteprise class RDBMS ACID compliant HA and DR With one tiny little catch... No automated backups or failover Federico Campoli Protecting your data with Patroni and pgBackRest Somewhere in the time vortex 6 / 66
7. Some PostgreSQL auto failover options B.Y.O.T, shell script, manually operated, Nazgul summoning... Automated with third party tools repmgr pg auto failover PostgreSQL Automatic Failover (PAF) Patroni Federico Campoli Protecting your data with Patroni and pgBackRest Somewhere in the time vortex 7 / 66
8. 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 Protecting your data with Patroni and pgBackRest Somewhere in the time vortex 8 / 66
9. Patroni Developed in Python Supports for DCS etcd,consul,zookeper Support for python RAFT (requires pysyncobj module) Federico Campoli Protecting your data with Patroni and pgBackRest Somewhere in the time vortex 9 / 66
10. Patroni Automated boostrap and replica setup Automated failover/switchover Centralised configuration for PostgreSQL stored in DCS Very resilient to split brain HAProxy can query the api for connection routing Federico Campoli Protecting your data with Patroni and pgBackRest Somewhere in the time vortex 10 / 66
11. 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 Protecting your data with Patroni and pgBackRest Somewhere in the time vortex 11 / 66
12. Some PostgreSQL backup options logical with pg dump physical with tools like pg basebackup barman WAL-E/WAL-G pgBackRest Federico Campoli Protecting your data with Patroni and pgBackRest Somewhere in the time vortex 12 / 66
13. pgBackRest https://pgbackrest.org/ pgBackRest is a simple and reliable solution for automatic the backups. pgBackRest is a community driven project. Federico Campoli Protecting your data with Patroni and pgBackRest Somewhere in the time vortex 13 / 66
14. pgBackRest Physical backup tool Implements DR with Point In Time Recovery Differential,incremental and full backup Parallel jobs configurable Can backup from the standby servers Async WAL push and pull Federico Campoli Protecting your data with Patroni and pgBackRest Somewhere in the time vortex 14 / 66
15. 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 Protecting your data with Patroni and pgBackRest Somewhere in the time vortex 15 / 66
16. 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 Protecting your data with Patroni and pgBackRest Somewhere in the time vortex 16 / 66
17. The rule of thumb RTFM Federico Campoli Protecting your data with Patroni and pgBackRest Somewhere in the time vortex 17 / 66
18. 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 Protecting your data with Patroni and pgBackRest Somewhere in the time vortex 18 / 66
19. 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 Protecting your data with Patroni and pgBackRest Somewhere in the time vortex 19 / 66
20. Patroni super biased configuration Federico Campoli Protecting your data with Patroni and pgBackRest Somewhere in the time vortex 20 / 66
21. Setup For our example we’ll use Rocky Linux 8 on GCP and a GCP bucket patroni-0 patroni-1 patroni-2 Federico Campoli Protecting your data with Patroni and pgBackRest Somewhere in the time vortex 21 / 66
22. Configure and install the software Deploy the machines with terraform Install etcd on the nodes Configure and start etcd Install PostgreSQL 14 and patroni on the nodes Configure patroni Start patroni Profit Federico Campoli Protecting your data with Patroni and pgBackRest Somewhere in the time vortex 22 / 66
23. Terraform 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 Protecting your data with Patroni and pgBackRest Somewhere in the time vortex 23 / 66
24. Terraform 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 Protecting your data with Patroni and pgBackRest Somewhere in the time vortex 24 / 66
25. 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 Protecting your data with Patroni and pgBackRest Somewhere in the time vortex 25 / 66
26. 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 Protecting your data with Patroni and pgBackRest Somewhere in the time vortex 26 / 66
27. 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 Protecting your data with Patroni and pgBackRest Somewhere in the time vortex 27 / 66
28. 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 Protecting your data with Patroni and pgBackRest Somewhere in the time vortex 28 / 66
29. patroni install Install the epel-release, patroni and patroni-etcd sudo dnf install -y epel -release sudo dnf install -y patroni patroni -etcd Federico Campoli Protecting your data with Patroni and pgBackRest Somewhere in the time vortex 29 / 66
30. 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 Protecting your data with Patroni and pgBackRest Somewhere in the time vortex 30 / 66
31. 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 Protecting your data with Patroni and pgBackRest Somewhere in the time vortex 31 / 66
32. 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 Protecting your data with Patroni and pgBackRest Somewhere in the time vortex 32 / 66
33. 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 Protecting your data with Patroni and pgBackRest Somewhere in the time vortex 33 / 66
34. 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 Protecting your data with Patroni and pgBackRest Somewhere in the time vortex 34 / 66
35. 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 Protecting your data with Patroni and pgBackRest Somewhere in the time vortex 35 / 66
36. 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 Protecting your data with Patroni and pgBackRest Somewhere in the time vortex 36 / 66
37. Enable and start the patroni service On the patroni machines sudo systemctl daemon -reload sudo systemctl enable patroni sudo systemctl start patroni Federico Campoli Protecting your data with Patroni and pgBackRest Somewhere in the time vortex 37 / 66
38. 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 Protecting your data with Patroni and pgBackRest Somewhere in the time vortex 38 / 66
39. 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 Protecting your data with Patroni and pgBackRest Somewhere in the time vortex 39 / 66
40. The light cycle maze Copyright Walt Disney LTD Image source https://geektyrant.com/news/the-history-of-the-tron-lightcycle-infographic Federico Campoli Protecting your data with Patroni and pgBackRest Somewhere in the time vortex 40 / 66
41. Patroni and pgBackRest Federico Campoli Protecting your data with Patroni and pgBackRest Somewhere in the time vortex 41 / 66
42. Configure and install the software Install pgBackRest on the patroni servers Create the GCP bucket Create a new service account on gcp 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’s first backup Profit Federico Campoli Protecting your data with Patroni and pgBackRest Somewhere in the time vortex 42 / 66
43. Install pgBackRest install sudo dnf install -y pgbackrest Federico Campoli Protecting your data with Patroni and pgBackRest Somewhere in the time vortex 43 / 66
44. 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 Protecting your data with Patroni and pgBackRest Somewhere in the time vortex 44 / 66
45. 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 Protecting your data with Patroni and pgBackRest Somewhere in the time vortex 45 / 66
46. 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 Protecting your data with Patroni and pgBackRest Somewhere in the time vortex 46 / 66
47. 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 Protecting your data with Patroni and pgBackRest Somewhere in the time vortex 47 / 66
48. 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 Protecting your data with Patroni and pgBackRest Somewhere in the time vortex 48 / 66
49. 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 Protecting your data with Patroni and pgBackRest Somewhere in the time vortex 49 / 66
50. 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 Protecting your data with Patroni and pgBackRest Somewhere in the time vortex 50 / 66
51. 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 Protecting your data with Patroni and pgBackRest Somewhere in the time vortex 51 / 66
52. End of line Copyright Walt Disney LTD Image source https://tron.fandom.com/wiki/Master Control Program Federico Campoli Protecting your data with Patroni and pgBackRest Somewhere in the time vortex 52 / 66
53. Wisdom An ancient Italian proverb tells: Il backup ` e quella cosa che andava fatta prima. Translation: You should have done your backup before (the disaster). Federico Campoli Protecting your data with Patroni and pgBackRest Somewhere in the time vortex 53 / 66
54. 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 Protecting your data with Patroni and pgBackRest Somewhere in the time vortex 54 / 66
55. 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 Protecting your data with Patroni and pgBackRest Somewhere in the time vortex 55 / 66
56. 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 Protecting your data with Patroni and pgBackRest Somewhere in the time vortex 56 / 66
57. 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 Protecting your data with Patroni and pgBackRest Somewhere in the time vortex 57 / 66
58. 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 Protecting your data with Patroni and pgBackRest Somewhere in the time vortex 58 / 66
59. 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 Protecting your data with Patroni and pgBackRest Somewhere in the time vortex 59 / 66
60. 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 Protecting your data with Patroni and pgBackRest Somewhere in the time vortex 60 / 66
61. 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 Protecting your data with Patroni and pgBackRest Somewhere in the time vortex 61 / 66
62. I fight for the users Copyright Walt Disney LTD Image source https://disney.fandom.com/wiki/Tron (character) Federico Campoli Protecting your data with Patroni and pgBackRest Somewhere in the time vortex 62 / 66
63. Wrap up The demo repository for this example is available on github. https://github.com/the4thdoctor/patroni-pgbackrest-demo Federico Campoli Protecting your data with Patroni and pgBackRest Somewhere in the time vortex 63 / 66
64. 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 Protecting your data with Patroni and pgBackRest Somewhere in the time vortex 64 / 66
65. Thank you for listening! Any questions? Copyright by dan232323 http://dan232323.deviantart.com/art/Pinkie-Pie-Thats-All-Folks-454693000 Federico Campoli Protecting your data with Patroni and pgBackRest Somewhere in the time vortex 65 / 66
66. Protecting your data with Patroni and pgBackRest Incontro Devops Italia 2022 Federico Campoli Somewhere in the time vortex Federico Campoli Protecting your data with Patroni and pgBackRest Somewhere in the time vortex 66 / 66