Re: Tables getting stuck at 's' state during logical replication

On Fri, May 5, 2023 at 7:27 PM Padmavathi G <padma9.9.1999@gmail.com> wrote:
>
> Some background on the setup on which I am trying to carry out the upgrade:
>
> We have a pod in a kubernetes cluster which contains the postgres 11 image. We are following the logical replication
processfor upgrade 
>
> Steps followed for logical replication:
>
> 1. Created a new pod in the same kubernetes cluster with the latest postgres 15 image
> 2. Created a publication  (say publication 1) in the old pod including all tables in a database
> 3. Created a subscription (say subscription 1) in the new pod for the above mentioned publication
> 4. When monitoring the subscription via pg_subscription_rel in the subscriber, I noticed that out of 45 tables 20
werein the 'r' state and 25 were in 's' state and they remained in the same state for almost 2 days, there was no
improvementin the state. But the logs showed that the tables which had 's' state also had "synchronization workers for
<table_name>finished". 
>

I think the problem happened in this step where some of the tables
remained in 's' state. It is not clear why this could happen because
apply worker should eventually update these relations state to 'r'. If
this is reproducible, we can try two things to further investigate the
issue: (a) Disable and Enable the subscription once and see if that
helps; (b) try increasing the LOG level to DEBUG2 and see if we get
any useful LOG message.

--
With Regards,
Amit Kapila.