Re: pg_restore scan

Hi All,

Thanks for the quick and accurate response! I never been so happy seeing
IOwait on my system!

I might be blind as I can't find information about 'offset' in pg_dump
documentation.
Where can I find more info about this?

Regards,
Rianto

On Wed, 17 Sept 2025 at 13:48, Ron Johnson <ronljohnsonjr(at)gmail(dot)com> wrote:

>
> PG 17 has integrated zstd compression, while --format=directory 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 PM R Wahyudi <rwahyudi(at)gmail(dot)com> wrote:
>
>> Sorry for not including the full command - yes , its piping to a
>> compression command :
>> | lbzip2 -n <threadsforbzipgoeshere>--best > <filenamegoeshere>
>>
>>
>> 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 <ronljohnsonjr(at)gmail(dot)com>
>> 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=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!
>>>
>>
>
> --
> Death to <Redacted>, and butter sauce.
> Don't boil me, I'm still alive.
> <Redacted> lobster!
>