Logical Replication in PostgreSQL 10

Slide 1

Slide 1 text

Logical Replication in PostgreSQL 10 Peter Eisentraut [email protected] @petereisentraut

Slide 2

Slide 2 text

Uses partial replication upgrades anything where physical replication is insufﬁcient

Slide 3

Slide 3 text

What about BDR pglogical

Slide 4

Slide 4 text

source database publication target database subscription tbl1 tbl2 tbl2 tbl3 tbl1 tbl4

Slide 5

Slide 5 text

Tables, columns, types mapped by name different column orders possible data currently in text format table changes are allowed

Slide 6

Slide 6 text

Publications CREATE PUBLICATION mypub1 FOR TABLE tbl1, tbl2; CREATE PUBLICATION mypub2 FOR ALL TABLES; CREATE PUBLICATION mypub3 FOR TABLE tbl3 WITH (publish = 'insert,update');

Slide 7

Slide 7 text

Publications ALTER PUBLICATION mypub1 ADD TABLE tbl4, tbl5; ALTER PUBLICATION mypub1 SET TABLE tbl4, tbl5; ALTER PUBLICATION mypub1 DROP TABLE tbl4, tbl5;

Slide 8

Slide 8 text

Publications DROP PUBLICATION mypub1; DROP PUBLICATION mypub2, mypub3;

Slide 9

Slide 9 text

Subscriptions CREATE SUBSCRIPTION mysub1 CONNECTION 'host=elsewhere ' 'dbname=mydb ' 'user=repuser' PUBLICATION mypub1, mypub2;

Slide 10

Slide 10 text

Subscriptions CREATE SUBSCRIPTION mysub1 CONNECTION '...' PUBLICATION ... WITH (enabled = false, create_slot = false, slot_name = 'myslot', copy_data = false, ...);

Slide 11

Slide 11 text

Subscriptions ALTER SUBSCRIPTION mysub1 ENABLE; ALTER SUBSCRIPTION mysub1 DISABLE; ALTER SUBSCRIPTION mysub1 CONNECTION 'host=newhost ...';

Slide 12

Slide 12 text

Subscriptions ALTER SUBSCRIPTION mysub1 SET (slot_name = 'newslot', ...); ALTER SUBSCRIPTION mysub1 SET (slot_name = NONE);

Slide 13

Slide 13 text

Subscriptions ALTER SUBSCRIPTION mysub1 SET PUBLICATION mypub3 REFRESH; ALTER SUBSCRIPTION mysub1 REFRESH PUBLICATION;

Slide 14

Slide 14 text

Subscriptions DROP SUBSCRIPTION mysub1;

Slide 15

Slide 15 text

Conﬁguration settings Publisher: wal_level = logical (max_wal_senders) (max_replication_slots) for remote: listen_addresses + pg_hba.conf

Slide 16

Slide 16 text

Conﬁguration settings Subscriber: (max_replication_slots) (max_worker_processes) (max_logical_replication_workers) (max_sync_workers_per_subscription)

Slide 17

Slide 17 text

Security publication: GRANT CREATE ON DATABASE + table owner subscription: local superuser + remote replication user hba: host dbname repuser 0.0.0.0/0 scram-sha-256

Slide 18

Slide 18 text

Monitoring pg_stat_replication pg_replication_slots pg_stat_subscription pg_stat_activity

Slide 19

Slide 19 text

Other features synchronous replication ✔ cascading ✔ triggers ✔ constraints ✔ writing to subscribed tables ✔

Slide 20

Slide 20 text

Missing features does not replicate schema/DDL does not replicate sequences does not replicate TRUNCATE only supports replicating base table to base table

Slide 21

Slide 21 text

Usability issues more moving parts than physical replication replica identity handling issues with long-running transactions pg_dump behavior no failback

Slide 22

Slide 22 text

Future ﬁx missing features multimaster better integration with physical replication more granular security transforming, ﬁltering, ...

Slide 23

Slide 23 text

Credits Petr Jelinek Andres Freund Erik Rijkers Craig Ringer Masahiko Sawada