Re: Subscription stuck at initialize state

Hi Vijaykumar,

I checked the pg_subscription_rel and all the tables in that subscription
are in the state - i (initialize).
I also tried creating a new publication on the source DB with just one
table and tried to subscribe it, it doesn't work either.
However, when I try to subscribe it on some other DB than the one mentioned
above, it works.
By which I am deducing that publication and the source DB are okay, the
problem is on the target DB and it's subscription.
Maybe I will have to restart the DB as a last resort, but I am not sure if
that will solve the problem either.

On Thu, Feb 3, 2022 at 3:33 PM Vijaykumar Jain <
vijaykumarjain(dot)github(at)gmail(dot)com> wrote:

>
>
> On Thu, Feb 3, 2022, 10:32 AM Abhishek Bhola <
> abhishek(dot)bhola(at)japannext(dot)co(dot)jp> wrote:
>
>> So far I figured out that the problem is on the subscriber side.
>> The same publication, when subscribed to on another DB, works fine.
>> Also noticed that the remote_lsn value on the target DB is still 0/0.
>>
>> 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)
>>
>> Would really appreciate it if someone could help me with this.
>>
>>
>>
>> On Thu, Feb 3, 2022 at 9:53 AM Abhishek Bhola <
>> abhishek(dot)bhola(at)japannext(dot)co(dot)jp> wrote:
>>
>>> 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.
>>>>
>>>
>
> This might help you track subscription state along with
> pg_stat_subscription.
> https://www.postgresql.org/docs/10/catalog-pg-subscription-rel.html
>
> It might be a huge table being 'copy' ed and writes would be slow due to
> too many indexes etc.
>
> https://postgresteam.slack.com/files/UQMFAU01W/F02V69YK59P/untitled.sql
>
> also if you see nothing is moving, you may want to login to the server and
> strace the worker processes pid to see if you see any movement at all or it
> keeps looping on some errors.
>
>

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