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
On Thu, Sep 18, 2025 at 5:37 PM R Wahyudi wrote:
> I've been given a database dump file daily and I've been asked to restore
> it.
> I tried everything I could to speed up the process, including using -j 40.
>
> I discovered that at the later stage of the restore process, the
> following behavio
>> The input must be a regular file or directory (not, for example, a pipe
or standard input).
Thanks again for the pointer!
I successfully ran a parallel restore with no warnings presented.
I didn't really pay attention to how the dump was taken until I
accidentally stumbled upon your post.
Re
On 9/18/25 2:36 PM, R Wahyudi wrote:
I've been given a database dump file daily and I've been asked to
restore it.
I tried everything I could to speed up the process, including using -j 40.
I discovered that at the later stage of the restore process, the
following behaviour repeated a few
I've been given a database dump file daily and I've been asked to restore
it.
I tried everything I could to speed up the process, including using -j 40.
I discovered that at the later stage of the restore process, the
following behaviour repeated a few times :
40 x pg_restore process doing 100% C
On 9/18/25 05:58, R Wahyudi wrote:
Hi All,
Thanks for the quick and accurate response! I never been so happy
seeing IOwait on my system!
Because?
What did you find?
I might be blind as I can't find information about 'offset' in pg_dump
documentation.
Where can I find more info about t
It's towards the end of this long mailing list thread from a couple of
weeks ago.
https://www.postgrespro.com/list/id/[email protected]
On Thu, Sep 18, 2025 at 8:58 AM R Wahyudi wrote:
> Hi All,
>
> Thanks for the quick and accurate response! I never been so happy se
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 wrote:
pg_dump was done using the following command :
pg_dump -Fc -Z 0 -h -U -w -d
On Wed, 17 Sept 2025 at 08:36, Adrian Klaver
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 sca
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 -
Sorry for not including the full command - yes , its piping to a
compression command :
| lbzip2 -n --best >
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 wrote:
> So, piping or redirecting to a file? If so, then that
On 9/16/25 17:54, R Wahyudi wrote:
pg_dump was done using the following command :
pg_dump -Fc -Z 0 -h -U -w -d
What do you do with the output?
--
Adrian Klaver
[email protected]
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 wrote:
> pg
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. Th
14 matches
Mail list logo
