Hi folks,
We have a streaming replication setup running PostgreSQL 17.2 as follows:
- Server A (primary)
- Server B (replica)
We also have another separate PostgreSQL 17.2 (Server C), that is replicating some of the tables from the cluster above logically.
When we try to CREATE SUBSCRIPTION from Server C to Server B (streaming replica), the command hangs indefinitely. Eventually we tried to create the replication slot manually on Server B, but pg_create_logical_replication_slot() also hangs. With the log_min_messages set to debug5 we get the output below.
db=# SELECT pg_create_logical_replication_slot('xxx', 'pgoutput');
DEBUG: StartTransaction(1) name: unnamed; blockState: DEFAULT; state: INPROGRESS, xid/subid/cid: 0/1/0
DEBUG: xmin required by slots: data 900062771, catalog 900062771
DEBUG: find_in_dynamic_libpath: trying "/usr/lib/postgresql/17/lib/pgoutput"
DEBUG: find_in_dynamic_libpath: trying "/usr/lib/postgresql/17/lib/pgoutput.so"
DEBUG: searching for logical decoding starting point, starting at 1C64/B9B92760
DEBUG: switched to timeline 1 valid until 0/0
At this stage the command is hanging. Now, as we did our own research through the available resources online, we suspected that the command could be blocked by some long lasting write transaction. Given that Server B is a read-only replica, the only "writing" part could be the WAL stream coming from the primary. And indeed, when we restarted the PostgreSQL service on the primary Server A, the slot creation was let through.
LOG: logical decoding found consistent point at 1C64/B9B927D8
DETAIL: There are no running transactions.
DEBUG: CommitTransaction(1) name: unnamed; blockState: STARTED; state: INPROGRESS, xid/subid/cid: 0/1/0
LOG: duration: 26063.058 ms statement: select pg_create_logical_replication_slot('xxx', 'pgoutput');
pg_create_logical_replication_slot
────────────────────────────────────
(xxx,1C64/B9B92810)
(1 row)
Is there some better way to "unhang" the slot creation other than restarting our primary server? We also tried pg_wal_replay_pause(), without luck.
DEBUG: StartTransaction(1) name: unnamed; blockState: DEFAULT; state: INPROGRESS, xid/subid/cid: 0/1/0
DEBUG: xmin required by slots: data 900062771, catalog 900062771
DEBUG: find_in_dynamic_libpath: trying "/usr/lib/postgresql/17/lib/pgoutput"
DEBUG: find_in_dynamic_libpath: trying "/usr/lib/postgresql/17/lib/pgoutput.so"
DEBUG: searching for logical decoding starting point, starting at 1C64/B9B92760
DEBUG: switched to timeline 1 valid until 0/0
At this stage the command is hanging. Now, as we did our own research through the available resources online, we suspected that the command could be blocked by some long lasting write transaction. Given that Server B is a read-only replica, the only "writing" part could be the WAL stream coming from the primary. And indeed, when we restarted the PostgreSQL service on the primary Server A, the slot creation was let through.
LOG: logical decoding found consistent point at 1C64/B9B927D8
DETAIL: There are no running transactions.
DEBUG: CommitTransaction(1) name: unnamed; blockState: STARTED; state: INPROGRESS, xid/subid/cid: 0/1/0
LOG: duration: 26063.058 ms statement: select pg_create_logical_replication_slot('xxx', 'pgoutput');
pg_create_logical_replication_slot
────────────────────────────────────
(xxx,1C64/B9B92810)
(1 row)
Is there some better way to "unhang" the slot creation other than restarting our primary server? We also tried pg_wal_replay_pause(), without luck.
Note that none of this behaviour is described in the documentation for CREATE SUBSCRIPTION.
Regards,
Regards,
--
Kouber Saparev
