Content-Type: multipart/alternative;
 boundary=------------RVbB3CWM0f6FwWwPrSLKqHBd
Message-Id: <7dee2c00-6184-49c5-b303-58113c1d04c6@talentstack.to>
Date: Thu, 18 Jul 2024 16:53:23 -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>
 <d4cdd945-a48b-41e9-8c25-1b198d86b91e@talentstack.to>
 <CANzqJaCpSNZU8AaHPLS2Ms7WzsqBX0L1ZufD8cmUaLi78uA1=A@mail.gmail.com>
Content-Language: en-CA
From: Thomas Simpson <ts@talentstack.to>
In-Reply-To: <CANzqJaCpSNZU8AaHPLS2Ms7WzsqBX0L1ZufD8cmUaLi78uA1=A@mail.gmail.com>
Archived-At: <https://www.postgresql.org/message-id/7dee2c00-6184-49c5-b303-58113c1d04c6%40talentstack.to>
Precedence: bulk

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


On 18-Jul-2024 16:32, Ron Johnson wrote:
> On Thu, Jul 18, 2024 at 3:01=E2=80=AFPM Thomas Simpson <ts@talentstack.=
to> wrote:
> [snip]
>
>     [BTW, v9.6 which I know is old but this server is stuck there]
>
> [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 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.
>
> =C2=A0No database does fast single-threaded backups.

Agreed.=C2=A0 My thought is that is should be possible for a 'new =
dumpall' to=20
be multi-threaded.

Something like :

* Set number of threads on 'source' (perhaps by querying a listening=20
destination for how many threads it is prepared to accept via a control=20
port)

* Select each database in turn

* Organize the tables which do not have references themselves

* Send each table separately in each thread (or queue them until a=20
thread is available)=C2=A0 ('Stage 1')

* Rendezvous stage 1 completion (pause sending, wait until feedback from=20
destination confirming all completed) so we have a known consistent=20
state that is safe to proceed to subsequent tables

* Work through tables that do refer to the previously sent in the same=20
way (since the tables they reference exist and have their data) ('Stage =
2')

* Repeat progressively until all tables are done ('Stage 3', 4 etc. as=20
necessary)

The current dumpall is essentially doing this table organization=20
currently [minus stage checkpoints/multi-thread] otherwise the dump/load=20
would not work.=C2=A0 It may even be doing a lot of this for 'directory'=20
mode?=C2=A0 The change here is organizing n threads to process them=20
concurrently where possible and coordinating the pipes so they only send=20
data which can be accepted.

The destination would need to have a multi-thread listen and co-ordinate=20
with the sender on some control channel so feed back completion of each=20
stage.

Something like a destination host and control channel port to establish=20
the pipes and create additional netcat pipes on incremental ports above=20
the control port for each thread used.

Dumpall seems like it could be a reasonable start point since it is=20
already doing the complicated bits of serializing the dump data so it=20
can be consistently loaded.

Probably not really an admin question at this point, more a feature=20
enhancement.

Is there anything fundamentally wrong that someone with more intimate=20
knowledge of dumpall could point out?

Thanks

Tom


--------------RVbB3CWM0f6FwWwPrSLKqHBd
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 16:32, Ron Johnson
      wrote:<br>
    </div>
    <blockquote type=3D"cite"
cite=3D"mid:CANzqJaCpSNZU8AaHPLS2Ms7WzsqBX0L1ZufD8cmUaLi78uA1=3DA@mail.gm=
ail.com">
      <meta http-equiv=3D"content-type" content=3D"text/html; charset=3DU=
TF-8">
      <div dir=3D"ltr">
        <div dir=3D"ltr">On Thu, Jul 18, 2024 at 3:01=E2=80=AFPM Thomas =
Simpson
          &lt;<a href=3D"mailto:ts@talentstack.to" moz-do-not-send=3D"tru=
e"
            class=3D"moz-txt-link-freetext">ts@talentstack.to</a>&gt;
          wrote:</div>
        <div>[snip]</div>
        <div class=3D"gmail_quote">
          <blockquote class=3D"gmail_quote"
style=3D"margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204);=
padding-left:1ex">
            <div>
              <p>[BTW, v9.6 which I know is old but this server is stuck
                there]<br>
              </p>
            </div>
          </blockquote>
          <div>[snip]=C2=A0</div>
          <blockquote class=3D"gmail_quote"
style=3D"margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204);=
padding-left:1ex">
            <div>
              <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.</p>
            </div>
          </blockquote>
          <div>=C2=A0No database does fast single-threaded backups.</div>
        </div>
      </div>
    </blockquote>
    <p>Agreed.=C2=A0 My thought is that is should be possible for a 'new
      dumpall' to be multi-threaded.</p>
    <p>Something like :</p>
    <p>* Set number of threads on 'source' (perhaps by querying a
      listening destination for how many threads it is prepared to
      accept via a control port)<br>
    </p>
    <p>* Select each database in turn</p>
    <p>* Organize the tables which do not have references themselves</p>
    <p>* Send each table separately in each thread (or queue them until
      a thread is available)=C2=A0 ('Stage 1')<br>
    </p>
    <p>* Rendezvous stage 1 completion (pause sending, wait until
      feedback from destination confirming all completed) so we have a
      known consistent state that is safe to proceed to subsequent
      tables<br>
    </p>
    <p>* Work through tables that do refer to the previously sent in the
      same way (since the tables they reference exist and have their
      data) ('Stage 2')<br>
    </p>
    <p>* Repeat progressively until all tables are done ('Stage 3', 4
      etc. as necessary)<br>
    </p>
    <p>The current dumpall is essentially doing this table organization
      currently [minus stage checkpoints/multi-thread] otherwise the
      dump/load would not work.=C2=A0 It may even be doing a lot of this =
for
      'directory' mode?=C2=A0 The change here is organizing n threads to
      process them concurrently where possible and coordinating the
      pipes so they only send data which can be accepted.</p>
    <p>The destination would need to have a multi-thread listen and
      co-ordinate with the sender on some control channel so feed back
      completion of each stage.</p>
    <p>Something like a destination host and control channel port to
      establish the pipes and create additional netcat pipes on
      incremental ports above the control port for each thread used.</p>
    <p>Dumpall seems like it could be a reasonable start point since it
      is already doing the complicated bits of serializing the dump data
      so it can be consistently loaded.<br>
    </p>
    <p>Probably not really an admin question at this point, more a
      feature enhancement.</p>
    <p>Is there anything fundamentally wrong that someone with more
      intimate knowledge of dumpall could point out?</p>
    <p>Thanks</p>
    <p>Tom</p>
    <p><br>
    </p>
  </body>
</html>

--------------RVbB3CWM0f6FwWwPrSLKqHBd--