VACUUM FULL, power failure results in unrecoverable space

From: "Pierre Barre" <pierre(at)barre(dot)sh>
To: pgsql-general(at)lists(dot)postgresql(dot)org
Subject: VACUUM FULL, power failure results in unrecoverable space
Date: 2024-12-03 00:02:13
Message-ID: 0d11ad5e-5903-49ce-8fff-4650919f4b44@app.fastmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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
3. The newly wasted space (bloat + partial VACUUM FULL) is not shown using the above query, only the initial bloat.

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

Thanks.

Best,
Pierre

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Adrian Klaver 2024-12-03 00:20:49 Re: Help in vetting my steps for Postgres DB upgrade from Ver 13.X to ver 15.X
Previous Message Bharani SV-forum 2024-12-02 23:52:59 Re: Help in vetting my steps for Postgres DB upgrade from Ver 13.X to ver 15.X