Hi Hayato san,
Thank you for your response. Thanks again to your colleagues Amit and Vignesh as well for spending their valuable time to help.
I have altered all my subscriptions to enable streaming. I just set it on to begin with, and did not set it to parallel. I will also watch pg_stat_activity and identify the large transactions that may be causing the spills.
Will update back if anything worthwhile comes up.
On a completely different note, is there any possibility for you developers at PostgreSQL to consider making the tables at the subscriber side read only, so that nobody makes changes to them accidentally? I am asking this because sometimes when it happens, the logical replication fails, and making the subscriber tables read only can reduce a lot of headache. Please consider.
Thadeus Anand.
Dear Thadeus, Nantha,
> Can these spill files in the pg_replslot folder be deleted safely, if all the subscribers have
> caught up?
Do not remove them manually. Spill files exist because the logical replication
system considers they are needed. I cannot know your system well, but in your
first example, there is a possibility that transaction 10861356 is not committed
yet thus walsender keep decoding and spilling. Actually it looks for me that
subscribers have not been caught up.
Removing them may cause unexpected behavior.
> (I noticed last night when I restarted my publisher, the spill files were removed
> automatically)
When postmaster restarts, it checks all directory under pg_replslot and removes
all .spill files. This is harmless because spilled changes would be decoded again.
> Can I use ALTER SUBSCRIPTION to set streaming on, with my existing subscriptions?
Yes, you can. More detail, after you run ALTER SUBSCRIPTION command, the apply
worker on the subscriber restarts and establishes the connection again with the
different streaming parameter.
Note that pg_stat_activity (with the pid) is still needed to understand the issue correctly.
Best regards,
Hayato Kuroda
FUJITSU LIMITED
