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 1uyj96-00BsD9-Km for pgsql-general@arkaria.postgresql.org; Wed, 17 Sep 2025 03:47:52 +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 1uyj93-004Pj9-OF for pgsql-general@arkaria.postgresql.org; Wed, 17 Sep 2025 03:47:50 +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 1uyj93-004Pj1-CS for pgsql-general@lists.postgresql.org; Wed, 17 Sep 2025 03:47:50 +0000 Received: from mail-oi1-x234.google.com ([2607:f8b0:4864:20::234]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.96) (envelope-from ) id 1uyj8z-001F3v-2P for pgsql-general@lists.postgresql.org; Wed, 17 Sep 2025 03:47:49 +0000 Received: by mail-oi1-x234.google.com with SMTP id 5614622812f47-43d271944beso250923b6e.0 for ; Tue, 16 Sep 2025 20:47:46 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20230601; t=1758080864; x=1758685664; darn=lists.postgresql.org; h=to:subject:message-id:date:from:in-reply-to:references:mime-version :from:to:cc:subject:date:message-id:reply-to; bh=vo7FlHNKYka5Nw26rub55ziA2Zdj0BA9JPgSYrMF9MM=; b=WpbXALUc8nkyugVhZk8qroSHpHAdHQmh24gAqnIQmCIP5lkE3J7JCZkwpJZKdZcrUm 80UEX1RVCeyuDebZPGlMvZmXK9MSYf7ES1W/pky8YsTr05O2NkhwyLAeGxbJrnHOWKMw 4Bhj56cnclsREqHk1lGRIhb4wTk7PaWkqDDmyH5tPGbOS1HYyZeU55FxXXF9pM4e3ViB TG681EU1HKOtmpjMXXEAPoE/8/pjvqfPfieDYWInTNrPj3VG+4kPHy03Ezs9AXPvcF73 VquiDCc8NUde978O+6YIoFv29lSD4KCH6gWs6Tq9d6e81FiQazZ9NiVEvpzaFTP3oYOr ILUA== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1758080864; x=1758685664; h=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=vo7FlHNKYka5Nw26rub55ziA2Zdj0BA9JPgSYrMF9MM=; b=J7LK9zMr8RqciOEW+a3JsERqW2EVmhqD8Nygq98dsue+0dSjON5bAKOnYKzPuOyUVE gRT6Cs3QboUA61HrfF9G0yMS7OIiZHbVq4A7gZolJNg+jz7brcsdWeEo6YtucaaEdCas 0b5ZOPeQ9wQvxvwLk1YnfWaR4xqFunPTDmxNmirZgZeZEga4zgIL1OlC86MnUjyFJkng +/5ElXOHxzZHOI25o0iVBIJj9M+yx1mb+m0ekejsXE2QYxhwggG+ipLbcxURaajwb1Nw PJCJ1KOnTP8tjADymnleeihe3Ys7zE1k2X++Q0iwv7IzWUSWQMZsXFu2i2i9bUaLY3bZ dtPQ== X-Gm-Message-State: AOJu0Ywr0F2dlKKiatRZYs5VU3B05I/Rs0/oJoz1FKijWk92+VSyMngm jrOXvqoQQ8aiHKtoe6pBtA7JZ0GLe0ClWF0gm0txNYwWYwiAusPxVzMUcpmZrKXcu1i/b/ykW3w iX3VZXWxFQGmgj1ZS4LMkg2L+Uf14PxBTKICO X-Gm-Gg: ASbGncvCj6/dFzK7KArQWgF7VfEZtRIcn3VEgbdtKLoVESYnZsrk8XUnrlRrJKSD/nc HGIEtCyVkuucr0Np5iFmpBJmzQnKA26sqa5pOpkGAyua8rx+xBsvOM8JkDbNKmeGh2601btAiAm EXt1qP0Fj6nLRSrznkMJrLE4KFAnLQvHvZZjLZ15pV1JFMpy5fYAoMvZWMe8e3fbokXzllyNKOY E8n5QGJ X-Google-Smtp-Source: AGHT+IH7216nocbFPjPkt+e9ifgnkpeyBhoZSm2mlNmkINHgLLguiZ9asOyj0EdM+yFzCQYBvogaEcjyt1rbuLPnJKY= X-Received: by 2002:a05:6808:4f0f:b0:43d:2e4a:e5c0 with SMTP id 5614622812f47-43d3f31d67dmr2370009b6e.1.1758080863799; Tue, 16 Sep 2025 20:47:43 -0700 (PDT) MIME-Version: 1.0 References: In-Reply-To: From: Ron Johnson Date: Tue, 16 Sep 2025 23:47:32 -0400 X-Gm-Features: AS18NWBIyA6wOKsJur0Pcz6Cp1GBGy2OBDVHedRMskHV2yM4RXEO2P91bN2IGZQ Message-ID: Subject: Re: pg_restore scan To: "pgsql-generallists.postgresql.org" Content-Type: multipart/alternative; boundary="0000000000005f8da2063ef7195f" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --0000000000005f8da2063ef7195f Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable 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 wro= te: > 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}.lo= g >> >> On Tue, Sep 16, 2025 at 8:54=E2=80=AFPM R Wahyudi w= rote: >> >>> 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 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! >> > --=20 Death to , and butter sauce. Don't boil me, I'm still alive. lobster! --0000000000005f8da2063ef7195f Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable

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-t= hreaded compression program.

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

On Tue, Sep 16, 2025 at 10:50=E2=80=AFPM R Wahyudi <rwahyudi@gmail.com> wrote:
Sorry for not including the= full command - yes , its piping to a compression command :=C2=A0
=C2=A0| lbzip2 -n <threadsforbzipgoeshere>--best > <filenamego= eshere>


I think=C2=A0we found th= e issue! I'll do further testing and see how it goes !





On Wed, 17 Sept 2025 at 11:02, Ron Johnson <ronljohnsonjr@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 custo= m 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> wrote:
pg_dump was done using the fo= llowing 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 Kl= aver <adr= ian.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!
--0000000000005f8da2063ef7195f--