Re: RDS No free space

Hi all, we've identified the issue. We had a very laggy replication
instance a high volume of data in our replication slots, I'm not sure the
underlying PG number here, but the RDS metric is "OldestReplicationSlotLag"
and it was by terabytes (as the replication instance had crashed).

I appreciate the info and ideas here. Very grateful for this mailing list.

On Sun, May 21, 2023 at 11:20 AM Wells Oliver <wells(dot)oliver(at)gmail(dot)com>
wrote:

> We've dug a little further and we feel somewhat confident that it's
> retaining WAL logs forever. Our free storage starts to drop
> steadily/precipitously when we enabled WAL. Could someone point me to some
> settings/queries to dig a bit on that?
>
> On Sun, May 21, 2023 at 11:14 AM Wells Oliver <wells(dot)oliver(at)gmail(dot)com>
> wrote:
>
>> Dustin, how did you come to see the incomplete vacuum and the orphaned
>> files?
>>
>> On Sun, May 21, 2023 at 10:59 AM Dustin Jantz <djantz(at)frontporch(dot)com>
>> wrote:
>>
>>> To check the replication slots run the following query. The lag is how
>>> much the WAL file is holding on to data. If the slot is not active you can
>>> remove the slot and that will free up the space the WAL file is holding on
>>> to.
>>>
>>> SELECT
>>>
>>> rps.slot_name,
>>>
>>> pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn))
>>> pretty_replication_slot_lag,
>>>
>>> pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(),
>>> confirmed_flush_lsn)) pretty_confirmed_lag,
>>>
>>> rps.active slot_active,pg_wal_lsn_diff(pg_current_wal_lsn(),
>>> restart_lsn) as replication_slot_
>>>
>>> lag,pg_wal_lsn_diff(pg_current_wal_lsn(),
>>>
>>> confirmed_flush_lsn) as confirmed_lag,
>>>
>>> rps.active_pid
>>>
>>> FROM pg_replication_slots rps
>>>
>>>
>>>
>>>
>>>
>>> Remove the inactive replication slot:
>>>
>>>
>>>
>>> SELECT pg_drop_replication_slot('<slot_name>');
>>>
>>>
>>>
>>>
>>>
>>>
>>>
>>> The only other time I had this issue was because of an incomplete vacuum
>>> full on a large table. This caused there to be orphaned files which took up
>>> a lot of space.
>>>
>>>
>>>
>>>
>>>
>>>
>>>
>>> Kind regards,
>>>
>>>
>>>
>>>
>>>
>>> Dustin Jantz
>>>
>>> djantz(at)frontporch(dot)com
>>>
>>>
>>>
>>> *From:* Wells Oliver <wells(dot)oliver(at)gmail(dot)com>
>>> *Sent:* Sunday, May 21, 2023 10:48 AM
>>> *To:* Jim Mlodgenski <jimmy76(at)gmail(dot)com>
>>> *Cc:* Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>; pgsql-admin <
>>> pgsql-admin(at)postgresql(dot)org>
>>> *Subject:* Re: RDS No free space
>>>
>>>
>>>
>>> I'm not too familiar with that. Can you point me in the direction of
>>> some config settings and maybe queries to execute?
>>>
>>>
>>>
>>> On Sun, May 21, 2023 at 10:43 AM Jim Mlodgenski <jimmy76(at)gmail(dot)com>
>>> wrote:
>>>
>>>
>>>
>>>
>>>
>>> On Sun, May 21, 2023 at 1:38 PM Wells Oliver <wells(dot)oliver(at)gmail(dot)com>
>>> wrote:
>>>
>>> So we run on RDS, and we clearly used up all of our provisioned storage.
>>> However, I am baffled, and while I am emailing our AWS support, I wondered
>>> if this list might point me in some direction too.
>>>
>>>
>>>
>>> Our provisioned storage was 15TB. The size of our database -- shown
>>> in pg_database -- is only 6TB. What in the world could be using that
>>> remaining space? I am at a loss, that's a _ton_ of space being used up. Is
>>> it some temporary allocation during script execution (seems ginormous,
>>> impossible)? It it some WAL log thing?
>>>
>>>
>>>
>>> A fairly common cause of this is orphan replication slots so WAL files
>>> are retained. Check
>>>
>>> to see if there is an inactive slot that may be preventing the files to
>>> be removed.
>>>
>>>
>>>
>>>
>>>
>>>
>>> --
>>>
>>> Wells Oliver
>>> wells(dot)oliver(at)gmail(dot)com <wellsoliver(at)gmail(dot)com>
>>>
>>
>>
>> --
>> Wells Oliver
>> wells(dot)oliver(at)gmail(dot)com <wellsoliver(at)gmail(dot)com>
>>
>
>
> --
> Wells Oliver
> wells(dot)oliver(at)gmail(dot)com <wellsoliver(at)gmail(dot)com>
>

--
Wells Oliver
wells(dot)oliver(at)gmail(dot)com <wellsoliver(at)gmail(dot)com>