Re: [CAUTION: SUSPECT SENDER] RE: [CAUTION: SUSPECT SENDER] RE: BUG #19029: Replication Slot size keeps increasing while logical subscription works fine

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(at)rmkv(dot)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(at)rmkv(dot)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(dot)kapila16(at)gmail(dot)com>
>> wrote:
>>
>>> On Tue, Aug 26, 2025 at 1:45 PM Thadeus Anand <thadeus(at)rmkv(dot)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.
>>>
>>