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."
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)
