Content-Type: multipart/alternative;
 boundary=------------PJP9xEYAPgH0BMQgu04ucoZg
Message-Id: <38b20a9f-ef8a-486a-bb7d-f7a7be20f98d@talentstack.to>
Date: Wed, 17 Jul 2024 09:24:43 -0400
Mime-Version: 1.0
User-Agent: Mozilla Thunderbird
Subject: Re: filesystem full during vacuum - space recovery issues
To: Laurenz Albe <laurenz.albe@cybertec.at>, pgsql-admin@lists.postgresql.org
References: <d141b2e2-797c-487a-b492-e4fbeeafad3e@talentstack.to>
 <f08b1edf09b40062d355225fe1502ecf04571075.camel@cybertec.at>
Content-Language: en-CA
From: Thomas Simpson <ts@talentstack.to>
In-Reply-To: <f08b1edf09b40062d355225fe1502ecf04571075.camel@cybertec.at>
Archived-At: 
 <https://www.postgresql.org/message-id/38b20a9f-ef8a-486a-bb7d-f7a7be20f98d%40talentstack.to>
Precedence: bulk

--------------PJP9xEYAPgH0BMQgu04ucoZg
Content-Type: text/plain; charset=utf-8; format=flowed
Content-Transfer-Encoding: quoted-printable

Thanks Laurenz & Imran for your comments.

My responses inline below.

Thanks

Tom


On 15-Jul-2024 20:58, Laurenz Albe wrote:
> 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=C2=A0files 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 corruptio=
n.

No, I just removed the excess already archived WALs to get space and=20
restarted.=C2=A0 The vacuum full that was running had created files for =
the=20
large table it was processing and these are still hanging around eating=20
space without doing anything useful.=C2=A0 The shutdown prevented the=20
rollback cleanly removing them which seems to be the core problem.

> 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.

My issue now is the dump & reload is taking a huge time; I know the=20
hardware is capable of multi-GB/s throughput but the reload is taking a=20
long time - projected to be about 10 days to reload at the current rate=20
(about 30Mb/sec).=C2=A0 The old server and new server have a 10G link =
between=20
them and storage is SSD backed, so the hardware is capable of much much=20
more than it is doing now.

Is there a way to improve the reload performance?=C2=A0 Tuning of any =
type -=20
even if I need to undo it later once the reload is done.

My backups were in progress when all the issues happened, so they're not=20
such a good starting point and I'd actually prefer the clean reload=20
since this DB has been through multiple upgrades (without reloads) until=20
now so I know it's not especially clean. The size has always prevented=20
the full reload before but the database is relatively low traffic now so=20
I can afford some time to reload, but ideally not 10 days.

> Yours,
> Laurenz Albe

--------------PJP9xEYAPgH0BMQgu04ucoZg
Content-Type: text/html; charset=utf-8
Content-Transfer-Encoding: quoted-printable

<!DOCTYPE html>
<html>
  <head>
    <meta http-equiv=3D"Content-Type" content=3D"text/html; charset=3DUTF=
-8">
  </head>
  <body>
    <p>Thanks Laurenz &amp; Imran for your comments.</p>
    <p>My responses inline below.</p>
    <p>Thanks</p>
    <p>Tom</p>
    <p><br>
    </p>
    <div class=3D"moz-cite-prefix">On 15-Jul-2024 20:58, Laurenz Albe
      wrote:<br>
    </div>
    <blockquote type=3D"cite"
cite=3D"mid:f08b1edf09b40062d355225fe1502ecf04571075.camel@cybertec.at">
      <pre class=3D"moz-quote-pre" wrap=3D"">On Mon, 2024-07-15 at 14:47 =
-0400, Thomas Simpson wrote:
</pre>
      <blockquote type=3D"cite">
        <pre class=3D"moz-quote-pre" wrap=3D"">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?
</pre>
      </blockquote>
      <pre class=3D"moz-quote-pre" wrap=3D"">
That reads like the sad old story: "cleaning down" WAL files - you mean =
deleting the
very=C2=A0files 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.
</pre>
    </blockquote>
    <p>No, I just removed the excess already archived WALs to get space
      and restarted.=C2=A0 The vacuum full that was running had created =
files
      for the large table it was processing and these are still hanging
      around eating space without doing anything useful.=C2=A0 The =
shutdown
      prevented the rollback cleanly removing them which seems to be the
      core problem.<br>
    </p>
    <blockquote type=3D"cite"
cite=3D"mid:f08b1edf09b40062d355225fe1502ecf04571075.camel@cybertec.at">
      <pre class=3D"moz-quote-pre" wrap=3D"">
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.
</pre>
    </blockquote>
    <p>My issue now is the dump &amp; reload is taking a huge time; I
      know the hardware is capable of multi-GB/s throughput but the
      reload is taking a long time - projected to be about 10 days to
      reload at the current rate (about 30Mb/sec).=C2=A0 The old server =
and
      new server have a 10G link between them and storage is SSD backed,
      so the hardware is capable of much much more than it is doing =
now.<br>
    </p>
    <p>Is there a way to improve the reload performance?=C2=A0 Tuning of =
any
      type - even if I need to undo it later once the reload is done.</p>
    <p>My backups were in progress when all the issues happened, so
      they're not such a good starting point and I'd actually prefer the
      clean reload since this DB has been through multiple upgrades
      (without reloads) until now so I know it's not especially clean.=C2=
=A0
      The size has always prevented the full reload before but the
      database is relatively low traffic now so I can afford some time
      to reload, but ideally not 10 days.<br>
    </p>
    <blockquote type=3D"cite"
cite=3D"mid:f08b1edf09b40062d355225fe1502ecf04571075.camel@cybertec.at">
      <pre class=3D"moz-quote-pre" wrap=3D"">
Yours,
Laurenz Albe
</pre>
    </blockquote>
  </body>
</html>

--------------PJP9xEYAPgH0BMQgu04ucoZg--