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 <[email protected]> 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 <[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! >
