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