数据库内核月报 - 2019 / 05

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

背景

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

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

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

RDS for PostgreSQL 10 实践

确认发布端和订阅端网络

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

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

发布端修改wal_level

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

image.png

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

发布端创建 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个,目前不支持修改

订阅端

  1. max_replication_slots,大于等于该实例总共需要创建的订阅数

  2. max_logical_replication_workers,大于等于该实例总共需要创建的订阅数

  3. 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 日志不能及时清理,从而造成磁盘满锁定
  • 订阅端可以同时修改订阅的表
  • 订阅端的表可以配合触发器完成更丰富的功能
  • 如果被订阅的数据有主外键约束,请将其作为一个订阅。否则可能会有约束的问题