public inbox for [email protected]  

help / color / mirror / Atom feed

From: Thomas Simpson <[email protected]>
To: [email protected]
Subject: Re: filesystem full during vacuum - space recovery issues
Date: Thu, 18 Jul 2024 16:53:23 -0400
Message-ID: <[email protected]> (raw)
In-Reply-To: <CANzqJaCpSNZU8AaHPLS2Ms7WzsqBX0L1ZufD8cmUaLi78uA1=A@mail.gmail.com>
References: <[email protected]>
	<[email protected]>
	<[email protected]>
	<CANzqJaCpSNZU8AaHPLS2Ms7WzsqBX0L1ZufD8cmUaLi78uA1=A@mail.gmail.com>


On 18-Jul-2024 16:32, Ron Johnson wrote:
> On Thu, Jul 18, 2024 at 3:01 PM Thomas Simpson <[email protected]> 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.  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.
>
>  No database does fast single-threaded backups.

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

Something like :

* 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)

* 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 
thread is available)  ('Stage 1')

* 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

* 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')

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

The current dumpall is essentially doing this table organization 
currently [minus stage checkpoints/multi-thread] otherwise the dump/load 
would not work.  It may even be doing a lot of this for 'directory' 
mode?  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.

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.

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.

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.

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

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

Thanks

Tom

view thread (4+ messages)  latest in thread

reply

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: [email protected]
  Cc: [email protected], [email protected]
  Subject: Re: filesystem full during vacuum - space recovery issues
  In-Reply-To: <[email protected]>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox