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

Hannes Erven <hannes(at)erven(dot)at> writes:
> Am 10.02.19 um 16:41 schrieb Tom Lane:
>> What do you mean by "crash" exactly?

> Here's the exact log (the pgadmin process was running the VACCUM FULL):
> 2019-02-09 23:44:53.516 CET [20341] @/ <> PANIC: could not write to
> file "pg_wal/xlogtemp.20341": No space left on device

Ah, so Andrew was correct: we panicked due to lack of WAL space, and
that explains why the vacuuming process didn't have an opportunity
to delete the files belonging to the uncommitted new relation.

If you're concerned about this sort of thing, you might consider putting
the WAL directory on a separate filesystem from the main database files.

>>> 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.

> OK, I see. I came up with this:

I think this isn't accounting for files in non-default
tablespaces, but maybe that's not a problem for your usage.

> Is this interesting enough to further investigate?

It's a pretty well-understood dynamic, I believe. Perhaps we should
try harder to recover cleanly, but I don't know of anyone putting
effort into the case.

regards, tom lane