Re: RDS No free space

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>