bigywater.tech
PostgreSQL · 19

PostgreSQL 逻辑复制报 duplicate key 卡住了怎么办:replica identity、DDL 不复制和 ALTER SUBSCRIPTION SKIP 实测

“PostgreSQL 逻辑复制报 duplicate key 卡住了怎么办、cannot update table because it does not have a replica identity、DDL 要不要手动同步” 都是上线逻辑复制后最先遇到的问题。这篇在本机自己起一个发布端和一个订阅端,把这几个报错逐个复现,并验证 SKIP、REPLICA IDENTITY FULL 和删槽的做法。

duplicate key
订阅端已有同主键行时 apply worker 的报错,会不停重试;ALTER SUBSCRIPTION … SKIP (lsn = …) 跳过出错事务
missing replicated column
发布端加列而订阅端没加时的报错;DDL 和序列都不会自动复制
65 MB
订阅端停机、发布端写 20 万行后,复制槽为它保留的 WAL;drop subscription 后槽还在发布端

搭建

实测 · 本机执行输出 
  建订阅后 0.12 s,订阅端 items 有 1000 行(初始拷贝)
  发布端 insert 1 行、update id=1、delete id=2 之后,订阅端:行数 1000 ,id=1 的 qty = 99 ,id=2 还在不在: 0 行,id=1001 在不在: 1 行
  发布端 pg_stat_replication: ('sub', 'streaming') ;复制槽: ('sub', 'pgoutput', 'logical', True)
  订阅端 pg_stat_subscription: ('sub', True)

发布端 CREATE PUBLICATION,订阅端 CREATE SUBSCRIPTION;初始数据自动拷贝,之后按行复制。

冲突

实测 · 本机执行输出 
      ERROR:  duplicate key value violates unique constraint "items_pkey"
      DETAIL:  Key (id)=(5000) already exists.
      CONTEXT:  processing remote data for replication origin "pg_<oid>" during message type "INSERT" for replication target relation "public.items" in transaction <xid>, finished at 0/<lsn>
      LOG:  logical replication apply worker for subscription "sub" has started
  复制卡住了:订阅端 id=5001 在不在: 0 行;worker 重试次数(日志里 ERROR 出现次数): 4
  日志里的 CONTEXT 写着 “finished at <lsn>”: finished at 0/<lsn>
  alter subscription sub skip (lsn = …) 之后约 0.9 s:id=5001 到了订阅端 1 行;被跳过的那个事务里 id=5000 在订阅端仍然是: local-only

订阅端已有同主键行时 apply worker 报 duplicate key 并重试;用日志里的 finished at LSN 配合 SKIP 跳过。

replica identity

实测 · 本机执行输出 
  发布端 update nopk: ObjectNotInPrerequisiteState: cannot update table "nopk" because it does not have a replica identity and publishes updates | HINT:  To enable updating the table, set REPLICA IDENTITY using ALTER TABLE.
  设成 REPLICA IDENTITY FULL 之后 update 成功,订阅端 约 0.06 s 后看到 b = y;发布端的 relreplident = f

没有主键的表在发布端 UPDATE / DELETE 会报错;设 REPLICA IDENTITY FULL 或加主键。

DDL 与序列

实测 · 本机执行输出 
      ERROR:  logical replication target relation "public.items" is missing replicated column: "note"
      CONTEXT:  processing remote data for replication origin "pg_<oid>" during message type "INSERT" in transaction <xid>, finished at 0/<lsn>
  在订阅端补上同一列之后约 0.9 s,id=7000 到了订阅端: 1 行
  发布端插入 5 行后序列 seq1:发布端 last_value = 5 ,订阅端 last_value = 1 ;订阅端 s1 的行数 5 (切换到订阅端写入时要手动 setval)

DDL、序列不复制;订阅端缺列时 apply worker 报 missing replicated column。

复制槽

实测 · 本机执行输出 
  订阅端停机、发布端又写入 20 万行之后:slot sub,active = False,wal_status = reserved,保留的 WAL = 65 MB
  订阅端重新启动后约 1.8 s追平:items 行数 201003 = 发布端 201003
  直接 drop subscription(槽留在发布端):发布端槽还在吗: 1 个 → 要手动 pg_drop_replication_slot
  手动 pg_drop_replication_slot 之后: 0 个

订阅端停机时槽保留 WAL;drop subscription 要先解除槽再手动删发布端的槽。

分步演示

逻辑复制实测
本步的对象

    本文用到的工具与边界

    • 本机 macOS 27 arm64 / PostgreSQL 16.15(Homebrew);每个实验自己 initdb 一个数据目录,端口 9261 起,只监听 127.0.0.1,不碰本机已有的 5432 上的实例;命令行工具统一用 LC_ALL=C 取英文原文,路径换成 ;计时用 time.perf_counter 或 EXPLAIN ANALYZE 的 Execution Time,数字每次运行会有小幅抖动;实验结束后整个数据目录删除。发布端 9261(wal_level = logical),订阅端 9263 是另一个独立 initdb 的实例;本机回环,wal_retrieve_retry_interval = 1s 让重试更快;日志里的 LSN、事务号、origin 名已换成占位符
    • 没有验证:跨主版本的逻辑复制;分区表、TRUNCATE、大对象的复制;行过滤和列过滤(PostgreSQL 15 起的 publication 选项);streaming = on 对大事务的影响;并行 apply;双向复制与 origin 过滤;PostgreSQL 其他大版本里 SKIP 的可用性
    京ICP备2026067502号-1京公网安备11010502063487号