注：此篇，摘自 《PostgreSQL修炼之道》从小工到专家 第2版。结合自己的理解，有稍做修改之处。实战测试是自己亲测。也为方便自己以后查漏补缺。

1 什么是逻辑复制

逻辑复制（Logical Replication）是 PostgreSQL10 开始，新增特性。流复制是基于 WAL 日志（流）不间断地从主库发送到备库，备库应用WAL日志，实现的物理复制。逻辑复制，则是基于逻辑解析（Logical Dcoding），核心原理是，主库作为发布端，把 WAL 日志解析成一定格式的数据流，订阅端收到后，应用解析的 WAL 数据流，实现数据同步。

逻辑复制，使用的是经过解析后的数据格式，不是原始的 WAL 日志文件。

逻辑复制，使用类似消息队列的发布者（publication）/（subscription）订阅者模型。

逻辑复制，使用复制槽技术，可并行地传输 WAL 日志。通过在订阅端回放 WAL 日志中的逻辑条目保持数据同步。需要注意的是，这里不是“SQL”复制，而是复制 SQL 的操作过程。

逻辑复制，主要应用场景：

  • 将多个数据库的数据合并到一个数据仓库的数据库中，用于数据仓库的数据分析；
  • 不同大版本 PostgreSQL 之间的数据复制，可以辅助数据库跨大版本升级；
  • 捕获本机数据库的增量更新，发送给其他数据库或通知其他应用；
  • 多个数据库之间共享部分数据。

2 什么是逻辑复制的发布

逻辑复制的发布，一般是指创建发布（publication）。

逻辑复制的参数设置：

  • 主库中，逻辑复制的用户必须具有 replication 或 superuser 角色。（备库）订阅者要使用该用户，需要通过流复制协议，连接到源库中的发布者上。
  • 主库中，参数 wal_level 需要设置为 “logical”。
  • 由于每一个订阅，都会消耗掉一个 Replication Slot，一个 WAL Sender，一个 Worker 进程，所以需要设置足够大的 Replication Slot，设置足够多的 WAL Sender，开启足够多的 Worker 进程。
  • 主库的 pg_hba.conf ，需要设置 replication 条目，允许（备库）订阅者连接。

逻辑复制的注意事项：

  • 目前仅支持发布表，不允许发布其他对象。
  • 同一张表可以多次发布。
  • 允许发布时，选择发布 INSERT、UPDATA、DELETE，即当 INSERT 发布是，不发布 UPDATA、DELETE。
  • 当发布了表的 UPDATA、DELETE时，表必须设置 replica identigy，即标识旧行，将其标注为副本身份，通过 pk 或者 uk 或者 full。如果设置了 nothing，则执行 UPDATA、DELETE 时会报错。
  • 一个数据库中可以有多个发布，但是不能重名。通过 pg_publication 查看。
  • 允许一次发布所有表。语法： CREATE PUBLICATION alltables FOR ALL TABLES。
  • 一个发布允许有多个订阅者。
  • 使用创建发布 或者 在发布内容中添加或删除表时，都是事务级别。要么全成功，要么全失败，不会只复制部分事务的情况。

3 什么是逻辑复制的订阅

逻辑复制的订阅，一般是指创建订阅（subscription）。

（备库）订阅端，需要有指向（主库）发布端的连接信息。

一个数据库（订阅端）中可以有多个订阅者（subscription）。这些订阅者（subscription），可以被一个或多个发布者（publication）使用。

每一个订阅，都需要在发布端创建一个 Replication Slot (复制槽)，即使是同一个发布端，只要订阅了多次，就需要创建多个 Replication Slot，因为其中记录了同步的 LSN 信息。

可以使用 ENABLE/DISABLE 命令，启用/暂停订阅。

发布端增加表名，订阅端需要执行命令 “ALTER SUBSCRIPTION sub1 REFRESH PUBLICATION”。

使用pg_dump 导出数据库逻辑数据时，使用 --include-subscriptions ，同时会导出 subscription 的定义，否则不会。

如果要完全删除订阅，使用命令 “DROP SUBSCRIPTION”。删除订阅后，本地的表及数据不会被删除，仅仅是不再接收该订阅传递过来的同步数据。

删除订阅后，如果要重新使用该订阅，数据会全部重新同步过来（ASYNC 强同步），随后进入增量同步。

（主库）发布端和（备库）订阅端，表的模式名、表名必须一致。订阅端的表允许有额外的字段。

订阅时，不会自动创建发布端的表，所以需要在订阅端，先创建与发布端完全一致的表（schema、tablename、字段名称、字段类型必须一致）。字段顺序可以不一致。

必须使用超级用户创建订阅。如 postgres.

4 逻辑复制的问题处理

4.1 冲突

唯反任何约束，如主键冲突，都会导致逻辑复制停止，这被称为冲突。冲突产生的错误日志，记录在订阅者的服务器日志中。

冲突的解决方法主要有：

一：通过修改订阅端的数据解决。例如，当 INSERT 操作时，违反了唯一约束，导致逻辑复制停止。此时，先在订阅端删除冲突的记录，即删除插入的语句，再使用命令“ALTER SUBSCRIPTION sub1 ENABLE”启用订阅。

二：在订阅端调用函数 pg_replicatioin_origin_advance(node_name text,pos pg_lsn)，跳过冲突的事务。node_name 就是 subscription name，pos 指重新开始的 LSN。

4.2 限制

逻辑复制的版本限制方面 ，如果你使用的是 PostgreSQL10 及以后的版本，则无需关心。

这里主要说一下其他方面的限制：

  • DDL 操作不支持复制；
  • 序列不支持复制；
  • TEMPORARY表和UNLOGGEN表不支持复制；
  • 大对象（Large Object）不支持复制；
  • 只有超级用户才有权限添加所有表；
  • 不支持双向复制；
  • 表必须有主键或唯一约束，否则借 UPDATA 或 DELETE 此类操作不支持复制；
  • 表名必须相同；
  • 列名结构必须相同，列的数据类型也必须相同；

5 逻辑复制的监控与安全

两个监控视图：

  • pg_stat_replication：显示连接到当前（主库）发布羰的所有订阅都信息。
  • pg_stat_subscription：显示订阅端的订阅状态。

发布端/订阅端的安全（这里在前面已经有描述）：

  • 发布端必须配置 pg_hba.conf；
  • 发布端 wal_level 必须配置为 logical ，这是逻辑复制的前提；
  • 发布端创建 publication 的用户，必须具有 CREATE 权限；
  • 订阅端的连接信息中，发布端的角色必须具备 replication 或者 superuser ；
  • 订阅端创建 subscription 的用户，必须是超级用户；
  • 权限检测权在连接发布端的时候进行，后期不会检测。比如，从发布端获取数据或者应用数据时，不再检测是否为超级用户。

6 逻辑复制相关的参数配置

# 低于此级别，逻辑复制不能工作
wal_level = logical

# 默认为10 “0”表示复制被禁止
max_wal_senders = 10

# 默认为10 
# 只能在服务器启动时设置此参数
# 如将其设置为小于当前现有的复制槽数值，将导致服务器中无法启动
max_replication_slot = 10

# 默认为60秒 “0”表示禁用超时机制
wal_sender_timeout = 60

# 记录事务的提交时间
track_commit_timestamp = off

7 实战测试

PostgreSQL-逻辑复制「实战」