Re: Unused files in the database directory after crashed VACUUM FULL

Hannes Erven <hannes(at)erven(dot)at> writes:
> I've just had a "VACUUM FULL <table>" crash due to 100% disk usage.
> Clearly my fault, I was expecting the new table to be small enough.

What do you mean by "crash" exactly? A normal transactional failure
should've cleaned up orphaned files. I suppose if the kernel decided to
kill -9 the vacuum process, that wouldn't happen --- but that's not
the usual response to out-of-disk-space.

> After freeing up space, restarting the cluster and issuing another
> VACCUM FULL, I noticed that the cluster was way bigger that it should be.
> In the base/<db>/ folder, there was a large number of files with one
> certain number that pg_filenode_relation() could not turn into a
> relation. As that number was just a bit smaller that the
> pg_relation_filepath() of the table I was working on, I guess these were
> the remains of the failed VACUUM FULL operation?
> I removed those files and a VACCUM ANALYZE of the whole database went fine.

> So... is this the expected behaviour? Is there a safe procedure how to
> check/clean up "unnecessary" files in the cluster directory?

You just described it --- verify with pg_filenode_relation that the
file doesn't correspond to any pg_class entry, then manually delete.

(There is a crash-recovery phase that does something similar, but
I think it's only designed to clean up temporary files.)

regards, tom lane