Re: pg_restore scan

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=zstd:long -v -d${db} -f ${db}.dump 2> ${db}.log

On Tue, Sep 16, 2025 at 8:54 PM R Wahyudi <rwahyudi(at)gmail(dot)com> wrote:

> 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(dot)klaver(at)aklaver(dot)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(dot)klaver(at)aklaver(dot)com
>>
>

--
Death to <Redacted>, and butter sauce.
Don't boil me, I'm still alive.
<Redacted> lobster!