From: Laurenz Albe <laurenz.albe@cybertec.at>
To: Thomas Simpson <ts@talentstack.to>
To: pgsql-admin@lists.postgresql.org
Subject: Re: filesystem full during vacuum - space recovery issues
Date: Tue, 16 Jul 2024 02:58:43 +0200
Message-ID: <f08b1edf09b40062d355225fe1502ecf04571075.camel@cybertec.at> (raw)
In-Reply-To: <d141b2e2-797c-487a-b492-e4fbeeafad3e@talentstack.to>
References: <d141b2e2-797c-487a-b492-e4fbeeafad3e@talentstack.to>
On Mon, 2024-07-15 at 14:47 -0400, Thomas Simpson wrote:
> I have a large database (multi TB) which had a vacuum full running but the database
> ran out of space during the rebuild of one of the large data tables.
>
> Cleaning down the WAL files got the database restarted (an archiving problem led to
> the initial disk full).
>
> However, the disk space is still at 99% as it appears the large table rebuild files
> are still hanging around using space and have not been deleted.
>
> 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.
>
> Any suggestions here?
That reads like the sad old story: "cleaning down" WAL files - you mean deleting the
very files that would have enabled PostgreSQL to recover from the crash that was
caused by the full file system.
Did you run "pg_resetwal"? If yes, that probably led to data corruption.
The above are just guesses. Anyway, there is no good way to get rid of the files
that were left behind after the crash. The reliable way of doing so is also the way
to get rid of potential data corruption caused by "cleaning down" the database:
pg_dump the whole thing and restore the dump to a new, clean cluster.
Yes, that will be a painfully long down time. An alternative is to restore a backup
taken before the crash.
Yours,
Laurenz Albe
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: laurenz.albe@cybertec.at, ts@talentstack.to, pgsql-admin@lists.postgresql.org
Subject: Re: filesystem full during vacuum - space recovery issues
In-Reply-To: <f08b1edf09b40062d355225fe1502ecf04571075.camel@cybertec.at>
* 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