数据库内核月报

背景

在RDS for PostgreSQL 10 中，不再需要使用额外的插件，直接在内核里面实现了表级别的逻辑订阅功能。如果用户具有以下的几个场景，可以尝试使用该逻辑订阅的功能：

某些表复制到不同实例的需求。如果某个表需要在全国各地都有接入点的查询，可以使用不同的实例，其中一个为中心实例，其他查询实例订阅该表。
将多个数据库实例的数据，同步到一个实例目标数据库进行数据分析。
在不同的数据库版本之间，复制数据。
将同一个数据库实例的表，复制到同一实例不同的目标库。

注：因为网络连通原因，目前只支持RDS for PostgreSQL 10 基础版开放。下面简单介绍下其具体的使用方法。

RDS for PostgreSQL 10 实践

确认发布端和订阅端网络

只有发布端和订阅端网络可连才能创建逻辑订阅。注意发布端的白名单需要增加订阅端IP：

相同实例不同数据库无需增加
不同实例如果在相同VPC 内部，最好是在白名单中增加VPC 的网段

发布端修改wal_level

要使用逻辑订阅，必须要设置发布端实例的wal_level 参数 >=logical。RDS for PostgreSQL 10 控制台支持该参数的修改，如下图：

注：该参数会重启实例，请选择合理的修改时间。

发布端创建 PUBLICATION

在发布端对特殊的表（ALL 代表全部的表）创建PUBLICATION 如下：

 CREATE PUBLICATION mypub FOR TABLE test ;

其中：

逻辑订阅目前支持 insert, update, delete, truncate 中一种或者多种的订阅。
支持update 和 delete 订阅的表需要设置 REPLICA IDENTITY 唯一标示一行。
同一个表可以发布多次。
一个PUBLICATION 可以允许多个SUBSCRIPTION。
保证事务级别的逻辑订阅，不会出现某个事务复制一半的情况。

其他 CREATE PUBLICATION 的语法详见链接。

发布端带有replication 权限用户

复制源端必须提供带有replication 权限的用户，在RDS for PostgreSQL 10 中，初始账号具有replication 权限，由初始账号执行create role xxx with superuser 的用户也具有replication 权限。

发布端其他参数

max_replication_slots 默认16个，目前不支持修改
max_wal_senders 默认16个，目前不支持修改

订阅端

max_replication_slots，大于等于该实例总共需要创建的订阅数
max_logical_replication_workers，大于等于该实例总共需要创建的订阅数
max_worker_processes，大于等于max_logical_replication_workers + 1 + CPU并行计算 + 其他插件需要fork的进程数.

订阅端创建 SUBSCRIPTION

在订阅端创建于发布端相同的表结构：

create table test(id int);

在订阅端创建SUBSCRIPTION：

CREATE SUBSCRIPTION mysub CONNECTION 'dbname=demodb host=xxx.aliyun-inc.com port=3432 user=acc password=xxx' PUBLICATION mypub with (copy_data=true);

其中：

必须使用订阅端实例的初始账号（或者初始账号创建的其他的rds_superuser 账号）来创建订阅。
如果相同的实例不同数据库之间的订阅，host=localhost 而port 参数需要执行show port; 来获取。而且订阅端需要先手动创建replication_slot 如下，另外创建订阅时参数create_slot=false，否则创建订阅的语句会一直卡在那里。

select * from pg_create_logical_replication_slot(‘订阅名称>’,’ pgoutput’);

copy_data 表示如果复制源端已经有数据，则会把数据先全部复制过来。
订阅端需要通过流复制协议连接到发布端，同时需要在发布端创建replication slot。
要完全删除订阅，使用drop subscription，删除订阅后，本地的表不会被删除，数据也不会清除，仅仅是不在接收发布端的数据，对应发布端的replication slot 也会被删除。
如果删除订阅时，发布端不能连接，则删除失败，需要先暂停该订阅（alter subscription），但是发布端的replication slot 没有被删除，需要手工维护。

冲突处理

逻辑订阅可以简单理解为将xlog 解析，然后在订阅端执行对应的SQL。当订阅端执行SQL 失败，则订阅就会暂停。冲突修复一般有如下方法：

修改订阅端的数据解决冲突。例如insert违反了唯一约束时，可以DELETE订阅端造成唯一约束冲突的记录，然后启动逻辑订阅。
在订阅端调用pg_replication_origin_advance(node_name text, pos pg_lsn)函数，node_name就是subscription name，pos指重新开始的LSN，从而跳过有冲突的事务，详见链接。

逻辑订阅的监控

发布端

逻辑订阅属于一种逻辑复制，可以在发布端查看pg_stat_replication 来查看复制的各个lsn 位点如下:

demodb=> select * from pg_stat_replication;
-[ RECORD 1 ]----+------------------------------
pid              | 67297
usesysid         | 16384
usename          | acc
application_name | mysub
client_addr      | xxx
client_hostname  |
client_port      | 58841
backend_start    | 2019-04-18 18:27:29.031333+08
backend_xmin     |
state            | streaming
sent_lsn         | 0/11DB8728
write_lsn        | 0/11DB8728
flush_lsn        | 0/11DB8728
replay_lsn       | 0/11DB8728
write_lag        |
flush_lag        |
replay_lag       |
sync_priority    | 0
sync_state       | async

订阅端

订阅端提供了pg_stat_subscription 的视图来查看当前接受的lsn 位点，如下：

demodb=> select * from pg_stat_subscription;
-[ RECORD 1 ]---------+------------------------------
subid                 | 56043
subname               | mysub
pid                   | 59777
relid                 |
received_lsn          | 0/11DB8728
last_msg_send_time    | 2019-04-18 23:16:10.879015+08
last_msg_receipt_time | 2019-04-18 23:16:10.88364+08
latest_end_lsn        | 0/11DB8728
latest_end_time       | 2019-04-18 23:16:10.879015+08

其他

不要用循环复制
要维护好replication slot，如果管理不当，会造成WAL 日志不能及时清理，从而造成磁盘满锁定
订阅端可以同时修改订阅的表
订阅端的表可以配合触发器完成更丰富的功能
如果被订阅的数据有主外键约束，请将其作为一个订阅。否则可能会有约束的问题

数据库内核月报－ 2019 / 05

PgSQL · 最佳实践 · RDS for PostgreSQL 的逻辑订阅

背景