Re: Subscription stuck at initialize state

Update: Tried dropping and recreating publication on the source DB and
subscription still won't move ahead.
Not sure what I am missing.

On Wed, Feb 2, 2022 at 1:20 PM Abhishek Bhola <
abhishek(dot)bhola(at)japannext(dot)co(dot)jp> wrote:

> 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?
>
> On Wed, Feb 2, 2022 at 11:31 AM Steve Baldwin <steve(dot)baldwin(at)gmail(dot)com>
> wrote:
>
>> Hi Abishek,
>>
>> Have you checked the subscriber and publisher database log files to see
>> if there is a problem blocking the subscription? For example, a subscribed
>> table missing a column that exists in the publisher.
>>
>> Cheers,
>>
>> Steve
>>
>> On Wed, Feb 2, 2022 at 1:26 PM Abhishek Bhola <
>> abhishek(dot)bhola(at)japannext(dot)co(dot)jp> wrote:
>>
>>> 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?
>>>
>>>
>>> *This correspondence (including any attachments) is for the intended
>>> recipient(s) only. It may contain confidential or privileged information or
>>> both. No confidentiality or privilege is waived or lost by any
>>> mis-transmission. If you receive this correspondence by mistake, please
>>> contact the sender immediately, delete this correspondence (and all
>>> attachments) and destroy any hard copies. You must not use, disclose, copy,
>>> distribute or rely on any part of this correspondence (including any
>>> attachments) if you are not the intended
>>> recipient(s).本メッセージに記載および添付されている情報（以下、総称して「本情報」といいます。）は、本来の受信者による使用のみを意図しています。誤送信等により本情報を取得された場合でも、本情報に係る秘密、または法律上の秘匿特権が失われるものではありません。本電子メールを受取られた方が、本来の受信者ではない場合には、本情報及びそのコピーすべてを削除・破棄し、本電子メールが誤って届いた旨を発信者宛てにご通知下さいますようお願いします。本情報の閲覧、発信または本情報に基づくいかなる行為も明確に禁止されていることをご了承ください。*
>>
>>

--
_This correspondence (including any attachments) is for the intended
recipient(s) only. It may contain confidential or privileged information or
both. No confidentiality or privilege is waived or lost by any
mis-transmission. If you receive this correspondence by mistake, please
contact the sender immediately, delete this correspondence (and all
attachments) and destroy any hard copies. You must not use, disclose, copy,
distribute or rely on any part of this correspondence (including any
attachments) if you are not the intended
recipient(s).本メッセージに記載および添付されている情報（以下、総称して「本情報」といいます。）は、本来の受信者による使用のみを意図しています。誤送信等により本情報を取得された場合でも、本情報に係る秘密、または法律上の秘匿特権が失われるものではありません。本電子メールを受取られた方が、本来の受信者ではない場合には、本情報及びそのコピーすべてを削除・破棄し、本電子メールが誤って届いた旨を発信者宛てにご通知下さいますようお願いします。本情報の閲覧、発信または本情報に基づくいかなる行為も明確に禁止されていることをご了承ください。_