PostgreSQL 10逻辑复制初始同步过慢问题咨询

首先，5万行数据逻辑复制初始同步花数小时完全不正常——哪怕是机械硬盘，正常情况下几分钟就能搞定。下面一步步帮你排查问题、提速：

一、先做基础排查，定位卡在哪 #

• 检查资源占用：本地主机是不是有其他进程占了CPU、磁盘IO？用top看CPU使用率，iostat -x 1看磁盘%util（如果接近100%，说明磁盘瓶颈）。同时看发布端和订阅端的postgresql.log，有没有慢查询、锁等待或者复制相关的报错信息。
• 确认表的核心约束：逻辑复制要求发布的表有唯一索引（最好是主键），不然初始同步会效率极低，甚至出问题。你可以查一下：

  SELECT conname FROM pg_constraint 
  WHERE conrelid = 'active_directory.security_principal'::regclass 
    AND contype = 'p'; -- 查主键
  

  如果返回空，赶紧给表加个主键——这是逻辑复制的必备前提，也是提速的关键。

二、索引要不要加？分情况 #

• 发布端：如果表已经有主键/唯一索引，不需要额外加索引。如果没有，先补主键（上面说的），这比加普通索引有用得多。
• 订阅端：初始同步是插入数据，此时如果订阅端表上有非必要的索引（比如联合索引、非唯一索引），反而会拖慢插入速度。可以先临时删除这些索引，同步完再重建；但主键/唯一索引还是要保留（不然后续增量同步会出问题）。

三、最有效的优化：预加载数据跳过初始同步 #

逻辑复制的默认初始同步用COPY拉数据，效率不如pg_dump。直接用预加载的方式跳过这一步：

1. 如果已经创建了订阅，先暂停它：

   ALTER SUBSCRIPTION active_directory_sub DISABLE;
   

2. 在发布端导出表数据：

   pg_dump -h localhost -d 你的发布端数据库名 -t active_directory.security_principal -f security_principal_dump.sql
   

3. 在订阅端导入数据：

   psql -h localhost -d 你的订阅端数据库名 -f security_principal_dump.sql
   

4. 修改订阅，告诉它不需要再做初始同步：

   ALTER SUBSCRIPTION active_directory_sub SET (copy_data = false);
   

5. 重新启用订阅：

   ALTER SUBSCRIPTION active_directory_sub ENABLE;
   

   这样订阅只会同步后续的增量数据，完全跳过慢得离谱的初始同步环节。

四、调整复制参数，提升同步效率 #

如果不想预加载，也可以调参数优化默认的初始同步：

• 订阅端临时调高work_mem：COPY操作会用到work_mem，如果太小会频繁刷磁盘。临时设置：

  SET work_mem = '64MB'; -- 根据你的内存调整，比如16G内存可以设到128MB
  

  同步完再改回默认值（比如4MB）。
• 增加复制 worker 数量：订阅端的max_logical_replication_workers默认是4，调高一点（比如到8）：

  -- 临时生效，重启后需要改postgresql.conf
  SET max_logical_replication_workers = 8;
  

• 发布端检查WAL相关参数：确保wal_level = logical（已经开了，不然没法逻辑复制），max_wal_senders足够（默认10，一般够，但如果有其他复制可以调高）。

五、移除订阅端的“拖油瓶”约束/触发器 #

如果订阅端的表有额外的触发器（比如BEFORE INSERT做复杂校验）、外键约束，会严重拖慢插入速度：

1. 临时禁用触发器和非必要约束：

   -- 禁用所有触发器
   ALTER TABLE active_directory.security_principal DISABLE TRIGGER ALL;
   -- 禁用外键约束（替换成你的约束名）
   ALTER TABLE active_directory.security_principal DISABLE CONSTRAINT fk_xxx;
   

2. 同步完成后再启用：

   ALTER TABLE active_directory.security_principal ENABLE TRIGGER ALL;
   ALTER TABLE active_directory.security_principal ENABLE CONSTRAINT fk_xxx;
   

最后总结 #

优先用预加载数据+跳过初始同步的方案，这是最快解决问题的办法。如果一定要用默认初始同步，先补主键，再调work_mem，禁用订阅端的触发器/非必要约束，同时排查磁盘IO瓶颈。

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