PostgreSQL逻辑复制执行CREATE SUBSCRIPTION时挂起求助

一、创建发布时的backup_user错误处理 #

创建发布后日志出现的ERROR: role "backup_user" does not exist，是因为备份中包含了给backup_user角色的权限授予语句，但当前源数据库未创建该角色。

• 这个错误不影响发布本身的有效性（你已经能在pg_publication中看到发布），但如果后续需要相关权限，可选择：
  • 创建缺失角色：CREATE ROLE backup_user;（根据需求添加LOGIN等属性）
  • 清理无效权限记录：通过DROP OWNED BY backup_user;（若该角色残留权限存在）或从备份恢复时跳过相关GRANT语句

二、创建订阅卡住的核心解决思路 #

日志中Waiting for transactions (approximately 1) older than 3712 to end是卡住的根本原因：逻辑复制需要等待源库中所有早于起始点的事务完成，才能确定一致性快照，避免数据同步异常。

步骤1：定位长时间运行的事务 #

在源数据库执行以下查询，找出未结束的事务：

SELECT 
  pid, 
  datname, 
  usename, 
  state, 
  query, 
  now() - xact_start AS transaction_duration
FROM pg_stat_activity
WHERE xact_start IS NOT NULL 
  AND state IN ('idle in transaction', 'active');

重点关注transaction_duration最长的记录，对应就是日志中提到的未结束事务。

步骤2：终止或完成该事务 #

• 如果是你自己的会话，直接提交/回滚事务，或关闭会话
• 如果是其他会话，确认无业务影响后，执行终止命令：

  SELECT pg_terminate_backend(【查到的pid】);
  

  替换【查到的pid】为步骤1中获取的进程ID。

步骤3：重新创建订阅 #

事务终止后，重新执行CREATE SUBSCRIPTION语句即可。若想跳过初始数据复制（你已恢复空表），可添加参数优化：

CREATE SUBSCRIPTION sub_test 
CONNECTION 'dbname=【源库名】 host=localhost port=5432 user=postgres password=12345' 
PUBLICATION repl_name
WITH (copy_data = false);

注意：确保dbname填写的是源数据库的名称，避免与目标库混淆。

额外检查项 #

• 确认源库postgresql.conf中max_replication_slots值足够（默认10，执行SELECT * FROM pg_replication_slots;查看已占用slot，若有废弃slot可执行DROP_REPLICATION_SLOT slot_name;清理）
• 验证源库与目标库的表结构完全一致（包括序列、约束等），避免后续同步报错

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