logical replication in postgresql · logical replication • target node is writeable – allows...
TRANSCRIPT
![Page 1: Logical Replication in PostgreSQL · Logical Replication • Target node is writeable – Allows temp tables ... Logical Streaming Replication Provider Subscriber WAL Apps Heap WalSender](https://reader034.vdocuments.us/reader034/viewer/2022052320/5f0ce9cf7e708231d437c054/html5/thumbnails/1.jpg)
© 2ndQuadrant 2016
Logical Replication in PostgreSQL
Tallinn 2016
Petr Jelínek
![Page 2: Logical Replication in PostgreSQL · Logical Replication • Target node is writeable – Allows temp tables ... Logical Streaming Replication Provider Subscriber WAL Apps Heap WalSender](https://reader034.vdocuments.us/reader034/viewer/2022052320/5f0ce9cf7e708231d437c054/html5/thumbnails/2.jpg)
© 2ndQuadrant 2016
Whoami
• 2ndQuadrant– PostgreSQL developer and consultant
• PostgreSQL contributor for over a decade– DO, default privileges, TABLESAMPLE, etc
• Pgbouncer co-maintainer
• Contacts– [email protected]
– https://github.com/pjmodos
![Page 3: Logical Replication in PostgreSQL · Logical Replication • Target node is writeable – Allows temp tables ... Logical Streaming Replication Provider Subscriber WAL Apps Heap WalSender](https://reader034.vdocuments.us/reader034/viewer/2022052320/5f0ce9cf7e708231d437c054/html5/thumbnails/3.jpg)
© 2ndQuadrant 2016
Logical Replication
• Target node is writeable– Allows temp tables
– Allows different indexes
– Allows different security
– Allows data transformation
• Selective Replication– Can replicate subset of database
• Cross-version
![Page 4: Logical Replication in PostgreSQL · Logical Replication • Target node is writeable – Allows temp tables ... Logical Streaming Replication Provider Subscriber WAL Apps Heap WalSender](https://reader034.vdocuments.us/reader034/viewer/2022052320/5f0ce9cf7e708231d437c054/html5/thumbnails/4.jpg)
© 2ndQuadrant 2016
History
![Page 5: Logical Replication in PostgreSQL · Logical Replication • Target node is writeable – Allows temp tables ... Logical Streaming Replication Provider Subscriber WAL Apps Heap WalSender](https://reader034.vdocuments.us/reader034/viewer/2022052320/5f0ce9cf7e708231d437c054/html5/thumbnails/5.jpg)
© 2ndQuadrant 2016
Logical Replication History
• Trigger based solutions– Slony (~2004)
– Londiste (~2007)
• Run outside of the PostgreSQL
• Use table(s) as queue– Amplify load on the upstream
– No sync replication
• Complex code to ensure commit order
![Page 6: Logical Replication in PostgreSQL · Logical Replication • Target node is writeable – Allows temp tables ... Logical Streaming Replication Provider Subscriber WAL Apps Heap WalSender](https://reader034.vdocuments.us/reader034/viewer/2022052320/5f0ce9cf7e708231d437c054/html5/thumbnails/6.jpg)
© 2ndQuadrant 2016
Current Development
• BDR– Modified PostgreSQL 9.4 + extension
– 9.6 coming soon (extension only)
– Multi-master
– Transparent DDL
• pglogical– Extension for 9.4+
– Mostly for one way replication
– Replacement for trigger-based solutions
![Page 7: Logical Replication in PostgreSQL · Logical Replication • Target node is writeable – Allows temp tables ... Logical Streaming Replication Provider Subscriber WAL Apps Heap WalSender](https://reader034.vdocuments.us/reader034/viewer/2022052320/5f0ce9cf7e708231d437c054/html5/thumbnails/7.jpg)
© 2ndQuadrant 2016
Streaming Replication
![Page 8: Logical Replication in PostgreSQL · Logical Replication • Target node is writeable – Allows temp tables ... Logical Streaming Replication Provider Subscriber WAL Apps Heap WalSender](https://reader034.vdocuments.us/reader034/viewer/2022052320/5f0ce9cf7e708231d437c054/html5/thumbnails/8.jpg)
© 2ndQuadrant 2016
Physical Streaming Replication
MasterMaster StandbyStandby
WALWAL
Apps
HeapHeap
WalSenderWalSender
ExecutorExecutor
WALWAL HeapHeap
ExecutorExecutor
WalReceiverWalReceiver
![Page 9: Logical Replication in PostgreSQL · Logical Replication • Target node is writeable – Allows temp tables ... Logical Streaming Replication Provider Subscriber WAL Apps Heap WalSender](https://reader034.vdocuments.us/reader034/viewer/2022052320/5f0ce9cf7e708231d437c054/html5/thumbnails/9.jpg)
© 2ndQuadrant 2016
Logical Streaming Replication
ProviderProvider SubscriberSubscriber
WALWAL
Apps
HeapHeap
WalSenderWalSenderOutput pluginOutput plugin
ExecutorExecutor
WALWAL HeapHeap
ExecutorExecutor
ApplyApply
![Page 10: Logical Replication in PostgreSQL · Logical Replication • Target node is writeable – Allows temp tables ... Logical Streaming Replication Provider Subscriber WAL Apps Heap WalSender](https://reader034.vdocuments.us/reader034/viewer/2022052320/5f0ce9cf7e708231d437c054/html5/thumbnails/10.jpg)
© 2ndQuadrant 2016
pglogical
![Page 11: Logical Replication in PostgreSQL · Logical Replication • Target node is writeable – Allows temp tables ... Logical Streaming Replication Provider Subscriber WAL Apps Heap WalSender](https://reader034.vdocuments.us/reader034/viewer/2022052320/5f0ce9cf7e708231d437c054/html5/thumbnails/11.jpg)
© 2ndQuadrant 2016
PGLogical
ProviderProvider SubscriberSubscriber
WALWAL
Apps
HeapHeap
WalSenderWalSenderOutput pluginOutput plugin
ExecutorExecutor
WALWAL HeapHeap
ExecutorExecutor
ApplyApply
![Page 12: Logical Replication in PostgreSQL · Logical Replication • Target node is writeable – Allows temp tables ... Logical Streaming Replication Provider Subscriber WAL Apps Heap WalSender](https://reader034.vdocuments.us/reader034/viewer/2022052320/5f0ce9cf7e708231d437c054/html5/thumbnails/12.jpg)
© 2ndQuadrant 2016
pglogical
• Selective Replication
• Online Upgrade
• Data Transport– Data integration
– Streaming changes to analytical database
– Master configuration data management
– …
• Optionally synchronous apply
![Page 13: Logical Replication in PostgreSQL · Logical Replication • Target node is writeable – Allows temp tables ... Logical Streaming Replication Provider Subscriber WAL Apps Heap WalSender](https://reader034.vdocuments.us/reader034/viewer/2022052320/5f0ce9cf7e708231d437c054/html5/thumbnails/13.jpg)
© 2ndQuadrant 2016
pglogical
• Installs as extension– Runs as part of PostgreSQL instance
– All configuration is inside the database
• Uses logical decoding to read WAL– Minimal overhead on provider
– Transactions are sent in commit order
• Executes triggers marked as ENABLE REPLICA on subscriber
![Page 14: Logical Replication in PostgreSQL · Logical Replication • Target node is writeable – Allows temp tables ... Logical Streaming Replication Provider Subscriber WAL Apps Heap WalSender](https://reader034.vdocuments.us/reader034/viewer/2022052320/5f0ce9cf7e708231d437c054/html5/thumbnails/14.jpg)
© 2ndQuadrant 2016
Installation
• Extension– CREATE EXTENSION pglogical;
• Provider– create_node(‘myprovider’, ‘dbname=foo
host=10.10.1.1’)
• Subscriber– create_node(‘mysubscriber’, ‘dbname=foo
host=10.10.1.2’)
– create_subscription(‘mysubscription’, ‘dbname=foo host=10.10.1.1’)
![Page 15: Logical Replication in PostgreSQL · Logical Replication • Target node is writeable – Allows temp tables ... Logical Streaming Replication Provider Subscriber WAL Apps Heap WalSender](https://reader034.vdocuments.us/reader034/viewer/2022052320/5f0ce9cf7e708231d437c054/html5/thumbnails/15.jpg)
© 2ndQuadrant 2016
Replication Sets
• Replication is defined in terms of groups (sets) of tables, rather than individual tables
– Need to be defined on each provider node
• Table is not replicated until added to a set
• Tables may be defined in more than one set, but changes for the table will only be sent once to each subscription
![Page 16: Logical Replication in PostgreSQL · Logical Replication • Target node is writeable – Allows temp tables ... Logical Streaming Replication Provider Subscriber WAL Apps Heap WalSender](https://reader034.vdocuments.us/reader034/viewer/2022052320/5f0ce9cf7e708231d437c054/html5/thumbnails/16.jpg)
© 2ndQuadrant 2016
Replication Sets
• By default new replication sets replicate all actions
– INSERT, UPDATE, DELETE, TRUNCATE
• It's possible to filter actions for given replication set
• Useful for data aggregation, data warehousing etc.
• Predefined sets, “default”, “default_insert_only”, “ddl_sql”
![Page 17: Logical Replication in PostgreSQL · Logical Replication • Target node is writeable – Allows temp tables ... Logical Streaming Replication Provider Subscriber WAL Apps Heap WalSender](https://reader034.vdocuments.us/reader034/viewer/2022052320/5f0ce9cf7e708231d437c054/html5/thumbnails/17.jpg)
© 2ndQuadrant 2016
Table replication
• Add table to replication set– pglogical.replication_set_add_table(
set_name := ‘default’, relation := ‘public.users’, synchronize_data := true);
• Full data resynchronization possible at later time
– pglogical.alter_subscription_resynchronize_table
• Structure cannot be synchronized automatically yet
![Page 18: Logical Replication in PostgreSQL · Logical Replication • Target node is writeable – Allows temp tables ... Logical Streaming Replication Provider Subscriber WAL Apps Heap WalSender](https://reader034.vdocuments.us/reader034/viewer/2022052320/5f0ce9cf7e708231d437c054/html5/thumbnails/18.jpg)
© 2ndQuadrant 2016
Sequences
• Replicated using replication sets just like tables
– pglogical.replication_set_add_sequence
• Replicated periodically in bulk
• Dynamic buffering of last value– Subscriber is in front of the provider
– This is similar to how Londiste replicates sequences
![Page 19: Logical Replication in PostgreSQL · Logical Replication • Target node is writeable – Allows temp tables ... Logical Streaming Replication Provider Subscriber WAL Apps Heap WalSender](https://reader034.vdocuments.us/reader034/viewer/2022052320/5f0ce9cf7e708231d437c054/html5/thumbnails/19.jpg)
© 2ndQuadrant 2016
DDL Replication
• Initial schema either fully synchronized or not at all
• The DDL commands are not automatically replicated yet
• pglogical.replicate_ddl_command( command [, replication_sets])
– replication_sets defaults to “ddl_sql”
![Page 20: Logical Replication in PostgreSQL · Logical Replication • Target node is writeable – Allows temp tables ... Logical Streaming Replication Provider Subscriber WAL Apps Heap WalSender](https://reader034.vdocuments.us/reader034/viewer/2022052320/5f0ce9cf7e708231d437c054/html5/thumbnails/20.jpg)
© 2ndQuadrant 2016
Txn2Txn2
Example setup
Txn1Txn1
AnalyticsAnalytics
ConfigConfig
![Page 21: Logical Replication in PostgreSQL · Logical Replication • Target node is writeable – Allows temp tables ... Logical Streaming Replication Provider Subscriber WAL Apps Heap WalSender](https://reader034.vdocuments.us/reader034/viewer/2022052320/5f0ce9cf7e708231d437c054/html5/thumbnails/21.jpg)
© 2ndQuadrant 2016
Performance (pgbench)
1 2 4 8 16 24 320
2000
4000
6000
8000
10000
12000
14000
pglocicalslonylondiste3SR
![Page 22: Logical Replication in PostgreSQL · Logical Replication • Target node is writeable – Allows temp tables ... Logical Streaming Replication Provider Subscriber WAL Apps Heap WalSender](https://reader034.vdocuments.us/reader034/viewer/2022052320/5f0ce9cf7e708231d437c054/html5/thumbnails/22.jpg)
© 2ndQuadrant 2016
Caveats
• Big transactions may cause replication to lag– This is common problem for transactional
replication systems
• Does not play well with physical replication yet– Failover
• Currently requires superuser
![Page 23: Logical Replication in PostgreSQL · Logical Replication • Target node is writeable – Allows temp tables ... Logical Streaming Replication Provider Subscriber WAL Apps Heap WalSender](https://reader034.vdocuments.us/reader034/viewer/2022052320/5f0ce9cf7e708231d437c054/html5/thumbnails/23.jpg)
© 2ndQuadrant 2016
Future
![Page 24: Logical Replication in PostgreSQL · Logical Replication • Target node is writeable – Allows temp tables ... Logical Streaming Replication Provider Subscriber WAL Apps Heap WalSender](https://reader034.vdocuments.us/reader034/viewer/2022052320/5f0ce9cf7e708231d437c054/html5/thumbnails/24.jpg)
© 2ndQuadrant 2016
pglogical 2.0
![Page 25: Logical Replication in PostgreSQL · Logical Replication • Target node is writeable – Allows temp tables ... Logical Streaming Replication Provider Subscriber WAL Apps Heap WalSender](https://reader034.vdocuments.us/reader034/viewer/2022052320/5f0ce9cf7e708231d437c054/html5/thumbnails/25.jpg)
© 2ndQuadrant 2016
Column Filtering
• Add table to replication set– pglogical.replication_set_add_table(
set_name := ‘default’, relation := ‘public.users’, columns := ‘{id,name,...}’);
• Array of replicated columns
• REPLICA IDENTITY columns required
• The table on subscriber does not need the extra columns
![Page 26: Logical Replication in PostgreSQL · Logical Replication • Target node is writeable – Allows temp tables ... Logical Streaming Replication Provider Subscriber WAL Apps Heap WalSender](https://reader034.vdocuments.us/reader034/viewer/2022052320/5f0ce9cf7e708231d437c054/html5/thumbnails/26.jpg)
© 2ndQuadrant 2016
Row based Filtering
• Add table to replication set– pglogical.replication_set_add_table(
set_name := ‘default’, relation := ‘public.users’, row_filter := ‘expression’);
• Standard SQL expression
• Same limitations as CHECK CONSTRAINT
• Executed during replication– Session variables of the replication connection
![Page 27: Logical Replication in PostgreSQL · Logical Replication • Target node is writeable – Allows temp tables ... Logical Streaming Replication Provider Subscriber WAL Apps Heap WalSender](https://reader034.vdocuments.us/reader034/viewer/2022052320/5f0ce9cf7e708231d437c054/html5/thumbnails/27.jpg)
© 2ndQuadrant 2016
PostgreSQL 10
![Page 28: Logical Replication in PostgreSQL · Logical Replication • Target node is writeable – Allows temp tables ... Logical Streaming Replication Provider Subscriber WAL Apps Heap WalSender](https://reader034.vdocuments.us/reader034/viewer/2022052320/5f0ce9cf7e708231d437c054/html5/thumbnails/28.jpg)
© 2ndQuadrant 2016
Thanks!
• https://2ndquadrant.com/en/pglogical/
• https://github.com/2ndQuadrant/pglogical