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 1uzEE0-0011FO-N3 for pgsql-general@arkaria.postgresql.org; Thu, 18 Sep 2025 12:59:00 +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 1uzEDy-00GMWS-3a for pgsql-general@arkaria.postgresql.org; Thu, 18 Sep 2025 12:58:58 +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 1uzEDx-00GMWJ-L2 for pgsql-general@lists.postgresql.org; Thu, 18 Sep 2025 12:58:57 +0000 Received: from mail-pf1-x432.google.com ([2607:f8b0:4864:20::432]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.96) (envelope-from ) id 1uzEDv-0015jF-12 for pgsql-general@lists.postgresql.org; Thu, 18 Sep 2025 12:58:56 +0000 Received: by mail-pf1-x432.google.com with SMTP id d2e1a72fcca58-77280e7bde3so131288b3a.2 for ; Thu, 18 Sep 2025 05:58:55 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20230601; t=1758200335; x=1758805135; 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=JJZjrNFkCqwyv9IDP2s6e3Csf19bYa1y5eGI2Mv+cXk=; b=EfV1kTigb+sbIhdwBjjaj9JzUeHj9A8IOlfxA9/Td3E4Z4WsrpwVAv7U6vm07xCrWy 3D6o7DhbEMLsB7WoAixNtDsdKPFXmwEDmWc/dXOSEEleFv3POEc3RL0GfVJwm/qkzFo1 o8f5jQaVj4bLSmaLb+42sgoBchdaJ1EwD8FXl4Mpd/gR4bJKBZ0CYiyx8C9cmA+yOhRs dySivANPZHRzV857sxIpUcahtCI2+Zd5PaVWpO2qPvKfOlRc0iv8uKYupoder+NGEpl/ pyxGRQg6vx7M34G/YFNDofhtwnMvAdN+QRLuC1Fj7H92vUxpVo3UpXylxeUzImyF6OX3 TByQ== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1758200335; x=1758805135; 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=JJZjrNFkCqwyv9IDP2s6e3Csf19bYa1y5eGI2Mv+cXk=; b=vtuR8UubAhRUXTOHWxByred261HHuczX0ReIzhJ0RoCewphtfJ5UoGghy9QekNVkuo GfvhUdNpcFDWIRISn60Ks2p0w1B8uJ6BSRQw2RJia5d03oPQQMJlxGpNjsCSdxsbRRhW Pq5v0mbaEyNwbqyIxYtwfeQ9DoC6lDlxhTo1YggxVSGYGvskM38wewjhUlTDan2koE3i VZAj8qaqu/P232NMtLKzl5yO2ZRFdJB7qt8ekltL6QusupmdBT0g9/yBvd5cuPup6SID D715EaCds6fnkGUurpmTKnT9b1NUNy+Me3cjzCOX2ka4LKGu9Qiwvo2kgrJodg8e20z8 49hw== X-Gm-Message-State: AOJu0YzimiACEieWOuiIXdO8CtzyLyv4C9aIBiDcBFi1ZqBTzU+WHGjc 9HrPLI3xAMsgZwdw2rZFi/3gYfAlXy8s+c59TOvf0jaMWJ0Jf14bJ0HQ8yXyzYc5tqJShjQh61r kQU98iJUxdDzVwZXpSAdwD9anRZ4J05Fr9SyM X-Gm-Gg: ASbGnctfejhHek6cLdUxL3w7hz+HSWTG7sDxaEOSXmy/2ZvFUp5O1M8pDlu5aak23Gp jez8L/gJlB0z96XCBah7N+ZmmXqr2U2wjpFqucNYfeQaNBZPdDx+Qo5TYMg+JvkFCWSuifsaiiZ KOQq8KIJQ/Q+NWAU0mQKlTRGWyFmXH0++1jGEbQRhcq2OWGrJMBHwy8qTpLnPqhdYBA4v62JCn9 8OocOKcC12Mc5uyxv38DWRg7yn3rTR3j0igAsI= X-Google-Smtp-Source: AGHT+IGsD5olB73m9MQ16+sn4Lt7NGJBu4w0Hszk3B1rqikR2TRokPGp5mrkZ7TXKW8ajlYhOwgF2tnrULb/ZLTWF9Q= X-Received: by 2002:a17:902:c40d:b0:269:8407:499a with SMTP id d9443c01a7336-269840753a2mr21867445ad.1.1758200334578; Thu, 18 Sep 2025 05:58:54 -0700 (PDT) MIME-Version: 1.0 References: In-Reply-To: From: R Wahyudi Date: Thu, 18 Sep 2025 22:58:42 +1000 X-Gm-Features: AS18NWCFdyorQsPn00hYzwV6ZmiswPZpjWIYw9fSDmP0u4cceNBWh1zttT41MuU Message-ID: Subject: Re: pg_restore scan To: Ron Johnson Cc: "pgsql-generallists.postgresql.org" Content-Type: multipart/alternative; boundary="00000000000062fa4a063f12eac1" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --00000000000062fa4a063f12eac1 Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable 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 yo= u > do multi-threaded dumps. That's much faster than a single-threaded pg_du= mp > into a multi-threaded compression program. > > (If for _Reasons_ you require a single-file backup, then tar the director= y > of compressed files using the --remove-files option.) > > On Tue, Sep 16, 2025 at 10:50=E2=80=AFPM R Wahyudi w= rote: > >> 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}.l= og >>> >>> 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 inefficient way, with many seek call= s >>>>> 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 a= s >>>>> 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! > --00000000000062fa4a063f12eac1 Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
Hi All,=C2=A0

Thanks for = the quick and accurate response!=C2=A0 I never been so happy seeing IOwait = on my system!=C2=A0

I might be blind as=C2=A0 I can't find informatio= n about 'offset' in pg_dump documentation.
Where can I find more info about this?=C2=A0

Regards,
Rianto

On Wed, 17 S= ept 2025 at 13:48, Ron Johnson <ronljohnsonjr@gmail.com> wrote:

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

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

On Tue, Sep 16, 2025 at 10:50=E2=80=AFP= M R Wahyudi <rwa= hyudi@gmail.com> wrote:
Sorry fo= r not including the full command - yes , its piping to a compression comman= d :=C2=A0
=C2=A0| lbzip2 -n <threadsforbzipgoeshere>--best = > <filenamegoeshere>


I thi= nk=C2=A0we found the issue! I'll do further testing and see how it goes= !





On Wed, 17 Sept 2025 at 11:02, Ron Johnson &l= t;ronljohnsonj= r@gmail.com> wrote:
So, piping or redirecting to a= file?=C2=A0 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 $BackupDi= r
pg_dump -Fc --compress=3Dzstd:l= ong -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> wrote:
pg_dump wa= s done using the following command :=C2=A0
pg_dump -Fc -Z 0 -h <host&= gt; -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!
--00000000000062fa4a063f12eac1--