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 1uzMRS-002aTf-6K for pgsql-general@arkaria.postgresql.org; Thu, 18 Sep 2025 21:45:26 +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 1uzMRQ-002H4l-Kw for pgsql-general@arkaria.postgresql.org; Thu, 18 Sep 2025 21:45:24 +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 1uzMRP-002H4O-Ih for pgsql-general@lists.postgresql.org; Thu, 18 Sep 2025 21:45:24 +0000 Received: from fout-b8-smtp.messagingengine.com ([202.12.124.151]) by makus.postgresql.org with smtp (Exim 4.96) (envelope-from ) id 1uzMRM-0019vS-1p for pgsql-general@lists.postgresql.org; Thu, 18 Sep 2025 21:45:22 +0000 Received: from phl-compute-05.internal (phl-compute-05.internal [10.202.2.45]) by mailfout.stl.internal (Postfix) with ESMTP id C4D191D002E5; Thu, 18 Sep 2025 17:45:19 -0400 (EDT) Received: from phl-mailfrontend-02 ([10.202.2.163]) by phl-compute-05.internal (MEProxy); Thu, 18 Sep 2025 17:45:19 -0400 DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=aklaver.com; h= cc:cc:content-transfer-encoding:content-type:content-type:date :date:from:from:in-reply-to:in-reply-to:message-id:mime-version :references:reply-to:subject:subject:to:to; s=fm3; t=1758231919; x=1758318319; bh=IBVxDwlZtLAcS7HP4gSW7YrSK7VHhol1vnu2sIrxi5Q=; b= dC6FRhyQUmOG0+SVLIS8hL/Ehm2h/wVVCfyET2z2slC/qpYnen2zVn7JYN6+YTzw 2otoxB22Nyp1wwxmU5+f7GSnjYMknEcQC4x7TwXPH+BXHVlVuI1MPJbrrH0z2N3I b9khBLEzGe3GIHFQTaRKHkx9XfJWRRmGy5/6OtP1eDm1q03U5+32cXxwA/UiEPfu SzI/0x6vQIRYH6pwdFH4DdRE4SdJxdnxfRR3TuyvYIxIwOIiQRWR2LrMyBexW039 6/C/mmC6Z/ukvD+UI6irWKN50ekEPhKpoCOQtCPfFIItAmFtj58nvcWPq6GsfJmK 7HJoys0FgshK+UsWsDQ3sA== DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d= messagingengine.com; h=cc:cc:content-transfer-encoding :content-type:content-type:date:date:feedback-id:feedback-id :from:from:in-reply-to:in-reply-to:message-id:mime-version :references:reply-to:subject:subject:to:to:x-me-proxy :x-me-sender:x-me-sender:x-sasl-enc; s=fm1; t=1758231919; x= 1758318319; bh=IBVxDwlZtLAcS7HP4gSW7YrSK7VHhol1vnu2sIrxi5Q=; b=R dEtVDRicWbCe6jk2buM1bKgNcaUWHFq3hDDB4hQy4h76QmuFk8AnwL1THlQrEEKz PfxDO/R0aMBPsRHd9zG9CF1ygF/JJSxVitZkfbCNpcnB0/qf4XmXd/76v40Bpin/ 39fXXcnGKjjUtA1ETCkP71oGlLuFHUx8j8RArZfRAT3yJqYIG0MejDOJcC509kS+ S0q0Snpl/pKDJExhqsizRVkD53rgMNvTucae+GKvHGn3GOK75iSApoCAetKFRnHl vWwVyxrrcn9ayFK1rpJKH00gdS4Iv6UIl2JXEBOfPwjwjWgIOvr+T5Emj2La2HPN l4dWi0CNukhzVPwpOKrog== X-ME-Sender: X-ME-Received: X-ME-Proxy-Cause: gggruggvucftvghtrhhoucdtuddrgeeffedrtdeggdegjeeggecutefuodetggdotefrod ftvfcurfhrohhfihhlvgemucfhrghsthforghilhdpuffrtefokffrpgfnqfghnecuuegr ihhlohhuthemuceftddtnecusecvtfgvtghiphhivghnthhsucdlqddutddtmdenucfjug hrpefkffggfgfuvfevfhfhjggtgfesthekredttddvjeenucfhrhhomheptegurhhirghn ucfmlhgrvhgvrhcuoegrughrihgrnhdrkhhlrghvvghrsegrkhhlrghvvghrrdgtohhmqe enucggtffrrghtthgvrhhnpeffuefhgfffgedviefhteejkeffveevtefhueegtddttedv kedviedukedvfeeiueenucffohhmrghinhepphhoshhtghhrvghsqhhlrdhorhhgpdhpgh hhrdhprgdruhhspdhgihhthhhusgdrtghomhdpgedtghhmgidrnhgvthenucevlhhushht vghrufhiiigvpedtnecurfgrrhgrmhepmhgrihhlfhhrohhmpegrughrihgrnhdrkhhlrg hvvghrsegrkhhlrghvvghrrdgtohhmpdhnsggprhgtphhtthhopeefpdhmohguvgepshhm thhpohhuthdprhgtphhtthhopehrfigrhhihuhguihesghhmrghilhdrtghomhdprhgtph htthhopehrohhnlhhjohhhnhhsohhnjhhrsehgmhgrihhlrdgtohhmpdhrtghpthhtohep phhgshhqlhdqghgvnhgvrhgrlheslhhishhtshdrphhoshhtghhrvghsqhhlrdhorhhg X-ME-Proxy: Feedback-ID: i76984098:Fastmail Received: by mail.messagingengine.com (Postfix) with ESMTPA; Thu, 18 Sep 2025 17:45:18 -0400 (EDT) Message-ID: <9a935fe8-d6e2-4ff2-ace3-8f6f1992c519@aklaver.com> Date: Thu, 18 Sep 2025 14:45:17 -0700 MIME-Version: 1.0 User-Agent: Mozilla Thunderbird Subject: Re: pg_restore scan To: R Wahyudi Cc: Ron Johnson , "pgsql-generallists.postgresql.org" References: Content-Language: en-US From: Adrian Klaver In-Reply-To: Content-Type: text/plain; charset=UTF-8; format=flowed Content-Transfer-Encoding: 8bit List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk 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 times : > 40 x pg_restore process doing 100% CPU > 40 x  postgres process doing COPY but using 0% CPU > ..... and zero disk write activity > > I don't see this behaviour when restoring the database that was dumped > with -Fd. > Also with an un-piped backup file, I can restore a specific table > without having to wait for hours. From the docs: https://www.postgresql.org/docs/current/app-pgrestore.html " -j number-of-jobs Only the custom and directory archive formats are supported with this option. The input must be a regular file or directory (not, for example, a pipe or standard input). Also, multiple jobs cannot be used together with the option --single-transaction. " > > > -- > > > > > > On Fri, 19 Sept 2025 at 01:54, Adrian Klaver > wrote: > > 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 this? > > It is not in the user documentation. > >  From the thread Ron referred to, there is an explanation here: > > https://www.postgresql.org/message- > id/366773.1756749256%40sss.pgh.pa.us message-id/366773.1756749256%40sss.pgh.pa.us> > > I believe the actual code, for the -Fc format, is in pg_backup_custom.c > here: > > https://github.com/postgres/postgres/blob/master/src/bin/pg_dump/ > pg_backup_custom.c#L723 master/src/bin/pg_dump/pg_backup_custom.c#L723> > > Per comment at line 755: > > " >   If possible, re-write the TOC in order to update the data offset > information.  This is not essential, as pg_restore can cope in most > cases without it; but it can make pg_restore significantly faster > in some situations (especially parallel restore).  We can skip this > step if we're not dumping any data; there are no offsets to update > in that case. > " > > > > > Regards, > > Rianto > > > > On Wed, 17 Sept 2025 at 13:48, Ron Johnson > > > >> 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 > > >     >> wrote: > > > >         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'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_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 > >                     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- > www.postgresql.org/message-id/E48B611D-7D61-4575-A820- www.postgresql.org/message-id/E48B611D-7D61-4575-A820->> > >                      > B2C3EC2E0551%40gmx.net > > > >                      > www.postgresql.org/message-id/ 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 > > adrian.klaver@aklaver.com > >                      > > > > > > > > >             -- > >             Death to , and butter sauce. > >             Don't boil me, I'm still alive. > >              lobster! > > > > > > > >     -- > >     Death to , and butter sauce. > >     Don't boil me, I'm still alive. > >      lobster! > > > > > -- > Adrian Klaver > adrian.klaver@aklaver.com > -- Adrian Klaver adrian.klaver@aklaver.com