PostgreSQL逻辑复制创建订阅挂起无日志及发布者清理咨询

问题背景 #

已在PostgreSQL中配置逻辑复制，postgres.conf已设置wal_level = 'logical'，创建订阅的前置配置步骤正常，但执行创建订阅命令时出现挂起，且日志无任何错误信息。

创建订阅命令：

CREATE SUBSCRIPTION SUB_NL_LIVE_NEW
CONNECTION 'host=x.x.x.x port=5432 user=postgres password=xxx dbname=main sslmode=require'
PUBLICATION NL_LIVE;

当前日志配置：

log_error_verbosity = verbose
log_min_messages = debug1

1. 创建订阅挂起的可能原因及排查方案 #

• 网络与访问权限问题：订阅端无法连接到发布端，常见原因包括防火墙/安全组拦截5432端口，或发布端pg_hba.conf未允许订阅端IP访问。直接用psql测试连接：

  psql -h x.x.x.x -p 5432 -U postgres -d main
  

  若连接失败，优先排查网络和pg_hba.conf配置。

• SSL握手异常：命令指定sslmode=require，但发布端未正确配置SSL（如未启用SSL、证书缺失/无效），导致连接握手卡住。可临时将sslmode改为prefer（仅用于测试），若能创建订阅，则说明SSL配置存在问题，需检查发布端ssl = on等相关参数及证书文件。

• 发布端资源或锁阻塞：发布端存在长事务持有锁，或CPU、内存耗尽，无法响应订阅初始化请求。在发布端执行以下查询排查：

  -- 查看锁等待情况
  SELECT * FROM pg_locks WHERE NOT granted;
  

  同时结合top、iostat等系统命令查看资源占用，若有长事务，可等待其结束或谨慎终止。

• 全量数据同步耗时：若发布的表数据量极大，创建订阅时默认的全量数据同步会耗时很久，看似挂起。可通过以下视图确认状态：

  -- 订阅端查看同步状态
  SELECT subname, sync_state FROM pg_stat_subscription;
  -- 发布端查看复制进程
  SELECT * FROM pg_stat_replication WHERE application_name LIKE 'sub_%';
  

  若sync_state为syncing，说明正在同步数据，等待完成即可。

• 日志配置未生效：虽已设置日志参数，但未重新加载配置。先确认配置是否生效：

  SHOW log_error_verbosity;
  SHOW log_min_messages;
  

  若未生效，执行SELECT pg_reload_conf();或重启实例，再执行创建订阅并查看日志。

2. 发布端的清理方式 #

• DROP PUBLICATION的局限性：执行DROP PUBLICATION pub_name;仅会删除发布对象本身，不会自动清理订阅端的订阅，也不会删除发布端关联的逻辑复制槽，残留的复制槽会持续占用WAL空间。

• 完整清理步骤：

  1. 清理订阅端：在所有订阅该发布的节点上执行DROP SUBSCRIPTION SUB_NL_LIVE_NEW;，避免后续连接失败。
  2. 删除发布对象：在发布端执行DROP PUBLICATION NL_LIVE;。
  3. 清理复制槽：在发布端查询所有逻辑复制槽，确认无关联订阅后删除：

     -- 查看逻辑复制槽
     SELECT slot_name, database, active FROM pg_replication_slots WHERE slot_type = 'logical';
     -- 删除无用复制槽
     SELECT pg_drop_replication_slot('slot_name');
     

  4. 表清理（可选）：若发布的表不再需要，可直接删除表，此步骤与发布清理无直接关联。

内容的提问来源于stack exchange，提问作者opensource-developer