Content-Type: text/plain;
	charset=utf-8
Mime-Version: 1.0 (Mac OS X Mail 16.0 \(3774.600.62\))
Subject: Re: filesystem full during vacuum - space recovery issues
From: Scott Ribe <scott_ribe@elevated-dev.com>
In-Reply-To: 
 <CANzqJaD_bEyG9Fve0OPGUfAdjJL36_ZKm4aLQuMytCBp0yYOOA@mail.gmail.com>
Date: Thu, 18 Jul 2024 20:59:23 -0600
Cc: Pgsql-admin <pgsql-admin@lists.postgresql.org>
Content-Transfer-Encoding: quoted-printable
Message-Id: <AF87CB39-4713-424A-9B61-4CB3B8E590FC@elevated-dev.com>
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>
 <7dee2c00-6184-49c5-b303-58113c1d04c6@talentstack.to>
 <CANzqJaD_bEyG9Fve0OPGUfAdjJL36_ZKm4aLQuMytCBp0yYOOA@mail.gmail.com>
To: Thomas Simpson <ts@talentstack.to>
Archived-At: 
 <https://www.postgresql.org/message-id/AF87CB39-4713-424A-9B61-4CB3B8E590FC%40elevated-dev.com>
Precedence: bulk

1) Add new disk, use a new tablespace to move some big tables to it, to =
get back up and running
2) Replica server provisioned sufficiently for the db, pg_basebackup to =
it
3) Get streaming replication working
4) Switch over to new server

In other words, if you don't want terrible downtime, you need yet =
another server fully provisioned to be able to run your db.

--
Scott Ribe
scott_ribe@elevated-dev.com
https://www.linkedin.com/in/scottribe/


> On Jul 18, 2024, at 4:41=E2=80=AFPM, Ron Johnson =
<ronljohnsonjr@gmail.com> wrote:
>=20
> Multi-threaded writing to the same giant text file won't work too =
well, when all the data for one table needs to be together.
>=20
> Just temporarily add another disk for backups.
>=20
> On Thu, Jul 18, 2024 at 4:55=E2=80=AFPM Thomas Simpson =
<ts@talentstack.to> wrote:
>=20
> 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]=20
>> 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
>=20