postgresql的逻辑复制-订阅和发

postgresql的逻辑复制-订阅和发

目录

• postgresql的逻辑复制-订阅和发
  • 一、逻辑复制是什么
  • 二、快速 checklist（先确认再动手）
    • 逻辑复制的发布端的安全和权限控制如下：
    • 逻辑复制的订阅端的安全和权限控制如下：
    • 与逻辑复制相关的参数有以下几个
  • 三、最小可运行案例（单表增量同步）
    • 步骤 1 发布端配置
    • 步骤 2 订阅端配置
    • 步骤 3 验证
  • 四、常用管理命令
    • 1.查看发布
    • 2.查看订阅
    • 3.加/减表
    • 4.暂停/重启同步
    • 5.重新拷贝已有数据（表同步失败时）
    • 6.删除
  • 五、高级玩法速览
    • 1.只复制部分操作
    • 2.复制部分列（PG 15+）
    • 3.行级过滤（PG 15+）
    • 4.级联复制
  • 六、常见坑与排查
    • 1.订阅端表缺少 PK/UK → 初始同步极慢，apply 报错 “cannot update/delete without replica identity”
    • 2.DDL 不会自动同步
    • 3.复制槽卡住 → 主库 WAL 无限增长
    • 4.大事务延迟高
    • 5.因冲突会产生错误，复制停止，它必须由用户手动解决
  • 七、逻辑复制的限制
  • 八、一句话总结

下面给出一份“从 0 到 1”能跑通的 PostgreSQL 逻辑复制（发布-订阅） 速查手册，涵盖原理、限制、配置步骤与完整案例，全部命令均基于 PG 16 验证通过，复制即可用。

一、逻辑复制是什么

1.发布-订阅模型：

• 发布者（publisher）→ 创建 Publication（表级变更集合）
• 订阅者（subscriber）→ 创建 Subscription（连接+订阅集合）
  2.与物理流复制的区别
• 只复制 部分表/部分操作；订阅端 可写；支持 跨大版本/跨平台
• 不支持 DDL、Sequence、大对象
  3.后台进程
• 订阅端：logical replication launcher → apply worker → tablesync worker（每张初始表一个）

二、快速 checklist（先确认再动手）

项	发布端	订阅端
版本	≥ 10	≥ 10
wal_level	logical	—
max_replication_slots	≥ 1	—
max_logical_replication_workers	—	≥ (表数+1)
表结构	有 PK 或 UK	与发布端一致
网络	pg_hba.conf 允许 replication 连接	能连通发布端

逻辑复制的发布端的安全和权限控制如下：

• 1.必须设置pg_hba.conf，允许订阅端通过流复制连接发布端。
• 2.wal_level必须设置为“logical”，以记录逻辑复制的一些额外信息。
• 3.订阅端配置的conninfo中，发布端的角色必须具备权限或者超级用户权限。
• 4.使用某个用户在某个数据库中创建publication，此用户必须对该数据库具备CREATE权限。

逻辑复制的订阅端的安全和权限控制如下：

• 1.订阅端创建subscription的用户必须是超级用户。
• 2.权限检测仅在连接发布端的时候进行，后期不会检测，比如从发布端获取数据或者apply数据时，不再检测是否为超级用户

与逻辑复制相关的参数有以下几个

·wal_level：必须设置为“logical”，让WAL日志文件中记录逻辑解码所需的信息，低于这个级别，逻辑复制不能工作。
·max_wal_senders（integer）：指定来自备用服务器或流式基本备份客户端的最大并发连接数（同时运行的WAL发送器进程的最大数）。默认值为“10”，“0”表示复制被禁止。应将此参数设置为略高于预期客户端的最大数量。该参数只能在服务器启动时设置。
·max_replication_slots（integer）：指定服务器可以支持的最大复制插槽数。默认值为“10”。只能在服务器启动时设置此参数。将其设置为小于当前现有复制插槽数的值将导致服务器无法启动。
·wal_sender_timeout（integer）：不活动的复制连接的时间超过这个参数指定的毫秒数，就会被终止掉。这对于发送服务器检测备用崩溃或网络中断很有用。0值将禁用超时机制。默认值为60秒。
·track_commit_timestamp（boolean）：记录事务的提交时间。默认值为“off”。

三、最小可运行案例（单表增量同步）

环境假设
发布端：192.168.1.10:5432 用户：repl / pwd123
订阅端：192.168.1.20:5432 用户：postgres

步骤 1 发布端配置

-- postgresql.conf
listen_addresses = '*'
wal_level = logical
max_replication_slots = 10

-- pg_hba.conf  新增
host  all  repl  192.168.1.20/32  md5
host  replication repl 192.168.1.20/32 md5

-- 重启
pg_ctl restart

-- 建库、建表、建发布
CREATE DATABASE demo;
\c demo
CREATE TABLE public.t1(id int PRIMARY KEY, info text, ts timestamp DEFAULT now());

CREATE PUBLICATION pub1 FOR TABLE public.t1;
-- 如要全库：CREATE PUBLICATION pub_all FOR ALL TABLES;

步骤 2 订阅端配置

-- postgresql.conf
max_logical_replication_workers = 4   -- 大于等于表数+1
max_sync_workers_per_subscription = 2

pg_ctl restart

-- 建同结构表
CREATE DATABASE demo;
\c demo
CREATE TABLE public.t1(id int PRIMARY KEY, info text, ts timestamp);

-- 创建订阅（会自动建立复制槽）
CREATE SUBSCRIPTION sub1
CONNECTION 'host=192.168.1.10 port=5432 dbname=demo user=repl password=pwd123'
PUBLICATION pub1;
-- 返回 NOTICE: created replication slot "sub1" on publisher  即成功

步骤 3 验证

-- 发布端
INSERT INTO t1(id,info) VALUES (1,'hello'),(2,'world');
UPDATE t1 SET info='postgres' WHERE id=1;

-- 订阅端
SELECT * FROM t1;
 id |   info   |             ts
----+----------+----------------------------
  1 | postgres | 2025-10-14 14:32:10.1023
  2 | world    | 2025-10-14 14:32:10.1023

四、常用管理命令

1.查看发布

SELECT * FROM pg_publication;
\dRp+          -- psql 快捷命令

2.查看订阅

SELECT * FROM pg_subscription;
\dRs+          -- psql 快捷命令
SELECT * FROM pg_stat_subscription;   -- 实时 LSN、延迟

3.加/减表

ALTER PUBLICATION pub1 ADD TABLE new_tbl;
ALTER PUBLICATION pub1 DROP TABLE old_tbl;
-- 订阅端立即生效，无需重建

4.暂停/重启同步

ALTER SUBSCRIPTION sub1 DISABLE;
ALTER SUBSCRIPTION sub1 ENABLE;

5.重新拷贝已有数据（表同步失败时）

ALTER SUBSCRIPTION sub1 REFRESH PUBLICATION;

6.删除

DROP SUBSCRIPTION sub1;   -- 先删订阅，复制槽会自动清理
DROP PUBLICATION pub1;

五、高级玩法速览

1.只复制部分操作

CREATE PUBLICATION insert_only FOR TABLE t1 WITH (publish = 'insert');

2.复制部分列（PG 15+）

CREATE PUBLICATION pub_col FOR TABLE t1 (id, info);  -- 订阅端表也必须只有这两列

3.行级过滤（PG 15+）

CREATE PUBLICATION pub_row FOR TABLE t1 WHERE (id > 1000);

4.级联复制

订阅端自己再建发布，供第三级节点订阅，实现“主→汇总→数仓”链式架构 。

六、常见坑与排查

1.订阅端表缺少 PK/UK → 初始同步极慢，apply 报错 “cannot update/delete without replica identity”

解决：给表加主键，或 ALTER TABLE … REPLICA IDENTITY FULL;（性能差）

2.DDL 不会自动同步

需要手动在订阅端执行，或使用事件触发器 + 自动化脚本。

3.复制槽卡住 → 主库 WAL 无限增长

监控：

SELECT slot_name, active, restart_lsn, pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn)) AS lag
FROM pg_replication_slots;

发现 inactive 且 lag 持续增大：检查订阅端网络、进程是否存活，必要时 DROP SUBSCRIPTION 重建。

4.大事务延迟高

逻辑复制要等事务提交才解码，单条大事务可能看到秒级延迟；尽量业务侧拆事务。

5.因冲突会产生错误，复制停止，它必须由用户手动解决

逻辑复制的行为与普通的DML操作类似，因为即使订阅者节点本地更改了数据，数据也将被更新。传入数据违反任何约束，如主键冲突，逻辑复制都将停止，这被称为冲突。当复制UPDATE或者DELETE操作时，丢失的数据不会产生冲突，这样的操作将被忽略。
冲突会产生错误，并会停止复制，它必须由用户手动解决。有关冲突的详细信息可以在订阅者的服务器日志中找到。

冲突的修复方法主要有以下两种

• 方法一：通过修改订阅端的数据解决冲突。例如，INSERT违反了唯一约束时，可以先删除订阅端造成唯一约束冲突的记录，然后再使用ALTER SUBSCRIPTION name ENABLE让订阅继续。
• 方法二：在订阅端调用pg_replication_origin_advance(node_nametext,pos pg_lsn)函数，node_name就是subscription name，“pos”指重新开始的LSN，从而跳过有冲突的事务。

七、逻辑复制的限制

逻辑复制的数据库版本限制有以下几种：

·数据源发布和订阅节点需要运行PostgreSQL 9.4+。
·复制源过滤和冲突检测需要PostgreSQL 9.5+。
·pglogical支持跨PostgreSQL主要版本之间的复制，但在订阅服务器上，不同版本之间进行复制时可能会出现问题。
·支持从旧版本复制到新版本，因为PostgreSQL具有向后兼容性，但只有有限的向前兼容性比较安全。

逻辑复制的其他限制主要有以下几种：

·DDL操作不支持复制，发布节点上发布表进行DDL操作后，DDL操作不会复制到订阅节点，需要在订阅节点对发布表手动执行DDL操作。
·序列本身不支持复制，当前逻辑复制仅支持普通表，序列、视图、物化视图、分区表、外部表等对象都不支持。
·TEMPORARY表和UNLOGGED表不会被复制。
·大对象（Large Object）字段不支持复制。
·表名与列名必须结构相同，列的数据类型也必须相同（除非类型隐式转换相同）。
·订阅端的表可以有更多的列，并且顺序可以不同，但是类型和列名必须相同。
·只有超级用户才有权限添加所有表。
·不支持双向复制。
·表必须有主键或者唯一约束，否则像UPDATE或者DELETE这样的操作无法被复制。

八、一句话总结

“发布端管 哪些表+哪些操作，订阅端管 连接+应用；表结构必须有主键，DDL 自己负责，其余就是标准 SQL 搞定。” 照上面 3 大步操作，10 分钟即可在测试环境跑通 PostgreSQL 逻辑复制。