Re: filesystem full during vacuum - space recovery issues

On 15/07/2024 19:47, Thomas Simpson wrote:
>
> My problem now is how do I get this space back to return my free space
> back to where it should be?
>
> I tried some scripts to map the data files to relations but this
> didn't work as removing some files led to startup failure despite them
> appearing to be unrelated to anything in the database - I had to put
> them back and then startup worked.
>
I don't know what you tried to do

What would normally happen on a failed VACUUM FULL that fills up the
disk so the server crashes is that there are loads of data files
containing the partially rebuilt table. Nothing 'internal' to PostgreSQL
will point to those files as the internal pointers all change to the new
table in an ACID way, so you should be able to delete them.

You can usually find these relatively easily by looking in the relevant
tablespace directory for the base filename for a new huge table (lots
and lots of files with the same base name - eg looking for files called
*.1000 will find you base filenames for relations over about 1TB) and
checking to see if pg_filenode_relation() can't turn the filenode into a
relation. If that's the case that they're not currently in use for a
relation, then you should be able to just delete all those files

Is this what you tried, or did your 'script to map data files to
relations' do something else? You were a bit ambiguous about that part
of things.

Paul