Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It only takes a minute to sign up.
Sign up to join this community
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)
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
But, quoting the manual:
To vacuum a table, one must ordinarily be the table's owner or a superuser. However, database owners are allowed to vacuum all tables in their databases, except shared catalogs. (The restriction for shared catalogs means that a true database-wide
VACUUMcan only be performed by a superuser.)VACUUMwill skip over any tables that the calling user does not have permission to vacuum.
Meaning, if you have issued VACUUM FULL as superuser, shared objects may also be affected, and you need to dump and restore the whole cluster to be sure: (pg_dumpall). (Typically, the lion's share of zombi-files would be located in the directory of the affected database, though.)
I thought of creating a new database based on the afflicted one as TEMPLATE directly, which is much faster than a full dump/restore cycle. (Would require sufficient available storage, of course). Unfortunately, since that just copies physical files of a database, it would include the zombi-files you are trying to get rid of.
Remember that you need sufficient free space for a complete pg_dump. The dump file can be bigger or smaller than the database itself. Since you have problems with available free storage, consider dumping to a different physical location.
Maybe that's not exactly the solution to your problem, but I went through the same problem last week.
I've made some testing in our staging environment, and after upgrading the PostgreSQL major version we got a 10% space savings, nothing more than just running pg_upgrade from version 12 to 13.
