Re: Subscription stuck at initialize state

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).本メッセージに記載および添付されている情報（以下、総称して「本情報」といいます。）は、本来の受信者による使用のみを意図しています。誤送信等により本情報を取得された場合でも、本情報に係る秘密、または法律上の秘匿特権が失われるものではありません。本電子メールを受取られた方が、本来の受信者ではない場合には、本情報及びそのコピーすべてを削除・破棄し、本電子メールが誤って届いた旨を発信者宛てにご通知下さいますようお願いします。本情報の閲覧、発信または本情報に基づくいかなる行為も明確に禁止されていることをご了承ください。*