5

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)

Improve this question
2
  • "Is there any way to free the space used by the failed VACUUM FULL run?" - after vacuum full failed, it will release that space immediately. There must be something else going on that you are not telling us. Commented Jan 18, 2022 at 7:06
  • I added the options that I used when vacuuming. I ran it and noticed that it failed after filling the disk completely. I have no idea why the space wasn't released. Commented Jan 18, 2022 at 7:10

3 Answers 3

Reset to default
7

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:

  1. pg_dump the database

  2. drop the database and create it again (empty)

  3. restore the dump from the first step

Improve this answer
1

What Laurenz said.

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 VACUUM can only be performed by a superuser.) VACUUM will 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.

Improve this answer
1
  • Same issue with pg_basebackup I suppose? Does it mean that if a vacuum fails on a primary all the replicas (using streaming replication) will be affected too? Commented Jan 19, 2022 at 8:40
0

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.

Improve this answer

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.