如何在PostgreSQL复制槽中避免LSN交错？

一、如何让事务按顺序输出 #

test_decoding的输出本质遵循事务提交顺序，出现LSN交错通常是因为开启了大事务的流式传输（未提交就输出中间变更），可通过以下方式处理：

1. 关闭大事务流式传输，仅输出已提交事务 #

如果不需要实时获取大事务的中间变更，可强制test_decoding仅在事务提交后输出完整内容，此时输出顺序严格遵循提交顺序，不会出现交错：

• 调用读取接口时添加参数：

  SELECT * FROM pg_logical_slot_get_changes('your_slot_name', NULL, NULL, 'streaming', 'off');
  

• 或创建复制槽时直接禁用流式：

  SELECT pg_create_logical_replication_slot('your_slot_name', 'test_decoding', false, 'streaming', 'off');
  

2. 客户端侧按事务提交LSN排序（流式场景） #

如果必须保留大事务流式传输，无法避免变更交错，可在客户端做二次处理：

• 识别事务边界：test_decoding输出会包含BEGIN（带事务ID）、COMMIT [xid] at [lsn]、ROLLBACK标记。
• 按事务ID收集所有关联变更，存储时绑定其提交LSN（COMMIT语句中的LSN值）。
• 待事务提交后，按提交LSN从小到大排序输出事务内容，保证全局顺序。

二、跟踪读取偏移量，确保后续流式传输的连续性 #

无论事务是否交错，都需要准确跟踪消费位置，避免数据重复或丢失：

1. 记录每次处理的最大LSN #

每次调用pg_logical_slot_get_changes时，结果集中的lsn字段对应每条变更的WAL位置。处理完一批变更后，记录这批数据的最大LSN，作为下一次读取的起始点：

-- 下一次读取从已处理完成的LSN开始
SELECT * FROM pg_logical_slot_get_changes('your_slot_name', '0/123ABC', NULL);

2. 手动更新复制槽的restart_lsn #

复制槽的restart_lsn决定PostgreSQL清理WAL的阈值，需定期将其更新到已处理完成的LSN位置，防止WAL被提前清理：

-- 将restart_lsn更新到已处理的LSN
SELECT pg_replication_slot_advance('your_slot_name', '0/123ABC');

3. 处理事务回滚场景 #

如果收到ROLLBACK标记，需丢弃该事务的所有变更，并将跟踪的LSN更新到ROLLBACK对应的LSN之后，避免后续重复读取回滚的变更。

4. 定期检查复制槽状态 #

通过以下SQL查看复制槽的当前状态，确认restart_lsn和confirmed_flush_lsn与自己跟踪的偏移量一致：

SELECT slot_name, restart_lsn, confirmed_flush_lsn FROM pg_replication_slots WHERE slot_name = 'your_slot_name';

内容的提问来源于stack exchange，提问作者Adrij Shikhar