pgconf.ru - 2019 - patroni master class

How to build simple failover cluster on postgres, patroni, wal-g and s3.

Transcript

1. simple postgres HA using patroni, ansible, s3, wal-g Андрей Фефелов mastery.pro
2. host (h2) pg-h2 host (h1) pg-h1 host (h0) pg-h0 postgres, patroni, wal-g, s3, ansible postgres patroni consul* postgres* patroni consul postgres patroni consul S3 wal-g wal-g wal-g Andy Fefelov - mastery.pro - http://pgconf.ru/2019 2
3. our setup pgconf.mastery.pro http://userX:passwd@pgconf.mastery.pro:800X dind (afefelov/masterclass-dind-gotty) dind (afefelov/baseimage-dind) – [h0, h1, h2] consul postgres -> afefelov/patroni-class-pg-9.6-walg Andy Fefelov - mastery.pro - http://pgconf.ru/2019 3
4. commands docker version http://github.com/afefelov/patroni-class Ctrl+C, Ctrl+V, Shift+Ins, Ctrl+Ins? No extensions Andy Fefelov - mastery.pro - http://pgconf.ru/2019 4
5. our setup ansible: roles infra (drop the world) consul (1st class service) patroni (postgres with patroni) Andy Fefelov - mastery.pro - http://pgconf.ru/2019 5
6. patroni by zalando -“bot pattern” for postgres -using DCS as storage (consul) -failover -switchover -fencing -sync/async mode Andy Fefelov - mastery.pro - http://pgconf.ru/2019 6
7. patroni by zalando -patronictl -custom bootstrap -standby cluster -hooks -python based Andy Fefelov - mastery.pro - http://pgconf.ru/2019 7
8. host (h2) host (h1) host (h0) setup Andy Fefelov - mastery.pro - http://pgconf.ru/2019 8
9. console ansible-playbook -i inventory init.yml docker ps Andy Fefelov - mastery.pro - http://pgconf.ru/2019 9
10. consul -DCS -kv -dns resolver -internal checks -ui -by hashicorp Andy Fefelov - mastery.pro - http://pgconf.ru/2019 10
11. host (h2) host (h1) host (h0) consul consul* consul consul Andy Fefelov - mastery.pro - http://pgconf.ru/2019 11
12. console - consul ./dynamic_inventory.py ansible-playbook -i dynamic_inventory.py consul.yml docker logs h0 Andy Fefelov - mastery.pro - http://pgconf.ru/2019 12
13. patroni by zalando -“bot pattern” for postgres - using DCS as storage (consul) - failover, switchover - promoting, demoting, fencing - hooks - custom bootstrap -python based Andy Fefelov - mastery.pro - http://pgconf.ru/2019 13
14. patroni by zalando name: {{ patroni_node_name }} scope: {{ patroni_scope}} watchdog: mode: off consul: host: "{{ ansible_host }}:8500" register_service: true restapi: listen: 0.0.0.0:8008 connect_address: "{{ ansible_host }}:8008" auth: 'username:password' Andy Fefelov - mastery.pro - http://pgconf.ru/2019 14
15. patroni by zalando bootstrap: dcs: ttl: 30 loop_wait: 10 maximum_lag_on_failover: 1048576 postgresql: use_pg_rewind: true use_slots: true parameters: … Andy Fefelov - mastery.pro - http://pgconf.ru/2019 15
16. host (h2) host (h1) host (h0) pg-h0 single master postgres * patroni consul* consul consul Andy Fefelov - mastery.pro - http://pgconf.ru/2019 16
17. console - patroni ./dynamic_inventory.py # h0->172.18.0.2 ansible-playbook -i dynamic_inventory.py patroni.yml --tags=patroni-init --limit=172.18.0.2 docker exec -it h0 docker ps Andy Fefelov - mastery.pro - http://pgconf.ru/2019 17
18. console - master docker exec -it h0 docker logs pg-h0 docker exec -it h0 docker exec -it pg-h0 ping master.patroni-class.service.consul Andy Fefelov - mastery.pro - http://pgconf.ru/2019 18
19. host (h2) host (h1) pg-h1 host (h0) pg-h0 master + slave postgres patroni consul* postgres * patroni consul consul Andy Fefelov - mastery.pro - http://pgconf.ru/2019 19
20. console - slave ansible-playbook -i dynamic_inventory.py patroni.yml --tags=patroni-init --limit=172.18.0.3 docker exec -it h0 docker exec -it pg-h0 ping replica.patroni-class.service.consul docker exec -it h1 docker logs --tail 100 pg-h1 Andy Fefelov - mastery.pro - http://pgconf.ru/2019 20
21. console - check docker exec -it h0 docker exec -it pg-h0 gosu postgres psql -c "CREATE TABLE bins AS SELECT * FROM GENERATE_SERIES(1, 10000) AS id; " docker exec -it h1 docker exec -it pg-h1 gosu postgres psql -c "SELECT max(id) from bins;" Andy Fefelov - mastery.pro - http://pgconf.ru/2019 21
22. console - switchover docker exec -it h1 docker exec -it pg-h1 bash patronictl –help patronictl -c /var/lib/postgresql/patroni.yml list patronictl -c /var/lib/postgresql/patroni.yml switchover patronictl -c /var/lib/postgresql/patroni.yml list ping master.patroni-class.service.consul exit Andy Fefelov - mastery.pro - http://pgconf.ru/2019 22
23. host (h2) host (h0) pg-h0 failover postgres* patroni consul* host (h1) pg-h1 postgres * patroni consul consul Andy Fefelov - mastery.pro - http://pgconf.ru/2019 23
24. console - fencing docker network disconnect bridge h1 docker exec -it h0 docker exec -it pg-h0 patronictl -c /var/lib/postgresql/patroni.yml list docker network connect bridge h1 docker exec -it h0 docker exec -it pg-h0 patronictl -c /var/lib/postgresql/patroni.yml list Andy Fefelov - mastery.pro - http://pgconf.ru/2019 24
25. host (h2) host (h1) pg-h1 host (h0) pg-h0 fencing postgres * patroni consul* postgres patroni consul consul Andy Fefelov - mastery.pro - http://pgconf.ru/2019 25
26. wal-g -inspired by wal-e -fast -really fast -simple - well, production ready Andy Fefelov - mastery.pro - http://pgconf.ru/2019 26
27. custom init bootstrap: method: walg walg: command: "/usr/bin/restore_backup.sh" keep_existing_recovery_conf: false recovery_conf: recovery_target_action: promote recovery_target_timeline: latest restore_command: "/usr/bin/restore_command.sh %f %p" Andy Fefelov - mastery.pro - http://pgconf.ru/2019 27
28. host (h2) host (h1) pg-h1 host (h0) pg-h0 custom init postgres * patroni consul* postgres patroni consul consul S3 pg-h2 postgres patroni wal-g Andy Fefelov - mastery.pro - http://pgconf.ru/2019 28
29. console – custom init # Let's init cluster from s3# we changed scope from patroni-class to patroni-class-walg ansible-playbook -i dynamic_inventory.py patroni.yml --tags=patroni-init-walg --limit=172.18.0.4 docker exec -it h2 docker logs --tail 100 pg-h2 docker exec -it pg-h2 gosu postgres psql -c "SELECT max(id) from walg3;" Andy Fefelov - mastery.pro - http://pgconf.ru/2019 29
30. upcoming - 6.02 – 13:00 – Андрей Бородин: Резервные копии с WAL-G. Что там в 2019? - 6.02 – 17:00 Александр Кукушкин Типичные ошибки при построении высокодоступных кластеров и как их избежать - 5.02 – 11:45 Андрей Фефелов Как мы выбирали среди patroni, stolon, repmgr для нашего отказоустойчивого Постгреса Andy Fefelov - mastery.pro - http://pgconf.ru/2019 30
31. thank you -Александр Кукушкин -Андрей Бородин -Алексей Лесовский -> Вахов -mastery.pro team -Selectel Andy Fefelov - mastery.pro - http://pgconf.ru/2019 31
32. telegram group patroni-class Andy Fefelov - mastery.pro - http://pgconf.ru/2019 32
33. host (h2) pg-h2 host (h1) pg-h1 host (h0) pg-h0 alternative: haproxy+keepalived postgres patroni consul* postgres* patroni consul postgres patroni consul wal-g wal-g wal-g haproxy haproxy haproxy keepalived (vrrp/carp) Andy Fefelov - mastery.pro - http://pgconf.ru/2019 33
34. links -patroni.readthedocs.io/ -github.com/wal-g/wal-g -github.com/afefelov/patroni-class -afefelov/patroni-class-pg-9.6-walg -afefelov/baseimage-dind -afefelov/masterclass-dind-gotty Andy Fefelov - mastery.pro - http://pgconf.ru/2019 34