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 1uzFKD-001Got-6t for pgsql-general@arkaria.postgresql.org; Thu, 18 Sep 2025 14:09: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 1uzFKB-00Ghdj-SS for pgsql-general@arkaria.postgresql.org; Thu, 18 Sep 2025 14:09: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 1uzFKB-00Ghdb-Ds for pgsql-general@lists.postgresql.org; Thu, 18 Sep 2025 14:09:27 +0000 Received: from mail-oa1-x36.google.com ([2001:4860:4864:20::36]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.96) (envelope-from ) id 1uzFK8-0016DC-2C for pgsql-general@lists.postgresql.org; Thu, 18 Sep 2025 14:09:26 +0000 Received: by mail-oa1-x36.google.com with SMTP id 586e51a60fabf-30cce892b7dso561597fac.1 for ; Thu, 18 Sep 2025 07:09:24 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20230601; t=1758204564; x=1758809364; 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=3dPLGeEe42Fs0LgEAktXd2D4FvxdvTaoid7hbRS3/Co=; b=X0S+Bgw4ITj7RHm0fF1MOZEqNWgVnqhlKwbIgnrPqczBGUUIdoOmJ10Jut6YDIyjN9 Pk+CFYVmFx8gABCVQS1RMBVtv0+4ZgGSwF1ClY25gPuTfpa27bcAvFSrRuvA7aq2vvGm BxD62FQVqQ1XTeMJIbbjJUVaevuis1TkyjN9aNp8xZoak0tgLJEcODwRVORnmhjy4yCS +Txuq2rLvOrSZwittdKuYnVj+OEvyJVYhIsdPnMnsdaptMZmSR+63RKTX7fqj0PbWFvj akEwQrQCaiCXrw3cUgJCnmmF9ab+96MAVQQ7eIgfezgsvxLbTjvf7O3a6sS+af+YLxBS 6uSw== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1758204564; x=1758809364; 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=3dPLGeEe42Fs0LgEAktXd2D4FvxdvTaoid7hbRS3/Co=; b=RqvzMwfwiQwxG+FpwAkGKHUrphxN0aP5ZJiupbOYjdQeLgwCz3DxLb6bUXFvPGK6JZ GT3VpwiEzyZ3ANc7zqz9ve7uRfgPjqSXKG/q58TIymxadJXuX0wGNjuagBp/UNNos4RI y6YNaFxfhMdbMGRyBV2VI4VsXUp73OFjdHr6SqnVAdE+RXAumn7ILiKFYpucuVphzEce eaYxJIW1HzBHU712KjsnjFjt8TjsfJtTYUr3LzDXM8PEbZndt/mAswzmr6terNMlIttT 0w1+6xggnZYkaqTQUTKajLJdHBY2hl+qzTxGh/7h6+HJwUgCWdwd+xf2c0F5AEfjDHJK WN6Q== X-Gm-Message-State: AOJu0YxKMt8SE5dCf+q56RcY6LF7BSZMlUVuAgFYXnvfYv+6U96WePWv uSy0J5Kjm9HWtPCLBQO8Jxh6IITc2h8xJYjJsS3mUKq74d3fhTbBaI3pQeKIXbHtRqxAaXLbQKj A4yjjgo87PcsXm2oIQ+Ksn2WeHeG46iM= X-Gm-Gg: ASbGnctqN2RLqFJypZiJ3lYs6FIG+6/o0zE5BXe8tWhwSlNrygz3jsQdEB2ztMy1xiy NtuvpTUXYTnbqPGfHjdRCS0ZzexXUpVAftYih2k3hUv/t23aL66/HMl4AOT7w3QV19xXQQlbEFy mZkZeV9NfwCo/Sw2lVJQqEtQr6X9MdGBkLOwxFXD8kAXBm0JipFA0RZ6tgehW79chbJRpUIOBBa ojoLVrH2fU0nRBhY6RxixjPF/0= X-Google-Smtp-Source: AGHT+IE0FW8y5b1pchV9xY5uLfSn+S4w1/y6Un+Ah0m/MFqoZ3DclEbnBUIHBWqYh/TIiJH/aa+I4t9YYnZ+SVqFAkw= X-Received: by 2002:a05:6870:20c:b0:30b:beb3:5420 with SMTP id 586e51a60fabf-335be1b8492mr3015929fac.17.1758204563990; Thu, 18 Sep 2025 07:09:23 -0700 (PDT) MIME-Version: 1.0 References: In-Reply-To: From: Ron Johnson Date: Thu, 18 Sep 2025 10:09:13 -0400 X-Gm-Features: AS18NWDGqixMwaNb5hoQJEA8eIvc8xgoGkZzJWIYtVlWhRCnhYkK8J4wn7mtJvg Message-ID: Subject: Re: pg_restore scan To: R Wahyudi Cc: "pgsql-generallists.postgresql.org" Content-Type: multipart/alternative; boundary="0000000000007aabc2063f13e600" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --0000000000007aabc2063f13e600 Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable It's towards the end of this long mailing list thread from a couple of weeks ago. https://www.postgrespro.com/list/id/s0491qrn-343s-0757-8sn5-120rr8610qqq@tz= k.arg On Thu, Sep 18, 2025 at 8:58=E2=80=AFAM R Wahyudi wrot= e: > Hi All, > > Thanks for the quick and accurate response! I never been so happy seeing > IOwait on my system! > > I might be blind as I can't find information about 'offset' in pg_dump > documentation. > Where can I find more info about this? > > Regards, > Rianto > > On Wed, 17 Sept 2025 at 13:48, Ron Johnson > wrote: > >> >> PG 17 has integrated zstd compression, while --format=3Ddirectory lets y= ou >> do multi-threaded dumps. That's much faster than a single-threaded pg_d= ump >> 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 < >>>>> adrian.klaver@aklaver.com> 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 inefficient 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- >>>>>> > B2C3EC2E0551%40gmx.net >>>>> > E48B611D-7D61-4575-A820-B2C3EC2E0551%40gmx.net> >>>>>> >>>>>> This was for pg_dump output that was streamed 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! >> > --=20 Death to , and butter sauce. Don't boil me, I'm still alive. lobster! --0000000000007aabc2063f13e600 Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable

It's towards the end of this= long mailing=C2=A0list thread from a couple of weeks ago.

On Thu, Sep= 18, 2025 at 8:58=E2=80=AFAM R Wahyudi <rwahyudi@gmail.com> wrote:
Hi All,=C2=A0
<= br>
Thanks for the quick and accurate response!=C2=A0 I never b= een so happy seeing IOwait on my system!=C2=A0

I might be blind as=C2=A0 I can't find information about 'offset&#= 39; in pg_dump documentation.
Where can I find more info about = this?=C2=A0

Regards,
Rianto
<= /div>
O= n Wed, 17 Sept 2025 at 13:48, Ron Johnson <ronljohnsonjr@gmail.com> wrote:
<= /div>

PG 17 has integrated zstd compression, while -= -format=3Ddirectory lets you do multi-threaded dumps.=C2=A0 That's much= faster than a single-threaded pg_dump into a multi-threaded compression pr= ogram.

(If for _Reasons_ you require a single-file= backup, then tar the directory of compressed files using the=C2=A0--remove= -files option.)

On Tue, Se= p 16, 2025 at 10:50=E2=80=AFPM R Wahyudi <rwahyudi@gmail.com> wrote:
Sorry for not including the full command - yes , its p= iping to a compression command :=C2=A0
=C2=A0| lbzip2 -n <thre= adsforbzipgoeshere>--best > <filenamegoeshere>


I think=C2=A0we found the issue! I'll do furthe= r testing and see how it goes !




On Wed, 17 Sept = 2025 at 11:02, Ron Johnson <ronljohnsonjr@gmail.com> wrote:
S= o, piping or redirecting to a file?=C2=A0 If so, then that's the proble= m.

pg_dump directly to a file puts file offsets in the T= OC.

This how I do custom dumps:
cd $BackupDir
pg= _dump -Fc --compress=3Dzstd:long -v -d${db} -f ${db}.dump =C2=A02> ${db}= .log

On Tue, Sep 16, 2025 at 8:54=E2=80=AFPM R Wahyudi <rwahyudi@gmail.com&= gt; wrote:
pg_dump was done using the following command :=C2=A0
= pg_dump -Fc -Z 0 -h <host> -U <user> -w -d <database>=C2= =A0

On Wed, 17 Sept 2025 at 08:36, Adrian Klaver <adrian.klaver@aklaver.com= > 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= :
>
>=C2=A0 > "scanning happens in a very inefficient 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-
> B2C3EC2E0551%40gmx.net <https://www.postgresql.org/message-= id/
> E48B611D-7D61-4575-A820-B2C3EC2E0551%40gmx.net>

This was for pg_dump output that was streamed to a Borg archive and as
result had no object offsets in the TOC.

How are you doing your pg_dump?



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


--
Death to <Redacted>, and butter sauce.Don't boil me, I'm still alive.
<Redacted> lobs= ter!


--
Death to <Redacted>, and butter sauce.Don't boil me, I'm still alive.
<Redacted> lobs= ter!


--
Death to <Redacted>, and butter sauce.Don't boil me, I'm still alive.
<Redacted> lobs= ter!
--0000000000007aabc2063f13e600--