0

In following setup, create subscription command hangs and there is no error in logs

HOST1          --> HOST2                --> HOST3
Master         --> phsyical replication --> logical replication
Version 16.1   --> Version 16.1         --> Version 16.2

some addtional information is

  • HOST1 and HOST2 almost in sync while run create subscription command
  • created publication on HOST1, checked publication on HOST2 and it's ok
  • run select pg_log_standby_snapshot() and checkpoint on HOST1 before create subscription
  • I can see logical replication slot in HOST2 but create subscription command is not finished yet

on HOST2

postgres=# select * from pg_replication_slots ;
-[ RECORD 1 ]-------+-------------
slot_name           | lrep_slot
plugin              | pgoutput
slot_type           | logical
datoid              | 13753820
database            | proddb
temporary           | f
active              | t
active_pid          | 495948
xmin                | 
catalog_xmin        | 29726257
restart_lsn         | AC8/19059C00
confirmed_flush_lsn | 
wal_status          | reserved
safe_wal_size       | 
two_phase           | f
conflicting         | f

proddb=# \dRp
List of publications
-[ RECORD 1 ]---------
Name       | pubmedas8
Owner      | postgres
All tables | f
Inserts    | t
Updates    | t
Deletes    | t
Truncates  | t
Via root   | f

proddb=# select pid, backend_xid, backend_xmin, wait_event, wait_event_type, query  from pg_stat_activity ;
  pid   | backend_xid | backend_xmin |    wait_event     | wait_event_type |                                               query                                                
--------+-------------+--------------+-------------------+-----------------+----------------------------------------------------------------------------------------------------
 495948 |             |              |                   |                 | CREATE_REPLICATION_SLOT "lrep_slot" LOGICAL pgoutput (SNAPSHOT 'nothing')
 508553 |             |     29666602 |                   |                 | select pid, backend_xid, backend_xmin, wait_event, wait_event_type, query  from pg_stat_activity ;
 495929 |             |              | RecoveryWalStream | Activity        | 
 495928 |             |              | BgWriterMain      | Activity        | 
 495927 |             |              | CheckpointerMain  | Activity        | 
 495930 |             |              | WalReceiverMain   | Activity        | 
(6 rows)

proddb=# select locktype, pid, mode from pg_locks;
  locktype  |  pid   |      mode       
------------+--------+-----------------
 relation   | 508553 | AccessShareLock
 virtualxid | 508553 | ExclusiveLock
 virtualxid | 495929 | ExclusiveLock
3
  • Try calling pg_log_standby_snapshot() after you have started CREATE SUBSCRIPTION. Feb 15 at 12:32
  • @LaurenzAlbe just called, no change Feb 15 at 12:40
  • No idea then. Seems like CREATE_REPLICATION_SLOT is busy (no wait events). Feb 15 at 13:43

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Browse other questions tagged or ask your own question.