Choosing From the Many Flavors of PostgreSQL Replication

One of the most important tasks of a DBA or sysadmin is to make sure there is a copy of a database's contents available, either in case of a catastrophic failure or to ensure scalability of the entire application stack. Within the PostgreSQL community, there are several options to choose from for replication, and making the choice isn't always trivial. Several factors need to be considered, ranging from how much of the database needs to be replicated, to how much of a performance hit the entire system can afford. This presentation will walk through some of the most reliable replication engines developed over the years, including PostgreSQL's very own built-in Streaming Replication, and discuss the pros and cons among the major players.

Transcript

1. Choosing from the Many Flavors of PostgreSQL Replication Richard Yen PG Conf Silicon Valley 2019
2. Disclaimer This is not an EDB talk!
3. Why Replication?
4. Why Replication? • Because one copy is not enough!
5. Why Replication? • Because one copy is not enough! • Fault-Tolerance
6. Why Replication? • Because one copy is not enough! • Fault-Tolerance • Scalability
7. Why Replication? • Because one copy is not enough! • Fault-Tolerance • Scalability • Recoverability
8. Why Replication? • Because one copy is not enough! • Fault-Tolerance • Scalability • Recoverability • People want “real” data to work with
9. Which one is for me? • Streaming replication • Logical replication • Slony • Bucardo • Londiste • DRBD • Corosync • 2ndQ BDR • pg_shard • pglogical • Daffodil • Mammoth • Pgpool • EDB Replication Server • Spectral Core Replicator Pro • And More!
10. It all boils down to…
11. It all boils down to… Trigger-based | WAL-based
12. Trigger-based Replication
13. Trigger-based Replication • Relies on PostgreSQL’s trigger mechanism to log all DML into event/ shadow tables
14. 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
15. 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
16. Trigger-based Replication
17. Trigger-based Replication • Main players
18. Trigger-based Replication • Main players • Slony — written in C
19. Trigger-based Replication • Main players • Slony — written in C • Londiste — written in Python
20. Trigger-based Replication • Main players • Slony — written in C • Londiste — written in Python • Bucardo — written in Perl
21. Trigger-based Pros & Cons
22. Trigger-based Pros & Cons • Select which tables to replicate
23. Trigger-based Pros & Cons • Select which tables to replicate • Enables row-based/column-based replication
24. Trigger-based Pros & Cons • Select which tables to replicate • Enables row-based/column-based replication • Major-version upgrades
25. Trigger-based Pros & Cons • Select which tables to replicate • Enables row-based/column-based replication • Major-version upgrades • Master-master replication (Bucardo only)
26. Trigger-based Pros & Cons
27. Trigger-based Pros & Cons • Primary keys required
28. Trigger-based Pros & Cons • Primary keys required • Limited ability to replicate DDL
29. Trigger-based Pros & Cons • Primary keys required • Limited ability to replicate DDL • Large object replication not available
30. Trigger-based Pros & Cons • Primary keys required • Limited ability to replicate DDL • Large object replication not available • Trigger activity generates additional I/O
31. 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
32. 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 • Hard to scale up
33. 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 • Hard to scale up • Infrastructure is brittle
34. WAL-based Replication
35. WAL-based Replication • WAL files are the mechanism to recover from crashes
36. WAL-based Replication • WAL files are the mechanism to recover from crashes • WAL files can be used to replay DML on remote servers
37. 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
38. WAL-based Pros & Cons
39. WAL-based Pros & Cons • It's fast
40. WAL-based Pros & Cons • It's fast • Easy to set up
41. WAL-based Pros & Cons • It's fast • Easy to set up • Requires no additional software, I/O, bandwidth
42. WAL-based Pros & Cons • It's fast • Easy to set up • Requires no additional software, I/O, bandwidth • Exact replica provides assurance for backups
43. 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
44. 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 • Synchronous replication is possible
45. WAL-based Pros & Cons
46. WAL-based Pros & Cons • All or nothing
47. WAL-based Pros & Cons • All or nothing • No major version upgrades
48. WAL-based Pros & Cons • All or nothing • No major version upgrades • Query cancellations
49. WAL-based Pros & Cons • All or nothing • No major version upgrades • Query cancellations • Storage impact on primary • wal_keep_segments • replication slots
50. Middle Ground: Logical Replication
51. Middle Ground: Logical Replication • Introduced in v. 10
52. Middle Ground: Logical Replication • Introduced in v. 10 • Based off Logical Decoding framework introduced in v. 9.4
53. 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
54. 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
55. 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 • Happy-medium
56. Best of Both Worlds?
57. Best of Both Worlds? • Like Trigger-based Replication: • Still need uniqueness/PK • DDL is not replicated • Sequences are not replicated
58. 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 filters (yet)
59. Additional Gotchas
60. 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
61. 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
62. 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 • Partitioned tables cannot be easily replicated
63. Other options
64. 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
65. 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
66. How do you choose?
67. It all depends on your requirements Tolerances, privacy, etc.
68. Process of Elimination
69. Start with built-in Streaming Replication
70. Start with built-in Streaming Replication • Super-simple to set up
71. Start with built-in Streaming Replication • Super-simple to set up • No additional software
72. Start with built-in Streaming Replication • Super-simple to set up • No additional software • Best performance, least impact
73. Start with built-in Streaming Replication • Super-simple to set up • No additional software • Best performance, least impact • Good for most use-cases
74. Streaming Replication X
75. Next, try Logical Replication
76. Next, try Logical Replication • Pick and choose which tables to replicate
77. Next, try Logical Replication • Pick and choose which tables to replicate • WAL-based replication performance
78. Next, try Logical Replication • Pick and choose which tables to replicate • WAL-based replication performance • Zero-downtime major-version upgrades
79. 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
80. Streaming Replication Logical Replication X X
81. Next, try pglogical or EPRS
82. Next, try pglogical or EPRS • Replication engines supporting Logical Decoding
83. Next, try pglogical or EPRS • Replication engines supporting Logical Decoding • pglogical
84. Next, try pglogical or EPRS • Replication engines supporting Logical Decoding • pglogical • EnterpriseDB Postgres Replication Server 7 (EPRS7 — Limited Availability)
85. Next, try pglogical or EPRS • Replication engines supporting Logical Decoding • pglogical • EnterpriseDB Postgres Replication Server 7 (EPRS7 — Limited Availability) • Row-level and column-level filters
86. Next, try pglogical or EPRS • Replication engines supporting Logical Decoding • pglogical • EnterpriseDB Postgres Replication Server 7 (EPRS7 — Limited Availability) • Row-level and column-level filters • Still WAL-based
87. Streaming Replication Logical Replication pglogical | EPRS7 X X X
88. Use a Trigger-based Solution
89. Use a Trigger-based Solution
90. Use a Trigger-based Solution • But please upgrade soon!
91. Use a Trigger-based Solution • But please upgrade soon! • Impacts performance
92. Use a Trigger-based Solution • But please upgrade soon! • Impacts performance • Often difficult to administration
93. Use a Trigger-based Solution • But please upgrade soon! • Impacts performance • Often difficult to administration • Logical Replication is the future
94. RDS?
95. RDS? Try Bucardo
96. Multi-Master Replication?
97. Multi-Master Replication? • Holy Grail of replication solutions? • First, ask yourself if you REALLY need it • Consistency • Performance • Conflict Resolution • Often, the other options are good enough ?
98. 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
99. Q & A
100. Thank You! Richard Yen Principal Support Engineer, EnterpriseDB support@enterprisedb.com richyen.com