multi-master postgresql architectures in cloud · agenda what is multi-master? physical vs logical...

Multi-MasterMulti-MasterPostgreSQLPostgreSQLArchitecturesArchitecturesin Cloudin CloudGülçin Yıldırım JelínekGülçin Yıldırım Jelínek 1

select * from me;select * from me;Board of Directors @ Cloud Services Manager @ Main Organizer @ Member @  MSc, Computer & Systems Eng. @ BSc, Applied Mathematics @ Writes on 2ndQuadrant From TurkeyLives in Prague

PostgreSQL Europe2ndQuadrant

Prague PostgreSQL MeetupPostgres Women

TalTechYildiz Technical University

blog

Github: @apatheticmagpie

gulcin 2

AgendaAgendaWhat is Multi-Master?What is Multi-Master?Physical vs Logical ReplicationPhysical vs Logical ReplicationPostgres-BDR Postgres-BDR Multi-Master ArchitecturesMulti-Master ArchitecturesMulti-Master Postgres in Cloud (GDS)Multi-Master Postgres in Cloud (GDS)Our Experience with Cloud PostgresOur Experience with Cloud PostgresConclusionConclusionQuestionsQuestions

3

What is Multi-Master?What is Multi-Master?

Replicate writes between multiple mastersReplicate writes between multiple mastersAsynchronous with conflictsAsynchronous with conflictsConflict-free (consensus)Conflict-free (consensus)

4

Physical vs Logical ReplicationPhysical vs Logical Replication

Replay WALWAL cannot be changed

Bit copy of upstreamAll or nothingSame major version

Can't write to downstream

Logical decoding Extension APIRow changesCommitted changes

Selective replicationCross-versionCan write to all nodes

5

Physical vs Logical ReplicationPhysical vs Logical Replication

6

Postgres-BDRPostgres-BDR

Multi-masterMulti-masterAsynchronousAsynchronousEventually consistentEventually consistent

Does not prevent concurrent writesDoes not prevent concurrent writesOptimistic conflict detection (after commit)Optimistic conflict detection (after commit)Automatic conflict resolutionAutomatic conflict resolution

Used for Logical Replication development in PGUsed for Logical Replication development in PG

7

Multi-Master Architectures - IMulti-Master Architectures - I

Geographically distributed clusterGeographically distributed cluster8

AlwaysOn ArchitectureAlwaysOn Architecture

Multi-Master Architectures - IIMulti-Master Architectures - II

9

AlwaysOn ArchitectureAlwaysOn Architecture

Application

ConnectionRouter

M-Lead

Multi-Master Architectures - IIMulti-Master Architectures - II

9

AlwaysOn ArchitectureAlwaysOn Architecture

Application

ConnectionRouter

Postgres-BDR

M-Lead M-Shadow

Multi-Master Architectures - IIMulti-Master Architectures - II

9

Global Database as a ServiceGlobal Database as a Service

Bundled up best PostgreSQLpractices into our cloudservice, with high-availability,and 24x7 support by the bestPostgreSQL engineers! 

We developWe hostWe manageWe supportYOU USE!

10

GDS FlavoursGDS Flavours

11

Single MasterSingle Master

Development1 primary databaseNo support

Test1 primary database1 replica databaseGold Support

Production1 primary database2 replica databasesBackupsPlatinum Support

12

Multi MasterMulti Master

Bi-Directional Replication withPostgres-BDR 3.0High availabilityGeographically distributed clusterZero Downtime Upgrades

Different Postgres versionsFlexibility

Different configurationDifferent instance sizes (CPU,disk, memory etc)

13

physical rep.

bi-directional replication

subcluster a subcluster b

geo cluster

Multi Master ArchitectureMulti Master Architecture

physical rep.

14

AlwaysOnAlwaysOnProvides at least 4 Nines (99.99%)availabilityDeployed in single region with twodifferent availability zones:

2 BDR primary2 physical replicaBackup

“Very High Availability” described inthe BDR whitepaper.

15

AlwaysOn ArchitectureAlwaysOn Architecture

M-Lead M-Shadow

bi-directional replication

physical rep.

16

How to Connect to your DB?How to Connect to your DB?

17

How to Connect to your DB?How to Connect to your DB?

1. Set your database password first2. Choose the instance you want to connect3. Choose one of the provided connection string types4. Connect with your db client

17

How to Connect to your DB?How to Connect to your DB?

1. Set your database password first2. Choose the instance you want to connect3. Choose one of the provided connection string types4. Connect with your db client

17

How to Connect to your DB?How to Connect to your DB?

1. Set your database password first2. Choose the instance you want to connect3. Choose one of the provided connection string types4. Connect with your db client

17

How to Connect to your DB?How to Connect to your DB?

1. Set your database password first2. Choose the instance you want to connect3. Choose one of the provided connection string types4. Connect with your db client

17

How to Connect to your DB?How to Connect to your DB?

1. Set your database password first2. Choose the instance you want to connect3. Choose one of the provided connection string types4. Connect with your db client

17

How to Connect to your DB?How to Connect to your DB?

1. Set your database password first2. Choose the instance you want to connect3. Choose one of the provided connection string types4. Connect with your db client

17

Database ConfigurationDatabase Configuration

18

Database ConfigurationDatabase Configuration

18

Database ConfigurationDatabase Configuration

Search parameter name

18

Monitoring DashboardMonitoring Dashboard

19

Dynamic ScalingDynamic Scaling

20

Dynamic ScalingDynamic Scaling

20

Dynamic ScalingDynamic Scaling

20

Dynamic ScalingDynamic Scaling

20

LoggingLogging

21

LoggingLogging

21

LoggingLogging

Action log of all clusters

21

LoggingLogging

Action log of all clusters

21

LoggingLogging

Action log of all clusters

Filter by cluster name

21

BenchmarksBenchmarks

22

BenchmarksBenchmarks

22

BenchmarksBenchmarks

22

BenchmarksBenchmarks

TPS

22

BenchmarksBenchmarks

TPS

Latency

22

SecuritySecurity

SSL connections only

Encrypted Data at Rest

No SUPERUSER 

VPC Peering

IP Whitelisting

Authenticate with 2ndQuadrant SSO

23

Automated BackupsAutomated Backups

24

Automated BackupsAutomated Backups

24

Automated BackupsAutomated Backups

24

Automated BackupsAutomated Backups

24

Automated BackupsAutomated Backups

24

Automated BackupsAutomated Backups

Filter backups by status and cluster 24

Scheduled BackupsScheduled Backups

25

Scheduled BackupsScheduled Backups

25

Scheduled BackupsScheduled Backups

Backup schedule

25

Scheduled BackupsScheduled Backups

Backup schedule

25

Scheduled BackupsScheduled Backups

Backup retentionBackup schedule

25

Point-in-Time RecoveryPoint-in-Time Recovery

26

Point-in-Time RecoveryPoint-in-Time Recovery

26

Point-in-Time RecoveryPoint-in-Time Recovery

Pick time26

DocumentationDocumentation

27

DocumentationDocumentation

27

DocumentationDocumentation

Docs (WiP)

27

DocumentationDocumentation

Docs (WiP)

27

DocumentationDocumentation

Docs (WiP)

Interactive API docs

27

Postgres Cloud ManagerPostgres Cloud Manager

28

ConclusionConclusionPostgres in Cloud is a go!Postgres in Cloud is a go!Multi-Master addsMulti-Master adds new possibilitiesnew possibilitiesIt's possible to createIt's possible to create your own cloudyour own cloudindependent ofindependent of the platformthe platformAutomation enablesAutomation enables easy managementeasy managementPostgres SupportPostgres Support applies to Cloud!applies to Cloud!

29

Questions?Questions?

Tešekkürler!Tešekkürler!

30