Downloaded 13 times
Uploaded byEDB
True Postgres High Availability Architecture with Industry Standard Open-Source Tooling
The document outlines a high availability architecture for PostgreSQL using industry-standard open-source tools, focusing on requirements like automatic failover and minimal data loss. It discusses various tools such as keepalived, haproxy, repmgr, and barman for deployment, monitoring, backup, and maintenance. The importance of testing and avoiding manual work is emphasized to ensure reliability and efficiency in the setup.
True Postgres High Availability Architecture with Industry Standard Open-Source Tooling
- 1. True Postgres HighAvailability Architecture with industry standard open-source tooling Mike Sijmons (Nibble-IT) maandag 21 december 2020
- 2. Michel Sijmons • Co-Founderof Nibble-IT (1990) • Postgres user since 2002 • 1st EDB Partner in Europe (2010) • Oracle/DB2/SQL Server to Postgres Migrations Monday, 21 December 2020
- 3. Agenda • Requirements • TheDesign • The Tools • Demo • Wrap-up • Questions maandag 21 december 2020
- 4. Requirements • No applicationreconfigure after fail-over/switch-over • RPO = 0 after fail-over/switch-over • Fail-over should be automatic • Back-up en recovery • Automatic deployment • Monitoring • Encryption • Auditing • Automatic maintenance • Performance insights in queries maandag 21 december 2020
- 5. The Design maandag 21december 2020
- 6.
- 7. Application design maandag 21december 2020 • Detect connection problems • Retry reconnect for a period of time • Resubmit failed transaction • This as extra loop around normal transaction handling
- 8. The Tools maandag 21december 2020
- 9. No application reconfigure •IP Address should not change • Virtual ip address • Keepalived (https://www.keepalived.org) • HAProxy (https://www.haproxy.org) • Routing to primary server • Xinetd daemon maandag 21 december 2020
- 10. RPO = 0 •No committed transaction may be lost • Postgres ACID properties • Uncommitted/Inflight transactions could be retryed • Application design maandag 21 december 2020
- 11. Automatic fail-over • Repmgr(https://repmgr.org) • Easy management of HA stack • Repmgrd • Fencing of lost primary • Possibility of witness maandag 21 december 2020
- 12. Back-up and recovery •Barman (https://www.pgbarman.org) • Back-up catalogs • Full/Incremental backups • Back-up server • Integration of pg_receivewal maandag 21 december 2020
- 13. Automatic Deployment • Ansible(https://www.ansible.com) • Agentless architecture • Manages Unix/Linux and Windows systems • Can manage large number of hosts in parallel • Extendable through modules • Playbooks are machine and human readable maandag 21 december 2020
- 14. Monitoring • Prometheus (https://prometheus.io) •Grafana (https://grafana.com) • Scalable • Collectors for Postgres, OS, Keepalived, HAProxy • (https://github.com/wrouesnel/postgres_exporter) (https://github.com/prometheus/node_exporter) (https://github.com/cafebazaar/keepalived-exporter) (* no separate exporter is needed for HAProxy Metrics) maandag 21 december 2020
- 15. Encryption • Storage layercan take care of data in rest • Using SSL protocol for data in transit • Possibility to inject certificates or use self-signed • Close DBMS servers for interactive access maandag 21 december 2020
- 16. Auditing • pgAudit (https://www.pgaudit.org) •comply with government, financial and ISO certifications • Easy to use maandag 21 december 2020
- 17. Automatic Maintenance • Cronjob on DBMS Servers • Clean-up logfiles • Analyzes and vacuums tables missed by AV • Reporting of work done maandag 21 december 2020
- 18. Performance insights • pgBadger(http://pgbadger.darold.net) • Insights for developers • Complements with OS/Database Metrics maandag 21 december 2020
- 19.
- 20.
- 21. Wrap-up maandag 21 december2020 • Proven reliable • Use the strength of each tool • The chain is only as strong as its weakest link • Avoid manual work, script tasks • Testing is important • Future steps
- 22. maandag 21 december2020 CentOS https://www.centos.org/ Postgresql https://www.postgresql.org/ Repmgr https://repmgr.org/ Barman https://www.pgbarman.org/ HAProxy https://www.haproxy.org/ Keepalived https://www.keepalived.org/ Prometheus https://prometheus.io/ https://github.com/wrouesnel/postgres_exporter/ https://github.com/prometheus/node_exporter/ https://github.com/cafebazaar/keepalived-exporter/ Grafana https://grafana.com/ pgAudit https://www.pgaudit.org/ pgBadger http://pgbadger.darold.net/ Ansible https://www.ansible.com/ Questions If you want more information about the implementation, you can always contact me at m.sijmons@nibble-it.nl