Message-ID: <ef82293d-b970-4678-ba18-ed1560a6922b@pscs.co.uk>
Date: Thu, 18 Jul 2024 16:19:31 +0100
MIME-Version: 1.0
User-Agent: Mozilla Thunderbird
Subject: Re: filesystem full during vacuum - space recovery issues
Content-Language: en-GB
To: pgsql-admin@lists.postgresql.org
References: <d141b2e2-797c-487a-b492-e4fbeeafad3e@talentstack.to>
From: Paul Smith* <paul@pscs.co.uk>
In-Reply-To: <d141b2e2-797c-487a-b492-e4fbeeafad3e@talentstack.to>
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 7bit
Archived-At: 
 <https://www.postgresql.org/message-id/ef82293d-b970-4678-ba18-ed1560a6922b%40pscs.co.uk>
Precedence: bulk

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