PostgreSQL 逻辑复制在 CREATE SUBSCRIPTION 上挂起

我在 PostgreSQL 逻辑复制版本 15 上遇到问题。（我也在 v10 和 v12 上进行了测试，但遇到了同样的问题）。 它需要复制来进行测试，因此源数据库和目标数据库位于同一服务器上。

在我设置的配置文件中：

postgresql.conf:

wal_level = logical  # if I have "replica", I can't subscribe

pg_hba.conf:

local replication all
host replication all 127.0.0.1/32 trust
host replication all ::1/128               trust

在源数据库中我这样做：

CREATE PUBLICATION repl_name
   FOR TABLE table1, table2, table3, ...;

在这个阶段的 pgAdmin 中我有：

CREATE PUBLICATION
Query returned successfully in 59 msec.

但是，在日志中我为每个表都有一条消息：

2023-05-17 17:02:07.537 CEST [25356] ERROR: role "backup_user" does not exist
2023-05-17 17:02:07.537 CEST [25356] STATEMENT:  GRANT SELECT ON SEQUENCE public.table1 TO backup_user;

这可能与以下事实有关：在我的计算机上，它正在从进行此复制的客户端恢复备份。

但我不知道这是否打扰你，因为输入后：

select * from pg_catalog.pg_publication;

我可以看到我的出版物：

"20438"   "stack_repl"    "10"   false   true    true   true   true   false

但我在这里没有看到它：

select * from pg_stat_replication;

我认为这可能与尚未订阅有关。

当我想要订阅时，真正的问题出现了。 首先，它使用如下表恢复数据库table1, table2, table3自然，桌子是空的。

我输入命令：

CREATE SUBSCRIPTION sub_test
   CONNECTION 'dbname=dbname host=localhost port=5432 user=postgres password=12345'
   PUBLICATION repl_name;

而 pgAdmin 则无休止地旋转。即使我周末离开电脑，它也无法完成。

它显示在日志中：

2023-05-17 17:22:24.178 CEST [25376] LOG:  logical decoding found initial starting point at 0/A0D6F338
2023-05-17 17:22:24.178 CEST [25376] DETAIL:  Waiting for transactions (approximately 1) older than 3712 to end.
2023-05-17 17:22:24.178 CEST [25376] STATEMENT:  CREATE_REPLICATION_SLOT "sub_test" LOGICAL pgoutput (SNAPSHOT 'nothing')

我向您保证，我长期以来一直在努力解决这个问题，并且我需要支持/提示来了解我可以采取哪些措施来使数据开始复制。

您可能正在同一数据库集群中的两个数据库之间设置逻辑复制。这使得CREATE SUBSCRIPTION永远悬挂，如文档描述：

仅当复制槽不是作为同一命令的一部分创建时，创建连接到同一数据库集群的订阅（例如，在同一集群中的数据库之间进行复制或在同一数据库内进行复制）才会成功。否则，CREATE SUBSCRIPTION呼叫将挂起。要实现此目的，请单独创建复制槽（使用函数pg_create_logical_replication_slot与插件名称pgoutput) 并使用参数创建订阅create_slot = false。这是一个实施限制，可能会在未来版本中取消。

所以这就是成功之路：

• 连接到主数据库并创建槽：

  SELECT pg_create_logical_replication_slot('sub_test', 'pgoutput');
  

• 然后连接到备用数据库并运行：

  CREATE SUBSCRIPTION sub_test
     CONNECTION 'dbname=dbname host=localhost port=5432 user=postgres password=12345'
     PUBLICATION repl_name
     WITH (create_slot = false);