patternsqlMinor
Reclaim space after a failed VACUUM FULL
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
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
The exception to that rule is if the server crashed while
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:
-
-
drop the database and create it again (empty)
-
restore the dump from the first step
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.