PostgreSQL逻辑复制Docker容器重启后数据重复问题咨询

数据重复的可能性肯定存在，原因主要有这几点： #

• 全量同步与增量复制衔接失误：如果用pg_dump导入全量数据后开启逻辑复制，容器重启时若从节点的复制槽LSN记录未正确保留，或全量导入的事务未标记完成，增量复制会从头推送数据，和已存在的全量数据重复插入。
• 订阅默认配置触发重复同步：默认copy_data = true，容器重启后订阅可能重新触发全量同步，把已存在的数据再导入一遍，直接造成数据叠加。
• 目标表无主键/唯一约束：逻辑复制的INSERT操作不会做重复校验，没有约束的话会持续插入重复数据，直接导致数据量膨胀。

实现容器重启后续传复制流的具体方案： #

1. 规范全量同步与逻辑复制的衔接流程 #

• 先在主节点创建逻辑复制槽：

  SELECT * FROM pg_create_logical_replication_slot('win_linux_repl_slot', 'pgoutput');
  

• 用pg_dump导出全量数据时，只导出数据不创建表：

  pg_dump -h 主节点IP -U 用户名 -d 数据库名 --data-only --no-create-table > full_dump.sql
  

• 从节点导入全量数据后，创建订阅并关闭自动全量同步：

  CREATE SUBSCRIPTION linux_sub
  CONNECTION 'host=主节点IP port=5432 dbname=目标库 user=repl_user password=你的密码'
  PUBLICATION win_pub
  WITH (copy_data = false, slot_name = 'win_linux_repl_slot');
  

2. 给复制表添加主键/唯一约束 #

• 所有需要同步的表必须配置主键或唯一约束，这样即使推送重复数据，PostgreSQL会触发唯一冲突，12+版本可配置订阅跳过这类错误：

  ALTER TABLE problem_table ADD PRIMARY KEY (id);
  -- 若需跳过冲突，创建订阅时追加配置
  -- WITH (copy_data = false, slot_name = 'win_linux_repl_slot', skip_errors = 'unique_violation');
  

3. Docker容器层面做持久化与幂等控制 #

• 将PostgreSQL的数据目录挂载到宿主机，确保容器重启后复制槽的LSN记录、已同步数据不会丢失：

  docker run -d \
    -v /your/host/path/postgres_data:/var/lib/postgresql/data \
    -e POSTGRES_PASSWORD=你的密码 \
    postgres:15
  

• 初始化脚本增加幂等检查：导入全量数据前先判断目标表是否为空，仅空表时执行导入：

  # 示例shell脚本片段
  psql -U postgres -d 目标库 -c "SELECT count(*) FROM problem_table" | grep -q 0
  if [ $? -eq 0 ]; then
    psql -U postgres -d 目标库 -f full_dump.sql
  fi
  

4. 已出现重复数据的修复方法 #

• 按主键分组保留最早的行，清理重复数据：

  DELETE FROM problem_table
  WHERE ctid NOT IN (
    SELECT min(ctid)
    FROM problem_table
    GROUP BY your_primary_key_column
  );
  

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