public inbox for [email protected]
help / color / mirror / Atom feedFrom: Ron Johnson <[email protected]>
To: pgsql-generallists.postgresql.org <[email protected]>
Subject: Re: pg_restore scan
Date: Tue, 16 Sep 2025 23:47:32 -0400
Message-ID: <CANzqJaAkTPeiuadRfZ7S4L2N7H7ayjW7bHqsfZ5wRDDvAmu89w@mail.gmail.com> (raw)
In-Reply-To: <CALWQLzSGPUeVDDp15inXiAFJe_rS_JObr_17Qq6Ns0s-p0YSvQ@mail.gmail.com>
References: <CALWQLzRmzT7bo0c6CUX9=L_oLD3oUN8fZ5yyGLEwe7y5rWoxmQ@mail.gmail.com>
<[email protected]>
<CALWQLzTWUnA09cAfsuzuv5Kmb+S8gcC9uBypRYW0UtsQgMyPJg@mail.gmail.com>
<CANzqJaCQo6A+QLqJGeXGasoK3aSFL5Ehf5x-Gt0T9DOaRFoxKw@mail.gmail.com>
<CALWQLzSGPUeVDDp15inXiAFJe_rS_JObr_17Qq6Ns0s-p0YSvQ@mail.gmail.com>
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 <[email protected]> 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 <[email protected]>
> 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 <[email protected]> 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 <[email protected]>
>>> 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
>>>> [email protected]
>>>>
>>>
>>
>> --
>> 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!
view thread (13+ messages) latest in thread
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: [email protected]
Cc: [email protected], [email protected]
Subject: Re: pg_restore scan
In-Reply-To: <CANzqJaAkTPeiuadRfZ7S4L2N7H7ayjW7bHqsfZ5wRDDvAmu89w@mail.gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox