Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1sUu9V-006YY6-JI for pgsql-admin@arkaria.postgresql.org; Fri, 19 Jul 2024 20:24:29 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.94.2) (envelope-from ) id 1sUu9T-005VMK-7d for pgsql-admin@arkaria.postgresql.org; Fri, 19 Jul 2024 20:24:27 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1sUu9S-005VLO-SE for pgsql-admin@lists.postgresql.org; Fri, 19 Jul 2024 20:24:27 +0000 Received: from mail.network-systems-solutions.net ([162.250.175.178]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1sUu9O-000QhT-S1 for pgsql-admin@lists.postgresql.org; Fri, 19 Jul 2024 20:24:25 +0000 Received: from messaging.network-systems-solutions.net (unknown [192.168.242.37]) by mail.network-systems-solutions.net (Postfix) with ESMTP id 6D057807DF; Fri, 19 Jul 2024 21:02:09 +0000 (UTC) Received: from aox (localhost [127.0.0.1]) by messaging.network-systems-solutions.net (Postfix) with ESMTP id 402AC8E05A0; Fri, 19 Jul 2024 16:24:12 -0400 (EDT) Received: from jim@talentstack.to by aox (Archiveopteryx 3.2.0) with esmtpsa id 1721420651-3614-3610/6/57; Fri, 19 Jul 2024 20:24:11 +0000 Content-Type: multipart/alternative; boundary=------------Y5sU2eN582dqsq1OwHIZJNFv Message-Id: <42e5a766-2af7-4e2b-b82d-373da77c35ed@talentstack.to> Date: Fri, 19 Jul 2024 16:23:29 -0400 Mime-Version: 1.0 User-Agent: Mozilla Thunderbird Subject: Re: Enhance pg_dump multi-threaded streaming (WAS: Re: filesystem full during vacuum - space recovery issues) To: Scott Ribe Cc: pgsql-admin@lists.postgresql.org References: <7dee2c00-6184-49c5-b303-58113c1d04c6@talentstack.to> <95f5bff5-b392-4c2f-9708-8fd5547c620c@talentstack.to> <6F46CFEB-3C3A-4CEC-89DF-57D8225A5863@elevated-dev.com> <6107c011-f907-48ba-94e4-884f59c3da1a@talentstack.to> <970C2168-9B22-41D4-A801-9215BB2EC15F@elevated-dev.com> Content-Language: en-CA From: Thomas Simpson In-Reply-To: <970C2168-9B22-41D4-A801-9215BB2EC15F@elevated-dev.com> X-NSSLTD-Archiving: Added to store 1 as 91340 X-Scanned-By: MIMEDefang 2.84 on 127.0.1.1 List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --------------Y5sU2eN582dqsq1OwHIZJNFv Content-Type: text/plain; charset=utf-8; format=flowed Content-Transfer-Encoding: quoted-printable Hi Scott On 19-Jul-2024 15:34, Scott Ribe wrote: >> On Jul 19, 2024, at 7:46=E2=80=AFAM, Thomas Simpson= 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=20 running and provided it completes ok (in about 20 days time at current=20 rate), I'll be fine with this. The database itself is essentially an archive of data so is no longer=20 being added to at this point, so it's an annoyance for the rebuild time=20 rather than a disaster. [But incidentally, I am working on an even larger project which is=20 likely to make this one seem small, so improvement around large=20 databases is important to me.] However, my thought is around how to avoid this issue in the future and=20 to improve the experience for others faced with the dump-reload which is=20 always the fall-back upgrade suggestion between versions. Getting parallelism should be possible and the current pg_dump does that=20 for directory mode from what I can see - making multiple threads etc.=C2=A0= =20 according to parallel.c in pg_dump, it even looks like most of where my=20 thought process was going is actually already there. The extension should be adding synchronization/checkpointing between the=20 generating dump and the receiving reload to ensure objects are not=20 processed until all their requirements are already present in the new=20 database.=C2=A0 This is all based around routing via network streams = instead=20 of the filesystem as currently happens. Perhaps this is already in place since the restore can be done in=20 parallel, so must need to implement that ordering already?=C2=A0 If = someone=20 with a good understanding of dump is able to comment or even give=20 suggestions, I'm not against making an attempt to implement something as=20 a first attempt. I see Tom Lane from git blame did a bunch of work around the parallel=20 dump back in 2020 - perhaps he could make suggestions either via private=20 direct email or the list ? Thanks Tom --------------Y5sU2eN582dqsq1OwHIZJNFv Content-Type: text/html; charset=utf-8 Content-Transfer-Encoding: quoted-printable

Hi Scott

On 19-Jul-2024 15:34, Scott Ribe = wrote:
On Jul 19, 2024, at =
7:46=E2=80=AFAM, 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.=C2=A0 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.=C2=A0 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?=C2=A0 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



    

  


--------------Y5sU2eN582dqsq1OwHIZJNFv--