Re: RDS No free space

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>