解决逻辑复制冲突（disable_on_error、SKIP）

命令

-- 1) Prevent a failing apply worker from looping forever:
ALTER SUBSCRIPTION sub_app SET (disable_on_error = true);
-- 2) Find the finish LSN of the conflicting transaction in the subscriber log:
--    CONTEXT: ... in transaction 749, finished at 0/5035378
-- 3a) Preferred: fix/delete the conflicting row on the subscriber, then re-enable:
ALTER SUBSCRIPTION sub_app ENABLE;
-- 3b) Or skip the whole remote transaction (NONE of its changes are applied on the subscriber):
ALTER SUBSCRIPTION sub_app SKIP (lsn = '0/5035378');
ALTER SUBSCRIPTION sub_app ENABLE;
SELECT subname, subenabled, subskiplsn FROM pg_subscription;

作用

当重复键、行缺失或权限问题等错误使订阅端的应用停止时：disable_on_error 会自动禁用订阅；可以修复冲突数据后 ENABLE，或者 SKIP 由日志中 'finished at' LSN 标识的整个远程事务。

适用场景

当 apply worker 因订阅端的直接写入、不完整的初始复制或双向写入而失败并不断重启时。

注意

SKIP 还会丢弃该事务中不冲突的变更，两侧将永久不一致；优先修复数据，并在 SKIP 后比对该表。在冲突解决之前，发布端的复制槽会持续堆积 WAL。请通过权限阻止应用写入订阅端的表。