使用pg_upgrade升级Postgres 11至14时pglogical扩展兼容问题求助

问题背景 #

• 运行Postgres 11的数据库，通过pglogical配置了逻辑复制，计划升级至Postgres 14
• 执行pg_upgrade兼容性检查时失败，提示新集群缺少pglogical共享库
• 不想删除旧集群的pglogical（删除会丢失所有复制集），曾尝试导出pglogical schema、删除扩展后重建再恢复，但复制集信息未恢复

错误输出 #

执行检查命令：

[postgres@prod-host]$ /usr/pgsql-14/bin/pg_upgrade -d /var/lib/pgsql/11/data -D /var/lib/pgsql/14/data -b /usr/pgsql-11/bin -B /usr/pgsql-14/bin --check
Performing Consistency Checks on Old Live Server
------------------------------------------------
Checking cluster versions                                   ok
Checking database user is the install user                  ok
Checking database connection settings                       ok
Checking for prepared transactions                          ok
Checking for system-defined composite types in user tables  ok
Checking for reg* data types in user tables                 ok
Checking for contrib/isn with bigint-passing mismatch       ok
Checking for user-defined encoding conversions              ok
Checking for user-defined postfix operators                 ok
Checking for incompatible polymorphic functions             ok
Checking for tables WITH OIDS                               ok
Checking for invalid "sql_identifier" user columns          ok
Checking for presence of required libraries                 fatal

Your installation references loadable libraries that are missing from the
new installation.  You can add these libraries to the new installation,
or remove the functions using them from the old installation.  A list of
problem libraries is in the file:
    loadable_libraries.txt

Failure, exiting

查看错误详情文件：

[postgres@prod-host]$ cat loadable_libraries.txt
could not load library "pglogical": ERROR:  pglogical is not in shared_preload_libraries
In database: postgres
could not load library "$libdir/pglogical": ERROR:  pglogical is not in shared_preload_libraries
In database: postgres

已尝试的无效操作 #

• 导出pglogical schema：pg_dump -d postgres -n pglogical -v > pglogical_schema.sql
• 重建扩展后恢复schema：psql -d postgres < pglogical_schema.sql，但复制集信息未恢复

解决方案 #

1. 在Postgres 14集群中安装并配置pglogical #

• 安装适配Postgres 14的pglogical扩展包（版本必须兼容Postgres 14）
• 修改Postgres 14的postgresql.conf，添加以下配置：

  shared_preload_libraries = 'pglogical'
  max_replication_slots = 10  # 根据实际需求调整，数值需大于旧集群设置
  max_wal_senders = 10        # 同上，确保满足复制需求
  wal_level = logical
  

• 重启Postgres 14服务，使配置生效

2. 重新执行兼容性检查 #

再次运行检查命令：

/usr/pgsql-14/bin/pg_upgrade -d /var/lib/pgsql/11/data -D /var/lib/pgsql/14/data -b /usr/pgsql-11/bin -B /usr/pgsql-14/bin --check

此时检查应能通过，因为新集群已加载pglogical共享库

3. 执行实际升级 #

检查通过后，移除--check参数执行正式升级：

/usr/pgsql-14/bin/pg_upgrade -d /var/lib/pgsql/11/data -D /var/lib/pgsql/14/data -b /usr/pgsql-11/bin -B /usr/pgsql-14/bin

4. 升级后验证复制状态 #

• 启动Postgres 14服务
• 登录数据库，验证pglogical的复制集和节点信息：

  SELECT * FROM pglogical.replication_set;
  SELECT * FROM pglogical.node;
  

• 确认复制连接正常运行，必要时重启复制进程

关键注意事项 #

• 无需删除旧集群的pglogical，pg_upgrade会自动迁移扩展元数据（包括复制集）
• 必须保证新集群的pglogical版本与旧集群兼容，优先使用对应Postgres版本的官方包
• 升级前务必备份整个旧集群，避免意外情况

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