Save 37% off PRO during our Black Friday Sale! »

Postgres Architectures in Production | Postgres Build 2020 | Dimitri Fontaine

024d6a0dd14fb31c804969a57a06dfbe?s=47 Citus Data
December 09, 2020

Postgres Architectures in Production | Postgres Build 2020 | Dimitri Fontaine

When using PostgreSQL in production it is important to implement a strategy for High Availability. With a database service, the HA properties apply to both the service itself and of course to the data set. In this talk, we learn how to think about the specific HA needs of your production environment and how to achieve your requirements with Open Source tooling when it comes to your database of choice, PostgreSQL. In particular, we dive into many options that could be implemented for Postgres to evolve its offering from being a toolset to being “batteries included”. What does it mean in the context of HA? How to achieve it?

024d6a0dd14fb31c804969a57a06dfbe?s=128

Citus Data

December 09, 2020
Tweet

Transcript

  1. PostgreSQL Architectures in Production Dimitri Fontaine PostgreSQL Major Contributor P

    O S T G R E S B U I L D 2 0 2 0 , D E C 8 - 9 , O N L I N E
  2. None
  3. PostgreSQL for developers Dimitri Fontaine PostgreSQL Major Contributor A B

    O O K A B O U T P O S T G R E S Q L B Y D I M I T R I F O N T A I N E
  4. S p e c i a l D i s

    c o u n t Use Code for 20% “Postgres Build 20”
  5. PostgreSQL Architectures in Production Dimitri Fontaine PostgreSQL Major Contributor P

    O S T G R E S B U I L D 2 0 2 0 , D E C 8 - 9 , O N L I N E
  6. Postgres Architectures in Production

  7. PostgreSQL The World's Most Advanced Open Source Relational Database

  8. Architectures Production Components and Dependencies

  9. in Production This is happening live!

  10. Availability: Service & Data

  11. High Availability • Maintain service • Failover • Automated? •

    Maintain data • Disaster Recovery • Streaming Replication
  12. Classic Postgres Architecture Using Streaming Replication with Two Standby Nodes

    to enable Service and Data Availability
  13. Postgres Failover

  14. Included in Postgres • Streaming Replication • Sync or Async

    • Per transaction setting • ALTER ROLE vip SET synchronous_commit TO ‘remote_apply’; • ALTER ROLE … SET … • ALTER DATABASE … SET … A B C
  15. Included in Postgres • Online Streaming changes • Replication Slots

    • pg_basebackup • pg_rewind • Fast forward • Cascading Replication • Online Standby Promotion A B C
  16. Included in Postgres • Disaster Recovery basics: PITR • Setup

    a “standby” with a target • Archiving provided separately A B C
  17. Included in Postgres • Client Side HA • Multi-Host Connection

    String • Client still has to reconnect when connection is lost, of course A B C postgres://hostA:5502,hostB:5503,hostC:5501/dbname?target_session_attrs=read-write&sslmode=require
  18. Not included in Postgres • Archiving • Base Backups •

    WAL Archiving • Retention setup for Disaster Recovery
  19. Not included in Postgres • Online “Membership” changes • Idea

    that roles are DYNAMIC • Ensure retention during failover • For WALs • And for base backups • Automated node role changes • Restart to reconnect to new primary
  20. Not included in Postgres • Configuration Management • postgresql.conf •

    pg_hba.conf • Some setting change require a restart • Extension Management • Postgres Upgrades • Minor upgrade (scheduled restarts) • Major upgrades
  21. Automated Failover

  22. pg_auto_failover

  23. Primary Secondary Application Monitor SQL SQL (fallback) Streaming Replication Health

    checks Health checks Single Standby Only provides failover, no HA
  24. Single Standby $ pg_autoctl create monitor —auth trust —self-signed-ssl —run

    $ pg_autoctl create postgres \ —auth trust \ —self-signed-ssl. \ —monitor postgres://host/pg_auto_failover \ —run $ pg_autoctl create postgres \ —auth trust \ —self-signed-ssl. \ —monitor postgres://host/pg_auto_failover \ —run
  25. number_sync_standby = Node A Primary replication quorum = true candidate

    priority = Node B Secondary replication quorum = true candidate priority = Node C Secondary replication quorum = true candidate priority = Application Monitor SQL SQL (fallback) Streaming Replication Health checks Two Standby Nodes Allows to implement HA
  26. Two Standby Nodes $ pg_autoctl create postgres \ —auth trust

    \ —self-signed-ssl. \ —monitor postgres://host/pg_auto_failover \ —run
  27. number_sync_standby = Node A Primary replication quorum = true candidate

    priority = Node B Secondary replication quorum = true candidate priority = Node C Secondary replication quorum = true candidate priority = Node D Secondary replication quorum = true candidate priority = Application Monitor SQL SQL (fallback) Health checks Streaming Replication Three Standby Nodes
  28. Three Standby Nodes $ pg_autoctl create postgres \ —auth trust

    \ —self-signed-ssl. \ —monitor postgres://host/pg_auto_failover \ —run
  29. number_sync_standby = Node A Primary replication quorum = true candidate

    priority = Node B Secondary replication quorum = true candidate priority = Node C Secondary replication quorum = false candidate priority = Node D Secondary replication quorum = true candidate priority = Application Monitor SQL SQL (fallback) Health checks Streaming Replication Three Standby Nodes, one async
  30. Three Standby Nodes, One Async $ pg_autoctl set node candidate-priority

    0 $ pg_autoctl set node replication-quorum false $ pg_autoctl create postgres \ —auth trust \ —self-signed-ssl. \ —monitor postgres://host/pg_auto_failover \ —replication-quorum false \ —candidate-priority 0 \ —run
  31. Included in pg_auto_failover • Streaming Replication • Replication slots •

    Maintained on standby nodes • Easy setup including SSL certificates • Dynamic settings, online changes $ pg_autoctl create postgres
  32. Included in pg_auto_failover • Online “Membership” changes • Idea that

    roles are DYNAMIC • Ensure retention during failover • For WALs • And for base backups • Automated HBA editing $ pg_autoctl show uri $ pg_autoctl show state $ pg_autoctl get formation settings
  33. Included in pg_auto_failover • Online membership changes • Online formation

    setting changes • Maintenance operations $ pg_autoctl set node candidate-priority 0 $ pg_autoctl set node replication-quorum false $ pg_autoctl set formation number-sync-standbys 2 $ pg_autoctl enable maintenance $ pg_autoctl disable maintenance
  34. Included in pg_auto_failover • Network split detection / protection •

    Active monitoring and role changes • Automated and manual Failover • Including manual Switchover • Including manual choice of target node $ pg_autoctl perform failover $ pg_autoctl perform switchover $ pg_autoctl perform promotion —name node_d
  35. Not included in pg_auto_failover yet • Archiving and Disaster Recovery

    • Configuration Management • postgresql.conf • pg_hba.conf • Some setting change require a restart • Extension Management • Postgres Upgrades • Minor upgrade (scheduled restarts) • Major upgrades
  36. PostgreSQL Architectures in Production Dimitri Fontaine PostgreSQL Major Contributor P

    O S T G R E S B U I L D 2 0 2 0 , D E C 8 - 9 , O N L I N E
  37. Ask Me Two Questions! Dimitri Fontaine Citus Data, Microsoft @tapoueh

    T H E A R T O F P O S T G R E S Q L