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 1uzOJr-002xjB-Ef for pgsql-general@arkaria.postgresql.org; Thu, 18 Sep 2025 23:45:43 +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 1uzOJq-002hT2-5W for pgsql-general@arkaria.postgresql.org; Thu, 18 Sep 2025 23:45:42 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1uzOJp-002hSu-Ow for pgsql-general@lists.postgresql.org; Thu, 18 Sep 2025 23:45:41 +0000 Received: from mail-pl1-x62e.google.com ([2607:f8b0:4864:20::62e]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.96) (envelope-from ) id 1uzOJj-001aic-2j for pgsql-general@lists.postgresql.org; Thu, 18 Sep 2025 23:45:41 +0000 Received: by mail-pl1-x62e.google.com with SMTP id d9443c01a7336-267dbd4e189so3202375ad.1 for ; Thu, 18 Sep 2025 16:45:37 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20230601; t=1758239135; x=1758843935; darn=lists.postgresql.org; h=cc:to:subject:message-id:date:from:in-reply-to:references :mime-version:from:to:cc:subject:date:message-id:reply-to; bh=V1r2M9iLrcf764C0slAy3/HkKdbEW2szB+0g40QMR48=; b=WCdHwGll5qcR8knLaWyOCNYQ5cbhEWpywmpKdhtRN+hyy6hpRJh4YyfVkjdK0BFDX8 WlZSZNqcPBVrl0GM+668LP83fPA3pfdtNrBBWxOTF9uC7H33dgZ+QxXuTFZ3ROzaDpwv HvxTrru5+Uz4VcrjgSVTQuoowRCQHcKjOF9C3Pdjz8tnp4n9ni18bTiYLELSzpg/rj6z khKkUxvNWfQ70JkdXnwQ8bu6NPgn/xFkUtOp3Kd5bdlqXILr8H79zrFRdmzfr4cI7WFE vXnexd1T3T+8063wcaZ6cfQfELfZuEfFzmQZRND9AMFxSAYG04B0eKNkex9ZEWSUnpWF WMiQ== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1758239135; x=1758843935; h=cc:to:subject:message-id:date:from:in-reply-to:references :mime-version:x-gm-message-state:from:to:cc:subject:date:message-id :reply-to; bh=V1r2M9iLrcf764C0slAy3/HkKdbEW2szB+0g40QMR48=; b=NMjrsaf8ZLH9Z2FMbLp9TOL4J3PfDCmGgcBAxNWm8OBniqGoEg5WvQxRIHxyP7BQdY l3ltNOfG8K7Ey1GO1rLkM9DhB7gVXZseHgEsP5RT7t/0WIhpnAFaNbPWx3KM0GGNt779 fGsSXbHCaavRAxpTfK3LN5QlUZlXhkYuScezmuLaJEQLucvyoWUI5wsQ+nY/QfSXmjXq W+xJgh+ron+z11bVwu2hTZsyCoo4jDdjnSBslRQib5pa7UKAEZsarosWnVL3A1HdZAMt 8Uk534nxcsoSGTjlu+emmC9bFDzHWHofsFzScDBI1InLDzdIFydYEifG+xEnavU2gKAx Ya9Q== X-Forwarded-Encrypted: i=1; AJvYcCWBOjrekFVCfP563DH0Bi4uvBfQ0HGSf0OuTq0Y0XcbUoeYiqaXhHJG/1JYLNEmNeY9gf/aR8NC6e5W892I@lists.postgresql.org X-Gm-Message-State: AOJu0YwMwLiZOFAC3OWOTmUVamAIhSXp9KOa7Bemie3Zp5DR9EQpqlTQ hs9LhRqvXzZIsS8Nde0i22IG6PR1oWR4jjyBASsF1eqVZfByJ7LOjiwyv4xRu4BDHEZDOFsix1e Q6jF9oZ4iYkryatws5c7SDfk+xPYe4XA7r/OM X-Gm-Gg: ASbGnctEsp9xVGK2dKSj4zXsG3NIPww6Cl9x51WQ765/Whtv8lZ1h8MySYIFhezrYcv 0EdiIeEe6+4G4VuknsjWMOINex4Quz4e3eIFhMILesx/Sumk0oDj7YTB1EG4WX/rrK0O6noGUj4 rqDAAaX0LhnOxqOK4nCtmormcLWL7rRu3HivrSyeON1KQJrDESBfQzrKXMxnNcjPj7g8D61Fjf2 xIKlhh8uhMgXHNS/rpRjA== X-Google-Smtp-Source: AGHT+IHVaCXCxG0I3PaSBHovpX1uf25JZhO+09KRYYBaBKA4S36oKxxGtJb0/hKfgykALU876YsoI+uPlT2Z2sDR4vI= X-Received: by 2002:a17:903:2348:b0:265:8af2:19b with SMTP id d9443c01a7336-269ba5c9fe6mr8857205ad.10.1758239135134; Thu, 18 Sep 2025 16:45:35 -0700 (PDT) MIME-Version: 1.0 References: <9a935fe8-d6e2-4ff2-ace3-8f6f1992c519@aklaver.com> In-Reply-To: <9a935fe8-d6e2-4ff2-ace3-8f6f1992c519@aklaver.com> From: R Wahyudi Date: Fri, 19 Sep 2025 09:45:22 +1000 X-Gm-Features: AS18NWAoKf2Dn0LvMCHPlwX15FDbFarLIL5Sv4jmjKiGO0qIOFqkB7-soVD8z8k Message-ID: Subject: Re: pg_restore scan To: Adrian Klaver Cc: Ron Johnson , "pgsql-generallists.postgresql.org" Content-Type: multipart/alternative; boundary="000000000000147a03063f1bf3ad" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --000000000000147a03063f1bf3ad Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable >> The input must be a regular file or directory (not, for example, a pipe or standard input). Thanks again for the pointer! I successfully ran a parallel restore with no warnings presented. I didn't really pay attention to how the dump was taken until I accidentally stumbled upon your post. Regards, Rianto On Fri, 19 Sept 2025 at 07:45, Adrian Klaver wrote: > > > On 9/18/25 2:36 PM, R Wahyudi wrote: > > I've been given a database dump file daily and I've been asked to > > restore it. > > I tried everything I could to speed up the process, including using -j > 40. > > > > I discovered that at the later stage of the restore process, the > > following behaviour repeated a few times : > > 40 x pg_restore process doing 100% CPU > > 40 x postgres process doing COPY but using 0% CPU > > ..... and zero disk write activity > > > > I don't see this behaviour when restoring the database that was dumped > > with -Fd. > > Also with an un-piped backup file, I can restore a specific table > > without having to wait for hours. > > From the docs: > > https://www.postgresql.org/docs/current/app-pgrestore.html > > " > -j number-of-jobs > > Only the custom and directory archive formats are supported with this > option. The input must be a regular file or directory (not, for example, > a pipe or standard input). Also, multiple jobs cannot be used together > with the option --single-transaction. > " > > > > > > > > -- > > > > > > > > > > > > On Fri, 19 Sept 2025 at 01:54, Adrian Klaver > > wrote: > > > > On 9/18/25 05:58, R Wahyudi wrote: > > > Hi All, > > > > > > Thanks for the quick and accurate response! I never been so hap= py > > > seeing IOwait on my system! > > > > Because? > > > > What did you find? > > > > > > > > I might be blind as I can't find information about 'offset' in > > pg_dump > > > documentation. > > > Where can I find more info about this? > > > > It is not in the user documentation. > > > > From the thread Ron referred to, there is an explanation here: > > > > https://www.postgresql.org/message- > > id/366773.1756749256%40sss.pgh.pa.us > message-id/366773.1756749256%40sss.pgh.pa.us> > > > > I believe the actual code, for the -Fc format, is in > pg_backup_custom.c > > here: > > > > https://github.com/postgres/postgres/blob/master/src/bin/pg_dump/ > > pg_backup_custom.c#L723 > master/src/bin/pg_dump/pg_backup_custom.c#L723> > > > > Per comment at line 755: > > > > " > > If possible, re-write the TOC in order to update the data offset > > information. This is not essential, as pg_restore can cope in most > > cases without it; but it can make pg_restore significantly faster > > in some situations (especially parallel restore). We can skip this > > step if we're not dumping any data; there are no offsets to update > > in that case. > > " > > > > > > > > Regards, > > > Rianto > > > > > > On Wed, 17 Sept 2025 at 13:48, Ron Johnson > > > > > > >> wrote: > > > > > > > > > PG 17 has integrated zstd compression, while -- > > format=3Ddirectory lets > > > you do multi-threaded dumps. That's much faster than a > single- > > > threaded pg_dump into a multi-threaded compression program. > > > > > > (If for _Reasons_ you require a single-file backup, then tar > the > > > directory of compressed files using the --remove-files > option.) > > > > > > On Tue, Sep 16, 2025 at 10:50=E2=80=AFPM R Wahyudi > > > > > >> > wrote: > > > > > > Sorry for not including the full command - yes , its > > piping to a > > > compression command : > > > | lbzip2 -n --best > > > > > > > > > > > > I think we found the issue! I'll do further testing and > > see how > > > it goes ! > > > > > > > > > > > > > > > > > > On Wed, 17 Sept 2025 at 11:02, Ron Johnson > > > > > >> > > wrote: > > > > > > So, piping or redirecting to a file? If so, then > > that's the > > > problem. > > > > > > pg_dump directly to a file puts file offsets in the > TOC. > > > > > > This how I do custom dumps: > > > cd $BackupDir > > > pg_dump -Fc --compress=3Dzstd:long -v -d${db} -f > ${db}.dump > > > 2> ${db}.log > > > > > > On Tue, Sep 16, 2025 at 8:54=E2=80=AFPM R Wahyudi > > > > > >> wrote: > > > > > > pg_dump was done using the following command : > > > pg_dump -Fc -Z 0 -h -U -w -d > > > > > > > On Wed, 17 Sept 2025 at 08:36, Adrian Klaver > > > > > > > > >> wrote: > > > > > > On 9/16/25 15:25, R Wahyudi wrote: > > > > > > > > I'm trying to troubleshoot the slowness > issue > > > with pg_restore and > > > > stumbled across a recent post about > pg_restore > > > scanning the whole file : > > > > > > > > > "scanning happens in a very inefficien= t > > way, > > > with many seek calls and > > > > small block reads. Try strace to see them= . > > This > > > initial phase can take > > > > hours in a huge dump file, before even > > starting > > > any actual restoration." > > > > see : https://www.postgresql.org/message- > > id/ > > > E48B611D-7D61-4575-A820- > > www.postgresql.org/message-id/E48B611D-7D61-4575-A820- > www.postgresql.org/message-id/E48B611D-7D61-4575-A820->> > > > > B2C3EC2E0551%40gmx.net > > > > > > > > > www.postgresql.org/message-id/ > message-id/>> > > > > E48B611D-7D61-4575-A820- > > B2C3EC2E0551%40gmx.net > > > >> > > > > > > This was for pg_dump output that was streame= d > > to a > > > Borg archive and as > > > result had no object offsets in the TOC. > > > > > > How are you doing your pg_dump? > > > > > > > > > > > > -- > > > Adrian Klaver > > > adrian.klaver@aklaver.com > > > > > > > > > > > > > > > > > -- > > > Death to , and butter sauce. > > > Don't boil me, I'm still alive. > > > lobster! > > > > > > > > > > > > -- > > > Death to , and butter sauce. > > > Don't boil me, I'm still alive. > > > lobster! > > > > > > > > > -- > > Adrian Klaver > > adrian.klaver@aklaver.com > > > > -- > Adrian Klaver > adrian.klaver@aklaver.com > > --000000000000147a03063f1bf3ad Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
>> The input must be a regular file or= directory (not, for example,=C2=A0a pipe or standard input).=C2=A0<= /span>

Thanks again for t= he pointer!=C2=A0

= = I successfully ran a parallel restore with no warnings presented.=C2=A0
I didn't really pay attention to how the dump wa= s taken until I accidentally stumbled=C2=A0upon your post.


Regards,
Rianto




On Fri, 19 Sept 2025 at 07:45,= Adrian Klaver <adrian.klaver@aklaver.com> wrote:


On 9/18/25 2:36 PM, R Wahyudi wrote:
> I've been given a database dump file daily and I've been asked= to
> restore it.
> I tried everything I could to speed up the process, including using -j= 40.
>
> I discovered that at the later stage of the restore process,=C2=A0 the=
> following=C2=A0behaviour repeated a few times :
> 40 x pg_restore process doing 100% CPU
> 40 x=C2=A0 postgres process doing COPY but using 0% CPU
> ..... and zero disk write activity
>
> I don't see this behaviour when restoring the database that was du= mped
> with -Fd.
> Also with an un-piped backup file, I can restore a specific table
> without having to wait for hours.

=C2=A0From the docs:

https://www.postgresql.org/docs/current/a= pp-pgrestore.html

"
-j number-of-jobs

Only the custom and directory archive formats are supported with this
option. The input must be a regular file or directory (not, for example, a pipe or standard input). Also, multiple jobs cannot be used together
with the option --single-transaction.
"


>
>
> --
>
>
>
>
>
> On Fri, 19 Sept 2025 at 01:54, Adrian Klaver <adrian.klaver@aklaver.com > <mailto:adrian.klaver@aklaver.com>> wrote:
>
>=C2=A0 =C2=A0 =C2=A0On 9/18/25 05:58, R Wahyudi wrote:
>=C2=A0 =C2=A0 =C2=A0 > Hi All,
>=C2=A0 =C2=A0 =C2=A0 >
>=C2=A0 =C2=A0 =C2=A0 > Thanks for the quick and accurate response!= =C2=A0 I never been so happy
>=C2=A0 =C2=A0 =C2=A0 > seeing IOwait on my system!
>
>=C2=A0 =C2=A0 =C2=A0Because?
>
>=C2=A0 =C2=A0 =C2=A0What did you find?
>
>=C2=A0 =C2=A0 =C2=A0 >
>=C2=A0 =C2=A0 =C2=A0 > I might be blind as=C2=A0 I can't find in= formation about 'offset' in
>=C2=A0 =C2=A0 =C2=A0pg_dump
>=C2=A0 =C2=A0 =C2=A0 > documentation.
>=C2=A0 =C2=A0 =C2=A0 > Where can I find more info about this?
>
>=C2=A0 =C2=A0 =C2=A0It is not in the user documentation.
>
>=C2=A0 =C2=A0 =C2=A0 =C2=A0From the thread Ron referred to, there is an= explanation here:
>
>=C2=A0 =C2=A0 =C2=A0https://www.postgresql.org/message- >=C2=A0 =C2=A0 =C2=A0id/366773.1756749256%40sss.pgh.pa.us <https= ://www.postgresql.org/
>=C2=A0 =C2=A0 =C2=A0message-id/366773.1756749256%40sss.pgh.pa.us> >
>=C2=A0 =C2=A0 =C2=A0I believe the actual code, for the -Fc format, is i= n pg_backup_custom.c
>=C2=A0 =C2=A0 =C2=A0here:
>
>=C2=A0 =C2=A0 =C2=A0https://git= hub.com/postgres/postgres/blob/master/src/bin/pg_dump/
>=C2=A0 =C2=A0 =C2=A0pg_backup_custom.c#L723 <https:= //github.com/postgres/postgres/blob/
>=C2=A0 =C2=A0 =C2=A0master/src/bin/pg_dump/pg_backup_custom.c#L723><= br> >
>=C2=A0 =C2=A0 =C2=A0Per comment at line 755:
>
>=C2=A0 =C2=A0 =C2=A0"
>=C2=A0 =C2=A0 =C2=A0 =C2=A0 If possible, re-write the TOC in order to u= pdate the data offset
>=C2=A0 =C2=A0 =C2=A0information.=C2=A0 This is not essential, as pg_res= tore can cope in most
>=C2=A0 =C2=A0 =C2=A0cases without it; but it can make pg_restore signif= icantly faster
>=C2=A0 =C2=A0 =C2=A0in some situations (especially parallel restore).= =C2=A0 We can skip this
>=C2=A0 =C2=A0 =C2=A0step if we're not dumping any data; there are n= o offsets to update
>=C2=A0 =C2=A0 =C2=A0in that case.
>=C2=A0 =C2=A0 =C2=A0"
>
>=C2=A0 =C2=A0 =C2=A0 >
>=C2=A0 =C2=A0 =C2=A0 > Regards,
>=C2=A0 =C2=A0 =C2=A0 > Rianto
>=C2=A0 =C2=A0 =C2=A0 >
>=C2=A0 =C2=A0 =C2=A0 > On Wed, 17 Sept 2025 at 13:48, Ron Johnson >=C2=A0 =C2=A0 =C2=A0<ronljohnsonjr@gmail.com <mailto:ronljohnsonjr@gmail.com>
>=C2=A0 =C2=A0 =C2=A0 > <mailto:ronljohnsonjr@gmail.com
>=C2=A0 =C2=A0 =C2=A0<mailto:ronljohnsonjr@gmail.com>>> wrote:
>=C2=A0 =C2=A0 =C2=A0 >
>=C2=A0 =C2=A0 =C2=A0 >
>=C2=A0 =C2=A0 =C2=A0 >=C2=A0 =C2=A0 =C2=A0PG 17 has integrated zstd = compression, while --
>=C2=A0 =C2=A0 =C2=A0format=3Ddirectory lets
>=C2=A0 =C2=A0 =C2=A0 >=C2=A0 =C2=A0 =C2=A0you do multi-threaded dump= s.=C2=A0 That's much faster than a single-
>=C2=A0 =C2=A0 =C2=A0 >=C2=A0 =C2=A0 =C2=A0threaded pg_dump into a mu= lti-threaded compression program.
>=C2=A0 =C2=A0 =C2=A0 >
>=C2=A0 =C2=A0 =C2=A0 >=C2=A0 =C2=A0 =C2=A0(If for _Reasons_ you requ= ire a single-file backup, then tar the
>=C2=A0 =C2=A0 =C2=A0 >=C2=A0 =C2=A0 =C2=A0directory of compressed fi= les using the=C2=A0--remove-files option.)
>=C2=A0 =C2=A0 =C2=A0 >
>=C2=A0 =C2=A0 =C2=A0 >=C2=A0 =C2=A0 =C2=A0On Tue, Sep 16, 2025 at 10= :50=E2=80=AFPM R Wahyudi
>=C2=A0 =C2=A0 =C2=A0<rwahyudi@gmail.com <mailto:rwahyudi@gmail.com>
>=C2=A0 =C2=A0 =C2=A0 >=C2=A0 =C2=A0 =C2=A0<mailto:rwahyudi@gmail.com <mailto:= rwahyudi@gmail.com<= /a>>>> wrote:
>=C2=A0 =C2=A0 =C2=A0 >
>=C2=A0 =C2=A0 =C2=A0 >=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0Sorry for no= t including the full command - yes , its
>=C2=A0 =C2=A0 =C2=A0piping to a
>=C2=A0 =C2=A0 =C2=A0 >=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0compression = command :
>=C2=A0 =C2=A0 =C2=A0 >=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0| lbz= ip2 -n <threadsforbzipgoeshere>--best >
>=C2=A0 =C2=A0 =C2=A0<filenamegoeshere>
>=C2=A0 =C2=A0 =C2=A0 >
>=C2=A0 =C2=A0 =C2=A0 >
>=C2=A0 =C2=A0 =C2=A0 >=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0I think=C2= =A0we found the issue! I'll do further testing and
>=C2=A0 =C2=A0 =C2=A0see how
>=C2=A0 =C2=A0 =C2=A0 >=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0it goes ! >=C2=A0 =C2=A0 =C2=A0 >
>=C2=A0 =C2=A0 =C2=A0 >
>=C2=A0 =C2=A0 =C2=A0 >
>=C2=A0 =C2=A0 =C2=A0 >
>=C2=A0 =C2=A0 =C2=A0 >
>=C2=A0 =C2=A0 =C2=A0 >=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0On Wed, 17 S= ept 2025 at 11:02, Ron Johnson
>=C2=A0 =C2=A0 =C2=A0 >=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0<ronljohnsonjr@gmail.c= om <mailto:ronljohnsonjr@gmail.com>
>=C2=A0 =C2=A0 =C2=A0<mailto:ronljohnsonjr@gmail.com <mailto:ronljohnsonjr@gmail.com&g= t;>>
>=C2=A0 =C2=A0 =C2=A0wrote:
>=C2=A0 =C2=A0 =C2=A0 >
>=C2=A0 =C2=A0 =C2=A0 >=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0So, piping or redirecting to a file?=C2=A0 If so, then
>=C2=A0 =C2=A0 =C2=A0that's the
>=C2=A0 =C2=A0 =C2=A0 >=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0problem.
>=C2=A0 =C2=A0 =C2=A0 >
>=C2=A0 =C2=A0 =C2=A0 >=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0pg_dump directly to a file puts file offsets in the TOC.
>=C2=A0 =C2=A0 =C2=A0 >
>=C2=A0 =C2=A0 =C2=A0 >=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0This how I do custom dumps:
>=C2=A0 =C2=A0 =C2=A0 >=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0cd $BackupDir
>=C2=A0 =C2=A0 =C2=A0 >=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0pg_dump -Fc --compress=3Dzstd:long -v -d${db} -f ${db}.dump
>=C2=A0 =C2=A0 =C2=A0 >=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A02> ${db}.log
>=C2=A0 =C2=A0 =C2=A0 >
>=C2=A0 =C2=A0 =C2=A0 >=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0On Tue, Sep 16, 2025 at 8:54=E2=80=AFPM R Wahyudi
>=C2=A0 =C2=A0 =C2=A0 >=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0<rwahyudi@gma= il.com <mailto:rwahyudi@gmail.com>
>=C2=A0 =C2=A0 =C2=A0<mailto:rwahyudi@gmail.com <mailto:rwahyudi@gmail.com>>> wrote: >=C2=A0 =C2=A0 =C2=A0 >
>=C2=A0 =C2=A0 =C2=A0 >=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0pg_dump was done using the following command :
>=C2=A0 =C2=A0 =C2=A0 >=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0pg_dump -Fc -Z 0 -h <host> -U <user> -w -d <= ;database>
>=C2=A0 =C2=A0 =C2=A0 >
>=C2=A0 =C2=A0 =C2=A0 >=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0On Wed, 17 Sept 2025 at 08:36, Adrian Klaver
>=C2=A0 =C2=A0 =C2=A0 >=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0<adrian.klaver@aklaver.com
>=C2=A0 =C2=A0 =C2=A0<mailto:adrian.klaver@aklaver.com>
>=C2=A0 =C2=A0 =C2=A0 >=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0<mailto:adrian.klaver@aklaver.com
>=C2=A0 =C2=A0 =C2=A0<mailto:adrian.klaver@aklaver.com>>> wrote:
>=C2=A0 =C2=A0 =C2=A0 >
>=C2=A0 =C2=A0 =C2=A0 >=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0On 9/16/25 15:25, R Wahyudi wrote:
>=C2=A0 =C2=A0 =C2=A0 >=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 >
>=C2=A0 =C2=A0 =C2=A0 >=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 > I'm trying to troubleshoot the slo= wness issue
>=C2=A0 =C2=A0 =C2=A0 >=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0with pg_restore and
>=C2=A0 =C2=A0 =C2=A0 >=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 > stumbled across a recent post about pg= _restore
>=C2=A0 =C2=A0 =C2=A0 >=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0scanning the whole file :
>=C2=A0 =C2=A0 =C2=A0 >=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 >
>=C2=A0 =C2=A0 =C2=A0 >=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 >=C2=A0 > "scanning happens in a= very inefficient
>=C2=A0 =C2=A0 =C2=A0way,
>=C2=A0 =C2=A0 =C2=A0 >=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0with many seek calls and
>=C2=A0 =C2=A0 =C2=A0 >=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 > small block reads. Try strace to see t= hem.
>=C2=A0 =C2=A0 =C2=A0This
>=C2=A0 =C2=A0 =C2=A0 >=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0initial phase can take
>=C2=A0 =C2=A0 =C2=A0 >=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 > hours in a huge dump file, before even=
>=C2=A0 =C2=A0 =C2=A0starting
>=C2=A0 =C2=A0 =C2=A0 >=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0any actual restoration."
>=C2=A0 =C2=A0 =C2=A0 >=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 > see : https://www.postgresql= .org/message-
>=C2=A0 =C2=A0 =C2=A0id/ <https://www.postgresql.org/mes= sage-id/>
>=C2=A0 =C2=A0 =C2=A0 >=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0E48B611D-7D61-4575-A820- <https://
>=C2=A0 =C2=A0 =C2=A0 > www.postg= resql.org/message-id/E48B611D-7D61-4575-A820- <http://
>=C2=A0 =C2=A0 =C2=A0www.postgresql.= org/message-id/E48B611D-7D61-4575-A820->>
>=C2=A0 =C2=A0 =C2=A0 >=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 > B2C3EC2E0551%40gmx.net <http://40gmx.net><= br> >=C2=A0 =C2=A0 =C2=A0<http://40gmx.net <http://40gmx.net>>
>=C2=A0 =C2=A0 =C2=A0 >=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0<https://www.postgresql.org/= message-id/
>=C2=A0 =C2=A0 =C2=A0<https://www.postgresql.org/message= -id/> <https://
>=C2=A0 =C2=A0 =C2=A0 > www.postgresql.org/message-id/ <http://www.postgresql.org/
>=C2=A0 =C2=A0 =C2=A0message-id/>>
>=C2=A0 =C2=A0 =C2=A0 >=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 > E48B611D-7D61-4575-A820-
>=C2=A0 =C2=A0 =C2=A0B2C3EC2E0551%40gmx.net <http://40gmx.net>
>=C2=A0 =C2=A0 =C2=A0 >=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0<http://40gmx.net <http://40gmx.net>>><= br> >=C2=A0 =C2=A0 =C2=A0 >
>=C2=A0 =C2=A0 =C2=A0 >=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0This was for pg_dump output that was streame= d
>=C2=A0 =C2=A0 =C2=A0to a
>=C2=A0 =C2=A0 =C2=A0 >=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0Borg archive and as
>=C2=A0 =C2=A0 =C2=A0 >=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0result had no object offsets in the TOC.
>=C2=A0 =C2=A0 =C2=A0 >
>=C2=A0 =C2=A0 =C2=A0 >=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0How are you doing your pg_dump?
>=C2=A0 =C2=A0 =C2=A0 >
>=C2=A0 =C2=A0 =C2=A0 >
>=C2=A0 =C2=A0 =C2=A0 >
>=C2=A0 =C2=A0 =C2=A0 >=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0--
>=C2=A0 =C2=A0 =C2=A0 >=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0Adrian Klaver
>=C2=A0 =C2=A0 =C2=A0 > adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com>
>=C2=A0 =C2=A0 =C2=A0 >=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0<mailto:adrian.klaver@aklaver.com
>=C2=A0 =C2=A0 =C2=A0<mailto:adrian.klaver@aklaver.com>>
>=C2=A0 =C2=A0 =C2=A0 >
>=C2=A0 =C2=A0 =C2=A0 >
>=C2=A0 =C2=A0 =C2=A0 >
>=C2=A0 =C2=A0 =C2=A0 >=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0--
>=C2=A0 =C2=A0 =C2=A0 >=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0Death to <Redacted>, and butter sauce.
>=C2=A0 =C2=A0 =C2=A0 >=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0Don't boil me, I'm still alive.
>=C2=A0 =C2=A0 =C2=A0 >=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0<Redacted> lobster!
>=C2=A0 =C2=A0 =C2=A0 >
>=C2=A0 =C2=A0 =C2=A0 >
>=C2=A0 =C2=A0 =C2=A0 >
>=C2=A0 =C2=A0 =C2=A0 >=C2=A0 =C2=A0 =C2=A0--
>=C2=A0 =C2=A0 =C2=A0 >=C2=A0 =C2=A0 =C2=A0Death to <Redacted>,= and butter sauce.
>=C2=A0 =C2=A0 =C2=A0 >=C2=A0 =C2=A0 =C2=A0Don't boil me, I'm= still alive.
>=C2=A0 =C2=A0 =C2=A0 >=C2=A0 =C2=A0 =C2=A0<Redacted> lobster!<= br> >=C2=A0 =C2=A0 =C2=A0 >
>
>
>=C2=A0 =C2=A0 =C2=A0--
>=C2=A0 =C2=A0 =C2=A0Adrian Klaver
>=C2=A0 =C2=A0 =C2=A0adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com><= br> >

--
Adrian Klaver
adrian.klave= r@aklaver.com

--000000000000147a03063f1bf3ad--