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

@apatheticmagpie

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?

  1. Set your database password first
  2. Choose the instance you want to connect
  3. Choose one of the provided connection string types
  4. 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?

Tešekkürler!

References

Multi-Master PostgreSQL Architectures in Cloud

By Gülçin Yıldırım Jelínek

Made with Slides.com

Multi-Master PostgreSQL Architectures in Cloud

This presentation is prepared for PGDay Istanbul 2018.

  • 2,556

More from Gülçin Yıldırım Jelínek