Search Postgresql Archives

Re: Subscription stuck at initialize state

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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.github@xxxxxxxxx> wrote:


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

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]

  Powered by Linux