如何使用pglogical升级PostgreSQL生产库并保留订阅端数据同步

问题根源 #

pglogical扩展对PostgreSQL主版本兼容性要求严格，当订阅端先升级到14而发布端仍停留在11时，两端适配的pglogical版本无法兼容，直接导致复制通道中断。由于订阅端存在不可丢失的本地数据，不能通过重新全量同步的方式恢复，必须通过版本统一+增量同步的方式修复。

分步修复步骤 #

1. 临时暂停写入，确保数据静态 #

• 发布端执行，切换WAL并暂停应用写入：

  SELECT pg_switch_wal();
  

  通知业务团队暂停向发布端写入数据，避免新变更生成。
• 订阅端暂停本地应用写入，防止后续同步过程中产生数据冲突。

2. 升级发布端到PostgreSQL 14 #

这一步是解决版本兼容的核心，必须保证发布端升级后与订阅端版本一致：

1. 停止PostgreSQL 11服务：

   sudo systemctl stop postgresql-11
   

2. 安装PostgreSQL 14及适配的pglogical扩展（需确保pglogical版本≥2.4.4以支持PG14）。
3. 使用pg_upgrade完成数据目录升级（提前做好发布端数据备份）：

   /usr/pgsql-14/bin/pg_upgrade \
     -b /usr/pgsql-11/bin \
     -B /usr/pgsql-14/bin \
     -d /var/lib/pgsql/11/data \
     -D /var/lib/pgsql/14/data \
     -U postgres \
     -c
   

4. 启动PostgreSQL 14服务：

   sudo systemctl start postgresql-14
   

5. 在发布端目标数据库中重新初始化pglogical：

   CREATE EXTENSION IF NOT EXISTS pglogical;
   

6. 验证发布配置是否正常：

   SELECT * FROM pglogical.publish;
   

   确认原有的发布集、表配置均存在。

3. 重建订阅端的pglogical订阅（保留本地数据） #

现在两端版本一致，重新建立订阅但跳过全量同步：

1. 删除失效的旧订阅（仅删除订阅关系，不影响本地表数据）：

   SELECT pglogical.drop_subscription('your_subscription_name');
   

2. 重新创建订阅，关键指定copy_data = false以避免全量覆盖：

   SELECT pglogical.create_subscription(
     subscription_name := 'your_subscription_name',
     provider_dsn := 'host=发布端IP port=5432 dbname=目标库名 user=复制账号 password=密码',
     replication_sets := ARRAY['你的发布集名称'],
     copy_data := false
   );
   

3. 检查订阅状态，确认进入同步状态：

   SELECT subscription_name, status FROM pglogical.subscription;
   

   状态应为replicating。

4. 校验数据一致性 #

必须确保两端数据无冲突，尤其是订阅端的本地数据表：

• 使用pglogical自带工具校验指定表：

  SELECT pglogical.verify_subscription('your_subscription_name', ARRAY['本地数据表名']);
  

• 若发现数据差异，根据业务规则手动修复（优先保留订阅端本地数据，或结合发布端数据合并）。

5. 恢复业务写入 #

• 先恢复发布端的应用写入，观察10-15分钟，确认订阅端能同步新产生的变更。
• 确认同步正常后，恢复订阅端的本地应用写入。

关键注意事项 #

• 绝对避免跨大版本（如11→14）的pglogical复制，必须保证两端PostgreSQL版本一致后再启用复制。
• copy_data = false是保留本地数据的核心，该参数让订阅端仅同步发布端升级后的新变更，不会覆盖已有本地数据。
• 升级前务必对发布端和订阅端做全量备份，防止操作失误导致数据丢失。

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