Demystifying Postgres Logical Replication

Postgres逻辑解码功能已经在9.4版中添加,幸好它由于活跃的开源社区而不断改进。在本次网络研讨会中,我们将介绍它的概念、用法以及在未来版本中将出现的一些新内容。
逻辑解码是BDR实现的特性之一,允许Postgres实例之间的双向数据流。它还允许您将Postgres之外的数据流传输到许多其他数据系统中。

展开查看详情

1. Demystifying Postgres Logical Replication An introduction to the upcoming feature Emanuel Calvo Sr. Technical Services Engineer - Remote DBA Webinar June 15th 1 © 2017 Percona

2.Who’s me? ▪ Known as 3manuek. More info at 3manuek.com ▪ Currently working as a Remote DBA at Percona. ▪ Past positions: PalominoDB, 2ndQuadrant, iMedicare, 8kData, Pythian, Globant. 2 © 2017 Percona

3.The path of the replication in Postgres ▪ Streaming replication incorporated in 9.0. ▪ Cascading streaming replication introduced in 9.2. ▪ Switch timeline added in 9.3. ▪ Logical Decoding added in 9.4. ▪ More support to LD added in 9.6. ▪ Postgres 10 Logical replication natively supported. 3 © 2017 Percona

4.Streaming and logical replication ▪ Streaming replication is a byte-by-byte replication, the whole instance (all databases) are replicated. ▪ Logical replication is supported through pglogical for +9.4 ▪ Natively supported in the next Postgres release (10). 4 © 2017 Percona

5.Replication flow for MySQL DBAs ▪ MySQL • Engine log + Binlog -> byte encoded -> binlog stream -> binlog apply • Cross-engine Events are append to the binlog (unless skipped sql_log_bin) • Slaves filter using do% • Row_format: Replicates the change or the complete statement ▪ Postgres • WAL -> Logical Decoding/output_plugin -> logical log -> sender -> receiver & apply • Filtering is done at publisher • Closer to row based replication 5 © 2017 Percona

6.Feature capabilities ▪ LR replicates data objects based upon their replication identity (generally a primary key). ▪ Destination server is writable. Different indexes and security definition. ▪ Cross-version support ▪ Event-based filtering ▪ Less write amplification than streaming replication ▪ Publications can have several subscriptions 6 © 2017 Percona

7.What can be achieved with LR? ▪ Storage flexibility through replicating smaller sets (even partitioned tables) ▪ Flexible topology ▪ Minimum server load compared with trigger based solutions ▪ Allows parallel streaming across publishers ▪ Migrations and upgrades ▪ Multi source replication for consolidation ▪ Data distribution ▪ Flexible replication chains ▪ Data transformation 7 © 2017 Percona

8.Limitations ▪ Can’t stream over to the same host (subscription will get locked). ▪ Tables must have the same full qualified name between publication and subscription. ▪ Subscriptions can have more columns or different order but the types and column names must match between P/S. ▪ Database superuser is needed for P/S creation. 8 © 2017 Percona

9.Elements ▪ Logical Decoding • Replication Slots • Output plugin ▪ Exported Snapshot ▪ Publication ▪ Subscription 9 © 2017 Percona

10.[Logical] Replication slots ▪ Keep track of the replication. ▪ Each replica stream has one in the origin for tracking consuming changes. ▪ Locations are explicitly in LSN (log sequence number). ▪ catalog_xmin is the transaction number ▪ Slots are placed in the origin. 10 © 2017 Percona

11.Example of [l] replication slots 11 © 2017 Percona

12.Output Plugin ▪ Converts WAL records entries into custom output ▪ Internal plugin name is pgoutput. ▪ For testing Logical Decoding capabilities, test_decoding. 12 © 2017 Percona

13.Exported snapshot ▪ Sharing visibility between transactions by exporting the current snapshot of the transaction. ▪ This is used for the initial COPY. ▪ Can be used to query outside a transaction but sharing its visibility. 13 © 2017 Percona

14.Publication ▪ Publications can have more than one subscriber. ▪ Tables added to the publication must be declared with REPLICA IDENTITY. Otherwise subsequent operations will fail. 14 © 2017 Percona

15.publication_parameter publish (string) 'insert, update, delete' is the default (all events). 15 © 2017 Percona

16.Subscription ▪ Subscriptions receive changes through replication slots. ▪ More than one replication slot may needed for the initial data copy. ▪ The session_replication_role is set to replica in order to avoid triggers on tables to be executed on replica. ▪ DDL of replicated tables must previously exist. ▪ If creating a replication slot, it will use the name of the subscriber, so beware as slots are in the origin (you will need to specify different subscription names across subscribers). ▪ You can have many subscriber to one publication. 16 © 2017 Percona

17.Subscription —cont ▪ You can synchronize tables by using REFRESH option. ALTER SUBSCRIPTION name SET PUBLICATION publication_name [, ...] { REFRESH [ WITH ( refresh_option value [, ... ] ) ] | SKIP REFRESH } ALTER SUBSCRIPTION name REFRESH PUBLICATION [ WITH ( refresh_option value [, ... ] ) ] refresh_option copy_data (boolean) 17 © 2017 Percona

18.subscription_parameter copy_data create_slot enabled slot_name synchronous_commit connect (afecta copy_data, create_slot y enabled) 18 © 2017 Percona

19.Examples 19 © 2017 Percona

20.Basic definition CREATE PUBLICATION P_main_P0 FOR TABLE main_shard0 WITH (publish = 'insert, update'); -- no delete CREATE SUBSCRIPTION S_main_P0 CONNECTION 'port=7777 user=postgres dbname=master' PUBLICATION P_main_P0 WITH (create_slot =true, copy_data =true); NOTE: Slot name will be the subscription name in the publisher 20 © 2017 Percona

21.Adding publication sources and updating subscriptions CREATE PUBLICATION P_queue_test FOR TABLE queue WITH (publish = 'insert, update,delete'); CREATE PUBLICATION P_queue2_test FOR TABLE queue2 WITH (publish = 'insert, update,delete'); CREATE SUBSCRIPTION S_queue_test CONNECTION 'port=8888 user=postgres dbname=percona' PUBLICATION P_queue_test WITH (create_slot =true, copy_data =true); ALTER SUBSCRIPTION S_queue_test SET PUBLICATION P_queue_test, P_queue2_test REFRESH WITH (copy_data = true); ALTER SUBSCRIPTION S_queue_test REFRESH PUBLICATION WITH (copy_data = true); 21 © 2017 Percona

22.Flow 22 © 2017 Percona

23.Conflicts ▪ Any violation in constraints stops replication. ▪ UPDATE and DELETE operations on missing data will be skipped. ▪ Transaction can be omitted using pg_replication_origin_advance(subscriber_name , position). aka sql_skip_counter. ▪ Current position can be seen at pg_replication_origin_status at subscriber. 23 © 2017 Percona

24.Replica Identity • Which identity is used for conflict resolution: REPLICA IDENTITY { DEFAULT | USING INDEX index_name | FULL | NOTHING } 24 © 2017 Percona

25.Monitoring ▪ Publisher: select * from pg_replication_slots; ▪ Subscribers: percona=# select pg_replication_origin_progress(r.r, true) FROM (select roname from pg_replication_origin where roident = 1) r(r); pg_replication_origin_progress | 0/17024E0 postgres=# select * from pg_replication_origin; roident | roname ---------+---------- 1 | pg_16394 percona=# select * from pg_replication_origin_status ; local_id | external_id | remote_lsn | local_lsn ----------+-------------+------------+----------- 1 | pg_16503 | 0/17024E0 | 0/16DEE30 25 © 2017 Percona

26.Monitoring — cont. ▪ Subscribers: select * from pg_stat_subscription where subname = 's_queue';" percona -[ RECORD 1 ]---------+------------------------------ subid | 16418 subname | s_queue pid | 5293 relid | received_lsn | 0/1678E98 last_msg_send_time | 2017-04-25 19:25:15.858439+00 last_msg_receipt_time | 2017-04-25 19:25:15.858475+00 latest_end_lsn | 0/1678E98 latest_end_time | 2017-04-25 19:25:15.858439+00 26 © 2017 Percona

27.Minimum configuration wal_level = logical #minimal, replica, or logical Max_wal_senders = 10 Wal_keep_segments # don’t use it if slots Max_replication_slots =10 max_worker_processes = 8 #Subscribers max_logical_replication_workers = 4 # taken from max_worker_processes max_sync_workers_per_subscription = 2 # taken from max_logical_replication_workers 27 © 2017 Percona

28.Related functions (decoding) • pg_create_logical_replication_slot • pg_drop_replication_slot Consuming (get) /Seeing(peek) changes (will fail with pgoutput, but this works with other logical decoding plugins): • pg_logical_slot_peek_changes • pg_logical_slot_get_changes • pg_logical_slot_get_binary_changes • pg_logical_slot_peek_binary_changes 28 © 2017 Percona

29.Partitions and Logical Replication 29 © 2017 Percona