Demystifying Postgres Logical Replication

下载 0

苏寒

发布于

1669

人观看

#信息技术

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

展开查看详情

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

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

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

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

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

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

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

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

0点赞

0收藏

0下载