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 1uyiFQ-00Bhsb-2r for pgsql-general@arkaria.postgresql.org; Wed, 17 Sep 2025 02:50:20 +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 1uyiFN-0049Eo-Nc for pgsql-general@arkaria.postgresql.org; Wed, 17 Sep 2025 02:50:18 +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 1uyiFN-0049Eg-8k for pgsql-general@lists.postgresql.org; Wed, 17 Sep 2025 02:50:18 +0000 Received: from mail-pf1-x42c.google.com ([2607:f8b0:4864:20::42c]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.96) (envelope-from ) id 1uyiFL-000pM8-2i for pgsql-general@lists.postgresql.org; Wed, 17 Sep 2025 02:50:17 +0000 Received: by mail-pf1-x42c.google.com with SMTP id d2e1a72fcca58-7724ad1e7c9so810181b3a.3 for ; Tue, 16 Sep 2025 19:50:16 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20230601; t=1758077415; x=1758682215; 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=8JgIJFRk4wEerS8CG0nyH8oxlFXnYvT/GxUSKVaKe10=; b=GT7k8gvjd6XOW56XY/YEhqua7/2Yz8pGOgr71NimeCugTG4jsrgYwRCh0y/88dF+tk FP79ckcO03XFaUa5YAZmaunbinfaynT8XS6ZsZEXQFoSkC2vWD+zWYnld5Y41VN/83DO i2TEIYFzYHmg8mMkYwIV8NsnN6e/nqs/xlhyxod0T6zE3ZioF32Lf0uR1BxBfB97x+80 wPhRGsaG19RGlXrn4qiCG71AOgbBJZPYz8fal3LLzx7MOF1WKBi2dhJEeiAzMQQx1FwC kLjKf+/KZk/XNpmCFapZZAJNwK+Mt7BBD1kxQ9luTXcZSZ3gEV/lBKA/c/WSptKT6OQp gaHg== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1758077415; x=1758682215; 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=8JgIJFRk4wEerS8CG0nyH8oxlFXnYvT/GxUSKVaKe10=; b=lE6qG02tLpGdrLYQEY7/gcw9Wt3ks95isx28ddPDdR55xIHIHGPMA224kKinROny2b +xQhleJFdvW191JdSGddcbTzZPSN3PAODyFTX4S6PA4g4g6NasG4UhC28CzFG6XrfGs3 ErlK82Vk5r60JRqQUAQkt7akg+Kd1YQbol/If0GawmtffetZJFqn31f37pP6ijS5kaCD OfkeltO4ZOA+iVoxKChiFvByWjjqA0Ka74+pXb4TLmHextPWBy5boBnzh0fgubJQ084X KhqfAq369JcAv41NvK3RAGo/C61Q9wphnsF2BLQ50ptfx44iac7vNOLQNfMaK73Bb9MR u/Iw== X-Gm-Message-State: AOJu0Yz2RnIZE1HHVud6DwERTKZfIBDS1uxmNuckhxtXPkEI6PekrY/w LcxXKzyRMlwRldgm4FCZ8APNgt/WufgxUvKrx+Z83WF5ZTaM1R/84gGTIvVSVL5uT9bCWFrsigG I0c/a5aaVIsOaVZGy/oO+mhcS2M4FTxU= X-Gm-Gg: ASbGnctFbFEatDcZ3WTiEPU+8DrQZXj88AChVurHWN6W+j38n+CMoEM2CmXeGHLH1YR RcbQ+qK2qnF4Y21ZDlbtRFWp6M4Q/P1JmZQd9B0TeBNBOUcXrvyd//Ex2cq2LjhBe9mH/pRF5GR NCJqX9SR4/vnuN3KlyEpUQ1a1UvOzQM1N4Hk+kvv3q1skRiVToa32a/kSvrjWso8asZLBvWJ10C UTYV3juS1J5iuzx+90L X-Google-Smtp-Source: AGHT+IHJGERixjB5mcy/un3TJqYjpvhjhvkniDuojVDCwtYFt9qJXshrD+F6xk0yoiI7RZMi1RcakD24R+/KBEBZHOU= X-Received: by 2002:a05:6a20:6a0e:b0:24e:2cee:957a with SMTP id adf61e73a8af0-27a70f0043bmr416832637.0.1758077415343; Tue, 16 Sep 2025 19:50:15 -0700 (PDT) MIME-Version: 1.0 References: In-Reply-To: From: R Wahyudi Date: Wed, 17 Sep 2025 12:50:03 +1000 X-Gm-Features: AS18NWCVYAe_exuLeno83H_O4niqUtFd5dAhRgk_XDyhJIeob9wCtI3qwgik5Xw Message-ID: Subject: Re: pg_restore scan To: Ron Johnson Cc: "pgsql-generallists.postgresql.org" Content-Type: multipart/alternative; boundary="000000000000d44b2c063ef64ba3" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --000000000000d44b2c063ef64ba3 Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable 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 wr= ote: > >> 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 fil= e >>> : >>> > >>> > > "scanning happens in a very inefficient way, with many seek calls >>> and >>> > small block reads. Try strace to see them. This initial phase can tak= e >>> > 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! > --000000000000d44b2c063ef64ba3 Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
Sorry for not i= ncluding the full command - yes , its piping to a compression command :=C2= =A0
=C2=A0| lbzip2 -n <threadsforbzipg= oeshere>--best > <filenamegoeshere>


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




On Wed, 1= 7 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 i= n the TOC.

This how I do custom dumps:
<= font face=3D"monospace">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 &= lt;rwahyudi@gmail.c= om> wrote:
pg_dump was done using the following command :=C2= =A0
pg_dump -Fc -Z 0 -h <host> -U <user> -w -d <database&= gt;=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!
--000000000000d44b2c063ef64ba3--