Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1uye7m-00Aqew-Fg for pgsql-general@arkaria.postgresql.org; Tue, 16 Sep 2025 22:26:10 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.94.2) (envelope-from ) id 1uye7k-002wA6-2D for pgsql-general@arkaria.postgresql.org; Tue, 16 Sep 2025 22:26:08 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1uye7j-002w9y-Jh for pgsql-general@lists.postgresql.org; Tue, 16 Sep 2025 22:26:08 +0000 Received: from mail-pg1-x534.google.com ([2607:f8b0:4864:20::534]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.96) (envelope-from ) id 1uye7e-000nMm-2c for pgsql-general@lists.postgresql.org; Tue, 16 Sep 2025 22:26:07 +0000 Received: by mail-pg1-x534.google.com with SMTP id 41be03b00d2f7-b4f18ddd8c3so591795a12.1 for ; Tue, 16 Sep 2025 15:26:03 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20230601; t=1758061562; x=1758666362; darn=lists.postgresql.org; h=to:subject:message-id:date:from:mime-version:from:to:cc:subject :date:message-id:reply-to; bh=VrIB/B8WIb3Bn4HheCDmGlS92MVsSX/hG5QbBez6L5w=; b=PMCJYJelZ1nHs4jeMwAsfk6kYffEXT2otHHWZ5FVAB7iES5ZLLRXYvHAuJ3F+wMyMA qb8pVmApsynBk2YchlEnkEko6jX+S+Bg5coGvWn1u5C+thGIORCCJhCa6v6Ysl9CNzdF N3ZtnG6ABseRgZnS+o56VCgqYlIk9j+H6zH8Xuy9UBJIP9gtD3JxnqDhIXhp2+uV8dm7 PYDRE4BaRYrZkGCeDFPDPM8T3fE19GeAOJsi5HYHNef/NsHxsAMf8ktljknWLJGpbNa9 wACzHXv87RhurVLdGq4Oh2tHl94J8doDhEY30MFtgmAv7GYFm6FHSnaRAkvKPH99pTAX rycw== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1758061562; x=1758666362; h=to:subject:message-id:date:from:mime-version:x-gm-message-state :from:to:cc:subject:date:message-id:reply-to; bh=VrIB/B8WIb3Bn4HheCDmGlS92MVsSX/hG5QbBez6L5w=; b=uYpeBpOCuSIN6aczxHhe9+bO31vGKUpepzsXylqaKbyYf/iynIPWaLnuE/OCBV2d9c Zu0v/r+qkWFNm/q/NvAh2e5wEKgLtUFm8zAEXq46H1bsvPr04UxAOf74jEXPJnihUrAB RwiUmRPtHfxwoVJP6VuBe0Lu9022IWFei+EhMIUCvf9jK4ImoTLDZNfkxXAvZrguhXJ8 LCJ8E302Jua0qF8ptfdMbQYqbm2sjaofe/Efas5T2eJcJLKHdS/hbIUIrSQ0LxX9Q4iF mLH0WBa4+jnq7Fyu679JRbAIQ5CjEgTv2ANeErRt9XJ7NDRR79WkplW6+/ejdPPUUW62 L3Yw== X-Gm-Message-State: AOJu0Ywf+zSrZVZlup2JGsnvnbfIB3kvczg0Ne10Bq4bJo/PYEjsGpWb 9oupUWN1cdFFc6AN5BerQlgaQJr+oRKcsUMKuwvkmUevJkd4oYNrdZrvk22fqRK37axu+v+5tUF DCpCwKrbwFBrtk5/3yUaEW7kBdxAQVPJ1kRL7zMQ= X-Gm-Gg: ASbGncvEpLvsYZB+Wfjlwa+bk1Sg04PJMYuwSf0gGXEW0LuFJ/WX5tg4KNfzt0EUPid WtDenyRFTtmW1n+4ujSsg45scmEc0b2sfZCT1/VGfySTUK9RuC2MWn2rjZj+v89yDEphXWV1SL9 yPOx6WYI7CSz7iJsrEuAmM6yrunYm8ugiBtGpeNeunxj3b0TC0NWlCV811PwQKNf1r2NvYtRx7x +y5kNbD2Rgc8xU0Wycq X-Google-Smtp-Source: AGHT+IGUULpg1snDLp0IMv6WJNkqfZF8MIf7icg0mL5gVIIHt6EQA7iL1EfaEdonDzMWmgJpUHqdJcH3iQmTQXamXOk= X-Received: by 2002:a17:90b:384f:b0:32e:74b6:4ef9 with SMTP id 98e67ed59e1d1-32ee3dc9f41mr10165a91.0.1758061561537; Tue, 16 Sep 2025 15:26:01 -0700 (PDT) MIME-Version: 1.0 From: R Wahyudi Date: Wed, 17 Sep 2025 08:25:49 +1000 X-Gm-Features: AS18NWA8zaPTFcGADWWV57dmThNtMPFBohQUqf4PD_gwqQk0F1YdQctChRzZrcY Message-ID: Subject: pg_restore scan To: pgsql-general@lists.postgresql.org Content-Type: multipart/alternative; boundary="000000000000de69c5063ef29ab9" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --000000000000de69c5063ef29ab9 Content-Type: text/plain; charset="UTF-8" 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 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) --000000000000de69c5063ef29ab9 Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable

I'm trying to tr= oubleshoot the slowness issue with pg_restore and stumbled across a recent = post about pg_restore scanning the whole file :=C2=A0
> "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 : ht= tps://www.postgresql.org/message-id/E48B611D-7D61-4575-A820-B2C3EC2E0551%40= gmx.net

I'm currently ha= ving this same issue.=C2=A0
=C2=A0
At= the early stage of restoration I can see lots of disk writes activities bu= t as time goes by, disk writes activities are reduced.
I= can see the COPY process in postgres but not using any CPU, and the proces= s that uses CPU are pg_restores.=C2=A0

I can recreate this issue when restoring a specific table to stdout.= =C2=A0

ie :
p= g_restore -vvvv -t <some_table_at_the> DB.pgdump -f -

If the table is at the bottom of the TOC it wil= l take=C2=A0 hours before I get a result, but I get an almost immediate res= ult when the table is at the top.=C2=A0
=C2=A0parallel r= estore suffers with the same issue where each process has to perform a scan= for each table.

What is the bes= t way to speed up the restore ?=C2=A0


More info about my environment :=C2=A0
<= div class=3D"gmail_default" style=3D"font-family:trebuchet ms,sans-serif;co= lor:#073763">pg_restore (PostgreSQL) 17.6

=
Archive :=C2=A0
; Archive created at 2025-= 09-16 16:08:28 AEST
; =C2=A0 =C2=A0 dbname: DB
; =C2=A0 =C2=A0 TOC En= tries: 8221
; =C2=A0 =C2=A0 Compression: none
; =C2=A0 =C2=A0 Dump Ve= rsion: 1.14-0
; =C2=A0 =C2=A0 Format: CUSTOM
; =C2=A0 =C2=A0 Integer:= 4 bytes
; =C2=A0 =C2=A0 Offset: 8 bytes
; =C2=A0 =C2=A0 Dumped from = database version: 14.15
; =C2=A0 =C2=A0 Dumped by pg_dump version: 14.19= (Ubuntu 14.19-1.pgdg22.04+1)






--000000000000de69c5063ef29ab9--