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?

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
   

   View Slide

2. View Slide

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
   

   View Slide

4. S p e c i a l D i s c o u n t
   Use Code for 20%
   “Postgres Build 20”
   

   View Slide

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
   

   View Slide

6. Postgres Architectures
   in Production
   

   View Slide

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

   View Slide

8. Architectures
   Production Components and Dependencies
   

   View Slide

9. in Production
   This is happening live!
   

   View Slide

10. Availability: Service & Data
    

    View Slide

11. High Availability
    • Maintain service
    • Failover
    • Automated?
    • Maintain data
    • Disaster Recovery
    • Streaming Replication
    

    View Slide

12. Classic Postgres Architecture
    Using Streaming Replication with Two Standby
    Nodes to enable Service and Data Availability
    

    View Slide

13. Postgres Failover
    

    View Slide

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
    

    View Slide

15. Included in Postgres
    • Online Streaming changes
    • Replication Slots
    • pg_basebackup
    • pg_rewind
    • Fast forward
    • Cascading Replication
    • Online Standby Promotion
    A
    B C
    

    View Slide

16. Included in Postgres
    • Disaster Recovery basics: PITR
    • Setup a “standby” with a target
    • Archiving provided separately A
    B C
    

    View Slide

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
    

    View Slide

18. Not included in Postgres
    • Archiving
    • Base Backups
    • WAL Archiving
    • Retention setup for Disaster Recovery
    

    View Slide

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
    

    View Slide

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
    

    View Slide

21. Automated Failover
    

    View Slide

22. pg_auto_failover
    

    View Slide

23. Primary
    Secondary
    Application Monitor
    SQL
    SQL (fallback)
    Streaming Replication
    Health checks
    Health checks
    Single Standby
    Only provides failover, no HA
    

    View Slide

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
    

    View Slide

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
    

    View Slide

26. Two Standby Nodes
    $ pg_autoctl create postgres \
    —auth trust \
    —self-signed-ssl. \
    —monitor postgres://host/pg_auto_failover \
    —run
    

    View Slide

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
    

    View Slide

28. Three Standby Nodes
    $ pg_autoctl create postgres \
    —auth trust \
    —self-signed-ssl. \
    —monitor postgres://host/pg_auto_failover \
    —run
    

    View Slide

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
    

    View Slide

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
    

    View Slide

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
    

    View Slide

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
    

    View Slide

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
    

    View Slide

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
    

    View Slide

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
    

    View Slide

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
    

    View Slide

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
    

    View Slide