Tables getting stuck at 's' state during logical replication

From: Padmavathi G <padma9(dot)9(dot)1999(at)gmail(dot)com>
To: pgsql-hackers(at)postgresql(dot)org
Subject: Tables getting stuck at 's' state during logical replication
Date: 2023-05-05 05:59:01
Message-ID: CABBh-xRmh8OK-EdopDrgy4PPvCTspug1WJ+PbeEtWs8SVqW9eQ@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

Hello,
I am trying out logical replication for upgrading postgres instance from
version 11 to 15.x. In the process, I noticed that some tables get stuck in
the 's' state during logical replication and they do not move to the 'r'
state. I tried to drop the subscription and create a new subscriber, but
the same thing repeated. Also, each time different tables get stuck, it is
not like the same tables get stuck every time. This also happens to tables
that do not have frequent writes to them. Then I tried to drop the tables
which got stuck from the original publication and created a new publication
and new subscribers just with the tables which got stuck in the previous
subscription. Now, in this new subscription, some tables go to 'r' state
(even though they did not in the older subscription) and some tables remain
in 's' state. If I continue doing this process for 5-6 times I am able to
cover all of the tables. Since these tables get stuck at 's' state, the
replication origins are not deleted automatically when I delete the table
from the publication and I had to manually delete the replication origin.
The logs contain "table synchronization worker for <table_name> has
finished even for table in 's' state. Can somebody please help with this
issue as I do not want to do manual intervention of creating a new
publication and subscription for tables that get stuck at 's'.

Configuration

max_replication_slots : 75 in publisher and subscriber
max_worker_processes: 60 (in subscriber)
max_logical_replication_workers: 55 (in subscriber)

Thanks,

Padmavathi

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Alexander Lakhin 2023-05-05 06:00:01 Re: Add LZ4 compression in pg_dump
Previous Message Peter Eisentraut 2023-05-05 05:39:20 Re: ssl tests aren't concurrency safe due to get_free_port()