PostgreSQL 16发布订阅数据同步失效问题求助

一、发布端（db-1）配置有效性检查 #

• 验证 wal_level 是否生效：在 db-1 执行 show wal_level;，确认返回值为 logical。如果不是，说明 postgresql.conf 的修改未生效——检查是否改了错误的配置文件（比如副本文件而非实际生效的主文件），或者重启后 PostgreSQL 服务未正常启动（查看服务日志确认）。
• 检查 pg_hba.conf 规则正确性：确保添加的规则允许 db-2 的 IP（*.*.*.2）通过你创建的全权限角色访问 db-1，正确格式示例：

  host    你的数据库名     全权限角色名    *.*.*.2/32    scram-sha-256
  host    你的数据库名     全权限角色名    *.*.*.2/32    md5
  

  执行 pg_ctl reload（或通过 pgAdmin 重启服务）后，在 db-1 运行 SELECT * FROM pg_hba_file_rules;，确认规则状态为 valid，且 IP 范围匹配。
• 确认发布对象覆盖范围：执行 SELECT * FROM pg_publication; 查看 my_publication 的 puballtables 字段是否为 t；再执行 SELECT * FROM pg_publication_tables; 确认需要同步的表都在发布列表中。注意：FOR ALL TABLES 仅包含创建发布时已存在的表，新增表需手动添加：ALTER PUBLICATION my_publication ADD TABLE 表名;

二、订阅端（db-2）配置检查 #

• 验证订阅连接与启用状态：在 db-2 执行 SELECT * FROM pg_subscription;，检查 subconninfo 是否正确填写了 db-1 的 IP、端口、数据库名、角色及密码；确认 subenabled 字段为 t，如果是 f，执行 ALTER SUBSCRIPTION 你的订阅名 ENABLE;。
• 查看订阅同步状态：执行 SELECT * FROM pg_subscription_rel;，关注 srsubstate 列：
  • i：正在初始化，需等待完成
  • r：正常同步
  • d：已删除
    若状态不是 r，直接查看 db-2 的 PostgreSQL 日志，排查连接错误、权限不足或表结构不匹配等具体报错。
• 确认两端表结构一致性：逻辑复制要求发布端与订阅端的表结构完全一致（列名、数据类型、约束等）。用 pgAdmin 对比两端表结构，或执行 \d 表名 分别查看，若有差异需同步调整。

三、网络与权限排查 #

• 测试网络连通性：在 server-2 上执行 ping *.*.*.1 确认基础网络通畅，再用 telnet *.*.*.1 5432（或 nc -zv *.*.*.1 5432）测试 PostgreSQL 端口是否开放。若不通，检查防火墙（iptables/ufw/云安全组）是否允许 5432 端口的双向流量。
• 验证角色权限：在 db-1 执行 SELECT rolsuper, rolreplication FROM pg_roles WHERE rolname='你的全权限角色名';，确认 rolsuper 或 rolreplication 为 t。然后在 server-2 尝试用该角色连接 db-1：psql -h *.*.*.1 -U 角色名 -d 数据库名，若连接失败，检查密码是否正确，或给角色添加登录权限：ALTER ROLE 角色名 LOGIN;

四、日志深度排查 #

• 查看 server-1 的 PostgreSQL 日志（通常在 /var/log/postgresql/ 或数据目录下的 pg_log 文件夹），搜索 publication 相关报错，比如权限不足、WAL 发送失败等信息。
• 查看 server-2 的 PostgreSQL 日志，搜索 subscription 相关报错，比如连接超时、表结构不匹配、复制槽异常等。

五、常见修复操作 #

• 若初始化同步未完成，等待一段时间（大表初始化耗时较长），或重新触发同步：

  ALTER SUBSCRIPTION 你的订阅名 REFRESH PUBLICATION;
  ALTER SUBSCRIPTION 你的订阅名 COPY DATA;
  

• 若复制槽异常，在 db-1 执行 SELECT * FROM pg_replication_slots;，查看对应订阅的槽是否存在、active 是否为 t。若槽未激活，删除后重建订阅：

  -- 在 db-1 删除异常复制槽
  SELECT pg_drop_replication_slot('槽名');
  -- 在 db-2 删除旧订阅后重建
  DROP SUBSCRIPTION 你的订阅名;
  CREATE SUBSCRIPTION 你的订阅名 CONNECTION 'host=*.*.*.1 port=5432 dbname=你的数据库名 user=你的角色名 password=你的密码' PUBLICATION my_publication;
  

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