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 1uygZB-00BMaD-VD for pgsql-general@arkaria.postgresql.org; Wed, 17 Sep 2025 01:02:38 +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 1uygZ9-003h9N-SC for pgsql-general@arkaria.postgresql.org; Wed, 17 Sep 2025 01:02:36 +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 1uygZ9-003h9F-GL for pgsql-general@lists.postgresql.org; Wed, 17 Sep 2025 01:02:36 +0000 Received: from mail-oa1-x2b.google.com ([2001:4860:4864:20::2b]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.96) (envelope-from ) id 1uygZ6-001Dma-01 for pgsql-general@lists.postgresql.org; Wed, 17 Sep 2025 01:02:35 +0000 Received: by mail-oa1-x2b.google.com with SMTP id 586e51a60fabf-3305aceab00so5405344fac.2 for ; Tue, 16 Sep 2025 18:02:32 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20230601; t=1758070951; x=1758675751; 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=nAxZFHs7wahFKMo8inFbs4vfgkB1QbnaPHNEgH0Ruk0=; b=dXP49FzL1+s+BPSGQa1vDXNtjVksg+vNwyNt0gmvvn4haTX0mcByctZa6jDJiIZYxP Zn6CVd8L8tk+tTOvIULyQtpbh+ysUff35k4uiytKED8vnLMUTSpAA5Brr8ircJxfDe82 cIcaiCGCgT6otWGSCAwEHiEXY7ifzlqj5BVrrUrtkiIR07ZHxNpBrC2svvhhVmYNhnqS xEb2pC/HqOUMrpC46YTSvGScO5M9jTpQDqDUNIp8RwvPlq3JTDQb/2+hS+1WvReh/S4J oDZ7Tqlp0cOvYHepGkUGJOvPWcrQTMuyCsC4xCA5IZMGa2BGeR6i5+eRZvMWi2Xb+qqj 4S1Q== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1758070951; x=1758675751; 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=nAxZFHs7wahFKMo8inFbs4vfgkB1QbnaPHNEgH0Ruk0=; b=j/+zKLAiNiPjX/6NbyZr1AGkKAWXprVoBEmMfUhhxnKLBFaDwKjMkwx7eplslRa3Hq drPPZKiPsqmN9FuTwdRQM84OgGAnMBeDdg/xAK86cGdyzbHH2MBGZHG7HDJkLOALJ4aN Io/VMUywdTMy8Ir9mQG7InZPXKxjucFbDi7WoldrE0+t4/H52RUmDq1C1DKEgTlJ3cYA 126MC6Vtoy233IUufwf6HXOec/ESB2wJfcPF5XqONzVQcJlk/RMWeklD8uP6hFFTaZJl 5W9pD08oGoGkNaf85tcg7UYfbmXgTRih9K9XdhQVXECthDGbJtZzTpW/jGBMDgDaChCJ 9iLw== X-Gm-Message-State: AOJu0YyXuLZYEt3mhXKSCV7pTgpX6xzh/PtpaPvVk/aFu7dgPd0eqUMF a3a91TYXMXoze9CtecvsHuxn8gg9LvY9HXV1u3+ScmAoxsJSC7uxg2iAqWFmF79k/nnV6M12lbV pd0xxEOe6CAVSk4RvaIFqDP/AftwE3JttPnl+ X-Gm-Gg: ASbGncsVNlfrfjWdioPn8v9ly2d41sk7j7sTkDHgPwUaTganwiuKLdEyjOZXas+rcck U55Cf8R36uTREUoUdpRHIia/+rdJG90+GyWo+0DvyhkTbWfwvnOVMvozeHQE8WtIB7EeBcGsHLf qMnLhnRvnUUiM22pwnv7uV9eJWWXhFEch2/2wieXEhiC3PR/BvTlcyPO+eBuMDanJGgtXdjnJVC kpJCHt1 X-Google-Smtp-Source: AGHT+IHWspdZmEBwFaI+ZJ2l97AyNK3V3NEI+oktkp5JunTaY97XT0GRGVzW+nFjX64R4hMfkjpfKmVzRdgHKdfOhA0= X-Received: by 2002:a05:6870:2192:b0:331:3bfc:480c with SMTP id 586e51a60fabf-335c03470a9mr222055fac.48.1758070950691; Tue, 16 Sep 2025 18:02:30 -0700 (PDT) MIME-Version: 1.0 References: In-Reply-To: From: Ron Johnson Date: Tue, 16 Sep 2025 21:02:19 -0400 X-Gm-Features: AS18NWBXTko8RHk_TE1bQJARIpBuhqqe1P-Qn5mRavqHBXWpdCeD7jAprIfIruI Message-ID: Subject: Re: pg_restore scan To: "pgsql-generallists.postgresql.org" Content-Type: multipart/alternative; boundary="000000000000818966063ef4ca52" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --000000000000818966063ef4ca52 Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable 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 wrot= e: > 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 >> > --=20 Death to , and butter sauce. Don't boil me, I'm still alive. lobster! --000000000000818966063ef4ca52 Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
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 $BackupDir<= /div>
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 do= ne 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 0= 8: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!
--000000000000818966063ef4ca52--