MIME-Version: 1.0
References: <CALWQLzRmzT7bo0c6CUX9=L_oLD3oUN8fZ5yyGLEwe7y5rWoxmQ@mail.gmail.com>
 <d39f835e-80dd-4e9f-8a19-c9af363be6d6@aklaver.com>
In-Reply-To: <d39f835e-80dd-4e9f-8a19-c9af363be6d6@aklaver.com>
From: R Wahyudi <rwahyudi@gmail.com>
Date: Wed, 17 Sep 2025 10:54:27 +1000
Message-ID: <CALWQLzTWUnA09cAfsuzuv5Kmb+S8gcC9uBypRYW0UtsQgMyPJg@mail.gmail.com>
Subject: Re: pg_restore scan
To: Adrian Klaver <adrian.klaver@aklaver.com>
Cc: pgsql-general@lists.postgresql.org
Content-Type: multipart/alternative; boundary="00000000000069d20a063ef4ae49"
Archived-At: <https://www.postgresql.org/message-id/CALWQLzTWUnA09cAfsuzuv5Kmb%2BS8gcC9uBypRYW0UtsQgMyPJg%40mail.gmail.com>
Precedence: bulk

--00000000000069d20a063ef4ae49
Content-Type: text/plain; charset="UTF-8"

pg_dump was done using the following command :
pg_dump -Fc -Z 0 -h <host> -U <user> -w -d <database>

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 <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.klaver@aklaver.com
>

--00000000000069d20a063ef4ae49
Content-Type: text/html; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

<div dir=3D"ltr"><div class=3D"gmail_default" style=3D"font-family:trebuche=
t ms,sans-serif;color:#073763">pg_dump was done using the following command=
 :=C2=A0<br>pg_dump -Fc -Z 0 -h &lt;host&gt; -U &lt;user&gt; -w -d &lt;data=
base&gt;=C2=A0</div></div><br><div class=3D"gmail_quote gmail_quote_contain=
er"><div dir=3D"ltr" class=3D"gmail_attr">On Wed, 17 Sept 2025 at 08:36, Ad=
rian Klaver &lt;<a href=3D"mailto:adrian.klaver@aklaver.com">adrian.klaver@=
aklaver.com</a>&gt; wrote:<br></div><blockquote class=3D"gmail_quote" style=
=3D"margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204);padding=
-left:1ex">On 9/16/25 15:25, R Wahyudi wrote:<br>
&gt; <br>
&gt; I&#39;m trying to troubleshoot the slowness issue with pg_restore and =
<br>
&gt; stumbled across a recent post about pg_restore scanning the whole file=
 :<br>
&gt; <br>
&gt;=C2=A0 &gt; &quot;scanning happens in a very inefficient way, with many=
 seek calls and <br>
&gt; small block reads. Try strace to see them. This initial phase can take=
 <br>
&gt; hours in a huge dump file, before even starting any actual restoration=
.&quot;<br>
&gt; see : <a href=3D"https://www.postgresql.org/message-id/E48B611D-7D61-4=
575-A820-" rel=3D"noreferrer" target=3D"_blank">https://www.postgresql.org/=
message-id/E48B611D-7D61-4575-A820-</a> <br>
&gt; B2C3EC2E0551%<a href=3D"http://40gmx.net" rel=3D"noreferrer" target=3D=
"_blank">40gmx.net</a> &lt;<a href=3D"https://www.postgresql.org/message-id=
/" rel=3D"noreferrer" target=3D"_blank">https://www.postgresql.org/message-=
id/</a> <br>
&gt; E48B611D-7D61-4575-A820-B2C3EC2E0551%<a href=3D"http://40gmx.net" rel=
=3D"noreferrer" target=3D"_blank">40gmx.net</a>&gt;<br>
<br>
This was for pg_dump output that was streamed to a Borg archive and as <br>
result had no object offsets in the TOC.<br>
<br>
How are you doing your pg_dump?<br>
<br>
<br>
<br>
-- <br>
Adrian Klaver<br>
<a href=3D"mailto:adrian.klaver@aklaver.com" target=3D"_blank">adrian.klave=
r@aklaver.com</a><br>
</blockquote></div>

--00000000000069d20a063ef4ae49--