agora inbox for pgsql-admin@postgresql.org
help / color / mirror / Atom feedEnhance pg_dump multi-threaded streaming (WAS: Re: filesystem full during vacuum - space recovery issues)
11+ messages / 5 participants
[nested] [flat]
* Enhance pg_dump multi-threaded streaming (WAS: Re: filesystem full during vacuum - space recovery issues)
@ 2024-07-18 23:08 Thomas Simpson <ts@talentstack.to>
0 siblings, 2 replies; 11+ messages in thread
From: Thomas Simpson @ 2024-07-18 23:08 UTC (permalink / raw)
To: pgsql-admin@lists.postgresql.org; pgsql-hackers@lists.postgresql.org
[Added cross post to pgsql-hackers@lists.postgresql.org - background is
multi-TB database needs recovered via pgdumpall & reload, thoughts on
ways to make pg_dump scale to multi-thread to expedite loading to a new
cluster. Straight dump to a file is impractical as the dump will be
>200TB; hackers may be a better home for the discussion than current
admin list]
Hi Ron
On 18-Jul-2024 18:41, Ron Johnson wrote:
> 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.
>
> Just temporarily add another disk for backups.
>
For clarity, I'm not proposing multi threaded writing to one file; the
proposal is a new special mode which specifically makes multiple output
streams across *network sockets* to a listener which is listening on the
other side. The goal is avoiding any files at all and only using
multiple network streams to gain multi-threaded processing with some
co-ordination to keep things organized and consistent.
This would really be specifically for the use-case of dump/reload
upgrade or recreate rather than everyday use. And particularly for very
large databases.
Looking at pg_dump.c it's doing the baseline organization but the
extension would be adding the required coordination with the
destination. So, for a huge table (I have many) these would go in
different streams but if there is a dependency (FK relations etc) the
checkpoint needs to ensure those are met before proceeding. Worst case
scenario it would end up using only 1 thread but it would be very
unusual to have a database where every table depends on another table
all the way down.
In theory at least, some gains should be achieved for typical databases
where a degree of parallelism is possible.
Thanks
Tom
> On Thu, Jul 18, 2024 at 4:55 PM Thomas Simpson <ts@talentstack.to> wrote:
>
>
> On 18-Jul-2024 16:32, Ron Johnson wrote:
>> On Thu, Jul 18, 2024 at 3:01 PM 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. 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
>
>
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Enhance pg_dump multi-threaded streaming (WAS: Re: filesystem full during vacuum - space recovery issues)
@ 2024-07-19 12:21 Ron Johnson <ronljohnsonjr@gmail.com>
parent: Thomas Simpson <ts@talentstack.to>
1 sibling, 0 replies; 11+ messages in thread
From: Ron Johnson @ 2024-07-19 12:21 UTC (permalink / raw)
To: Pgsql-admin <pgsql-admin@lists.postgresql.org>
200TB... how do you currently back up your database?
On Fri, Jul 19, 2024 at 5:08 AM Thomas Simpson <ts@talentstack.to> wrote:
> [Added cross post to pgsql-hackers@lists.postgresql.org - background is
> multi-TB database needs recovered via pgdumpall & reload, thoughts on ways
> to make pg_dump scale to multi-thread to expedite loading to a new
> cluster. Straight dump to a file is impractical as the dump will be
> >200TB; hackers may be a better home for the discussion than current admin
> list]
>
> Hi Ron
>
> On 18-Jul-2024 18:41, Ron Johnson wrote:
>
> 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.
>
> Just temporarily add another disk for backups.
>
> For clarity, I'm not proposing multi threaded writing to one file; the
> proposal is a new special mode which specifically makes multiple output
> streams across *network sockets* to a listener which is listening on the
> other side. The goal is avoiding any files at all and only using multiple
> network streams to gain multi-threaded processing with some co-ordination
> to keep things organized and consistent.
>
> This would really be specifically for the use-case of dump/reload upgrade
> or recreate rather than everyday use. And particularly for very large
> databases.
>
> Looking at pg_dump.c it's doing the baseline organization but the
> extension would be adding the required coordination with the destination.
> So, for a huge table (I have many) these would go in different streams but
> if there is a dependency (FK relations etc) the checkpoint needs to ensure
> those are met before proceeding. Worst case scenario it would end up using
> only 1 thread but it would be very unusual to have a database where every
> table depends on another table all the way down.
>
> In theory at least, some gains should be achieved for typical databases
> where a degree of parallelism is possible.
> Thanks
>
> Tom
>
>
>
> On Thu, Jul 18, 2024 at 4:55 PM Thomas Simpson <ts@talentstack.to> wrote:
>
>>
>> On 18-Jul-2024 16:32, Ron Johnson wrote:
>>
>> On Thu, Jul 18, 2024 at 3:01 PM 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. 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
>>
>>
>>
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Enhance pg_dump multi-threaded streaming (WAS: Re: filesystem full during vacuum - space recovery issues)
@ 2024-07-19 13:26 Scott Ribe <scott_ribe@elevated-dev.com>
parent: Thomas Simpson <ts@talentstack.to>
1 sibling, 1 reply; 11+ messages in thread
From: Scott Ribe @ 2024-07-19 13:26 UTC (permalink / raw)
To: Thomas Simpson <ts@talentstack.to>; +Cc: Pgsql-admin <pgsql-admin@lists.postgresql.org>; pgsql-hackers@lists.postgresql.org
Do you actually have 100G networking between the nodes? Because if not, a single CPU should be able to saturate 10G.
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.
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Enhance pg_dump multi-threaded streaming (WAS: Re: filesystem full during vacuum - space recovery issues)
@ 2024-07-19 13:46 Thomas Simpson <ts@talentstack.to>
parent: Scott Ribe <scott_ribe@elevated-dev.com>
0 siblings, 2 replies; 11+ messages in thread
From: Thomas Simpson @ 2024-07-19 13:46 UTC (permalink / raw)
To: Scott Ribe <scott_ribe@elevated-dev.com>; pgsql-hackers@lists.postgresql.org
Hi Scott,
I realize some of the background was snipped on what I sent to the
hacker list, I'll try to fill in the details.
Short background is very large database ran out of space during vacuum
full taking down the server. There is a replica which was applying the
WALs and so it too ran out of space. On restart after clearing some
space, the database came back up but left over the in-progress rebuild
files. I've cleared that replica and am using it as my rebuild target
just now.
Trying to identify the 'orphan' files and move them away always led to
the database spotting the supposedly unused files having gone and
refusing to start, so I had no successful way to clean up and get space
back.
Last resort after discussion is pg_dumpall & reload. I'm doing this via
a network pipe (netcat) as I do not have the vast amount of storage
necessary for the dump file to be stored (in any format).
On 19-Jul-2024 09:26, Scott Ribe wrote:
> Do you actually have 100G networking between the nodes? Because if not, a single CPU should be able to saturate 10G.
Servers connect via 10G WAN; sending is not the issue, it's application
of the incoming stream on the destination which is bottlenecked.
>
> 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?
In this case, it's not a local write, it's piped via netcat.
> 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.
The basebackup is, to the best of my understanding, essentially just
copying the database files. Since the failed vacuum has left extra
files, my expectation is these too would be copied, leaving me in the
same position I started in. If I'm wrong, please tell me as that would
be vastly quicker - it is how I originally set up the replica and it
took only a few hours on the 10G link.
The inability to get a clean start if I move any files out the way leads
me to be concerned for some underlying corruption/issue and the
recommendation earlier in the discussion was opt for dump/reload as the
fail-safe.
Resigned to my fate, my thoughts were to see if there is a way to
improve the dump-reload approach for the future. Since dump-reload is
the ultimate upgrade suggestion in the documentation, it seems
worthwhile to see if there is a way to improve the performance of that
especially as very large databases like mine are a thing with
PostgreSQL. From a quick review of pg_dump.c (I'm no expert on it
obviously), it feels like it's already doing most of what needs done and
the addition is some sort of multi-thread coordination with a restore
client to ensure each thread can successfully complete each task it has
before accepting more work. I realize that's actually difficult to
implement.
Thanks
Tom
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Enhance pg_dump multi-threaded streaming (WAS: Re: filesystem full during vacuum - space recovery issues)
@ 2024-07-19 19:34 Scott Ribe <scott_ribe@elevated-dev.com>
parent: Thomas Simpson <ts@talentstack.to>
1 sibling, 1 reply; 11+ messages in thread
From: Scott Ribe @ 2024-07-19 19:34 UTC (permalink / raw)
To: Thomas Simpson <ts@talentstack.to>; +Cc: Pgsql-admin <pgsql-admin@lists.postgresql.org>
> On Jul 19, 2024, at 7:46 AM, Thomas Simpson <ts@talentstack.to> wrote:
>
> I realize some of the background was snipped on what I sent to the hacker list, I'll try to fill in the details.
I was gone from my computer for a day and lost track of the thread.
Perhaps logical replication could help you out here?
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Enhance pg_dump multi-threaded streaming (WAS: Re: filesystem full during vacuum - space recovery issues)
@ 2024-07-19 20:23 Thomas Simpson <ts@talentstack.to>
parent: Scott Ribe <scott_ribe@elevated-dev.com>
0 siblings, 1 reply; 11+ messages in thread
From: Thomas Simpson @ 2024-07-19 20:23 UTC (permalink / raw)
To: Scott Ribe <scott_ribe@elevated-dev.com>; +Cc: pgsql-admin@lists.postgresql.org
Hi Scott
On 19-Jul-2024 15:34, Scott Ribe wrote:
>> On Jul 19, 2024, at 7:46 AM, Thomas Simpson<ts@talentstack.to> wrote:
>>
>> I realize some of the background was snipped on what I sent to the hacker list, I'll try to fill in the details.
> I was gone from my computer for a day and lost track of the thread.
>
> Perhaps logical replication could help you out here?
I'm not sure - perhaps, but at this point, I've got that dump/reload
running and provided it completes ok (in about 20 days time at current
rate), I'll be fine with this.
The database itself is essentially an archive of data so is no longer
being added to at this point, so it's an annoyance for the rebuild time
rather than a disaster.
[But incidentally, I am working on an even larger project which is
likely to make this one seem small, so improvement around large
databases is important to me.]
However, my thought is around how to avoid this issue in the future and
to improve the experience for others faced with the dump-reload which is
always the fall-back upgrade suggestion between versions.
Getting parallelism should be possible and the current pg_dump does that
for directory mode from what I can see - making multiple threads etc.
according to parallel.c in pg_dump, it even looks like most of where my
thought process was going is actually already there.
The extension should be adding synchronization/checkpointing between the
generating dump and the receiving reload to ensure objects are not
processed until all their requirements are already present in the new
database. This is all based around routing via network streams instead
of the filesystem as currently happens.
Perhaps this is already in place since the restore can be done in
parallel, so must need to implement that ordering already? If someone
with a good understanding of dump is able to comment or even give
suggestions, I'm not against making an attempt to implement something as
a first attempt.
I see Tom Lane from git blame did a bunch of work around the parallel
dump back in 2020 - perhaps he could make suggestions either via private
direct email or the list ?
Thanks
Tom
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Enhance pg_dump multi-threaded streaming (WAS: Re: filesystem full during vacuum - space recovery issues)
@ 2024-07-19 21:21 Doug Reynolds <mav@wastegate.net>
parent: Thomas Simpson <ts@talentstack.to>
0 siblings, 1 reply; 11+ messages in thread
From: Doug Reynolds @ 2024-07-19 21:21 UTC (permalink / raw)
To: Thomas Simpson <ts@talentstack.to>; +Cc: Scott Ribe <scott_ribe@elevated-dev.com>; pgsql-admin@lists.postgresql.org
--Apple-Mail-FB7C7539-B570-4E3F-9CDE-A069BFCCECB6
Content-Type: text/html;
charset=utf-8
Content-Transfer-Encoding: quoted-printable
<html><head><meta http-equiv=3D"content-type" content=3D"text/html; charset=3D=
utf-8"></head><body dir=3D"auto"><div dir=3D"ltr">Thomas=E2=80=94</div><div d=
ir=3D"ltr"><br></div><div dir=3D"ltr">Why are you using logical backups for a=
database this large? A solution like PgBackRest? Obviously, if y=
ou are going to upgrade, but for operational use, that seems to be a slow ch=
oice.</div><div dir=3D"ltr"><br></div><div dir=3D"ltr">Doug</div><div dir=3D=
"ltr"><br><blockquote type=3D"cite">On Jul 19, 2024, at 4:26=E2=80=AFPM, Tho=
mas Simpson <ts@talentstack.to> wrote:<br><br></blockquote></div><bloc=
kquote type=3D"cite"><div dir=3D"ltr">=EF=BB=BF
=20
<meta http-equiv=3D"Content-Type" content=3D"text/html; charset=3DUTF-8"=
>
=20
=20
<p>Hi Scott<br>
</p>
<div class=3D"moz-cite-prefix">On 19-Jul-2024 15:34, Scott Ribe wrote:<b=
r>
</div>
<blockquote type=3D"cite" cite=3D"mid:970C2168-9B22-41D4-A801-9215BB2EC1=
5F@elevated-dev.com">
<blockquote type=3D"cite">
<pre class=3D"moz-quote-pre" wrap=3D"">On Jul 19, 2024, at 7:46=E2=80=
=AFAM, Thomas Simpson <a class=3D"moz-txt-link-rfc2396E" href=3D"mailto:ts@t=
alentstack.to"><ts@talentstack.to></a> wrote:
I realize some of the background was snipped on what I sent to the hacker li=
st, I'll try to fill in the details.
</pre>
</blockquote>
<pre class=3D"moz-quote-pre" wrap=3D"">I was gone from my computer for=
a day and lost track of the thread.
Perhaps logical replication could help you out here?</pre>
</blockquote>
<p>I'm not sure - perhaps, but at this point, I've got that
dump/reload running and provided it completes ok (in about 20 days
time at current rate), I'll be fine with this.</p>
<p>The database itself is essentially an archive of data so is no
longer being added to at this point, so it's an annoyance for the
rebuild time rather than a disaster.</p>
<p>[But incidentally, I am working on an even larger project which
is likely to make this one seem small, so improvement around large
databases is important to me.]<br>
</p>
<p>However, my thought is around how to avoid this issue in the
future and to improve the experience for others faced with the
dump-reload which is always the fall-back upgrade suggestion
between versions.</p>
<p>Getting parallelism should be possible and the current pg_dump
does that for directory mode from what I can see - making multiple
threads etc. according to parallel.c in pg_dump, it even looks
like most of where my thought process was going is actually
already there.</p>
<p>The extension should be adding synchronization/checkpointing
between the generating dump and the receiving reload to ensure
objects are not processed until all their requirements are already
present in the new database. This is all based around routing vi=
a
network streams instead of the filesystem as currently happens.</p>
<p>Perhaps this is already in place since the restore can be done in
parallel, so must need to implement that ordering already? If
someone with a good understanding of dump is able to comment or
even give suggestions, I'm not against making an attempt to
implement something as a first attempt.</p>
<p>I see Tom Lane from git blame did a bunch of work around the
parallel dump back in 2020 - perhaps he could make suggestions
either via private direct email or the list ?<br>
</p>
<p>Thanks</p>
<p>Tom<br>
</p>
<p><br>
</p>
<blockquote type=3D"cite" cite=3D"mid:970C2168-9B22-41D4-A801-9215BB2EC1=
5F@elevated-dev.com">
<pre class=3D"moz-quote-pre" wrap=3D""></pre>
</blockquote>
=20
</div></blockquote></body></html>=
--Apple-Mail-FB7C7539-B570-4E3F-9CDE-A069BFCCECB6--
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Enhance pg_dump multi-threaded streaming (WAS: Re: filesystem full during vacuum - space recovery issues)
@ 2024-07-20 02:17 Thomas Simpson <ts@talentstack.to>
parent: Doug Reynolds <mav@wastegate.net>
0 siblings, 1 reply; 11+ messages in thread
From: Thomas Simpson @ 2024-07-20 02:17 UTC (permalink / raw)
To: pgsql-admin@lists.postgresql.org
Hi Doug
On 19-Jul-2024 17:21, Doug Reynolds wrote:
> Thomas—
>
> Why are you using logical backups for a database this large? A
> solution like PgBackRest? Obviously, if you are going to upgrade, but
> for operational use, that seems to be a slow choice.
In normal operation the server runs as a primary-replica and pgbackrest
handles backups. Right when disk space was used up, pgbackrest also
took a backup during the failed vacuum so going back to it (or anything
earlier) would also roll forward the WALs for recovery to date and put
me right back where I am just now by running out of space part way through.
It's a pragmatic decision that trying various things short of the
dump-reload would take a number of days for me to try and see if I could
get them to work with a high likelihood of needing to resort to
dump-reload anyway. I'd already tried a few file matching/moving
exercises by they all prevented the database starting up so I cut my
losses and started the dump-reload this week instead of next week since
there's a limited window before this becomes a larger problem.
My thoughts on improving pg_dump are to help make it a better tool for
worst case scenarios like this for the future or for those that like the
dump-reload as part of upgrades but have reasonable size databases.
Thanks
Tom
>
> Doug
>
>> On Jul 19, 2024, at 4:26 PM, Thomas Simpson <ts@talentstack.to> wrote:
>>
>>
>>
>> Hi Scott
>>
>> On 19-Jul-2024 15:34, Scott Ribe wrote:
>>>> On Jul 19, 2024, at 7:46 AM, Thomas Simpson<ts@talentstack.to> wrote:
>>>>
>>>> I realize some of the background was snipped on what I sent to the hacker list, I'll try to fill in the details.
>>> I was gone from my computer for a day and lost track of the thread.
>>>
>>> Perhaps logical replication could help you out here?
>>
>> I'm not sure - perhaps, but at this point, I've got that dump/reload
>> running and provided it completes ok (in about 20 days time at
>> current rate), I'll be fine with this.
>>
>> The database itself is essentially an archive of data so is no longer
>> being added to at this point, so it's an annoyance for the rebuild
>> time rather than a disaster.
>>
>> [But incidentally, I am working on an even larger project which is
>> likely to make this one seem small, so improvement around large
>> databases is important to me.]
>>
>> However, my thought is around how to avoid this issue in the future
>> and to improve the experience for others faced with the dump-reload
>> which is always the fall-back upgrade suggestion between versions.
>>
>> Getting parallelism should be possible and the current pg_dump does
>> that for directory mode from what I can see - making multiple threads
>> etc. according to parallel.c in pg_dump, it even looks like most of
>> where my thought process was going is actually already there.
>>
>> The extension should be adding synchronization/checkpointing between
>> the generating dump and the receiving reload to ensure objects are
>> not processed until all their requirements are already present in the
>> new database. This is all based around routing via network streams
>> instead of the filesystem as currently happens.
>>
>> Perhaps this is already in place since the restore can be done in
>> parallel, so must need to implement that ordering already? If
>> someone with a good understanding of dump is able to comment or even
>> give suggestions, I'm not against making an attempt to implement
>> something as a first attempt.
>>
>> I see Tom Lane from git blame did a bunch of work around the parallel
>> dump back in 2020 - perhaps he could make suggestions either via
>> private direct email or the list ?
>>
>> Thanks
>>
>> Tom
>>
>>
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Enhance pg_dump multi-threaded streaming (WAS: Re: filesystem full during vacuum - space recovery issues)
@ 2024-07-22 15:50 Andrew Dunstan <andrew@dunslane.net>
parent: Thomas Simpson <ts@talentstack.to>
1 sibling, 1 reply; 11+ messages in thread
From: Andrew Dunstan @ 2024-07-22 15:50 UTC (permalink / raw)
To: Thomas Simpson <ts@talentstack.to>; Scott Ribe <scott_ribe@elevated-dev.com>; pgsql-hackers@lists.postgresql.org
On 2024-07-19 Fr 9:46 AM, Thomas Simpson wrote:
>
> Hi Scott,
>
> I realize some of the background was snipped on what I sent to the
> hacker list, I'll try to fill in the details.
>
> Short background is very large database ran out of space during vacuum
> full taking down the server. There is a replica which was applying
> the WALs and so it too ran out of space. On restart after clearing
> some space, the database came back up but left over the in-progress
> rebuild files. I've cleared that replica and am using it as my
> rebuild target just now.
>
> Trying to identify the 'orphan' files and move them away always led to
> the database spotting the supposedly unused files having gone and
> refusing to start, so I had no successful way to clean up and get
> space back.
>
> Last resort after discussion is pg_dumpall & reload. I'm doing this
> via a network pipe (netcat) as I do not have the vast amount of
> storage necessary for the dump file to be stored (in any format).
>
> On 19-Jul-2024 09:26, Scott Ribe wrote:
>> Do you actually have 100G networking between the nodes? Because if not, a single CPU should be able to saturate 10G.
> Servers connect via 10G WAN; sending is not the issue, it's
> application of the incoming stream on the destination which is
> bottlenecked.
>> 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?
> In this case, it's not a local write, it's piped via netcat.
>> 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.
>
> The basebackup is, to the best of my understanding, essentially just
> copying the database files. Since the failed vacuum has left extra
> files, my expectation is these too would be copied, leaving me in the
> same position I started in. If I'm wrong, please tell me as that
> would be vastly quicker - it is how I originally set up the replica
> and it took only a few hours on the 10G link.
>
> The inability to get a clean start if I move any files out the way
> leads me to be concerned for some underlying corruption/issue and the
> recommendation earlier in the discussion was opt for dump/reload as
> the fail-safe.
>
> Resigned to my fate, my thoughts were to see if there is a way to
> improve the dump-reload approach for the future. Since dump-reload is
> the ultimate upgrade suggestion in the documentation, it seems
> worthwhile to see if there is a way to improve the performance of that
> especially as very large databases like mine are a thing with
> PostgreSQL. From a quick review of pg_dump.c (I'm no expert on it
> obviously), it feels like it's already doing most of what needs done
> and the addition is some sort of multi-thread coordination with a
> restore client to ensure each thread can successfully complete each
> task it has before accepting more work. I realize that's actually
> difficult to implement.
>
>
There is a plan for a non-text mode for pg_dumpall. I have started work
on it, and hope to have a WIP patch in a month or so. It's not my
intention to parallelize it for the first cut, but it could definitely
be parallelizable in future. However, it will require writing to disk
somewhere, albeit that the data will be compressed. It's well nigh
impossible to parallelize text format dumps.
Restoration of custom and directory format dumps has long been
parallelized. Parallel dumps require directory format, and so will
non-text pg_dumpall.
cheers
andrew
--
Andrew Dunstan
EDB: https://www.enterprisedb.com
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Enhance pg_dump multi-threaded streaming (WAS: Re: filesystem full during vacuum - space recovery issues)
@ 2024-07-23 05:21 Ron Johnson <ronljohnsonjr@gmail.com>
parent: Thomas Simpson <ts@talentstack.to>
0 siblings, 0 replies; 11+ messages in thread
From: Ron Johnson @ 2024-07-23 05:21 UTC (permalink / raw)
To: Pgsql-admin <pgsql-admin@lists.postgresql.org>
On Fri, Jul 19, 2024 at 10:19 PM Thomas Simpson <ts@talentstack.to> wrote:
> Hi Doug
> On 19-Jul-2024 17:21, Doug Reynolds wrote:
>
> Thomas—
>
> Why are you using logical backups for a database this large? A solution
> like PgBackRest? Obviously, if you are going to upgrade, but for
> operational use, that seems to be a slow choice.
>
> In normal operation the server runs as a primary-replica and pgbackrest
> handles backups.
>
Expire the oldest pgbackrest, so as to free up space for a multithreaded
pg_dump.
> Right when disk space was used up, pgbackrest also took a backup during
> the failed vacuum so going back to it (or anything earlier) would also roll
> forward the WALs for recovery to date and put me right back where I am just
> now by running out of space part way through.
>
Who says you have to restore to the failure point? That's what the
"--target" option is for.
For example, if you took a full backup on 7/14 at midnight, and want to
restore to 7/18 23:00, run:
declare LL=detail
declare PGData=/path/to/data
declare -i Threads=`nproc`-2
declare BackupSet=20240714-000003F
declare RestoreUntil="2024-07-18 23:00"
pgbackrest restore \
--stanza=localhost \
--log-level-file=$LL \
--log-level-console=$LL \
--process-max=${Threads}
--pg1-path=$PGData \
--set=$BackupSet \
--type=time --target="${RestoreUntil}"
>
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Enhance pg_dump multi-threaded streaming (WAS: Re: filesystem full during vacuum - space recovery issues)
@ 2024-07-23 14:39 Thomas Simpson <ts@talentstack.to>
parent: Andrew Dunstan <andrew@dunslane.net>
0 siblings, 0 replies; 11+ messages in thread
From: Thomas Simpson @ 2024-07-23 14:39 UTC (permalink / raw)
To: Andrew Dunstan <andrew@dunslane.net>; Scott Ribe <scott_ribe@elevated-dev.com>; pgsql-hackers@lists.postgresql.org
Hi Andrew,
This is very interesting.
I had started looking at pg_dumpall trying to work out an approach. I
noticed parallel.c essentially already does all the thread creation and
coordination that I knew would be needed. Given that is a solved
problem, I started to look further (continued below).
On 22-Jul-2024 11:50, Andrew Dunstan wrote:
>
> On 2024-07-19 Fr 9:46 AM, Thomas Simpson wrote:
>>
>> Hi Scott,
>>
>> I realize some of the background was snipped on what I sent to the
>> hacker list, I'll try to fill in the details.
>>
>> Short background is very large database ran out of space during
>> vacuum full taking down the server. There is a replica which was
>> applying the WALs and so it too ran out of space. On restart after
>> clearing some space, the database came back up but left over the
>> in-progress rebuild files. I've cleared that replica and am using it
>> as my rebuild target just now.
>>
>> Trying to identify the 'orphan' files and move them away always led
>> to the database spotting the supposedly unused files having gone and
>> refusing to start, so I had no successful way to clean up and get
>> space back.
>>
>> Last resort after discussion is pg_dumpall & reload. I'm doing this
>> via a network pipe (netcat) as I do not have the vast amount of
>> storage necessary for the dump file to be stored (in any format).
>>
>> On 19-Jul-2024 09:26, Scott Ribe wrote:
>>> Do you actually have 100G networking between the nodes? Because if
>>> not, a single CPU should be able to saturate 10G.
>> Servers connect via 10G WAN; sending is not the issue, it's
>> application of the incoming stream on the destination which is
>> bottlenecked.
>>> 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?
>> In this case, it's not a local write, it's piped via netcat.
>>> 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.
>>
>> The basebackup is, to the best of my understanding, essentially just
>> copying the database files. Since the failed vacuum has left extra
>> files, my expectation is these too would be copied, leaving me in the
>> same position I started in. If I'm wrong, please tell me as that
>> would be vastly quicker - it is how I originally set up the replica
>> and it took only a few hours on the 10G link.
>>
>> The inability to get a clean start if I move any files out the way
>> leads me to be concerned for some underlying corruption/issue and the
>> recommendation earlier in the discussion was opt for dump/reload as
>> the fail-safe.
>>
>> Resigned to my fate, my thoughts were to see if there is a way to
>> improve the dump-reload approach for the future. Since dump-reload
>> is the ultimate upgrade suggestion in the documentation, it seems
>> worthwhile to see if there is a way to improve the performance of
>> that especially as very large databases like mine are a thing with
>> PostgreSQL. From a quick review of pg_dump.c (I'm no expert on it
>> obviously), it feels like it's already doing most of what needs done
>> and the addition is some sort of multi-thread coordination with a
>> restore client to ensure each thread can successfully complete each
>> task it has before accepting more work. I realize that's actually
>> difficult to implement.
>>
>>
>
> There is a plan for a non-text mode for pg_dumpall. I have started
> work on it, and hope to have a WIP patch in a month or so. It's not my
> intention to parallelize it for the first cut, but it could definitely
> be parallelizable in future. However, it will require writing to disk
> somewhere, albeit that the data will be compressed. It's well nigh
> impossible to parallelize text format dumps.
>
> Restoration of custom and directory format dumps has long been
> parallelized. Parallel dumps require directory format, and so will
> non-text pg_dumpall.
>
My general approach (which I'm sure is naive) was:
Add to pg_dumpall the concept of backup phase and I have the basic hooks
in place. 0 = role grants etc. The stuff before dumping actual
databases. I intercepted the fprintf(OPF to a hook function that for
normal run just ends up doing the same as fprintf but for my parallel
mode, it has a hook to send the info via the network (still to be done
but I think I may need to alter the fprintf stuff with more granularity
of what is being processed at each output to help this part, such as
outputRoleCreate, outputComment etc.).
Each subsequent phase is a whole database - increment at each pg_dump
call. The actual pg_dump is to get a new format, -F N for network;
based around directory dump as the base, my intention was to make
multiple network pipes to send the data in place of the files within the
directory. Essentially relying on whatever is already done to organize
parallel dumps to disk to be sufficient for coordinating network streaming.
The restore side needs to do network listen plus some handshaking to
confirm completion of the incoming phases, any necessary dependency
tracking on restore etc.
My goal was to actively avoid the disk usage part through the
coordination over the network between dump and restore even though my
starting point is the pg_backup_directory code. Any problem on the
restore side would feed back and halt the dump side in error so this is
a new failure mode compared with how it works just now.
I'll hold off a bit as I'm very interested in any feedback you have,
particularly if you see serious flaws in my though process here.
>
> cheers
>
>
> andrew
>
>
>
> --
> Andrew Dunstan
> EDB: https://www.enterprisedb.com
>
Thanks
Tom
^ permalink raw reply [nested|flat] 11+ messages in thread
end of thread, other threads:[~2024-07-23 14:39 UTC | newest]
Thread overview: 11+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2024-07-18 23:08 Enhance pg_dump multi-threaded streaming (WAS: Re: filesystem full during vacuum - space recovery issues) Thomas Simpson <ts@talentstack.to>
2024-07-19 12:21 ` Ron Johnson <ronljohnsonjr@gmail.com>
2024-07-19 13:26 ` Scott Ribe <scott_ribe@elevated-dev.com>
2024-07-19 13:46 ` Thomas Simpson <ts@talentstack.to>
2024-07-19 19:34 ` Scott Ribe <scott_ribe@elevated-dev.com>
2024-07-19 20:23 ` Thomas Simpson <ts@talentstack.to>
2024-07-19 21:21 ` Doug Reynolds <mav@wastegate.net>
2024-07-20 02:17 ` Thomas Simpson <ts@talentstack.to>
2024-07-23 05:21 ` Ron Johnson <ronljohnsonjr@gmail.com>
2024-07-22 15:50 ` Andrew Dunstan <andrew@dunslane.net>
2024-07-23 14:39 ` Thomas Simpson <ts@talentstack.to>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox