Multi-Master PostgreSQL Architectures in Cloud
Gülçin Yıldırım Jelínek
select * from me;
Board of Directors @ PostgreSQL Europe
Cloud Services Manager @ 2ndQuadrant
Main Organizer @ Prague PostgreSQL Meetup
Member @ Postgres Women
MSc, Computer & Systems Eng. @ TalTech
BSc, Applied Mathematics @ Yildiz Technical University
Writes on 2ndQuadrant blog
From Turkey
Lives in Prague
Github: gulcin
Agenda
-
What is Multi-Master?
-
Physical vs Logical Replication
-
Postgres-BDR
-
Multi-Master Architectures
-
Multi-Master Postgres in Cloud (GDS)
-
Our Experience with Cloud Postgres
-
Conclusion
-
Questions
What is Multi-Master?
-
Replicate writes between multiple masters
-
Asynchronous with conflicts
-
Conflict-free (consensus)
-
Physical vs Logical Replication
- Replay WAL
- WAL cannot be changed
- Bit copy of upstream
- All or nothing
- Same major version
- Can't write to downstream
- Logical decoding
- Extension API
- Row changes
- Committed changes
- Selective replication
- Cross-version
- Can write to all nodes
Physical vs Logical Replication
Postgres-BDR
-
Multi-master
-
Asynchronous
-
Eventually consistent
-
Does not prevent concurrent writes
-
Optimistic conflict detection (after commit)
-
Automatic conflict resolution
-
-
Used for Logical Replication development in PG
Multi-Master Architectures - I
Geographically distributed cluster
AlwaysOn Architecture
Application
Connection Router
Postgres-BDR
M-Lead
M-Shadow
Multi-Master Architectures - II
Global Database as a Service
Bundled up best PostgreSQL practices into our cloud service, with high-availability, and 24x7 support by the best PostgreSQL engineers!
- We develop
- We host
- We manage
- We support
- YOU USE!
GDS Flavours
Single Master
- Development
- 1 primary database
- No support
- Test
- 1 primary database
- 1 replica database
- Gold Support
- Production
- 1 primary database
- 2 replica databases
- Backups
- Platinum Support
Multi Master
- Bi-Directional Replication with Postgres-BDR 3.0
- High availability
- Geographically distributed cluster
- Zero Downtime Upgrades
- Different Postgres versions
- Flexibility
- Different configuration
- Different instance sizes (CPU, disk, memory etc)
physical rep.
bi-directional replication
subcluster a
subcluster b
geo cluster
Multi Master Architecture
physical rep.
AlwaysOn
- Provides at least 4 Nines (99.99%) availability
- Deployed in single region with two different availability zones:
- 2 BDR primary
- 2 physical replica
- Backup
- “Very High Availability” described in the BDR whitepaper.
AlwaysOn Architecture
M-Lead
M-Shadow
bi-directional replication
physical rep.
How to Connect to your DB?
- Set your database password first
- Choose the instance you want to connect
- Choose one of the provided connection string types
- Connect with your db client
Database Configuration
Search parameter name
Monitoring Dashboard
Database metrics: DB size, table sizes, cache/hit ratio, db sessions
Query metrics: Long running queries, most common queries
Replication metrics: Replication status, slot type, replication lag
System metrics: Disk, memory, CPU
Dynamic Scaling
Logging
Action log of all clusters
Filter by cluster name
Benchmarks
TPS
Latency
Security
SSL connections only
Encrypted Data at Rest
No SUPERUSER
VPC Peering
IP Whitelisting
Authenticate with 2ndQuadrant SSO
Automated Backups
Filter backups by status
and subcluster
Scheduled Backups
Backup retention
Backup schedule
Point-in-Time Recovery
Pick a restore point
Switchover
Documentation
HTML docs
Interactive API docs
Postgres Cloud Manager
Conclusion
-
Postgres in Cloud is a go!
-
Multi-Master adds
new possibilities -
It's possible to create
your own cloud independent of
the platform -
Automation enables
easy management -
Postgres Support
applies to Cloud!
Questions?
