public inbox for [email protected]
help / color / mirror / Atom feedFrom: R Wahyudi <[email protected]>
To: [email protected]
Subject: pg_restore scan
Date: Wed, 17 Sep 2025 08:25:49 +1000
Message-ID: <CALWQLzRmzT7bo0c6CUX9=L_oLD3oUN8fZ5yyGLEwe7y5rWoxmQ@mail.gmail.com> (raw)
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
I'm currently having this same issue.
At the early stage of restoration I can see lots of disk writes activities
but as time goes by, disk writes activities are reduced.
I can see the COPY process in postgres but not using any CPU, and the
process that uses CPU are pg_restores.
I can recreate this issue when restoring a specific table to stdout.
ie :
pg_restore -vvvv -t <some_table_at_the> DB.pgdump -f -
If the table is at the bottom of the TOC it will take hours before I get a
result, but I get an almost immediate result when the table is at the top.
parallel restore suffers with the same issue where each process has to
perform a scan for each table.
What is the best way to speed up the restore ?
More info about my environment :
pg_restore (PostgreSQL) 17.6
Archive :
; Archive created at 2025-09-16 16:08:28 AEST
; dbname: DB
; TOC Entries: 8221
; Compression: none
; Dump Version: 1.14-0
; Format: CUSTOM
; Integer: 4 bytes
; Offset: 8 bytes
; Dumped from database version: 14.15
; Dumped by pg_dump version: 14.19 (Ubuntu 14.19-1.pgdg22.04+1)
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: <CALWQLzRmzT7bo0c6CUX9=L_oLD3oUN8fZ5yyGLEwe7y5rWoxmQ@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