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 1uskBa-00ACun-EZ for pgsql-general@arkaria.postgresql.org; Sun, 31 Aug 2025 15:41:44 +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 1uskBZ-0021bK-Mj for pgsql-general@arkaria.postgresql.org; Sun, 31 Aug 2025 15:41:42 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1uskBY-0021bC-MS for pgsql-general@lists.postgresql.org; Sun, 31 Aug 2025 15:41:41 +0000 Received: from fhigh-a3-smtp.messagingengine.com ([103.168.172.154]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.96) (envelope-from ) id 1uskBW-002qkY-0B for pgsql-general@lists.postgresql.org; Sun, 31 Aug 2025 15:41:40 +0000 Received: from phl-compute-06.internal (phl-compute-06.internal [10.202.2.46]) by mailfhigh.phl.internal (Postfix) with ESMTP id 594AA140008C; Sun, 31 Aug 2025 11:41:35 -0400 (EDT) Received: from phl-mailfrontend-02 ([10.202.2.163]) by phl-compute-06.internal (MEProxy); Sun, 31 Aug 2025 11:41:35 -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=1756654895; x=1756741295; bh=lieOz89NF5mfKBoFYQ7uPTknxTOrDWIKR5+9jfbhFq0=; b= frFlLRElTinhqQYLoJAtbOukxler1AfRpVUvN+gZIJc4xclsMvVall/E8eBGufGy rq+I68dEwfDiPupJ4zSj/FDAH1/OHL6vesSzpYx4zVCHKxZP0dg6h3aIA1Sgj1Ht ZyNyr0/nvqbPrLK+XPw5BvJCCMZUbGSiFFVbQ0naeyWNrBm30jwycIdeH916iu4r Um5ddlpklE7BKtcTl02O/jGY3BRpAqSAAk3l6zHIrwjxru+yemR7tq2FTZuVNTN0 w+wHdL7SWuqstQwKPnqJlDuxGDYU//M/j7MDGfhsAOWRIiWCsvo/FoglfaoO5qGD Bd+ByvPX6s4+sPh1NHRATw== 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=1756654895; x= 1756741295; bh=lieOz89NF5mfKBoFYQ7uPTknxTOrDWIKR5+9jfbhFq0=; b=H wulKDEVVlE45x+rGE+8sgS8Rs5ePfZonyw6sLXbI8tIf/1cgDeA19gRsVyzPn+4i /fBWTQtVf+RBzihS+bMFZxJN6anSJK+EEhp2wd17iemqEgINGMSNsvTHYAOUrvaO hekYkxIropcfT30bRcVeRgxGOY7p5q4faUSEgU3s1cNRJ9lQNCGPUL+Mbybm8OaE JwQRE4RwdG7xu/0q2HDShDsfE42vzltpC+4V1bbUGHBHlKaKE1cPYJCHCotvQS1q lxAj1TatUrnKRNs6p/xMp6HwDGtN83BC+3yz5KLSlmmT6h86MA4yV8z+60BDxzG8 vfqrKe6lsUHncVA2iFjuQ== X-ME-Sender: X-ME-Received: X-ME-Proxy-Cause: gggruggvucftvghtrhhoucdtuddrgeeffedrtdefgddukeelieehucetufdoteggodetrf dotffvucfrrhhofhhilhgvmecuhfgrshhtofgrihhlpdfurfetoffkrfgpnffqhgenuceu rghilhhouhhtmecufedttdenucenucfjughrpefkffggfgfuvfevfhfhjggtgfesthejre dttddvjeenucfhrhhomheptegurhhirghnucfmlhgrvhgvrhcuoegrughrihgrnhdrkhhl rghvvghrsegrkhhlrghvvghrrdgtohhmqeenucggtffrrghtthgvrhhnpefhveeihfevle fffedthedukeevfefhfedtgeeifeevteffgfefhedtffetudettdenucevlhhushhtvghr ufhiiigvpedtnecurfgrrhgrmhepmhgrihhlfhhrohhmpegrughrihgrnhdrkhhlrghvvg hrsegrkhhlrghvvghrrdgtohhmpdhnsggprhgtphhtthhopedvpdhmohguvgepshhmthhp ohhuthdprhgtphhtthhopehjihhmihhssehgmhigrdhnvghtpdhrtghpthhtohepphhgsh hqlhdqghgvnhgvrhgrlheslhhishhtshdrphhoshhtghhrvghsqhhlrdhorhhg X-ME-Proxy: Feedback-ID: i76984098:Fastmail Received: by mail.messagingengine.com (Postfix) with ESMTPA; Sun, 31 Aug 2025 11:41:34 -0400 (EDT) Message-ID: Date: Sun, 31 Aug 2025 08:41:34 -0700 MIME-Version: 1.0 User-Agent: Mozilla Thunderbird Subject: Re: In-order pg_dump (or in-order COPY TO) To: Dimitrios Apostolou Cc: pgsql-general@lists.postgresql.org References: <3541781s-75o7-26pp-46pp-qs54o4406192@tzk.arg> <4ss66r31-558o-qq24-332q-no351p7n5osr@tzk.arg> <0dc17a73-2372-4613-a50e-610ae7d02b93@aklaver.com> Content-Language: en-US From: Adrian Klaver In-Reply-To: Content-Type: text/plain; charset=UTF-8; format=flowed Content-Transfer-Encoding: 7bit List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk On 8/30/25 18:21, Dimitrios Apostolou wrote: > Sorry I was not remembering the details. Probably there is a TOC in your dump file, but it does not contain any positions for the data. The pg_restore command has to scan the whole file in advance, and fill in the TOC offsets in memory. > > This 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. It may be that my coffee is not strong enough, but I don't understand what you are trying to say. Are you using, from previous post, the following?: "Two patches for speeding up scanning an archive without TOC, like the one I'm having (because it is piped into borg, instead of written to file). These were activated, but shouldn't matter. They only build the TOC in pg_restore's memory. " The part I don't see is how you get a dump file without a TOC? When I do the pg_dump and pipe it to Borg the resulting file has a TOC. Can you show the rest of the | borg create ... command? > > Thank you for testing. > Dimitris > > On 30 August 2025 20:19:13 CEST, Adrian Klaver wrote: -- Adrian Klaver adrian.klaver@aklaver.com