public inbox for [email protected]  

help / color / mirror / Atom feed

From: Guillaume Lelarge <[email protected]>
To: Pierre Barre <[email protected]>
Cc: pgsql-generallists.postgresql.org <[email protected]>
Subject: Re: VACUUM FULL, power failure results in unrecoverable space
Date: Tue, 3 Dec 2024 07:13:20 +0100
Message-ID: <CAECtzeVZ1C_CmK_AxMV1B5VdhKRoH3DV7zu9Omr0yCijHQXa+g@mail.gmail.com> (raw)
In-Reply-To: <[email protected]>
References: <[email protected]>

Hi,


Le mar. 3 déc. 2024, 01:02, Pierre Barre <[email protected]> a écrit :

> Hello,
>
> I encountered an issue while attempting to reclaim space from a heavily
> bloated table:
>
> Initial analysis using
> https://github.com/ioguix/pgsql-bloat-estimation/blob/master/table/table_bloat.sql indicated
> approximately 600GB of bloat in the table.
>
> I initiated a VACUUM FULL operation to reclaim this space, but during
> execution, the server experienced a power failure.
>
>
> After server recovery:
>
> 1. The database came back online successfully
> 2. The space used by the in-progress table reconstruction was not freed
>

Because doesn't know it's here.

3. The newly wasted space (bloat + partial VACUUM FULL) is not shown using
> the above query, only the initial bloat.
>

Because it's not the same kind of bloat. Vacuum full builds another set of
files for the table, and these files aren't yet connected to the table,
since vacuum full didn't finish. You've got some orphaned files you'll have
to delete.


>
> Is there a way to reclaim this space without resorting to a full pg_dump
> and pg_restore cycle?
>

You can dump and restore (which will drop all bloat of this database), but
will probably take too long to finish. You can also see pg_orphaned to know
which files to delete (https://github.com/bdrouvot/pg_orphaned).

Regards.


-- 
Guillaume.

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: [email protected]
  Cc: [email protected], [email protected], [email protected]
  Subject: Re: VACUUM FULL, power failure results in unrecoverable space
  In-Reply-To: <CAECtzeVZ1C_CmK_AxMV1B5VdhKRoH3DV7zu9Omr0yCijHQXa+g@mail.gmail.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox