Feedback-ID: i97614980:Fastmail
MIME-Version: 1.0
Date: Tue, 03 Dec 2024 01:02:13 +0100
From: "Pierre Barre" <pierre@barre.sh>
To: pgsql-general@lists.postgresql.org
Message-Id: <0d11ad5e-5903-49ce-8fff-4650919f4b44@app.fastmail.com>
Subject: VACUUM FULL, power failure results in unrecoverable space
Content-Type: multipart/alternative;
 boundary=1dd51c45685f420ba1169b547ccde4cb
Archived-At: <https://www.postgresql.org/message-id/0d11ad5e-5903-49ce-8fff-4650919f4b44%40app.fastmail.com>
Precedence: bulk

--1dd51c45685f420ba1169b547ccde4cb
Content-Type: text/plain
Content-Transfer-Encoding: 7bit

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
--1dd51c45685f420ba1169b547ccde4cb
Content-Type: text/html
Content-Transfer-Encoding: quoted-printable

<!DOCTYPE html><html><head><title></title><style type=3D"text/css">p.Mso=
Normal,p.MsoNoSpacing{margin:0}</style></head><body><div>Hello,<br></div=
><div><br></div><div>I encountered an issue while attempting to reclaim =
space from a heavily bloated table:<br style=3D"max-width:100%;height:au=
to;"></div><div><br style=3D"max-width:100%;height:auto;"></div><div>Ini=
tial analysis using&nbsp;<a href=3D"https://github.com/ioguix/pgsql-bloa=
t-estimation/blob/master/table/table_bloat.sql">https://github.com/iogui=
x/pgsql-bloat-estimation/blob/master/table/table_bloat.sql</a>&nbsp;indi=
cated approximately 600GB of bloat in the table.<br></div><div><br></div=
><div>I initiated a VACUUM FULL operation to reclaim this space, but dur=
ing execution, the server experienced a power failure.<br></div><div><br=
></div><div><br style=3D"max-width:100%;height:auto;"></div><div>After s=
erver recovery:<br style=3D"max-width:100%;height:auto;"></div><div><br =
style=3D"max-width:100%;height:auto;"></div><div>1. The database came ba=
ck online successfully<br style=3D"max-width:100%;height:auto;"></div><d=
iv>2. The space used by the in-progress table reconstruction was not fre=
ed<br style=3D"max-width:100%;height:auto;"></div><div>3. The newly wast=
ed space (bloat + partial VACUUM FULL) is not shown using the above quer=
y, only the initial bloat.<br style=3D"max-width:100%;height:auto;"></di=
v><div><br style=3D"max-width:100%;height:auto;"></div><div><br style=3D=
"max-width:100%;height:auto;"></div><div>Is there a way to reclaim this =
space without resorting to a full pg_dump and pg_restore cycle?&nbsp;<br=
></div><div><br></div><div>Thanks.<br></div><div><br></div><div>Best,<br=
></div><div>Pierre</div></body></html>
--1dd51c45685f420ba1169b547ccde4cb--