Choosing From the Many Flavors of PostgreSQL Replication

Slide 1

Slide 1 text

Choosing from the Many Flavors of PostgreSQL Replication Richard Yen PG Conf Silicon Valley 2019

Slide 2

Slide 2 text

Disclaimer This is not an EDB talk!

Slide 3

Slide 3 text

Why Replication?

Slide 4

Slide 4 text

Why Replication? • Because one copy is not enough!

Slide 5

Slide 5 text

Why Replication? • Because one copy is not enough! • Fault-Tolerance

Slide 6

Slide 6 text

Why Replication? • Because one copy is not enough! • Fault-Tolerance • Scalability

Slide 7

Slide 7 text

Why Replication? • Because one copy is not enough! • Fault-Tolerance • Scalability • Recoverability

Slide 8

Slide 8 text

Why Replication? • Because one copy is not enough! • Fault-Tolerance • Scalability • Recoverability • People want “real” data to work with

Slide 9

Slide 9 text

Which one is for me? • Streaming replication • Logical replication • Slony • Bucardo • Londiste • DRBD • Corosync • 2ndQ BDR • pg_shard • pglogical • Daﬀodil • Mammoth • Pgpool • EDB Replication Server • Spectral Core Replicator Pro • And More!

Slide 10

Slide 10 text

It all boils down to…

Slide 11

Slide 11 text

It all boils down to… Trigger-based | WAL-based

Slide 12

Slide 12 text

Trigger-based Replication

Slide 13

Slide 13 text

Trigger-based Replication • Relies on PostgreSQL’s trigger mechanism to log all DML into event/ shadow tables

Slide 14

Slide 14 text

Trigger-based Replication • Relies on PostgreSQL’s trigger mechanism to log all DML into event/ shadow tables • Data from shadow tables ingested by another program and replayed to other nodes

Slide 15

Slide 15 text

Trigger-based Replication • Relies on PostgreSQL’s trigger mechanism to log all DML into event/ shadow tables • Data from shadow tables ingested by another program and replayed to other nodes • Very reliable: replicating production environments since 2003

Slide 16

Slide 16 text

Trigger-based Replication

Slide 17

Slide 17 text

Trigger-based Replication • Main players

Slide 18

Slide 18 text

Trigger-based Replication • Main players • Slony — written in C

Slide 19

Slide 19 text

Trigger-based Replication • Main players • Slony — written in C • Londiste — written in Python

Slide 20

Slide 20 text

Trigger-based Replication • Main players • Slony — written in C • Londiste — written in Python • Bucardo — written in Perl

Slide 21

Slide 21 text

Trigger-based Pros & Cons

Slide 22

Slide 22 text

Trigger-based Pros & Cons • Select which tables to replicate

Slide 23

Slide 23 text

Trigger-based Pros & Cons • Select which tables to replicate • Enables row-based/column-based replication

Slide 24

Slide 24 text

Trigger-based Pros & Cons • Select which tables to replicate • Enables row-based/column-based replication • Major-version upgrades

Slide 25

Slide 25 text

Trigger-based Pros & Cons • Select which tables to replicate • Enables row-based/column-based replication • Major-version upgrades • Master-master replication (Bucardo only)

Slide 26

Slide 26 text

Trigger-based Pros & Cons

Slide 27

Slide 27 text

Trigger-based Pros & Cons • Primary keys required

Slide 28

Slide 28 text

Trigger-based Pros & Cons • Primary keys required • Limited ability to replicate DDL

Slide 29

Slide 29 text

Trigger-based Pros & Cons • Primary keys required • Limited ability to replicate DDL • Large object replication not available

Slide 30

Slide 30 text

Trigger-based Pros & Cons • Primary keys required • Limited ability to replicate DDL • Large object replication not available • Trigger activity generates additional I/O

Slide 31

Slide 31 text

Trigger-based Pros & Cons • Primary keys required • Limited ability to replicate DDL • Large object replication not available • Trigger activity generates additional I/O • Event tracking consumes network bandwidth

Slide 32

Slide 32 text

Slide 33

Slide 33 text

Slide 34

Slide 34 text

WAL-based Replication

Slide 35

Slide 35 text

WAL-based Replication • WAL ﬁles are the mechanism to recover from crashes

Slide 36

Slide 36 text

WAL-based Replication • WAL ﬁles are the mechanism to recover from crashes • WAL ﬁles can be used to replay DML on remote servers

Slide 37

Slide 37 text

WAL-based Replication • WAL files are the mechanism to recover from crashes • WAL files can be used to replay DML on remote servers • Streaming WAL activity effectively creates an up-to-date clone

Slide 38

Slide 38 text

WAL-based Pros & Cons

Slide 39

Slide 39 text

WAL-based Pros & Cons • It's fast

Slide 40

Slide 40 text

WAL-based Pros & Cons • It's fast • Easy to set up

Slide 41

Slide 41 text

WAL-based Pros & Cons • It's fast • Easy to set up • Requires no additional software, I/O, bandwidth

Slide 42

Slide 42 text

WAL-based Pros & Cons • It's fast • Easy to set up • Requires no additional software, I/O, bandwidth • Exact replica provides assurance for backups

Slide 43

Slide 43 text

WAL-based Pros & Cons • It's fast • Easy to set up • Requires no additional software, I/O, bandwidth • Exact replica provides assurance for backups • Exact replica means all data types are supported

Slide 44

Slide 44 text

Slide 45

Slide 45 text

WAL-based Pros & Cons

Slide 46

Slide 46 text

WAL-based Pros & Cons • All or nothing

Slide 47

Slide 47 text

WAL-based Pros & Cons • All or nothing • No major version upgrades

Slide 48

Slide 48 text

WAL-based Pros & Cons • All or nothing • No major version upgrades • Query cancellations

Slide 49

Slide 49 text

WAL-based Pros & Cons • All or nothing • No major version upgrades • Query cancellations • Storage impact on primary • wal_keep_segments • replication slots

Slide 50

Slide 50 text

Middle Ground: Logical Replication

Slide 51

Slide 51 text

Middle Ground: Logical Replication • Introduced in v. 10

Slide 52

Slide 52 text

Middle Ground: Logical Replication • Introduced in v. 10 • Based oﬀ Logical Decoding framework introduced in v. 9.4

Slide 53

Slide 53 text

Middle Ground: Logical Replication • Introduced in v. 10 • Based oﬀ Logical Decoding framework introduced in v. 9.4 • WAL ﬁles are reconstructed into SQL statements

Slide 54

Slide 54 text

Middle Ground: Logical Replication • Introduced in v. 10 • Based off Logical Decoding framework introduced in v. 9.4 • WAL files are reconstructed into SQL statements • Paves the way to more granular replication filters

Slide 55

Slide 55 text

Slide 56

Slide 56 text

Best of Both Worlds?

Slide 57

Slide 57 text

Best of Both Worlds? • Like Trigger-based Replication: • Still need uniqueness/PK • DDL is not replicated • Sequences are not replicated

Slide 58

Slide 58 text

Best of Both Worlds? • Like Trigger-based Replication: • Still need uniqueness/PK • DDL is not replicated • Sequences are not replicated • Like WAL-based Replication • Still need to monitor storage impact • Can’t create column-level ﬁlters (yet)

Slide 59

Slide 59 text

Additional Gotchas

Slide 60

Slide 60 text

Additional Gotchas • WAL events are unrolled into individual SQL queries • COPY statements are translated into INSERTs • UPDATE on 1000 rows are translated into 1000 individual UPDATEs

Slide 61

Slide 61 text

Additional Gotchas • WAL events are unrolled into individual SQL queries • COPY statements are translated into INSERTs • UPDATE on 1000 rows are translated into 1000 individual UPDATEs • TRUNCATEs are replicated, but not cascaded

Slide 62

Slide 62 text

Slide 63

Slide 63 text

Other options

Slide 64

Slide 64 text

Other options • DRBD, Corosync, Windows Cluster • Basically, mirrors a disk or other block-level device • Doesn't support read-only standby • Use cases for these are somewhat rare

Slide 65

Slide 65 text

Other options • DRBD, Corosync, Windows Cluster • Basically, mirrors a disk or other block-level device • Doesn't support read-only standby • Use cases for these are somewhat rare • Use at your own discretion

Slide 66

Slide 66 text

How do you choose?

Slide 67

Slide 67 text

It all depends on your requirements Tolerances, privacy, etc.

Slide 68

Slide 68 text

Process of Elimination

Slide 69

Slide 69 text

Start with built-in Streaming Replication

Slide 70

Slide 70 text

Start with built-in Streaming Replication • Super-simple to set up

Slide 71

Slide 71 text

Start with built-in Streaming Replication • Super-simple to set up • No additional software

Slide 72

Slide 72 text

Start with built-in Streaming Replication • Super-simple to set up • No additional software • Best performance, least impact

Slide 73

Slide 73 text

Start with built-in Streaming Replication • Super-simple to set up • No additional software • Best performance, least impact • Good for most use-cases

Slide 74

Slide 74 text

Streaming Replication X

Slide 75

Slide 75 text

Next, try Logical Replication

Slide 76

Slide 76 text

Next, try Logical Replication • Pick and choose which tables to replicate

Slide 77

Slide 77 text

Next, try Logical Replication • Pick and choose which tables to replicate • WAL-based replication performance

Slide 78

Slide 78 text

Next, try Logical Replication • Pick and choose which tables to replicate • WAL-based replication performance • Zero-downtime major-version upgrades

Slide 79

Slide 79 text

Next, try Logical Replication • Pick and choose which tables to replicate • WAL-based replication performance • Zero-downtime major-version upgrades • Only available in v. 10 and later

Slide 80

Slide 80 text

Streaming Replication Logical Replication X X

Slide 81

Slide 81 text

Next, try pglogical or EPRS

Slide 82

Slide 82 text

Next, try pglogical or EPRS • Replication engines supporting Logical Decoding

Slide 83

Slide 83 text

Next, try pglogical or EPRS • Replication engines supporting Logical Decoding • pglogical

Slide 84

Slide 84 text

Next, try pglogical or EPRS • Replication engines supporting Logical Decoding • pglogical • EnterpriseDB Postgres Replication Server 7 (EPRS7 — Limited Availability)

Slide 85

Slide 85 text

Next, try pglogical or EPRS • Replication engines supporting Logical Decoding • pglogical • EnterpriseDB Postgres Replication Server 7 (EPRS7 — Limited Availability) • Row-level and column-level ﬁlters

Slide 86

Slide 86 text

Slide 87

Slide 87 text

Streaming Replication Logical Replication pglogical | EPRS7 X X X

Slide 88

Slide 88 text

Use a Trigger-based Solution

Slide 89

Slide 89 text

Use a Trigger-based Solution

Slide 90

Slide 90 text

Use a Trigger-based Solution • But please upgrade soon!

Slide 91

Slide 91 text

Use a Trigger-based Solution • But please upgrade soon! • Impacts performance

Slide 92

Slide 92 text

Use a Trigger-based Solution • But please upgrade soon! • Impacts performance • Often diﬃcult to administration

Slide 93

Slide 93 text

Use a Trigger-based Solution • But please upgrade soon! • Impacts performance • Often diﬃcult to administration • Logical Replication is the future

Slide 94

Slide 94 text

RDS?

Slide 95

Slide 95 text

RDS? Try Bucardo

Slide 96

Slide 96 text

Multi-Master Replication?

Slide 97

Slide 97 text

Multi-Master Replication? • Holy Grail of replication solutions? • First, ask yourself if you REALLY need it • Consistency • Performance • Conﬂict Resolution • Often, the other options are good enough ?

Slide 98

Slide 98 text

Multi-Master Replication • Trigger-based • Bucardo • XDB by EnterpriseDB • WAL-based • Bi-Directional Replication (BDR) by 2nd Quadrant • requires a custom compile/installation of PG • XDB/EPRS7 by EnterpriseDB

Slide 99

Slide 99 text

Q & A

Slide 100

Slide 100 text

Thank You! Richard Yen Principal Support Engineer, EnterpriseDB [email protected] richyen.com