Re: [CAUTION: SUSPECT SENDER] RE: [CAUTION: SUSPECT SENDER] RE: BUG #19029: Replication Slot size keeps increasing while logical subscription works fine - Mailing list pgsql-bugs
| From | Thadeus Anand |
|---|---|
| Subject | Re: [CAUTION: SUSPECT SENDER] RE: [CAUTION: SUSPECT SENDER] RE: BUG #19029: Replication Slot size keeps increasing while logical subscription works fine |
| Date | |
| Msg-id | CANUywMFcsELwhF4D9wd0Zh0m57WQRAsWa+R=bhM9_QLoF6Kn3g@mail.gmail.com Whole thread Raw |
| In response to | Re: [CAUTION: SUSPECT SENDER] RE: [CAUTION: SUSPECT SENDER] RE: BUG #19029: Replication Slot size keeps increasing while logical subscription works fine ("Nantha kumar.T." <nanthad@gmail.com>) |
| Responses |
RE: [CAUTION: SUSPECT SENDER] RE: [CAUTION: SUSPECT SENDER] RE: BUG #19029: Replication Slot size keeps increasing while logical subscription works fine
|
| List | pgsql-bugs |
Hi Nantha,
Thank you for your valuable input. So it's really not a bug, but an existing behavior you mean.
I do not know or understand what a "spill" is. I will look it up. But the tables that are part of the publication are not updated as part of any huge transaction. They may be part of some other long running procedures though. If that can create such spills, we can look into them.
Can someone please advise on how to avoid such spills please?
Thanks and regards,
Thadeus Anand.
On Wed, 27 Aug, 2025, 8:09 am Nantha kumar.T., <nanthad@gmail.com> wrote:
Hi Thadeus Anand, Recently in our postgres 16.4 version system we have encountered similar kind of pg_repslot folder size growing abnormally. The files in this folders are just spill over Wal files due to long running or complex transactions. There are commands to check the slot's holding transactions count which related to this spill. If required can share the same. So suggesting to monitor the transactions/sub transactions that are executing. Well for our case, one of the past transaction invoked the issue.Not sure whether it's bug which have to be addressed in 17.5.On Tue, 26 Aug, 2025, 17:51 Thadeus Anand, <thadeus@rmkv.com> wrote:Hi,Here is the result of "select * from pg_replication_slots"
slot_name plugin slot_type datoid database temporary active active_pid xmin catalog_xmin restart_lsn confirmed_flush_lsn wal_status safe_wal_size two_phase inactive_since conflicting invalidation_reason failover synced rmkv_crm_sub_twn pgoutput logical 5 postgres FALSE TRUE 240,409 [NULL] 10861325 2E9/296801D0 2EA/834D54B8 reserved 15,681,629,000 FALSE [NULL] FALSE [NULL] FALSE FALSE rmkv_main_sub_twn pgoutput logical 5 postgres FALSE TRUE 240,410 [NULL] 10861325 2E9/296801D0 2EA/834D54B8 reserved 15,681,629,000 FALSE [NULL] FALSE [NULL] FALSE FALSE standby_slot [NULL] physical [NULL] [NULL] FALSE TRUE 240,458 [NULL] [NULL] 2EA/834D54B8 [NULL] reserved 21,486,545,736 FALSE [NULL] [NULL] [NULL] FALSE FALSE rmkv_crm_sub_cbe pgoutput logical 5 postgres FALSE TRUE 240,404 [NULL] 10861325 2E9/296801D0 2EA/834D54B8 reserved 15,681,629,000 FALSE [NULL] FALSE [NULL] FALSE FALSE rmkv_main_sub_cbe pgoutput logical 5 postgres FALSE TRUE 240,406 [NULL] 10861325 2E9/296801D0 2EA/834D54B8 reserved 15,681,629,000 FALSE [NULL] FALSE [NULL] FALSE FALSE rmkv_crm_sub_tvl pgoutput logical 5 postgres FALSE TRUE 240,408 [NULL] 10861325 2E9/296801D0 2EA/834D54B8 reserved 15,681,629,000 FALSE [NULL] FALSE [NULL] FALSE FALSE rmkv_main_sub_tvl pgoutput logical 5 postgres FALSE TRUE 240,411 [NULL] 10861325 2E9/296801D0 2EA/834D54B8 reserved 15,681,629,000 FALSE [NULL] FALSE [NULL] FALSE FALSE rmkv_crm_sub_blr pgoutput logical 5 postgres FALSE TRUE 240,405 [NULL] 10861325 2E9/296801D0 2EA/834D54B8 reserved 15,681,629,000 FALSE [NULL] FALSE [NULL] FALSE FALSE rmkv_main_sub_blr pgoutput logical 5 postgres FALSE TRUE 240,407 [NULL] 10861325 2E9/296801D0 2EA/834D54B8 reserved 15,681,629,000 FALSE [NULL] FALSE [NULL] FALSE FALSE rmkv_crm_sub_vch pgoutput logical 5 postgres FALSE TRUE 240,403 [NULL] 10861325 2E9/296801D0 2EA/834D54B8 reserved 15,681,629,000 FALSE [NULL] FALSE [NULL] FALSE FALSE rmkv_main_sub_vch pgoutput logical 5 postgres FALSE TRUE 240,401 [NULL] 10861325 2E9/296801D0 2EA/834D54B8 reserved 15,681,629,000 FALSE [NULL] FALSE [NULL] FALSE FALSE rmkv_crm_sub_vdp pgoutput logical 5 postgres FALSE TRUE 240,400 [NULL] 10861325 2E9/296801D0 2EA/834D54B8 reserved 15,681,629,000 FALSE [NULL] FALSE [NULL] FALSE FALSE rmkv_main_sub_vdp pgoutput logical 5 postgres FALSE TRUE 419,107 [NULL] 10861325 2E9/296801D0 2EA/1E788780 reserved 15,681,629,000 FALSE [NULL] FALSE [NULL] FALSE FALSE Thadeus Anand.On Tue, Aug 26, 2025 at 5:44 PM Thadeus Anand <thadeus@rmkv.com> wrote:Hi,Since this afternoon, the replication slot folders have started to grow in size again. I have attached a couple of screenshots showing the size and some contents of a folder. Hope you find them useful.I checked the database log at the publisher, and could find nothing suspicious around that time when the spill files started appearing.Thadeus Anand.On Tue, Aug 26, 2025 at 4:05 PM Amit Kapila <amit.kapila16@gmail.com> wrote:On Tue, Aug 26, 2025 at 1:45 PM Thadeus Anand <thadeus@rmkv.com> wrote:
>
> We created the publications and subscriptions again last night after restarting the publisher and all the subscribers.
>
> So far, everything seems to be working fine, and the replication slot folders remain at 4 KB each.
>
> I did not change anything, and didn't do anything differently this time. In any case, I will monitor this closely and let you know if any malfunction.occurs.
>
Thanks for the update.
--
With Regards,
Amit Kapila.
pgsql-bugs by date:
Previous
From: "Nantha kumar.T."Date:
Subject: Re: [CAUTION: SUSPECT SENDER] RE: [CAUTION: SUSPECT SENDER] RE: BUG #19029: Replication Slot size keeps increasing while logical subscription works fine
Next
From: "Hayato Kuroda (Fujitsu)"Date:
Subject: RE: [CAUTION: SUSPECT SENDER] RE: [CAUTION: SUSPECT SENDER] RE: BUG #19029: Replication Slot size keeps increasing while logical subscription works fine