Ansible and pgbackrest The dynamic duo Federico Campoli Incontro Devops Italia 2019, Bologna 8 Mar 2019 Federico Campoli Ansible and pgbackrest Incontro Devops Italia 2019, Bologna 8 Mar 2019 1 / 44 

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 Ansible and pgbackrest Incontro Devops Italia 2019, Bologna 8 Mar 2019 2 / 44 

Wisdom Image source https://en.wikipedia.org/wiki/File:Train wreck at Montparnasse 1895.jpg Federico Campoli Ansible and pgbackrest Incontro Devops Italia 2019, Bologna 8 Mar 2019 3 / 44 

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 Ansible and pgbackrest Incontro Devops Italia 2019, Bologna 8 Mar 2019 4 / 44 

Table of contents 1 Greetings, programs! 2 The grid 3 The light cycle maze 4 End of line Federico Campoli Ansible and pgbackrest Incontro Devops Italia 2019, Bologna 8 Mar 2019 5 / 44 

Greetings, programs! Copyright Walt Disney LTD Image source https://liveforfilms.wordpress.com/2009/07/09/greetings-program-tron-2-synopsis/ Federico Campoli Ansible and pgbackrest Incontro Devops Italia 2019, Bologna 8 Mar 2019 6 / 44 

PostgreSQL Enteprise class RDBMS ACID compliant HA and DR With one little catch... No built in mechanism for automating backups https://www.postgresql.org/ Federico Campoli Ansible and pgbackrest Incontro Devops Italia 2019, Bologna 8 Mar 2019 7 / 44 

It’s a job for ansible and pgbackrest, the dynamic duo Copyright Federico Campoli Federico Campoli Ansible and pgbackrest Incontro Devops Italia 2019, Bologna 8 Mar 2019 8 / 44 

Ansible Agentless configuration and provisioning software Idempotent Rich module library Developed in python Playbooks written in YAML https://www.ansible.com/ Federico Campoli Ansible and pgbackrest Incontro Devops Italia 2019, Bologna 8 Mar 2019 9 / 44 

Pgbackrest Physical backup tool Implements HA/DR Several backup and restore strategies Developed in perl and C ini style configuration https://pgbackrest.org/ Federico Campoli Ansible and pgbackrest Incontro Devops Italia 2019, Bologna 8 Mar 2019 10 / 44 

A working example Using four servers we’ll setup the following: db01 and db02 as primary and secondary database servers backusrv,restoresrv as backup and restore servers Backup the clusters on the primary with pgbackrest Setup the secondary in hot standby with pgbackrest Schedule periodical backups and restore tests Federico Campoli Ansible and pgbackrest Incontro Devops Italia 2019, Bologna 8 Mar 2019 11 / 44 

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 Ansible and pgbackrest Incontro Devops Italia 2019, Bologna 8 Mar 2019 12 / 44 

Inventory [hosts] backupsrv ansible_host=192.168.56.22 db01 ansible_host=192.168.56.23 db02 ansible_host=192.168.56.24 restoresrv ansible_host=192.168.56.25 [dbserver] db01 db02 [bckserver] backupsrv [rstserver] restoresrv [apt:children] pgsql [ssh:children] hosts [pgsql:children] dbserver bckserver rstserver [pgbackrest:children] pgsql Federico Campoli Ansible and pgbackrest Incontro Devops Italia 2019, Bologna 8 Mar 2019 13 / 44 

The roles hosts, optional, for environment without DNS apt, setup the pgdg apt repository and install the packages ssh, enables the passwordless login between the machines pgsql, setup the database directories and create the clusters on primary and secondary pgbackrest, configure pgbackrest on the database servers, the backup and restore server and initialise the hot standby on the secondary server cron, configure the crontab for the backups and restore tests Federico Campoli Ansible and pgbackrest Incontro Devops Italia 2019, Bologna 8 Mar 2019 14 / 44 

group vars/all (partial) I p g s u p e r u s e r : " postgres " p g s u p e r p a s s w o r d : ! v a u l t | l o g r o t a t e p g s q l : "/ etc / logrotate .d/ pgsql_custom " d e f a u l t l o c a l e : " en_GB .UTF -8 " p g d i r s : d a t a a r e a : "/ var / lib / postgresql " l o g d i r e c t o r y : "/ var / log / postgresql " w a l a r e a : "/ var / lib / postgresql / pg_wal " l o g r o t a t e : mode: " weekly " keep: "10" p g c l u s t e r s : t r o n : p g d i r s : d a t a a r e a : "/ pg_data " l o g d i r e c t o r y : "/ pg_log " v e r s i o n : " 11 " l o c a l e : " en_US .UTF -8 " params: p or t : " 5432 " l i s t e n a d d r e s s e s : "*" l o g r o t a t e : mode: " daily " keep: "31 " Federico Campoli Ansible and pgbackrest Incontro Devops Italia 2019, Bologna 8 Mar 2019 15 / 44 

group vars/all (partial) II p g b a c k r e s t : b a c k u p s e r v e r : b a c k u p s r v primary: db01 secondary: db02 r e p o s i t o r y : / p g b a c k r e s t / r e c o v e r y o p t i o n : - " standby_mode = on " Federico Campoli Ansible and pgbackrest Incontro Devops Italia 2019, Bologna 8 Mar 2019 16 / 44 

Role pgsql pre-flight checks Check the ports are unique within the clusters dictionary − name: B u i l d the l i s t o f the PostgreSQL d e f i n e d p o r t s s e t f a c t : p g p o r t s : " {{ pg_ports | default ([]) + [ item . value . params . port ] }} " w i t h d i c t : " {{ pg_clusters }}" − name: Check the p o r t i s unique w i t h i n the c l u s t e r s a s s e r t : that: > p g p o r t s | count == p g p o r t s | unique | count msg: " Duplicated port detected in the cluster definition . ,,,," Federico Campoli Ansible and pgbackrest Incontro Devops Italia 2019, Bologna 8 Mar 2019 17 / 44 

Role pgsql create database directories Create the database directories using an include. Necessary evil for combining the default database directories with the directories defined in the dictionary pg clusters. − name: c r e a t e the d a t a b a s e d i r e c t o r i e s i n c l u d e : c r e a t e d b d i r . yml w i t h d i c t : " {{ pg_clusters }}" l o o p c o n t r o l : l o o p v a r : d b c l u s t e r Federico Campoli Ansible and pgbackrest Incontro Devops Italia 2019, Bologna 8 Mar 2019 18 / 44 

Role pgsql create database directories Include create db dir.yml combines the dictionary with the default database directories and create the directories using the schema DIRECTORY/VERSION/CLUSTERNAME − name: c r e a t i n g the d a t a b a s e d i r e c t o r i e s f o r the c l u s t e r {{ d b c l u s t e r . key }} f i l e : path: "{{ item . value }}/{{ db_cluster . value . version }}/{{ db_cluster . key }}/ " owner: "{{ pg_osuser }} " group: "{{ pg_osgroup }} " s t a t e : d i r e c t o r y mode: 0700 w i t h d i c t : " {{ pg_dirs | combine ( db_cluster . value . pg_dirs | default ( pg_dirs )) }}" Federico Campoli Ansible and pgbackrest Incontro Devops Italia 2019, Bologna 8 Mar 2019 19 / 44 

Role pgsql initialise the clusters Store temporarily the super user password in a file with minimal access rights, then using the same include strategy create the clusters. − name: C r e a t e the f i l e s with the p o s t g r e s q l s u p e r u s e r password l i n e i n f i l e : owner: "{{ pg_osuser }} " group: "{{ pg_osgroup }} " mode: 0600 dest: "{{ pg_home_dir }}/. pg_super_pwd_ {{ item . key }} " l i n e : "{{ item . value . pg_super_password | default ( pg_super_password ) }}" c r e a t e : y e s w i t h d i c t : " {{ pg_clusters }}" − name: I n i t i a l i s e the p o s t g r e s q l c l u s t e r s u s i n g an i n c l u d e i n c l u d e : i n i t c l u s t e r . yml w i t h d i c t : " {{ pg_clusters }}" l o o p c o n t r o l : l o o p v a r : d b c l u s t e r − name: Remove the the f i l e s with the p o s t g r e s q l s u p e r u s e r password f i l e : path: "{{ pg_home_dir }}/. pg_super_pwd_ {{ item . key }}" s t a t e : a b s e n t w i t h d i c t : " {{ pg_clusters }} " Federico Campoli Ansible and pgbackrest Incontro Devops Italia 2019, Bologna 8 Mar 2019 20 / 44 

Role pgsql initialise the clusters Include init cluster.yml Create a new variable with set fact combining the default directories with the cluster’s custom directories, if any. Then using the command module initialise the cluster with pg createcluster skipping the clusters already created. − name: C r e a t e the d a t a b a s e d i r e c t o r y d i c t i o n a r y f o r the c l u s t e r {{ d b c l u s t e r . key }} s e t f a c t : d b d i r : "{{ pg_dirs | combine ( db_cluster . value . pg_dirs | default ( pg_dirs )) }}" − name: i n i t i a l i s i n g the c l u s t e r {{ d b c l u s t e r . key }} command: | p g c r e a t e c l u s t e r − −l o c a l e {{ d b c l u s t e r . v a l u e . l o c a l e | d e f a u l t ( d e f a u l t l o c a l e ) }} −u {{ p g o s u s e r }} −l {{ d b d i r . l o g d i r e c t o r y }}/{{ d b c l u s t e r . v a l u e . v e r s i o n }}/{{ d b c l u s t e r . key}}/ p o s t g r e s −p {{ d b c l u s t e r . v a l u e . params . po rt }} −d {{ d b d i r . d a t a a r e a }}/{{ d b c l u s t e r . v a l u e . v e r s i o n }}/{{ d b c l u s t e r . key}} {{ d b c l u s t e r . v a l u e . v e r s i o n }} {{ d b c l u s t e r . key}} − − −X {{ d b d i r . w a l a r e a }}/{{ d b c l u s t e r . v a l u e . v e r s i o n }}/{{ d b c l u s t e r . key}} −U {{ p g o s u s e r }} − −p w f i l e={{ pg home dir }}/. pg super pwd {{ d b c l u s t e r . key}} a r g s : c r e a t e s : "{{ db_dir . data_area }}/{{ db_cluster . value . version }}/{{ db_cluster . key }}/ PG_VE Federico Campoli Ansible and pgbackrest Incontro Devops Italia 2019, Bologna 8 Mar 2019 21 / 44 

Role pgsql setup configuration − name: Ensure the c o n f . d d i r e c t o r y i s p r e s e n t i n the c o n f i g u r a t i o n f o l d e r f i l e : path: "/ etc / postgresql /{{ item . value . version }}/{{ item . key }}/ conf .d" owner: "{{ pg_osuser }} " group: "{{ pg_osgroup }} " mode: 0744 w i t h d i c t : " {{ pg_clusters }} " − name: Ensure the c o n f . d path i s s e t as i n c l u d e i n p o s t g r e s q l . c o n f l i n e i n f i l e : path: "/ etc / postgresql /{{ item . value . version }}/{{ item . key }}/ postgresql . conf " regexp: "^ include_dir = ’conf .d’" l i n e : " include_dir = ’conf .d ’" owner: "{{ pg_osuser }} " group: "{{ pg_osgroup }} " mode: 0644 w i t h d i c t : " {{ pg_clusters }} " − name: Ship the custom p o s t g r e s q l . c o n f i n c o n f . d template: s r c : p o s t g r e s q l . c o n f . j 2 dest: "/ etc / postgresql /{{ item . value . version }}/{{ item . key }}/ conf .d /01 postgresql . conf " owner: "{{ pg_osuser }} " group: "{{ pg_osgroup }} " mode: 0644 w i t h d i c t : " {{ pg_clusters }} " Federico Campoli Ansible and pgbackrest Incontro Devops Italia 2019, Bologna 8 Mar 2019 22 / 44 

Role pgsql tasks postgresql.conf.j2 The variable params is defined in the role’s defaults params: l i s t e n a d d r e s s e s : "*" s h a r e d b u f f e r s : " 256 MB " work mem: "10 MB " maintenance work mem: " 30 MB " max connections: " 50 " The template combines the value with the optional parameters defined into the cluster’s key to build the the cluster’s include. {% s e t c l u s t e r p a r a m s = params | combine ( item . v a l u e . params ) %} {%f o r parameter i n c l u s t e r p a r a m s %} {{ parameter }}=’{{ cluster_params [ parameter ] }} ’ {% e n d f o r %} Federico Campoli Ansible and pgbackrest Incontro Devops Italia 2019, Bologna 8 Mar 2019 23 / 44 

Role pgsql tasks − name: Ship the custom p g b a c k r e s t . c o n f i n c o n f . d template: s r c : p o s t g r e s q l p g b a c k r e s t . c o n f . j 2 dest: "/ etc / postgresql /{{ item . value . version }}/{{ item . key }}/ conf .d /99 postgresql_pgbackr owner: "{{ pg_osuser }} " group: "{{ pg_osgroup }} " mode: 0644 w i t h d i c t : " {{ pg_clusters }} " when: i n v e n t o r y h o s t n a m e i n groups [ ’ dbserver ’ ] Federico Campoli Ansible and pgbackrest Incontro Devops Italia 2019, Bologna 8 Mar 2019 24 / 44 

Role pgsql tasks postgresql pgbackrest.conf.j2 {% set cluster_params = params|combine(item.value.params) %} {% if (’wal_level’ not in cluster_params) %} {% set wal_level=’replica’ %} {% elif (cluster_params[’wal_level’] not in [’logical’,’replica’,’hot_standby’]) %} {% set wal_level=’replica’ %} {% else %} {% set wal_level=cluster_params[’wal_level’] %} {% endif %} max_wal_senders=3 wal_level={{ wal_level }} archive_mode=’on’ archive_command = ’pgbackrest --stanza={{item.key}} archive-push %p’ Federico Campoli Ansible and pgbackrest Incontro Devops Italia 2019, Bologna 8 Mar 2019 25 / 44 

Role pgsql tasks The remaining role’s tasks are: Ships the pg hba.conf in cluster’s config directory Ship the PostgreSQL’s logrotate configuration to the servers Configures the .pgpass file the super user’s connection data Configures the pg service file to allow quick connection using the cluster name Starts the postgresql service on the pgbackrest primary Federico Campoli Ansible and pgbackrest Incontro Devops Italia 2019, Bologna 8 Mar 2019 26 / 44 

pgbackrest The default configuration file is /etc/pgbackrest.conf Global and per cluster configuration Each cluster’s backup configuration is called stanza Federico Campoli Ansible and pgbackrest Incontro Devops Italia 2019, Bologna 8 Mar 2019 27 / 44 

pgbackrest A stanza i s the c o n f i g u r a t i o n f o r a PostgreSQL database c l u s t e r that d e f i n e s where i t i s located , how i t w i l l be backed up , a r c h i v i n g options , etc . Most db s e r v e r s w i l l only have one Postgres database c l u s t e r and t h e r e f o r e one stanza , whereas backup s e r v e r s w i l l have a stanza f o r every database c l u s t e r that needs to be backed up . Federico Campoli Ansible and pgbackrest Incontro Devops Italia 2019, Bologna 8 Mar 2019 28 / 44 

pgbackrest.conf database server [global] backup-host=backupsrv backup-user=postgres log-level-console=error log-level-file=error [tron] pg1-path=/pg_data/11/tron pg1-port=5432 recovery-option=standby_mode=on log-path=/pg_log/11/tron/ [clu] pg1-path=/var/lib/postgresql/9.6/clu pg1-port=5433 recovery-option=standby_mode=on log-path=/var/log/postgresql/9.6/clu/ [sark] pg1-path=/var/lib/postgresql/10/sark pg1-port=5434 recovery-option=standby_mode=on log-path=/var/log/postgresql/10/sark/ Federico Campoli Ansible and pgbackrest Incontro Devops Italia 2019, Bologna 8 Mar 2019 29 / 44 

pgbackrest.conf backup server I [global] repo-path=/pg_backrest/ log-level-console=error log-level-file=info log-path=/pg_backrest//logs/ repo1-retention-full=1 [tron] pg1-path=/pg_data/11/tron pg1-host-user=postgres pg1-port=5432 pg1-host=db01 pg2-host-user=postgres pg2-port=5432 pg2-host=db02 [clu] pg1-path=/var/lib/postgresql/9.6/clu pg1-host-user=postgres pg1-port=5433 pg1-host=db01 pg2-host-user=postgres pg2-port=5433 pg2-host=db02 Federico Campoli Ansible and pgbackrest Incontro Devops Italia 2019, Bologna 8 Mar 2019 30 / 44 

pgbackrest.conf backup server II [sark] pg1-path=/var/lib/postgresql/10/sark pg1-host-user=postgres pg1-port=5434 pg1-host=db01 pg2-host-user=postgres pg2-port=5434 pg2-host=db02 Federico Campoli Ansible and pgbackrest Incontro Devops Italia 2019, Bologna 8 Mar 2019 31 / 44 

Role backrest tasks I − name: c r e a t e the p g b a c k r e s t r e p o s i t o r y f i l e : path: "{{ pg_backrest . repository }}" owner: "{{ pg_osuser }} " group: "{{ pg_osgroup }} " s t a t e : d i r e c t o r y mode: 0700 when: i n v e n t o r y h o s t n a m e i n groups [ ’ bckserver ’ ] − name: c r e a t e the p g b a c k r e s t l o g d i r e c t o r y f i l e : path: "{{ pg_backrest . repository }}/ logs " owner: "{{ pg_osuser }} " group: "{{ pg_osgroup }} " s t a t e : d i r e c t o r y mode: 0700 when: i n v e n t o r y h o s t n a m e i n groups [ ’ bckserver ’ ] − name: d e p l o y p g b a c k r e s t . c o n f template: s r c : p g b a c k r e s t . c o n f . j 2 dest: "{{ pgbackrest_conf }}" owner: "{{ pg_osuser }} " group: "{{ pg_osgroup }} " mode: 0644 Federico Campoli Ansible and pgbackrest Incontro Devops Italia 2019, Bologna 8 Mar 2019 32 / 44 

Role backrest tasks II − name: c r e a t e s t a n z a s on the backup s e r v e r command: p g b a c k r e s t stanza−c r e a t e − −s t a n z a {{ item . key}} become: y e s become user: " {{ pg_osuser }}" a r g s : c r e a t e s : " {{ pg_backrest . repository }}/ backup /{{ item . key }}/ " when: i n v e n t o r y h o s t n a m e i n groups [ ’ bckserver ’ ] w i t h d i c t : " {{ pg_clusters }}" − name: e x e c u t e the f i r s t backup command: p g b a c k r e s t backup − −s t a n z a {{ item . key}} become: y e s become user: " {{ pg_osuser }}" a r g s : c r e a t e s : " {{ pg_backrest . repository }}/ backup /{{ item . key }}/ latest " when: i n v e n t o r y h o s t n a m e i n groups [ ’ bckserver ’ ] w i t h d i c t : " {{ pg_clusters }}" − name: r e s t o r e the backup on the s e c o n d a r y i n c l u d e : r e s t o r e s e c o n d a r y . yml w i t h d i c t : " {{ pg_clusters }}" when: i n v e n t o r y h o s t n a m e == p g b a c k r e s t . s e c o n d a r y l o o p c o n t r o l : l o o p v a r : d b c l u s t e r Federico Campoli Ansible and pgbackrest Incontro Devops Italia 2019, Bologna 8 Mar 2019 33 / 44 

Role backrest tasks III − name: S t a r t the p o s t g r e s q l s e r v i c e on the s e c o n d a r y s e r v i c e : name: p o s t g r e s q l s t a t e : s t a r t e d when: i n v e n t o r y h o s t n a m e == p g b a c k r e s t . s e c o n d a r y − name: d e p l o y muttrc template: s r c : muttrc . j 2 dest: "{{ pg_home_dir }}/. muttrc " owner: "{{ pg_osuser }} " group: "{{ pg_osgroup }} " mode: 0600 − name: d e p l o y run backup template: s r c : run backup . sh . j 2 dest: "{{ pg_home_dir }}/ run_backup .sh" owner: "{{ pg_osuser }} " group: "{{ pg_osgroup }} " mode: 0700 when: i n v e n t o r y h o s t n a m e i n groups [ ’ bckserver ’ ] Federico Campoli Ansible and pgbackrest Incontro Devops Italia 2019, Bologna 8 Mar 2019 34 / 44 

Role backrest tasks IV − name: Ship the l o g r o t a t e c o n f i g u r a t i o n f o r p g b a c k r e s t to the backup s e r v e r template: s r c : l o g r o t a t e p g b a c k r e s t . j 2 dest: "{{ logrotate_pgbackrest }}" owner: " root " group: "{{ pg_osgroup }} " mode: 0644 when: i n v e n t o r y h o s t n a m e i n groups [ ’ bckserver ’ ] − name: d e p l o y t e s t r e s t o r e template: s r c : t e s t r e s t o r e . sh . j 2 dest: "{{ pg_home_dir }}/ test_restore .sh" owner: "{{ pg_osuser }} " group: "{{ pg_osgroup }} " mode: 0700 when: i n v e n t o r y h o s t n a m e i n groups [ ’ rstserver ’ ] Federico Campoli Ansible and pgbackrest Incontro Devops Italia 2019, Bologna 8 Mar 2019 35 / 44 

run backup.sh Script to automate the backups Shipped to the backup server Can backup single or all stanzas incremental,differential or full Scheduled by the cron role Send a simple report email when complete postgres@backupsrv :˜ $ ./ run backup . sh f u l l Federico Campoli Ansible and pgbackrest Incontro Devops Italia 2019, Bologna 8 Mar 2019 36 / 44 

test restore.sh Script to automate the restore tests Shipped to the restore server Cleanup the data directory per each cluster and test the restore Check with pg isready if the cluster is correctly started Send a simple report email when complete postgres@backupsrv :˜ $ ./ t e s t r e s t o r e . sh Federico Campoli Ansible and pgbackrest Incontro Devops Italia 2019, Bologna 8 Mar 2019 37 / 44 

The light cycle maze Copyright Walt Disney LTD Image source https://geektyrant.com/news/the-history-of-the-tron-lightcycle-infographic Federico Campoli Ansible and pgbackrest Incontro Devops Italia 2019, Bologna 8 Mar 2019 38 / 44 

Time for a demo! Disclaimer The demo will fail miserably. Copyright Walt Disney LTD Image source https://tron.fandom.com/wiki/Master Control Program Federico Campoli Ansible and pgbackrest Incontro Devops Italia 2019, Bologna 8 Mar 2019 39 / 44 

End of line Copyright Walt Disney LTD Image source https://disney.fandom.com/wiki/Tron (character) Federico Campoli Ansible and pgbackrest Incontro Devops Italia 2019, Bologna 8 Mar 2019 40 / 44 

License This document is distributed under the terms of the Creative Commons Attribution, Not Commercial, Share Alike Federico Campoli Ansible and pgbackrest Incontro Devops Italia 2019, Bologna 8 Mar 2019 41 / 44 

Contacts Blog: http://www.pgdba.org Twitter: @4thdoctor scarf Github: https://github.com/the4thdoctor Linkedin: https://www.linkedin.com/in/federicocampoli/ Example repository: https://github.com/the4thdoctor/dynamic duo/tree/idevops Federico Campoli Ansible and pgbackrest Incontro Devops Italia 2019, Bologna 8 Mar 2019 42 / 44 

That’s all folks! Thank you for listening! Any questions? Copyright by dan232323 http://dan232323.deviantart.com/art/Pinkie-Pie-Thats-All-Folks-454693000 Federico Campoli Ansible and pgbackrest Incontro Devops Italia 2019, Bologna 8 Mar 2019 43 / 44 

Ansible and pgbackrest The dynamic duo Federico Campoli Incontro Devops Italia 2019, Bologna 8 Mar 2019 Federico Campoli Ansible and pgbackrest Incontro Devops Italia 2019, Bologna 8 Mar 2019 44 / 44