Slide 1

Slide 1 text

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

Slide 2

Slide 2 text

No content

Slide 3

Slide 3 text

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

Slide 4

Slide 4 text

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

Slide 5

Slide 5 text

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

Slide 6

Slide 6 text

Postgres Architectures in Production

Slide 7

Slide 7 text

PostgreSQL The World's Most Advanced Open Source Relational Database

Slide 8

Slide 8 text

Architectures Production Components and Dependencies

Slide 9

Slide 9 text

in Production This is happening live!

Slide 10

Slide 10 text

Availability: Service & Data

Slide 11

Slide 11 text

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

Slide 12

Slide 12 text

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

Slide 13

Slide 13 text

Postgres Failover

Slide 14

Slide 14 text

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

Slide 15

Slide 15 text

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

Slide 16

Slide 16 text

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

Slide 17

Slide 17 text

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

Slide 18

Slide 18 text

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

Slide 19

Slide 19 text

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

Slide 20

Slide 20 text

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

Slide 21

Slide 21 text

Automated Failover

Slide 22

Slide 22 text

pg_auto_failover

Slide 23

Slide 23 text

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

Slide 24

Slide 24 text

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

Slide 25

Slide 25 text

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

Slide 26

Slide 26 text

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

Slide 27

Slide 27 text

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

Slide 28

Slide 28 text

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

Slide 29

Slide 29 text

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

Slide 30

Slide 30 text

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

Slide 31

Slide 31 text

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

Slide 32

Slide 32 text

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

Slide 33

Slide 33 text

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

Slide 34

Slide 34 text

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

Slide 35

Slide 35 text

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

Slide 36

Slide 36 text

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

Slide 37

Slide 37 text

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