PostgreSQL logical replication - create subscription hangs

Question

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.

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.

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

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.

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

Bennettbenni

Are both databases in the same cluster, i.e., do they have the same host name and port? – Actinochemistry

@LaurenzAlbe They are on different cloud instances with different hostnames, ports are standard 5432 on both. – Bennettbenni

I don't know what a "cloud instance" is, but I'd double check that they are indeed in different clusters. Try to create the replication slot first and then the subscription using the replication slot. That would save the problem if my suspicion were right. – Actinochemistry

@LaurenzAlbe You are right it worked. Please post it as answer so I can accept it. Many thanks. – Bennettbenni

Answer 1

Since the databases are “in the cloud”, 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.

Actinochemistry