in 2018 postgresql replication - pgconf asia · 2018-12-19 · logical replication create table...
TRANSCRIPT
![Page 1: in 2018 PostgreSQL Replication - PGConf ASIA · 2018-12-19 · Logical replication CREATE TABLE testtable (a int PRIMARY KEY, b text); CREATE SUBSCRIPTION testsub ... pglogical Se](https://reader033.vdocuments.us/reader033/viewer/2022052612/5f0ce9d07e708231d437c059/html5/thumbnails/1.jpg)
PostgreSQL ReplicationPostgreSQL Replicationin 2018in 2018PGConf.ASIA 2018
Tokyo, Japan
Magnus Hagander [email protected]
![Page 2: in 2018 PostgreSQL Replication - PGConf ASIA · 2018-12-19 · Logical replication CREATE TABLE testtable (a int PRIMARY KEY, b text); CREATE SUBSCRIPTION testsub ... pglogical Se](https://reader033.vdocuments.us/reader033/viewer/2022052612/5f0ce9d07e708231d437c059/html5/thumbnails/2.jpg)
Magnus HaganderMagnus HaganderRedpill Linpro
Infrastructure servicesPrincipal database consultant
PostgreSQLCore Team memberCommitterPostgreSQL Europe
![Page 3: in 2018 PostgreSQL Replication - PGConf ASIA · 2018-12-19 · Logical replication CREATE TABLE testtable (a int PRIMARY KEY, b text); CREATE SUBSCRIPTION testsub ... pglogical Se](https://reader033.vdocuments.us/reader033/viewer/2022052612/5f0ce9d07e708231d437c059/html5/thumbnails/3.jpg)
ReplicationReplication
![Page 4: in 2018 PostgreSQL Replication - PGConf ASIA · 2018-12-19 · Logical replication CREATE TABLE testtable (a int PRIMARY KEY, b text); CREATE SUBSCRIPTION testsub ... pglogical Se](https://reader033.vdocuments.us/reader033/viewer/2022052612/5f0ce9d07e708231d437c059/html5/thumbnails/4.jpg)
"PostgreSQL doesn't have"PostgreSQL doesn't havereplication"replication"
![Page 5: in 2018 PostgreSQL Replication - PGConf ASIA · 2018-12-19 · Logical replication CREATE TABLE testtable (a int PRIMARY KEY, b text); CREATE SUBSCRIPTION testsub ... pglogical Se](https://reader033.vdocuments.us/reader033/viewer/2022052612/5f0ce9d07e708231d437c059/html5/thumbnails/5.jpg)
"PostgreSQL doesn't have"PostgreSQL doesn't havereplication"replication"
"So we have to use MySQL"
![Page 6: in 2018 PostgreSQL Replication - PGConf ASIA · 2018-12-19 · Logical replication CREATE TABLE testtable (a int PRIMARY KEY, b text); CREATE SUBSCRIPTION testsub ... pglogical Se](https://reader033.vdocuments.us/reader033/viewer/2022052612/5f0ce9d07e708231d437c059/html5/thumbnails/6.jpg)
ReplicationReplicationWasn't true back thenEven less true now!Now there are too many choices?
But you have to pick oneAnd can be hard to use
![Page 7: in 2018 PostgreSQL Replication - PGConf ASIA · 2018-12-19 · Logical replication CREATE TABLE testtable (a int PRIMARY KEY, b text); CREATE SUBSCRIPTION testsub ... pglogical Se](https://reader033.vdocuments.us/reader033/viewer/2022052612/5f0ce9d07e708231d437c059/html5/thumbnails/7.jpg)
ReplicationReplicationCan be done at different layersFrom hardware
(ish)To application
![Page 8: in 2018 PostgreSQL Replication - PGConf ASIA · 2018-12-19 · Logical replication CREATE TABLE testtable (a int PRIMARY KEY, b text); CREATE SUBSCRIPTION testsub ... pglogical Se](https://reader033.vdocuments.us/reader033/viewer/2022052612/5f0ce9d07e708231d437c059/html5/thumbnails/8.jpg)
Replication layersReplication layers↓ Application↓ App-in-database↓ Database logical↓ Database physical↓ Operating system↓ Hardware
![Page 9: in 2018 PostgreSQL Replication - PGConf ASIA · 2018-12-19 · Logical replication CREATE TABLE testtable (a int PRIMARY KEY, b text); CREATE SUBSCRIPTION testsub ... pglogical Se](https://reader033.vdocuments.us/reader033/viewer/2022052612/5f0ce9d07e708231d437c059/html5/thumbnails/9.jpg)
Start from the bottomStart from the bottom
![Page 10: in 2018 PostgreSQL Replication - PGConf ASIA · 2018-12-19 · Logical replication CREATE TABLE testtable (a int PRIMARY KEY, b text); CREATE SUBSCRIPTION testsub ... pglogical Se](https://reader033.vdocuments.us/reader033/viewer/2022052612/5f0ce9d07e708231d437c059/html5/thumbnails/10.jpg)
SAN replicationSAN replicationHardware takes care of replicationBlock levelTransparent to OS
And to PostgreSQLCommon enterprise solution
Especially with VMs
![Page 11: in 2018 PostgreSQL Replication - PGConf ASIA · 2018-12-19 · Logical replication CREATE TABLE testtable (a int PRIMARY KEY, b text); CREATE SUBSCRIPTION testsub ... pglogical Se](https://reader033.vdocuments.us/reader033/viewer/2022052612/5f0ce9d07e708231d437c059/html5/thumbnails/11.jpg)
SAN replicationSAN replicationFrom single rackTo multi-siteSynchronousGuaranteed to never fail
Riiiiight...
![Page 12: in 2018 PostgreSQL Replication - PGConf ASIA · 2018-12-19 · Logical replication CREATE TABLE testtable (a int PRIMARY KEY, b text); CREATE SUBSCRIPTION testsub ... pglogical Se](https://reader033.vdocuments.us/reader033/viewer/2022052612/5f0ce9d07e708231d437c059/html5/thumbnails/12.jpg)
Replication layersReplication layers↓ Application↓ App-in-database↓ Database logical↓ Database physical↓ Operating system↓ Hardware
![Page 13: in 2018 PostgreSQL Replication - PGConf ASIA · 2018-12-19 · Logical replication CREATE TABLE testtable (a int PRIMARY KEY, b text); CREATE SUBSCRIPTION testsub ... pglogical Se](https://reader033.vdocuments.us/reader033/viewer/2022052612/5f0ce9d07e708231d437c059/html5/thumbnails/13.jpg)
DRBDDRBDSimilar in style to SANImplementation in OS driverPerformance?
![Page 14: in 2018 PostgreSQL Replication - PGConf ASIA · 2018-12-19 · Logical replication CREATE TABLE testtable (a int PRIMARY KEY, b text); CREATE SUBSCRIPTION testsub ... pglogical Se](https://reader033.vdocuments.us/reader033/viewer/2022052612/5f0ce9d07e708231d437c059/html5/thumbnails/14.jpg)
Replication layersReplication layers↓ Application↓ App-in-database↓ Database logical↓ Database physical↓ Operating system↓ Hardware
![Page 15: in 2018 PostgreSQL Replication - PGConf ASIA · 2018-12-19 · Logical replication CREATE TABLE testtable (a int PRIMARY KEY, b text); CREATE SUBSCRIPTION testsub ... pglogical Se](https://reader033.vdocuments.us/reader033/viewer/2022052612/5f0ce9d07e708231d437c059/html5/thumbnails/15.jpg)
Database physicalDatabase physicalWAL based replicationFile based from 8.3Streaming since 9.0Synchronous since 9.1
Transaction level mixingQuorum commit since 10(etc)
![Page 16: in 2018 PostgreSQL Replication - PGConf ASIA · 2018-12-19 · Logical replication CREATE TABLE testtable (a int PRIMARY KEY, b text); CREATE SUBSCRIPTION testsub ... pglogical Se](https://reader033.vdocuments.us/reader033/viewer/2022052612/5f0ce9d07e708231d437c059/html5/thumbnails/16.jpg)
wal_level = 'replica'wal_level = 'replica'
![Page 17: in 2018 PostgreSQL Replication - PGConf ASIA · 2018-12-19 · Logical replication CREATE TABLE testtable (a int PRIMARY KEY, b text); CREATE SUBSCRIPTION testsub ... pglogical Se](https://reader033.vdocuments.us/reader033/viewer/2022052612/5f0ce9d07e708231d437c059/html5/thumbnails/17.jpg)
Synchronous modeSynchronous modeofflocalremote_writeonremote_apply
![Page 18: in 2018 PostgreSQL Replication - PGConf ASIA · 2018-12-19 · Logical replication CREATE TABLE testtable (a int PRIMARY KEY, b text); CREATE SUBSCRIPTION testsub ... pglogical Se](https://reader033.vdocuments.us/reader033/viewer/2022052612/5f0ce9d07e708231d437c059/html5/thumbnails/18.jpg)
Synchronous modeSynchronous modeSingle (9.1)
First (9.6)
Quorum (10)
synchronous_standby_names = s1,s2,s3
synchronous_standby_names = 2(s1,s2,s3)
synchronous_standby_names = ANY 2(s1,s2,s3)
![Page 19: in 2018 PostgreSQL Replication - PGConf ASIA · 2018-12-19 · Logical replication CREATE TABLE testtable (a int PRIMARY KEY, b text); CREATE SUBSCRIPTION testsub ... pglogical Se](https://reader033.vdocuments.us/reader033/viewer/2022052612/5f0ce9d07e708231d437c059/html5/thumbnails/19.jpg)
Streaming replicationStreaming replicationPrimary choice todayEasy to set upHard to get wrongEfficientBuilt-in
![Page 20: in 2018 PostgreSQL Replication - PGConf ASIA · 2018-12-19 · Logical replication CREATE TABLE testtable (a int PRIMARY KEY, b text); CREATE SUBSCRIPTION testsub ... pglogical Se](https://reader033.vdocuments.us/reader033/viewer/2022052612/5f0ce9d07e708231d437c059/html5/thumbnails/20.jpg)
Streaming replicationStreaming replication$ pg_basebackup -D /var/lib/pgsql \ -h primary -U replica \ -R -S replica1 -P $ sudo service postgresql-9.6 start
for pre-10, add -X stream
![Page 21: in 2018 PostgreSQL Replication - PGConf ASIA · 2018-12-19 · Logical replication CREATE TABLE testtable (a int PRIMARY KEY, b text); CREATE SUBSCRIPTION testsub ... pglogical Se](https://reader033.vdocuments.us/reader033/viewer/2022052612/5f0ce9d07e708231d437c059/html5/thumbnails/21.jpg)
Streaming replicationStreaming replicationArchitecture/compile flag dependentWhole cluster onlyStandby completely read-onlyPrimary → standby onlyExcellent for availability
![Page 22: in 2018 PostgreSQL Replication - PGConf ASIA · 2018-12-19 · Logical replication CREATE TABLE testtable (a int PRIMARY KEY, b text); CREATE SUBSCRIPTION testsub ... pglogical Se](https://reader033.vdocuments.us/reader033/viewer/2022052612/5f0ce9d07e708231d437c059/html5/thumbnails/22.jpg)
Streaming replicationStreaming replicationNo built-in cluster management
Manual or automaticProvides infrastructure
No fail-back(no easy one)
Easy to get started, harder to maintain
![Page 23: in 2018 PostgreSQL Replication - PGConf ASIA · 2018-12-19 · Logical replication CREATE TABLE testtable (a int PRIMARY KEY, b text); CREATE SUBSCRIPTION testsub ... pglogical Se](https://reader033.vdocuments.us/reader033/viewer/2022052612/5f0ce9d07e708231d437c059/html5/thumbnails/23.jpg)
Cluster managementCluster managementPatroniPatroni
Designed for automatic managementIncluding automatic failoverUses etcd, zookeeper, or consulIntegrates with haproxy
![Page 24: in 2018 PostgreSQL Replication - PGConf ASIA · 2018-12-19 · Logical replication CREATE TABLE testtable (a int PRIMARY KEY, b text); CREATE SUBSCRIPTION testsub ... pglogical Se](https://reader033.vdocuments.us/reader033/viewer/2022052612/5f0ce9d07e708231d437c059/html5/thumbnails/24.jpg)
Cluster managementCluster managementrepmgrrepmgr
Fewer pre-requisitesEasier for manual management
Comes with repmgrd that does automaticDoes not handle connection management
Use e.g. rebouncerOr haproxy
![Page 25: in 2018 PostgreSQL Replication - PGConf ASIA · 2018-12-19 · Logical replication CREATE TABLE testtable (a int PRIMARY KEY, b text); CREATE SUBSCRIPTION testsub ... pglogical Se](https://reader033.vdocuments.us/reader033/viewer/2022052612/5f0ce9d07e708231d437c059/html5/thumbnails/25.jpg)
Cluster managementCluster managementPAFPAF
Integrates with pacemaker/corosyncManagement of other servicesManages virtual IP
![Page 26: in 2018 PostgreSQL Replication - PGConf ASIA · 2018-12-19 · Logical replication CREATE TABLE testtable (a int PRIMARY KEY, b text); CREATE SUBSCRIPTION testsub ... pglogical Se](https://reader033.vdocuments.us/reader033/viewer/2022052612/5f0ce9d07e708231d437c059/html5/thumbnails/26.jpg)
Replication layersReplication layers↓ Application↓ App-in-database↓ Database logical↓ Database physical↓ Operating system↓ Hardware
![Page 27: in 2018 PostgreSQL Replication - PGConf ASIA · 2018-12-19 · Logical replication CREATE TABLE testtable (a int PRIMARY KEY, b text); CREATE SUBSCRIPTION testsub ... pglogical Se](https://reader033.vdocuments.us/reader033/viewer/2022052612/5f0ce9d07e708231d437c059/html5/thumbnails/27.jpg)
Database logicalDatabase logicalLogical decoding since 9.4Logical replication since 10
Built-in, that isPiggy-backs on WALVery low overhead
![Page 28: in 2018 PostgreSQL Replication - PGConf ASIA · 2018-12-19 · Logical replication CREATE TABLE testtable (a int PRIMARY KEY, b text); CREATE SUBSCRIPTION testsub ... pglogical Se](https://reader033.vdocuments.us/reader033/viewer/2022052612/5f0ce9d07e708231d437c059/html5/thumbnails/28.jpg)
wal_level = 'logical'wal_level = 'logical'
![Page 29: in 2018 PostgreSQL Replication - PGConf ASIA · 2018-12-19 · Logical replication CREATE TABLE testtable (a int PRIMARY KEY, b text); CREATE SUBSCRIPTION testsub ... pglogical Se](https://reader033.vdocuments.us/reader033/viewer/2022052612/5f0ce9d07e708231d437c059/html5/thumbnails/29.jpg)
Logical replicationLogical replicationReconstructs changes by rowReplicates row content
not SQL statementsFully transactional
![Page 30: in 2018 PostgreSQL Replication - PGConf ASIA · 2018-12-19 · Logical replication CREATE TABLE testtable (a int PRIMARY KEY, b text); CREATE SUBSCRIPTION testsub ... pglogical Se](https://reader033.vdocuments.us/reader033/viewer/2022052612/5f0ce9d07e708231d437c059/html5/thumbnails/30.jpg)
Logical replicationLogical replicationTable-level partial replicationTable-level bi-directional replication
![Page 31: in 2018 PostgreSQL Replication - PGConf ASIA · 2018-12-19 · Logical replication CREATE TABLE testtable (a int PRIMARY KEY, b text); CREATE SUBSCRIPTION testsub ... pglogical Se](https://reader033.vdocuments.us/reader033/viewer/2022052612/5f0ce9d07e708231d437c059/html5/thumbnails/31.jpg)
Logical replicationLogical replicationCREATE TABLE testtable (a int PRIMARY KEY, b text); CREATE PUBLICATION testpub FOR TABLE testtable;
![Page 32: in 2018 PostgreSQL Replication - PGConf ASIA · 2018-12-19 · Logical replication CREATE TABLE testtable (a int PRIMARY KEY, b text); CREATE SUBSCRIPTION testsub ... pglogical Se](https://reader033.vdocuments.us/reader033/viewer/2022052612/5f0ce9d07e708231d437c059/html5/thumbnails/32.jpg)
Logical replicationLogical replicationCREATE TABLE testtable (a int PRIMARY KEY, b text); CREATE SUBSCRIPTION testsub CONNECTION 'host=/tmp port=5500 dbname=postgres user=mha' PUBLICATION testpub;
![Page 33: in 2018 PostgreSQL Replication - PGConf ASIA · 2018-12-19 · Logical replication CREATE TABLE testtable (a int PRIMARY KEY, b text); CREATE SUBSCRIPTION testsub ... pglogical Se](https://reader033.vdocuments.us/reader033/viewer/2022052612/5f0ce9d07e708231d437c059/html5/thumbnails/33.jpg)
Logical replicationLogical replicationData replication only
No schemaNo sequences
Suitable for data distributionBut not for HALacks failover slots!
![Page 34: in 2018 PostgreSQL Replication - PGConf ASIA · 2018-12-19 · Logical replication CREATE TABLE testtable (a int PRIMARY KEY, b text); CREATE SUBSCRIPTION testsub ... pglogical Se](https://reader033.vdocuments.us/reader033/viewer/2022052612/5f0ce9d07e708231d437c059/html5/thumbnails/34.jpg)
pglogicalpglogicalExternal version of logical replicationMerged piece by pieceMore capabilities!Not as deeply integrated
![Page 35: in 2018 PostgreSQL Replication - PGConf ASIA · 2018-12-19 · Logical replication CREATE TABLE testtable (a int PRIMARY KEY, b text); CREATE SUBSCRIPTION testsub ... pglogical Se](https://reader033.vdocuments.us/reader033/viewer/2022052612/5f0ce9d07e708231d437c059/html5/thumbnails/35.jpg)
pglogicalpglogicalSequence replicationRow based filteringColumn based filteringMerging and conflict resolution...
![Page 36: in 2018 PostgreSQL Replication - PGConf ASIA · 2018-12-19 · Logical replication CREATE TABLE testtable (a int PRIMARY KEY, b text); CREATE SUBSCRIPTION testsub ... pglogical Se](https://reader033.vdocuments.us/reader033/viewer/2022052612/5f0ce9d07e708231d437c059/html5/thumbnails/36.jpg)
pglogicalpglogicalSupports PostgreSQL 9.4Zero (or close to zero) downtime upgrades!
![Page 37: in 2018 PostgreSQL Replication - PGConf ASIA · 2018-12-19 · Logical replication CREATE TABLE testtable (a int PRIMARY KEY, b text); CREATE SUBSCRIPTION testsub ... pglogical Se](https://reader033.vdocuments.us/reader033/viewer/2022052612/5f0ce9d07e708231d437c059/html5/thumbnails/37.jpg)
Replication layersReplication layers↓ Application↓ App-in-database↓ Database logical↓ Database physical↓ Operating system↓ Hardware
![Page 38: in 2018 PostgreSQL Replication - PGConf ASIA · 2018-12-19 · Logical replication CREATE TABLE testtable (a int PRIMARY KEY, b text); CREATE SUBSCRIPTION testsub ... pglogical Se](https://reader033.vdocuments.us/reader033/viewer/2022052612/5f0ce9d07e708231d437c059/html5/thumbnails/38.jpg)
App-in-databaseApp-in-databaseTrigger based systems
SlonyBucardoLondiste...
![Page 39: in 2018 PostgreSQL Replication - PGConf ASIA · 2018-12-19 · Logical replication CREATE TABLE testtable (a int PRIMARY KEY, b text); CREATE SUBSCRIPTION testsub ... pglogical Se](https://reader033.vdocuments.us/reader033/viewer/2022052612/5f0ce9d07e708231d437c059/html5/thumbnails/39.jpg)
Trigger basedTrigger basedFor a long time the only choiceNow mostly supersededMuch higher overhead than logicalComplex scenarios
![Page 40: in 2018 PostgreSQL Replication - PGConf ASIA · 2018-12-19 · Logical replication CREATE TABLE testtable (a int PRIMARY KEY, b text); CREATE SUBSCRIPTION testsub ... pglogical Se](https://reader033.vdocuments.us/reader033/viewer/2022052612/5f0ce9d07e708231d437c059/html5/thumbnails/40.jpg)
Multimaster?Multimaster?I I needneed it! it!
Do you really need it?Do you really know what it means?No transparent options
Because...
![Page 41: in 2018 PostgreSQL Replication - PGConf ASIA · 2018-12-19 · Logical replication CREATE TABLE testtable (a int PRIMARY KEY, b text); CREATE SUBSCRIPTION testsub ... pglogical Se](https://reader033.vdocuments.us/reader033/viewer/2022052612/5f0ce9d07e708231d437c059/html5/thumbnails/41.jpg)
Multimaster?Multimaster?OptionsOptions
BDRFork!
BucardoOr just don't?
![Page 42: in 2018 PostgreSQL Replication - PGConf ASIA · 2018-12-19 · Logical replication CREATE TABLE testtable (a int PRIMARY KEY, b text); CREATE SUBSCRIPTION testsub ... pglogical Se](https://reader033.vdocuments.us/reader033/viewer/2022052612/5f0ce9d07e708231d437c059/html5/thumbnails/42.jpg)
Replication layersReplication layers↓ Application↓ App-in-database↓ Database logical↓ Database physical↓ Operating system↓ Hardware
![Page 43: in 2018 PostgreSQL Replication - PGConf ASIA · 2018-12-19 · Logical replication CREATE TABLE testtable (a int PRIMARY KEY, b text); CREATE SUBSCRIPTION testsub ... pglogical Se](https://reader033.vdocuments.us/reader033/viewer/2022052612/5f0ce9d07e708231d437c059/html5/thumbnails/43.jpg)
ApplicationApplicationReplication done entirely in applicationVery difficult for transactionalUseful in limited cases
![Page 44: in 2018 PostgreSQL Replication - PGConf ASIA · 2018-12-19 · Logical replication CREATE TABLE testtable (a int PRIMARY KEY, b text); CREATE SUBSCRIPTION testsub ... pglogical Se](https://reader033.vdocuments.us/reader033/viewer/2022052612/5f0ce9d07e708231d437c059/html5/thumbnails/44.jpg)
SummarySummary
![Page 45: in 2018 PostgreSQL Replication - PGConf ASIA · 2018-12-19 · Logical replication CREATE TABLE testtable (a int PRIMARY KEY, b text); CREATE SUBSCRIPTION testsub ... pglogical Se](https://reader033.vdocuments.us/reader033/viewer/2022052612/5f0ce9d07e708231d437c059/html5/thumbnails/45.jpg)
High AvailabilityHigh AvailabilityUse streaming replicationMix of sync and asyncConsider patroni or repmgr
![Page 46: in 2018 PostgreSQL Replication - PGConf ASIA · 2018-12-19 · Logical replication CREATE TABLE testtable (a int PRIMARY KEY, b text); CREATE SUBSCRIPTION testsub ... pglogical Se](https://reader033.vdocuments.us/reader033/viewer/2022052612/5f0ce9d07e708231d437c059/html5/thumbnails/46.jpg)
Read query offloadingRead query offloadingUse streaming replication(see previous slide)
![Page 47: in 2018 PostgreSQL Replication - PGConf ASIA · 2018-12-19 · Logical replication CREATE TABLE testtable (a int PRIMARY KEY, b text); CREATE SUBSCRIPTION testsub ... pglogical Se](https://reader033.vdocuments.us/reader033/viewer/2022052612/5f0ce9d07e708231d437c059/html5/thumbnails/47.jpg)
Data distributionData distributionLogical replication in 10pglogical in 9.4+
Or in 10 if built-in is not enoughUpgrade away from your Slony...
![Page 48: in 2018 PostgreSQL Replication - PGConf ASIA · 2018-12-19 · Logical replication CREATE TABLE testtable (a int PRIMARY KEY, b text); CREATE SUBSCRIPTION testsub ... pglogical Se](https://reader033.vdocuments.us/reader033/viewer/2022052612/5f0ce9d07e708231d437c059/html5/thumbnails/48.jpg)
Need both?Need both?Use both!
![Page 49: in 2018 PostgreSQL Replication - PGConf ASIA · 2018-12-19 · Logical replication CREATE TABLE testtable (a int PRIMARY KEY, b text); CREATE SUBSCRIPTION testsub ... pglogical Se](https://reader033.vdocuments.us/reader033/viewer/2022052612/5f0ce9d07e708231d437c059/html5/thumbnails/49.jpg)
Thank you!Thank you!Magnus Hagander
[email protected] @magnushagander
https://www.hagander.net/talks/
This material is licensed