Ansible & pgbackrest, the dynamic duo

Ansible is an amazing product used by enterprises to automate their tasks. Pgbackrest allows simple and efficient backups of PostgreSQL. Combining the two tools it is possible to setup an environment where the backups are dynamically configured, performed and tested. The audience will learn how to configure two database servers in hot standby with a backup server and a fourth machine used for testing the backups.

Transcript

1. Ansible and pgbackrest The dynamic duo Federico Campoli PgDay Austria, 6 September 2019 Federico Campoli Ansible and pgbackrest PgDay Austria, 6 September 2019 1 / 60
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 Ansible and pgbackrest PgDay Austria, 6 September 2019 2 / 60
3. Kamedata Whether you need a simple audit a tailored training or support for your infrastructure, we can help you to improve. Devops PostgreSQL Support Training Audit Migrations https://kamedata.com Federico Campoli Ansible and pgbackrest PgDay Austria, 6 September 2019 3 / 60
4. Wisdom Image source https://en.wikipedia.org/wiki/File:Train wreck at Montparnasse 1895.jpg Federico Campoli Ansible and pgbackrest PgDay Austria, 6 September 2019 4 / 60
5. 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 PgDay Austria, 6 September 2019 5 / 60
6. Wisdom Conditions for a valid backup Take the backup (obviously!) Save the backup on a different machine, possibly off site Test the backup Federico Campoli Ansible and pgbackrest PgDay Austria, 6 September 2019 6 / 60
7. Table of contents 1 Greetings, programs! 2 The grid 3 The light cycle maze 4 End of line Federico Campoli Ansible and pgbackrest PgDay Austria, 6 September 2019 7 / 60
8. 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 PgDay Austria, 6 September 2019 8 / 60
9. PostgreSQL https://www.postgresql.org/ Enteprise class RDBMS ACID compliant HA and DR With one little catch... No built in mechanism for automating backups Federico Campoli Ansible and pgbackrest PgDay Austria, 6 September 2019 9 / 60
10. PostgreSQL backup options logical with pg dump physical with tools like pg basebackup barman WAL-E/WAL-G pgbackrest Federico Campoli Ansible and pgbackrest PgDay Austria, 6 September 2019 10 / 60
11. Pgbackrest: pros https://pgbackrest.org/ 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 Developed in perl and C ini style configuration Available in deb/yum pgdg repositories Backup repository on disk only Federico Campoli Ansible and pgbackrest PgDay Austria, 6 September 2019 11 / 60
12. Pgbackrest: cons Configuring remote backups may be complex The backup over ssh requires to manage multiple configurations In async WAL push there is the risk of invalidating the backup Repository on disk only Federico Campoli Ansible and pgbackrest PgDay Austria, 6 September 2019 12 / 60
13. Ansible on the resque! https://www.ansible.com/ Agent less configuration and provisioning software Idempotent Rich module library Developed in python Playbooks written in YAML Helps greatly in managing pgbackrest’s configuration on multiple servers Federico Campoli Ansible and pgbackrest PgDay Austria, 6 September 2019 13 / 60
14. Therefore... This is a job for ansible and pgbackrest! The dynamic duo! Copyright Federico Campoli Federico Campoli Ansible and pgbackrest PgDay Austria, 6 September 2019 14 / 60
15. A working example Using four servers we’ll setup the following: db01 and db02 as primary and secondary database servers backupsrv,restoresrv as backup and restore servers Take a backup from the clusters on the primary using pgbackrest Setup the secondary in hot standby using with pgbackrest Schedule periodical backups on backupsrv Schedule periodical restore tests on restoresrv Federico Campoli Ansible and pgbackrest PgDay Austria, 6 September 2019 15 / 60
16. A working example pgbackrest’s repository is a directory that may be local to the database server or set on a remote machine. Remote options should be preferred in order to guarantee that the backups are kept on a different machine. Some options are available. AWS S3 native support NFS/CIFS mount native ssh copy In our example will use the ssh copy setup Federico Campoli Ansible and pgbackrest PgDay Austria, 6 September 2019 16 / 60
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 Ansible and pgbackrest PgDay Austria, 6 September 2019 17 / 60
18. pgbackrest setup The default configuration file is /etc/pgbackrest.conf The configuration have a global section and per cluster configurations Each cluster’s backup configuration is called stanza When using the ssh copy method we need to configure pgbackrest differently on the backup and database servers Federico Campoli Ansible and pgbackrest PgDay Austria, 6 September 2019 18 / 60
19. pgbackrest setup 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 PgDay Austria, 6 September 2019 19 / 60
20. pgbackrest.conf database server I [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/ Federico Campoli Ansible and pgbackrest PgDay Austria, 6 September 2019 20 / 60
21. pgbackrest.conf database server II [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 PgDay Austria, 6 September 2019 21 / 60
22. 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 Federico Campoli Ansible and pgbackrest PgDay Austria, 6 September 2019 22 / 60
23. pgbackrest.conf backup server II [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 [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 PgDay Austria, 6 September 2019 23 / 60
24. The roles hosts, optional, for environment without DNS apt, setup the pgdg apt repository and installs the required packages ssh, enables the passwordless login between the machines pgsql setup the database directories creates the clusters on the servers primary, secondary, restore configures pgbackrest for the backups via ssh executes the first backup and initialises the standby clusters on the secondary server pgbackrest, configures the periodical backups and the restore tests and the backup/restore report via email using mutt Federico Campoli Ansible and pgbackrest PgDay Austria, 6 September 2019 24 / 60
25. Inventory I [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] Federico Campoli Ansible and pgbackrest PgDay Austria, 6 September 2019 25 / 60
26. Inventory II hosts [pgsql:children] dbserver bckserver rstserver [pgbackrest:children] bckserver rstserver Federico Campoli Ansible and pgbackrest PgDay Austria, 6 September 2019 26 / 60
27. 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 PgDay Austria, 6 September 2019 27 / 60
28. 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 r e s t o r e s e r v e r : r e s t o r e 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 " l o g r o t a t e : mode: " daily " keep: "31" smtp host: smtp . g m a i l . com smtp port: 587 smtp username: foo@bar . l o c a l smtp password: ! v a u l t s m t p r e c i p i e n t s : - i n f o @ f o o . bar - foo@bar . l o c a l Federico Campoli Ansible and pgbackrest PgDay Austria, 6 September 2019 28 / 60
29. Role pgsql pre-flight checks Check the ports are unique within the clusters dictionary - name: B u i l d t h e l i s t o f t h e P o s t g r e S Q L d e f i n e d p o r t s set_fact: pg_ports: " { { p g _ p o r t s | d e f a u l t ( [ ] ) + [ i t e m . v a l u e . p a r a m s . p o r t ] } } " with_dict: " { { p g _ c l u s t e r s } } " - name: C h e c k t h e p o r t i s u n i q u e w i t h i n t h e c l u s t e r s assert: that: > pg_ports | count == pg_ports | unique | count msg: " D u p l i c a t e d p o r t d e t e c t e d i n t h e c l u s t e r d e f i n i t i o n . , , , , " Federico Campoli Ansible and pgbackrest PgDay Austria, 6 September 2019 29 / 60
30. Role pgsql setup the clusters Setup the clusters only on the database and restore servers using an include - name: s e t u p t h e c l u s t e r s include: s e t u p _ c l u s t e r s . y m l when: ( ’ d b s e r v e r ’ i n g r o u p _ n a m e s ) o r ( ’ r s t s e r v e r ’ i n g r o u p _ n a m e s ) Federico Campoli Ansible and pgbackrest PgDay Austria, 6 September 2019 30 / 60
31. Role pgsql setup the clusters I Create the database directories, the super user password file and initialise the clusters using an include. The include is necessary for combining the general and the per cluster variables. - name: c r e a t e t h e d a t a b a s e d i r e c t o r i e s include: c r e a t e _ d b _ d i r . y m l with_dict: " { { p g _ c l u s t e r s } } " loop_control: loop_var: d b _ c l u s t e r - name: C r e a t e t h e f i l e s w i t h t h e p o s t g r e s q l s u p e r u s e r p a s s w o r d lineinfile: owner: " { { p g _ o s u s e r } } " group: " { { p g _ o s g r o u p } } " mode: 0 6 0 0 dest: " { { p g _ h o m e _ d i r } } / . p g _ s u p e r _ p w d _ { { i t e m . k e y } } " line: " { { i t e m . v a l u e . p g _ s u p e r _ p a s s w o r d | d e f a u l t ( p g _ s u p e r _ p a s s w o r d ) } } " create: y e s Federico Campoli Ansible and pgbackrest PgDay Austria, 6 September 2019 31 / 60
32. Role pgsql setup the clusters II with_dict: " { { p g _ c l u s t e r s } } " - name: I n i t i a l i s e t h e p o s t g r e s q l c l u s t e r s u s i n g a n i n c l u d e include: i n i t _ c l u s t e r . y m l with_dict: " { { p g _ c l u s t e r s } } " loop_control: loop_var: d b _ c l u s t e r - name: R e m o v e t h e t h e f i l e s w i t h t h e p o s t g r e s q l s u p e r u s e r p a s s w o r d file: path: " { { p g _ h o m e _ d i r } } / . p g _ s u p e r _ p w d _ { { i t e m . k e y } } " state: a b s e n t with_dict: " { { p g _ c l u s t e r s } } " Federico Campoli Ansible and pgbackrest PgDay Austria, 6 September 2019 32 / 60
33. Role pgsql create db dir.yml - name: c r e a t i n g t h e d a t a b a s e d i r e c t o r i e s f o r t h e c l u s t e r { { d b _ c l u s t e r . k e y } } file: path: " { { i t e m . v a l u e } } / { { 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 . k e y } } / " owner: " { { p g _ o s u s e r } } " group: " { { p g _ o s g r o u p } } " state: d i r e c t o r y mode: 0 7 0 0 with_dict: " { { p g _ d i r s | c o m b i n e ( d b _ c l u s t e r . v a l u e . p g _ d i r s | d e f a u l t ( p g _ d i r s ) ) } } " Federico Campoli Ansible and pgbackrest PgDay Austria, 6 September 2019 33 / 60
34. Role pgsql init cluster.yml I - name: C r e a t e t h e 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 t h e c l u s t e r { { d b _ c l u s t e r . k e y } } set_fact: db_dir: " { { p g _ d i r s | c o m b i n e ( d b _ c l u s t e r . v a l u e . p g _ d i r s | d e f a u l t ( p g _ d i r s ) ) } } " - name: i n i t i a l i s i n g t h e c l u s t e r { { d b _ c l u s t e r . k e y } } command: | pg_createcluster --locale {{ db_cluster.value.locale | default( default_locale ) }} -u {{ pg_osuser }} -l {{ db_dir. log_directory }}/{{ db_cluster.value.version }}/{{ db_cluster .key }}/ postgresql -{{ db_cluster .value.version }} -{{ db_cluster .key }}. log -p {{ db_cluster.value.params.port }} -d {{ db_dir.data_area }}/{{ db_cluster.value.version }}/{{ db_cluster .key }} {{ db_cluster .value.version }} {{ db_cluster.key }} -- Federico Campoli Ansible and pgbackrest PgDay Austria, 6 September 2019 34 / 60
35. Role pgsql init cluster.yml II -X {{ db_dir.wal_area }}/{{ db_cluster .value.version }}/{{ db_cluster .key }} -U {{ pg_osuser }} --pwfile ={{ pg_home_dir }}/. pg_super_pwd_ {{ db_cluster .key }} args: creates: " { { 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 . k e y } } / P G _ V E R S I O N " Federico Campoli Ansible and pgbackrest PgDay Austria, 6 September 2019 35 / 60
36. Role pgsql cluster setup I - name: E n s u r e t h e 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 t h e c o n f i g u r a t i o n f o l d e r file: path: " / e t c / p o s t g r e s q l / { { i t e m . v a l u e . v e r s i o n } } / { { i t e m . k e y } } / c o n f . d " owner: " { { p g _ o s u s e r } } " group: " { { p g _ o s g r o u p } } " mode: 0 7 4 4 with_dict: " { { p g _ c l u s t e r s } } " - name: E n s u r e t h e c o n f . d p a t h i s s e t a s i n c l u d e i n p o s t g r e s q l . c o n f lineinfile: path: " / e t c / p o s t g r e s q l / { { i t e m . v a l u e . v e r s i o n } } / { { i t e m . k e y } } / p o s t g r e s q l . c o n f " regexp: " ^ i n c l u d e _ d i r = ’ c o n f . d ’ " line: " i n c l u d e _ d i r = ’ c o n f . d ’ " owner: " { { p g _ o s u s e r } } " group: " { { p g _ o s g r o u p } } " mode: 0 6 4 4 with_dict: " { { p g _ c l u s t e r s } } " Federico Campoli Ansible and pgbackrest PgDay Austria, 6 September 2019 36 / 60
37. Role pgsql cluster setup II - name: S h i p t h e c u s t o m p o s t g r e s q l . c o n f i n c o n f . d template: src: p o s t g r e s q l . c o n f . j 2 dest: " / e t c / p o s t g r e s q l / { { i t e m . v a l u e . v e r s i o n } } / { { i t e m . k e y } } / c o n f . d / 0 1 p o s t g r e s q l . c o n f " owner: " { { p g _ o s u s e r } } " group: " { { p g _ o s g r o u p } } " mode: 0 6 4 4 with_dict: " { { p g _ c l u s t e r s } } " - name: S h i p t h e c u s t o m p g b a c k r e s t . c o n f i n c o n f . d template: src: 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: " / e t c / p o s t g r e s q l / { { i t e m . v a l u e . v e r s i o n } } / { { i t e m . k e y } } / c o n f . d / 9 9 p o s t g r e s q l _ p g b a c k r e s t . c o n f " owner: " { { p g _ o s u s e r } } " group: " { { p g _ o s g r o u p } } " mode: 0 6 4 4 with_dict: " { { p g _ c l u s t e r s } } " Federico Campoli Ansible and pgbackrest PgDay Austria, 6 September 2019 37 / 60
38. Role pgsql: postgresql.conf.j2 template postgresql.conf.j2 The variable params is defined in the role’s defaults params: listen_addresses: " * " shared_buffers: " 2 5 6 M B " work_mem: " 1 0 M B " maintenance_work_mem: " 3 0 M B " max_connections: " 5 0 " The template combines the value with the optional parameters defined into the cluster’s key to build the the cluster’s include. {% set cluster_params = params | combine(item.value.params) %} {% for parameter in cluster_params %} {{ parameter }}= ’ { { c l u s t e r _ p a r a m s [ p a r a m e t e r ] } } ’ {% endfor %} Federico Campoli Ansible and pgbackrest PgDay Austria, 6 September 2019 38 / 60
39. Role pgsql: postgresql pgbackrest.conf.j2 template {% 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 PgDay Austria, 6 September 2019 39 / 60
40. Role pgsql tasks The remaining role’s tasks are: Ships the pg hba.conf in cluster’s config directory 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 database primary Configure pgbackrest on the servers Federico Campoli Ansible and pgbackrest PgDay Austria, 6 September 2019 40 / 60
41. Role pgsql: configure pgbackrest I The template self adapts on whether the server is the backups server or a database server in order to generate the correct configuration we’ve seen before. - name: d e p l o y p g b a c k r e s t . c o n f template: src: p g b a c k r e s t . c o n f . j 2 dest: " { { p g b a c k r e s t _ c o n f } } " owner: " { { p g _ o s u s e r } } " group: " { { p g _ o s g r o u p } } " mode: 0 6 4 4 Federico Campoli Ansible and pgbackrest PgDay Austria, 6 September 2019 41 / 60
42. Role pgsql: configure pgbackrest I The template self adapts on whether the server is the backups server or a database server in order to generate the correct configuration we’ve seen before. - name: d e p l o y p g b a c k r e s t . c o n f template: src: p g b a c k r e s t . c o n f . j 2 dest: " { { p g b a c k r e s t _ c o n f } } " owner: " { { p g _ o s u s e r } } " group: " { { p g _ o s g r o u p } } " mode: 0 6 4 4 - name: s e t u p t h e b a c k u p s e r v e r include: s e t u p _ b c k s e r v e r . y m l 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 . b a c k u p _ s e r v e r Federico Campoli Ansible and pgbackrest PgDay Austria, 6 September 2019 42 / 60
43. Role pgsql: setup bckserver.yml I We initialise the stanzas and perform the first backup for all of the stanzas available. - name: c r e a t e t h e p g b a c k r e s t r e p o s i t o r y file: path: " { { p g _ b a c k r e s t . r e p o s i t o r y } } " owner: " { { p g _ o s u s e r } } " group: " { { p g _ o s g r o u p } } " state: d i r e c t o r y mode: 0 7 0 0 - name: c r e a t e t h e p g b a c k r e s t l o g d i r e c t o r y file: path: " { { p g _ b a c k r e s t . r e p o s i t o r y } } / l o g s " owner: " { { p g _ o s u s e r } } " group: " { { p g _ o s g r o u p } } " state: d i r e c t o r y mode: 0 7 0 0 Federico Campoli Ansible and pgbackrest PgDay Austria, 6 September 2019 43 / 60
44. Role pgsql: setup bckserver.yml II - name: c r e a t e s t a n z a s o n t h e b a c k u p s e r v e r command: p g b a c k r e s t s t a n z a - c r e a t e - - s t a n z a { { i t e m . k e y } } become: y e s become_user: " { { p g _ o s u s e r } } " args: creates: " { { p g _ b a c k r e s t . r e p o s i t o r y } } / b a c k u p / { { i t e m . k e y } } / " when: i n v e n t o r y _ h o s t n a m e i n g r o u p s [ ’ b c k s e r v e r ’ ] with_dict: " { { p g _ c l u s t e r s } } " - name: d e p l o y r u n _ b a c k u p template: src: r u n _ b a c k u p . s h . j 2 dest: " { { p g _ h o m e _ d i r } } / r u n _ b a c k u p . s h " owner: " { { p g _ o s u s e r } } " group: " { { p g _ o s g r o u p } } " mode: 0 7 0 0 Federico Campoli Ansible and pgbackrest PgDay Austria, 6 September 2019 44 / 60
45. Role pgsql: setup bckserver.yml III - name: e x e c u t e t h e f i r s t b a c k u p command: p g b a c k r e s t b a c k u p - - s t a n z a { { i t e m . k e y } } become: y e s become_user: " { { p g _ o s u s e r } } " args: creates: " { { p g _ b a c k r e s t . r e p o s i t o r y } } / b a c k u p / { { i t e m . k e y } } / l a t e s t " when: i n v e n t o r y _ h o s t n a m e i n g r o u p s [ ’ b c k s e r v e r ’ ] with_dict: " { { p g _ c l u s t e r s } } " Federico Campoli Ansible and pgbackrest PgDay Austria, 6 September 2019 45 / 60
46. 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 pgbackrest role Send a simple report email when complete postgres@backupsrv :˜ $ ./ run backup . sh f u l l Federico Campoli Ansible and pgbackrest PgDay Austria, 6 September 2019 46 / 60
47. Role pgsql: restore on the secondary I We restore the first backup on the secondary and then we start the postgres service on the secondary - name: r e s t o r e t h e b a c k u p o n t h e s e c o n d a r y include: r e s t o r e _ s e c o n d a r y . y m l with_dict: " { { p g _ c l u s t e r s } } " 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 loop_control: loop_var: d b _ c l u s t e r - name: S t a r t t h e p o s t g r e s q l s e r v i c e o n t h e s e c o n d a r y service: name: p o s t g r e s q l state: 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 Federico Campoli Ansible and pgbackrest PgDay Austria, 6 September 2019 47 / 60
48. Role pgsql: restore secondary.yml I - name: B u i l d a d i c t i o n a r y w i t h t h e d a t a b a s e d i r e c t o r i e s f o r t h e c l u s t e r { { d b _ c l u s t e r . k e y } } set_fact: db_dir: " { { p g _ d i r s | c o m b i n e ( d b _ c l u s t e r . v a l u e . p g _ d i r s | d e f a u l t ( p g _ d i r s ) ) } } " - name: r e s t o r e t h e b a c k u p o n t h e s e c o n d a r y command: p g b a c k r e s t r e s t o r e - - d e l t a - - s t a n z a { { d b _ c l u s t e r . k e y } } become: y e s become_user: " { { p g _ o s u s e r } } " args: creates: " { { 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 . k e y } } / r e c o v e r y . c o n f " Federico Campoli Ansible and pgbackrest PgDay Austria, 6 September 2019 48 / 60
49. Role pgsql: deploy test restore on the restore server I - name: d e p l o y t e s t _ r e s t o r e template: src: t e s t _ r e s t o r e . s h . j 2 dest: " { { p g _ h o m e _ d i r } } / t e s t _ r e s t o r e . s h " owner: " { { p g _ o s u s e r } } " group: " { { p g _ o s g r o u p } } " mode: 0 7 0 0 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 . r e s t o r e _ s e r v e r Federico Campoli Ansible and pgbackrest PgDay Austria, 6 September 2019 49 / 60
50. test restore.sh Script to automate the restore tests Shipped to the restore server Scheduled by the pgbackrest role 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 PgDay Austria, 6 September 2019 50 / 60
51. Role pgbackrest I - name: d e p l o y m u t t r c template: src: m u t t r c . j 2 dest: " { { p g _ h o m e _ d i r } } / . m u t t r c " owner: " { { p g _ o s u s e r } } " group: " { { p g _ o s g r o u p } } " mode: 0 6 0 0 - name: S h i p t h e 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 t o t h e b a c k u p s e r v e r template: src: l o g r o t a t e _ p g b a c k r e s t . j 2 dest: " { { l o g r o t a t e _ p g b a c k r e s t } } " owner: " r o o t " group: " { { p g _ o s g r o u p } } " mode: 0 6 4 4 when: i n v e n t o r y _ h o s t n a m e i n g r o u p s [ ’ b c k s e r v e r ’ ] - name: s e t u p t h e c r o n j o b s f o r a l l s t a n z a s o n t h e b a c k u p s e r v e r cron: " { { i t e m } } " Federico Campoli Ansible and pgbackrest PgDay Austria, 6 September 2019 51 / 60
52. Role pgbackrest II with_items: " { { c r o n _ c o n f i g . b a c k u p } } " when: i n v e n t o r y _ h o s t n a m e i n g r o u p s [ ’ b c k s e r v e r ’ ] - name: s e t u p t h e c r o n j o b s f o r r e s t o r e t e s t r e s t o r e s e r v e r cron: " { { i t e m } } " with_items: " { { c r o n _ c o n f i g . r e s t o r e } } " when: i n v e n t o r y _ h o s t n a m e i n g r o u p s [ ’ r s t s e r v e r ’ ] Federico Campoli Ansible and pgbackrest PgDay Austria, 6 September 2019 52 / 60
53. Role pgbackrest: muttrc I # Me set from = "{{pg_osuser}}@{{ansible_hostname}}" set realname = "{{pg_osuser}} at {{ansible_hostname}}" # My credentials set smtp_url = "smtp://{{smtp_username}}@{{smtp_host}}:{{smtp_port}}/" set smtp_pass = "{{smtp_password}}" set crypt_use_gpgme = "no" Federico Campoli Ansible and pgbackrest PgDay Austria, 6 September 2019 53 / 60
54. 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 PgDay Austria, 6 September 2019 54 / 60
55. 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 PgDay Austria, 6 September 2019 55 / 60
56. End of line Copyright Walt Disney LTD Image source https://disney.fandom.com/wiki/Tron (character) Federico Campoli Ansible and pgbackrest PgDay Austria, 6 September 2019 56 / 60
57. License This document is distributed under the terms of the Creative Commons Attribution, Not Commercial, Share Alike Federico Campoli Ansible and pgbackrest PgDay Austria, 6 September 2019 57 / 60
58. 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/pgday at Federico Campoli Ansible and pgbackrest PgDay Austria, 6 September 2019 58 / 60
59. 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 PgDay Austria, 6 September 2019 59 / 60
60. Ansible and pgbackrest The dynamic duo Federico Campoli PgDay Austria, 6 September 2019 Federico Campoli Ansible and pgbackrest PgDay Austria, 6 September 2019 60 / 60