HiveBrain v1.2.0
Get Started
← Back to all entries
patternsqlMinor

Reclaim space after a failed VACUUM FULL

Submitted by: @import:stackexchange-dba··
0
Viewed 0 times
afterspacevacuumfullreclaimfailed

Problem

I made small miscalculation and my VACUUM FULL ended up filling the disks of the instance. I managed to free enough space to restart postgres but I am afraid it won't be enough to run VACUUM FULL to completion.

Is there any way to free the space used by the failed VACUUM FULL run?

PS: Here is the options that I used VACUUM (FULL, VERBOSE, ANALYZE, INDEX_CLEANUP)

Solution

If VACUUM (FULL) fails, all the disk space it filled will be released.

The exception to that rule is if the server crashed while VACUUM (FULL) ran (which can happen if you run out of disk space). After a crash (and crash recovery), orphaned files can be left behind on the disk. There is no simple way to get rid of these files, and you certainly cannot do it via SQL.

If you know PostgreSQL very well, you may be able to figure out which files are safe to delete, but you run the risk of terminally corrupting your database if you delete a wrong file.

The only safe way I know is:

-
pg_dump the database

-
drop the database and create it again (empty)

-
restore the dump from the first step

Context

StackExchange Database Administrators Q#306203, answer score: 5

Revisions (0)

No revisions yet.