Content-Type: text/plain;
	charset=us-ascii
Mime-Version: 1.0 (Mac OS X Mail 16.0 \(3774.600.62\))
Subject: Re: Enhance pg_dump multi-threaded streaming (WAS: Re: filesystem
 full during vacuum - space recovery issues)
From: Scott Ribe <scott_ribe@elevated-dev.com>
In-Reply-To: <95f5bff5-b392-4c2f-9708-8fd5547c620c@talentstack.to>
Date: Fri, 19 Jul 2024 07:26:47 -0600
Cc: Pgsql-admin <pgsql-admin@lists.postgresql.org>,
 pgsql-hackers@lists.postgresql.org
Content-Transfer-Encoding: quoted-printable
Message-Id: <6F46CFEB-3C3A-4CEC-89DF-57D8225A5863@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>
 <95f5bff5-b392-4c2f-9708-8fd5547c620c@talentstack.to>
To: Thomas Simpson <ts@talentstack.to>
Archived-At: <https://www.postgresql.org/message-id/6F46CFEB-3C3A-4CEC-89DF-57D8225A5863%40elevated-dev.com>
Precedence: bulk

Do you actually have 100G networking between the nodes? Because if not, =
a single CPU should be able to saturate 10G.=20

Likewise the receiving end would need disk capable of keeping up. Which =
brings up the question, why not write to disk, but directly to the =
destination rather than write locally then copy?

Do you require dump-reload because of suspected corruption? That's a =
tough one. But if not, if the goal is just to get up and running on a =
new server, why not pg_basebackup, streaming replica, promote? That =
depends on the level of data modification activity being low enough that =
pg_basebackup can keep up with WAL as it's generated and apply it faster =
than new WAL comes in, but given that your server is currently keeping =
up with writing that much WAL and flushing that many changes, seems =
likely it would keep up as long as the network connection is fast =
enough. Anyway, in that scenario, you don't need to care how long =
pg_basebackup takes.

If you do need a dump/reload because of suspected corruption, the only =
thing I can think of is something like doing it a table at a =
time--partitioning would help here, if practical.=