Subject: Re: filesystem full during vacuum - space recovery issues

From: Thomas Simpson <[email-masked]>
Date: Thu, 18 Jul 2024 14:59:35 -0400

On 18-Jul-2024 11:19, Paul Smith* wrote:
> 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.
>
[BTW, v9.6 which I know is old but this server is stuck there]

Yes, I was querying relfilenode from pg_class to get the filename 
(integer) and then comparing a directory listing for files which did not 
match the relfilenode as candidates to remove.

I moved these elsewhere (i.e. not delete, just move out the way so I 
could move them back in case of trouble).

Without these apparently unrelated files, the database did not start and 
complained about them being missing, so I had to put them back.  This 
was despite not finding any reference to the filename/number in pg_class.

At that point I gave up since I cannot afford to make the problem worse!

I know I'm stuck with the slow rebuild at this point.  However, I doubt 
I am the only person in the world that needs to dump and reload a large 
database.  My thought is this is a weak point for PostgreSQL so it makes 
sense to consider ways to improve the dump reload process, especially as 
it's the last-resort upgrade path recommended in the upgrade guide and 
the general fail-safe route to get out of trouble.

Thanks

Tom

> Paul

---

From: Ron Johnson <[email-masked]>
Date: Thu, 18 Jul 2024 16:32:49 -0400

On Thu, Jul 18, 2024 at 3:01 PM Thomas Simpson <[email-masked]> wrote:
[snip]

> [snip]

> I know I'm stuck with the slow rebuild at this point.  However, I doubt I
> am the only person in the world that needs to dump and reload a large
> it's the last-resort upgrade path recommended in the upgrade guide and the
> general fail-safe route to get out of trouble.
>
 No database does fast single-threaded backups.