Postgres 13: Subscription stuck at initialize state

Question

Postgres version: 13.5

I have 2 sets of publication/subscription between my 2 DBs. One of them is working fine and the other one is stuck at initializing state for all the tables.

sourcedb=# select * from pg_publication;
  oid  |        pubname         | pubowner | puballtables | pubinsert | pubupdate | pubdelete | pubtruncate | pubviaroot
-------+------------------------+----------+--------------+-----------+-----------+-----------+-------------+------------
 19585 | omx_archive_big_tables |    16420 | f            | t         | t         | t         | t           | f
 19584 | omx_archive            |    16420 | f            | t         | t         | t         | t           | f

targetdb=# select * from pg_subscription_rel ;
  srsubid   |  srrelid   | srsubstate |   srsublsn
------------+------------+------------+---------------
 3615804367 | 3322052690 | i          |
 3615804367 | 3322052570 | i          |
 3615756798 | 3322051793 | r          | 9E7E/BF5F82D8
 3615804367 | 3322052133 | i          |
 3615804367 | 3322054214 | i          |
 3615756798 | 3322051802 | r          | 9E7E/C149BBD8
 3615804367 | 3322051757 | i          |

targetdb=# select * from pg_subscription;
-[ RECORD 1 ]---+----------------------------------------------------------------------------------------------------------
oid             | 3615756798
subdbid         | 16589
subname         | sub_omx_archive_big_tables_tci
subowner        | 16420
subenabled      | t
subconninfo     | xxx
subslotname     | sub_omx_archive_big_tables_tci
subsynccommit   | off
subpublications | {omx_archive_big_tables}
-[ RECORD 2 ]---+----------------------------------------------------------------------------------------------------------
oid             | 3615804367
subdbid         | 16589
subname         | sub_omx_archive_tci
subowner        | 16420
subenabled      | t
subconninfo     | xxxx
subslotname     | sub_omx_archive_tci
subsynccommit   | off
subpublications | {omx_archive}

I have dropped the subscription, recreated and refreshed it many times, but it won't move from the initializing phase.

Any suggestions on how to start copying the data again, other than dropping the publication and re-creating it?

The only statement I see on the target DB log is

logical replication apply worker for subscription ""sub_omx_archive_tci"" has started",,,,,,,,,"

I don't see the logical replication table synchronization worker started for any of the tables in this subscription as I see in the other one.

Is there anything in particular that I should be looking for in the log files?

Update: The error looks like on the subscriber side. The same publication, when subscribed to on another DB, works fine. On the subscriber side, I see 0/0 as the remote_lsn

targetdb=# select * from pg_replication_origin_status ;
 local_id |  external_id  |   remote_lsn   |   local_lsn
----------+---------------+----------------+----------------
        1 | pg_3615756798 | 9E96/37152C80  | 1518C/9014BD98
        2 | pg_3616584803 | 0/0            | 0/0
(2 rows)

Answer 1

Someone helped me on the Postgres community for this one, so answering it here.

The target DB was lacking enough max_logical_replication_workers.

Since I already had 2 subscriptions going and the max_logical_replication_workers on the target DB was set to 2. So when I started the second subscription with copy_data=true, it wasn't able to start the temporary subscription for copying the data from the source. That is why it was stuck in the initialize phase.

To rectify it, I increased the max_logical_replication_workers to 4 on the target DB. This needs a DB restart, so please note, that you will have to restart the receiving database.

And voila! it worked.

targetdb=# show max_logical_replication_workers ;
 max_logical_replication_workers
---------------------------------
 4
(1 row)

Please see that I was missing this WARNING in the log file.

"WARNING,53400,"out of logical replication worker slots",,"You might need to increase max_logical_replication_workers.",,,,,,,"","logical replication worker"

So please look out for such an error on your log files if you face this error.