背景

在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 如下：

  1.  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

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

  1. create table test(id int);

在订阅端创建SUBSCRIPTION：

  1. 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，否则创建订阅的语句会一直卡在那里。
  1. 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 位点如下:

  1. demodb=> select * from pg_stat_replication;
  2. -[ RECORD 1 ]----+------------------------------
  3. pid              | 67297
  4. usesysid         | 16384
  5. usename          | acc
  6. application_name | mysub
  7. client_addr      | xxx
  8. client_hostname  |
  9. client_port      | 58841
  10. backend_start    | 2019-04-18 18:27:29.031333+08
  11. backend_xmin     |
  12. state            | streaming
  13. sent_lsn         | 0/11DB8728
  14. write_lsn        | 0/11DB8728
  15. flush_lsn        | 0/11DB8728
  16. replay_lsn       | 0/11DB8728
  17. write_lag        |
  18. flush_lag        |
  19. replay_lag       |
  20. sync_priority    | 0
  21. sync_state       | async

订阅端

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

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

其他

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