From: Paul Smith* <paul@pscs.co.uk>
To: pgsql-admin@lists.postgresql.org
Subject: Re: filesystem full during vacuum - space recovery issues
Date: Thu, 18 Jul 2024 16:19:31 +0100
Message-ID: <ef82293d-b970-4678-ba18-ed1560a6922b@pscs.co.uk> (raw)
In-Reply-To: <d141b2e2-797c-487a-b492-e4fbeeafad3e@talentstack.to>
References: <d141b2e2-797c-487a-b492-e4fbeeafad3e@talentstack.to>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-admin@postgresql.org
Cc: paul@pscs.co.uk, pgsql-admin@lists.postgresql.org
Subject: Re: filesystem full during vacuum - space recovery issues
In-Reply-To: <ef82293d-b970-4678-ba18-ed1560a6922b@pscs.co.uk>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox