PostgreSQL10.0逻辑复制原理与最佳实践

1、PostgreSQL 10.0 逻辑复制原理与最佳实践本文章来自于阿里云云栖社区摘要： 标签 PostgreSQL , logical replication , 逻辑复制 , 最佳实践 背景 PostgreSQL 从2010年发布的9.0开始支持流式物理复制，备库可以作为只读库打开，提供给用户使用。标签PostgreSQL , logical replication , 逻辑复制 , 最佳实践背景PostgreSQL 从2010年发布的9.0开始支持流式物理复制，备库可以作为只读库打开，提供给用户使用。物理复制的好处1. 物理层面完全一致，这是许多商业数据库的惯用手段。例如Orac

2、le的DG。2. 延迟低，事务执行过程中产生REDO record，实时的在备库apply，事务结束时，备库立马能见到数据。不论事务多大，都一样。3. 物理复制的一致性、可靠性达到了金融级的需求，不必担心数据逻辑层面不一致。但是物理复制要求主备块级完全一致，所以有一些无法覆盖的应用场景，例如备库不仅要只读，还要可写。又比如备库不需要完全和主库一致，只需要复制部分数据，或者备库要从多个数据源复制数据，等等。物理复制无法覆盖的场景1. 数据库实例的部分，例如单个数据库或者某些表的复制需求。例如某个游戏业务，账号体系是一套数据库，如果全国各地有多个接入点，全部都连到中心数据库进行认证可能不太科学。那

3、么就希望将登陆需要用到的一些数据表同步到多个数据中心，而不是整个数据库实例。2. 数据到达subcriber后，针对不同数据，设置触发器。3. 将多个数据库实例的数据，同步到一个目标数据库。例如多个数据库同步到一个大的数据仓库。4. 在不同的数据库版本之间，复制数据5. 将一个数据库实例的不同数据，复制到不同的目标库。例如省级数据库的数据，按地区划分，分别复制到不同的地区。6. 在多个数据库实例之间，共享部分数据。例如某个业务按用户ID哈希，拆分成了8个数据库，但是有些小的维度表，需要在多个数据库之间共享。以上场景是物理复制无法覆盖的。逻辑复制应运而生，实际上，从2014年发布的9.4版本开始

4、，PostgreSQL就支持逻辑复制了，只是一直没有将其引入内核。2017年即将发布的10.0，将会在内核层面支持基于REDO流的逻辑复制。另一个好消息是，你可以针对同一个数据库实例，同时使用逻辑复制和物理复制，因为他们都是基于REDO的。下面我们来看一下逻辑复制的概念、架构、监控、安全、最佳实践。逻辑复制概念PostgreSQL 逻辑复制是事务级别的复制，引入了几个概念publication - 发布者发布者指数据上游节点，你需要将哪些表发布出去？上游节点需要配置这些东西1. 需要将数据库的REDO的wal_level配置为logical。2. 需要发布逻辑复制的表，必须配置表的REPLIC

5、A IDENTITY，即如何标示老的记录。被复制的表，建议有PK约束。alter table table_name REPLICA IDENTITY DEFAULT | USING INDEX index_name | FULL | NOTHING 解释REPLICA IDENTITY This form changes the information which is written to the write-ahead log to identify rows which are updated or deleted. This option has no effect except wh

6、en logical replication is in use. 记录PK列的 1. DEFAULT (the default for non-system tables) records the old values of the columns of the primary key, if any. 记录指定索引列（索引的所有列须是not null列，其实和PK一样，但是某些情况下，你可以选一个比PK更小的UK） 2. USING INDEX records the old values of the columns covered by the named index, which m

7、ust be unique, not partial, not deferrable, and include only columns marked NOT NULL. 记录完整记录 3. FULL records the old values of all columns in the row. 啥也不记录，这样做是否不支持update, delete? user_catalog_table=true或者系统表，默认为replica identity nothing啥也不记录。如果这种表发布出去了，允许insert，但是执行delete或者update时，会报错。 4. NOTHING r

8、ecords no information about the old row (This is the default for system tables.) 仅仅当数据有变更时才会记录old value，比如delete。 或者update前后old.*<>new.*。 In all cases, no old values are logged unless at least one of the columns that would be logged differs between the old and new versions of the row. 什么是syste

9、m table?指catalog或者user_catalog_table = true的表，不允许修改列的数据类型。postgres=# d+ c Table "public.c" Column | Type | Modifiers | Storage | Stats target | Description -+-+-+-+-+- id | integer | | plain | | info | text | | extended | | c | text | | extended | | Replica Identity: FULL Options: user_cat

10、alog_table=true postgres=# alter table c alter column c type int8; ERROR: column "c" cannot be cast automatically to type bigint HINT: You might need to specify "USING c:bigint". 解释create table table_name () with (user_catalog_table =true); user_catalog_table (boolean) Declare th

11、e table as an additional catalog table for purposes of logical replication. See Section 48.6.2, “Capabilities” for details. This parameter cannot be set for TOAST tables. To decode, format and output changes, output plugins can use most of the backend's normal infrastructure, including calling o

12、utput functions. Read only access to relations is permitted as long as only relations are accessed that either have been created by initdb in the pg_catalog schema, or have been marked as user provided catalog tables using ALTER TABLE user_catalog_table SET (user_catalog_table = true); CREATE TABLE

13、another_catalog_table(data text) WITH (user_catalog_table = true); Any actions leading to transaction ID assignment are prohibited. That, among others, includes writing to tables, performing DDL changes, and calling txid_current(). 3. output plugin发布者还需要一个output plugin，将redo按发布的定义，解析成需要的格式，等待订阅者的订阅。

14、bbbs://docs/devel/static/logicaldecoding-output-plugin.html是不是有点像这个呢？PostgreSQL 闪回 - flash back query emulate by trigger（原文链接：bbbs:/githubaaa/digoal/blog/blob/master/201408/20140828_01.md?spm=5176.100239.blogcont71128.21.HW4nWO&file=20140828_01.md）发布语法创建发布Command: CREATE PUBLICA

15、TION Description: define a new publication Syntax: CREATE PUBLICATION name FOR TABLE table_name , . | FOR ALL TABLES WITH ( option , . ) where option can be: PUBLISH INSERT | NOPUBLISH INSERT | PUBLISH UPDATE | NOPUBLISH UPDATE | PUBLISH DELETE | NOPUBLISH DELETE 默认发布insert,update,delete。 修改发布Comman

16、d: ALTER PUBLICATION Description: change the definition of a publication Syntax: ALTER PUBLICATION name WITH ( option , . ) where option can be: PUBLISH INSERT | NOPUBLISH INSERT | PUBLISH UPDATE | NOPUBLISH UPDATE | PUBLISH DELETE | NOPUBLISH DELETE ALTER PUBLICATION name OWNER TO new_owner | CURRE

17、NT_USER | SESSION_USER ALTER PUBLICATION name ADD TABLE table_name , . ALTER PUBLICATION name SET TABLE table_name , . ALTER PUBLICATION name DROP TABLE table_name , . 发布者小结1. 目前仅仅支持发布表，不允许发布其他对象。2. 同一张表，可以发布多次。3. 在同一个数据库中，可以创建多个publication，但是不能重名，通过系统表查看已创建的publicationpostgres=# d pg_publication Ta

18、ble "pg_catalog.pg_publication" Column | Type | Collation | Nullable | Default -+-+-+-+- pubname | name | | not null | pubowner | oid | | not null | puballtables | boolean | | not null | pubinsert | boolean | | not null | pubupdate | boolean | | not null | pubdelete | boolean | | not null

19、| Indexes: "pg_publication_oid_index" UNIQUE, btree (oid) "pg_publication_pubname_index" UNIQUE, btree (pubname) 4. 允许使用all tables发布所有表。5. 一个publication允许有多个订阅者。6. 目前publication仅支持insert, update, delete。7. 允许发布时，选择发布insert、update、delete，比如只发布insert，而不发布update, delete。8. 当发布了表的upd

20、ate, delete时，表必须设置replica identity，即如何标示OLD TUPLE，通过pk或者uk或者full。如果设置了nothing，则执行update,delete时会报错alter table table_name REPLICA IDENTITY DEFAULT | USING INDEX index_name | FULL | NOTHING 报错例子postgres=# delete from c; ERROR: cannot delete from table "c" because it does not have replica ide

21、ntity and publishes deletes HINT: To enable deleting from the table, set REPLICA IDENTITY using ALTER TABLE. 9. create publication或者alter publication，发布或者修改发布内容中添加或者删除表时，都是事务级别，不会出现复制了部分事务的情况。 so the table will start or stop replicating at the correct snapshot once the transaction has committed。10.

22、发布者需要设置wal_level=logical，同时开启足够的worker，设置足够大的replication slot，设置足够多的sender。因为每一个订阅，都要消耗掉一个replication slot，需要消耗一个wal sender，一个worker进程。发布者的pg_hba.conf需要设置replication条目，允许订阅者连接。发布者的数据库中，必须有replication角色的用户，或者超级用户，并且订阅者要使用它通过流复制协议连接到发布者。subscription - 订阅者订阅者，需要指定发布者的连接信息，以及 publication name，同时指定需要在pub

23、lication数据库中创建的slot name。在同一个数据库中，可以创建多个订阅。订阅者和发布者的角色可以同时出现在同一个实例的同一个数据库中。订阅语法创建订阅Command: CREATE SUBSCRIPTION Description: define a new subscription Syntax: CREATE SUBSCRIPTION subscription_name CONNECTION 'conninfo' PUBLICATION publication_name , . WITH ( option , . ) where option can be:

24、| ENABLED | DISABLED | CREATE SLOT | NOCREATE SLOT | SLOT NAME = slot_name 修改订阅Command: ALTER SUBSCRIPTION Description: change the definition of a subscription Syntax: ALTER SUBSCRIPTION name WITH ( option , . ) where option can be: SLOT NAME = slot_name ALTER SUBSCRIPTION name OWNER TO new_owner |

25、CURRENT_USER | SESSION_USER ALTER SUBSCRIPTION name CONNECTION 'conninfo' ALTER SUBSCRIPTION name SET PUBLICATION publication_name , . ALTER SUBSCRIPTION name ENABLE ALTER SUBSCRIPTION name DISABLE 订阅者小结1. 订阅者需要通过流复制协议连接到发布者，同时需要在发布者创建replication slot。因此发布者的pg_hba.conf中需要配置相应的replication条目，允

26、许订阅者通过流复制协议连接。同时连接发布者的用户，必须具备replication权限，或者具备超级用户权限。2. 同一个数据库中，可以创建多个subscription，这些subscription可以连自一个或多个发布者。3. 当同一个数据库中有多个subscription时，如果这些subscriptions是来自同一个发布者，那么他们之间发布的表不能重叠。也就是说，订阅者的同一张表，不能接受来自同一个源的多个发布。例如发布者create table public.a (id int primary key, info text); create publication pub1 for t

27、able a; create publication pub2 for table a; 订阅者A表接受了同一个源的多次发布，会报错。create table public.a (id int primary key, info text); create subscription sub1 connection 'hostaddr= port=1922 user=postgres dbname=postgres' publication pub1; create subscription sub2 connection 'hostaddr=127.0

28、.0.1 port=1922 user=postgres dbname=postgres' publication pub2; 4. 每一个订阅，都需要在发布端创建一个slot，可以使用slot name = ?指定，或者默认为subscription name。即使是同一个发布端，只要订阅了多次，就需要创建多个SLOT，因为slot中记录了同步的LSN信息。例如create table public.a (id int primary key, info text); create table public.b (id int primary key, info text); cre

29、ate publication pub1 for table a; create publication pub2 for table b; 订阅者create table public.a (id int primary key, info text); create table public.b (id int primary key, info text); create subscription sub1 connection 'hostaddr= port=1922 user=postgres dbname=postgres' publication

30、 pub1; create subscription sub2 connection 'hostaddr= port=1922 user=postgres dbname=postgres' publication pub2; 这种情况，对于这个订阅者，建议合并成一个，例如create subscription sub1 connection 'hostaddr= port=1922 user=postgres dbname=postgres' publication pub1, pub2; 5. pg_dump导出数据库逻辑数

31、据时，默认不会导出subscription的定义，除非使用选项 -include-subscriptions6. 在创建subscription或者alter subscription时，可以使用enable来启用该订阅，或者使用disable暂停该订阅。7. 如果要完全删除订阅，使用drop subscription，注意，删除订阅后，本地的表不会被删除，数据也不会清除，仅仅是不在接收该订阅的上游信息。这个也很好理解，因为同一个表可能接收多个订阅。删订阅和删表是两码事。8. 删除订阅后，如果要重新使用该订阅，数据需要resync，比如订阅的上游节点有100万数据，resync会将这100万数

32、据同步过来。随后进入增量同步。将来10.0正式发布时，也许会提供一个选项，选择要不要resync。(目前来说，一次订阅，意味着这些被订阅的表会和发布端一模一样（只要发布端发布了insert,update,delete语句）。如果发布端只发布了insert，那么源表的update和delete不会被订阅)9. 订阅时，不会自动创建发布端的表，所以表需要在订阅端先创建好。将来10.0正式发布时，也许会填补这个功能。目前发布端和订阅端的表定义必须完全一致，包括schema，表名必须一致。字段名和字段类型必须一致。字段顺序可以不一致。除了表，其他对象都不能被订阅，例如你不能将表订阅到一张视图中。10.

33、 必须使用超级用户创建订阅逻辑复制的冲突逻辑复制，本质上是事务层级的复制，需要在订阅端执行SQL。如果订阅端执行SQL失败（或者说引发了任何错误，包括约束等），都会导致该订阅暂停。注意，update, delete没有匹配的记录时，不会报错，也不会导致订阅暂停。用户可以在订阅端数据库日志中查看错误原因。冲突修复方法1. 通过修改订阅端的数据，解决冲突。例如insert违反了唯一约束时，可以删除订阅端造成唯一约束冲突的记录先DELETE掉。然后使用ALTER SUBSCRIPTION name ENABLE让订阅继续。2. 在订阅端调用pg_replication_origin_advance(

34、node_name text, pos pg_lsn)函数，node_name就是subscription name，pos指重新开始的LSN，从而跳过有冲突的事务。pg_replication_origin_advance(node_name text, pos pg_lsn) Set replication progress for the given node to the given position. This primarily is useful for setting up the initial position or a new position after configu

35、ration changes and similar. Be aware that careless use of this function can lead to inconsistently replicated data. 当前的lsn通过pg_replication_origin_status.remote_lsn查看。bbbs://docs/devel/static/view-pg-replication-origin-status.html逻辑复制架构1. 在创建subscription后，订阅者会在发布端创建一个快照，同时将发布端的数据，在同一

36、个快照内的视角，发送给订阅端。例如订阅了发布端的A,B,C三张表，那么这三张表的当前快照下的数据，会发送给订阅端。2. 订阅端接收完快照后，发布端会从快照的这个LSN开始，从WAL（REDO）日志中，根据发布定义的表以及过滤条件（INSERTUPDATEDELETE），按事务组装复制的消息包，通过流复制协议发送给订阅端的apply（wal receiver）进程。3. 订阅端接收到消息包之后，对于同一个订阅(wal reciever或applyer进程)来说，会按照事务的先后顺序，按事务apply。所以在订阅端，apply也是事务一致的。将来可能会考虑组复制，提高并发性。PS其实你可以把不同的

37、表分别放在不同的订阅中，这样就是并行的了。但是消耗的wal sender进程与连接会多一些。4. 在订阅端，wal receiver(applyer)的session_replication_role会设置为replica。这个影响数据库的trigger和rule。参考PostgreSQL trigger/rule based replication configure, DISABLE/ENABLE REPLICA | ALWAYS TRIGGER | RULE（原文链接：bbbs:/githubaaa/digoal/blog/blob/master/201506/20150615_01.m

38、d?spm=5176.100239.blogcont71128.33.gzECkP&file=20150615_01.md）Can session_replication_role used like MySQL's BlackHole Engine?（原文链接：bbbs:/githubaaa/digoal/blog/blob/master/201102/20110209_01.md?spm=5176.100239.blogcont71128.34.IqxAx2&file=20110209_01.md）逻辑复制的监控逻辑复制依旧使用的是2010年推出的流复制协议，所以监

39、控手段差别不大。订阅端视图pg_stat_subscription View "pg_catalog.pg_stat_subscription" Column | Type | Collation | Nullable | Default -+-+-+-+- subid | oid | | | subname | name | | | pid | integer | | | received_lsn | pg_lsn | | | last_msg_send_time | timestamp with time zone | | | last_msg_receipt_time

40、 | timestamp with time zone | | | latest_end_lsn | pg_lsn | | | latest_end_time | timestamp with time zone | | | 每一个subcription有一条记录，一个订阅可能有多个active subscription workers。对于一个已激活(enabled)的订阅，对应有1个apply进程，所以在这个视图中有一条记录。一个暂停或者crash的订阅，在这个视图中不会有记录。逻辑复制安全、权限发布端1. 必须设置pg_hba.conf，允许订阅端通过流复制连接发布端2. wal_lev

41、el必须设置为logical，记录逻辑复制的一些额外信息3. 订阅端配置的conninfo中，发布端的角色必须具备replication权限，或者超级用户权限4. 使用某个用户在某个数据库中创建publication，这个用户必须对该数据库具备create权限。订阅端1. 订阅端创建subscription的用户，必须是超级用户权限检测仅仅在连接发布端的时候，后期不会检测，比如从发布端获取数据，或者apply数据时，不再检测是否为超级用户。逻辑复制的postgresql.conf配置发布端1. wal_level=logical2. max_replication_slots，每一个订阅需要消

42、耗一个slot，每一个指定了slot的流式物理复制也要消耗一个slot。3. max_wal_senders，每一个slot要使用一个wal sender，每一个流式物理复制也要使用一个wal sender。4. max_worker_processes，必须大于等于max_wal_senders加并行计算进程，或者其他插件需要fork的进程数。订阅端1. max_replication_slots，大于等于该实例总共需要创建的订阅数2. max_logical_replication_workers，大于等于该实例总共需要创建的订阅数3. max_worker_processes， 大于等于

43、max_logical_replication_workers + 1 + CPU并行计算 + 其他插件需要fork的进程数.逻辑复制最佳实践case1. 部署PostgreSQL 10.0wget bbbs://pub/snapshot/dev/postgresql-snapshot.tar.bz2 tar -jxvf postgresql-snapshot.tar.bz2 cd postgresql-10devel export USE_NAMED_POSIX_SEMAPHORES=1 LIBS=-lpthread CC="/home/digo

44、al/gcc6.2.0/bin/gcc" CFLAGS="-O3 -flto" ./configure -prefix=/home/digoal/pgsql10 LIBS=-lpthread CC="/home/digoal/gcc6.2.0/bin/gcc" CFLAGS="-O3 -flto" make world -j 64 LIBS=-lpthread CC="/home/digoal/gcc6.2.0/bin/gcc" CFLAGS="-O3 -flto" make inst

45、all-world 2. 规划数据目录mkdir /disk1/digoal/pgdata/pg_root1922 3. 配置环境变量vi env_pg10.sh export PS1="$USER/bin/hostname -s-> " export PGPORT=1922 export PGDATA=/disk1/digoal/pgdata/pg_root1922 export LANG=en_US.utf8 export PGHOME=/home/digoal/pgsql10 export LD_LIBRARY_PATH=/home/digoal/python2

46、.7.12/lib:/home/digoal/gcc6.2.0/lib64:$PGHOME/lib:/lib64:/usr/lib64:/usr/local/lib64:/lib:/usr/lib:/usr/local/lib:$LD_LIBRARY_PATH export LD_RUN_PATH=$LD_LIBRARY_PATH export DATE=date +"%Y%m%d%H%M" export PATH=/home/digoal/python2.7.12/bin:/home/digoal/gcc6.2.0/bin:$PGHOME/bin:$PATH:. expo

47、rt MANPATH=$PGHOME/share/man:$MANPATH export PGHOST=$PGDATA export PGUSER=postgres export PGDATABASE=postgres alias rm='rm -i' alias ll='ls -lh' unalias vi 4. 初始化数据库initdb -D $PGDATA -U postgres -E SQL_ASCII -locale=C 5. 配置postgresql.conflisten_addresses = '' port = 19

48、22 max_connections = 1000 superuser_reserved_connections = 3 unix_socket_directories = '.' shared_buffers = 32GB maintenance_work_mem = 2GB autovacuum_work_mem = 2GB dynamic_shared_memory_type = posix vacuum_cost_delay = 0 bgwriter_delay = 10ms bgwriter_lru_maxpages = 1000 bgwriter_lru_multi

49、plier = 10.0 bgwriter_flush_after = 0 max_worker_processes = 128 max_parallel_workers_per_gather = 8 max_parallel_workers = 32 max_logical_replication_workers = 32 backend_flush_after = 0 wal_level = logical synchronous_commit = off full_page_writes = off wal_buffers = 2047MB wal_writer_delay = 10ms

50、 wal_writer_flush_after = 0 checkpoint_timeout = 55min max_wal_size = 64GB min_wal_size = 32GB checkpoint_completion_target = 0.05 checkpoint_flush_after = 0 max_wal_senders = 32 max_replication_slots = 32 random_page_cost = 1.5 parallel_tuple_cost = 0 parallel_setup_cost = 0 min_parallel_table_scan

51、_size = 8MB min_parallel_index_scan_size = 512kB effective_cache_size = 512GB wal_receiver_status_interval = 1s hot_standby_feedback = on log_destination = 'csvlog' logging_collector = on log_truncate_on_rotation = on log_checkpoints = on log_connections = on log_disconnections = on log_erro

52、r_verbosity = verbose log_timezone = 'PRC' log_autovacuum_min_duration = 0 autovacuum_naptime = 20s autovacuum_vacuum_scale_factor = 0.01 autovacuum_analyze_scale_factor = 0.05 autovacuum_vacuum_cost_delay = 0 datestyle = 'iso, mdy' timezone = 'PRC' lc_messages = 'C'

53、lc_monetary = 'C' lc_numeric = 'C' lc_time = 'C' default_text_search_config = 'pg_catalog.english' 6. 配置pg_hba.confhost replication postgres /32 trust host replication postgres :1/128 trust host all all /0 md5 host replication postgres /0 md5 7. 配置发布端postgres=# create database s