PostgreSQL逻辑复制-创建订阅挂起 [英] PostgreSQL logical replication - create subscription hangs

问题描述

我正在尝试使用Debian 9和PG 11.1在2个云实例之间设置逻辑复制。主服务器上的命令 CREATE PUBLICATION 成功，但是当我在预期的逻辑副本上启动命令 CREATE SUBSCRIPTION 时，命令无限期挂起。

I am trying to set logical replication between 2 cloud instances both with Debian 9 and PG 11.1. The command CREATE PUBLICATION on master was successful, but when I start the command CREATE SUBSCRIPTION on the intended logical replica, the command hangs indefinitely.

在主服务器上，我看到复制插槽已创建并且处于活动状态，并且可以看到新的walsender进程已创建并等待，并且在主服务器上的日志中我看到这些行：

On the master I can see that the replication slot was created and is active and I can see a new walsender process created and "waiting" and in the log on the master I see these these lines:

2019-01-14 14:20:39.924 UTC [8349] repl_user@db LOG:  logical decoding found initial starting point at 7B0/6C777D10
2019-01-14 14:20:39.924 UTC [8349] repl_user@db DETAIL:  Waiting for transactions (approximately 2) older than 827339177 to end.

但这就是全部。命令 CREATE SUBSCRIPTION 永不结束。

But that is all. The command CREATE SUBSCRIPTION never ends.

Master是一个带有大量插入的数据库，例如每分钟100s，但是它们始终是固定的。因此，应该不存在任何长时间未提交的事务。

Master is a db with heavy inserts, like 100s per minute, but they are all always committed. So there should not be any long time uncommitted transactions.

我尝试通过Google搜索此问题，但未找到任何内容。我缺少什么？

I tried to google for this problem but did not find anything. What am I missing?

推荐答案

由于数据库位于云中，所以您不知道它们真正在哪里

Since the databases are “in the cloud”, so you don't know where they really are.

可能是它们实际上在同一数据库集群中，这可能解释了您看到的僵局：创建订阅等待直到包含复制源数据库的群集上的所有并发事务都已完成，然后才能创建其复制插槽，但是由于两个数据库都位于同一群集中，因此它会等待自身完成，这显然不会发生。

Odds are that they are actually in the same database cluster, which would explain the deadlock you see: CREATE SUBSCRIPTION waits until all concurrent transactions on the cluster that contains the replication source database are finished before it can create its replication slot, but since both databases are in the same cluster, it waits for itself to finish, which obviously won't happen.

解决方案是在源数据库中显式创建一个逻辑复制插槽，并在创建订阅时使用该现有插槽。

The solution is to explicitly create a logical replication slot in the source database and to use that existing slot when you create the subscription.

这篇关于PostgreSQL逻辑复制-创建订阅挂起的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！