Content-Type: multipart/alternative;
 boundary=------------TJOGvoUyt3WXIwHhKt1yhzD7
Message-Id: <d4cdd945-a48b-41e9-8c25-1b198d86b91e@talentstack.to>
Date: Thu, 18 Jul 2024 14:59:35 -0400
Mime-Version: 1.0
User-Agent: Mozilla Thunderbird
Subject: Re: filesystem full during vacuum - space recovery issues
To: pgsql-admin@lists.postgresql.org
References: <d141b2e2-797c-487a-b492-e4fbeeafad3e@talentstack.to>
 <ef82293d-b970-4678-ba18-ed1560a6922b@pscs.co.uk>
Content-Language: en-CA
From: Thomas Simpson <ts@talentstack.to>
In-Reply-To: <ef82293d-b970-4678-ba18-ed1560a6922b@pscs.co.uk>
Archived-At: <https://www.postgresql.org/message-id/d4cdd945-a48b-41e9-8c25-1b198d86b91e%40talentstack.to>
Precedence: bulk

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


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=20
>> space back to where it should be?
>>
>> I tried some scripts to map the data files to relations but this=20
>> didn't work as removing some files led to startup failure despite=20
>> them appearing to be unrelated to anything in the database - I had to=20
>> 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=20
> disk so the server crashes is that there are loads of data files=20
> containing the partially rebuilt table. Nothing 'internal' to=20
> PostgreSQL will point to those files as the internal pointers all=20
> change to the new table in an ACID way, so you should be able to=20
> delete them.
>
> You can usually find these relatively easily by looking in the=20
> relevant tablespace directory for the base filename for a new huge=20
> table (lots and lots of files with the same base name - eg looking for=20
> files called *.1000 will find you base filenames for relations over=20
> about 1TB) and checking to see if pg_filenode_relation() can't turn=20
> the filenode into a relation. If that's the case that they're not=20
> currently in use for a relation, then you should be able to just=20
> delete all those files
>
> Is this what you tried, or did your 'script to map data files to=20
> relations' do something else? You were a bit ambiguous about that part=20
> 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=20
(integer) and then comparing a directory listing for files which did not=20
match the relfilenode as candidates to remove.

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

Without these apparently unrelated files, the database did not start and=20
complained about them being missing, so I had to put them back.=C2=A0 =
This=20
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.=C2=A0 However, I =
doubt=20
I am the only person in the world that needs to dump and reload a large=20
database.=C2=A0 My thought is this is a weak point for PostgreSQL so it =
makes=20
sense to consider ways to improve the dump reload process, especially as=20
it's the last-resort upgrade path recommended in the upgrade guide and=20
the general fail-safe route to get out of trouble.

Thanks

Tom


> Paul
>
>
>

--------------TJOGvoUyt3WXIwHhKt1yhzD7
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><br>
    </p>
    <div class=3D"moz-cite-prefix">On 18-Jul-2024 11:19, Paul Smith*
      wrote:<br>
    </div>
    <blockquote type=3D"cite"
      cite=3D"mid:ef82293d-b970-4678-ba18-ed1560a6922b@pscs.co.uk">On
      15/07/2024 19:47, Thomas Simpson wrote:
      <br>
      <blockquote type=3D"cite">
        <br>
        My problem now is how do I get this space back to return my free
        space back to where it should be?
        <br>
        <br>
        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.
        <br>
        <br>
      </blockquote>
      I don't know what you tried to do
      <br>
      <br>
      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.
      <br>
      <br>
      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
      <br>
      <br>
      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.
      <br>
      <br>
    </blockquote>
    <p>[BTW, v9.6 which I know is old but this server is stuck there]<br>
    </p>
    <p>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.</p>
    <p>I moved these elsewhere (i.e. not delete, just move out the way
      so I could move them back in case of trouble).</p>
    <p>Without these apparently unrelated files, the database did not
      start and complained about them being missing, so I had to put
      them back.=C2=A0 This was despite not finding any reference to the
      filename/number in pg_class.<br>
    </p>
    <p>At that point I gave up since I cannot afford to make the problem
      worse!</p>
    <p>I know I'm stuck with the slow rebuild at this point.=C2=A0 =
However, I
      doubt I am the only person in the world that needs to dump and
      reload a large database.=C2=A0 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.<br>
    </p>
    <p>Thanks</p>
    <p>Tom<br>
    </p>
    <p><br>
    </p>
    <blockquote type=3D"cite"
      cite=3D"mid:ef82293d-b970-4678-ba18-ed1560a6922b@pscs.co.uk">Paul
      <br>
      <br>
      <br>
      <br>
    </blockquote>
  </body>
</html>

--------------TJOGvoUyt3WXIwHhKt1yhzD7--