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 1usWlf-006cSd-1v for pgsql-general@arkaria.postgresql.org; Sun, 31 Aug 2025 01:22:04 +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 1usWld-0008UL-GU for pgsql-general@arkaria.postgresql.org; Sun, 31 Aug 2025 01:22:02 +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 1usWlc-0008UC-Vj for pgsql-general@lists.postgresql.org; Sun, 31 Aug 2025 01:22:01 +0000 Received: from mout.gmx.net ([212.227.17.21]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.96) (envelope-from ) id 1usWla-002bgs-0h for pgsql-general@lists.postgresql.org; Sun, 31 Aug 2025 01:22:00 +0000 DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmx.net; s=s31663417; t=1756603313; x=1757208113; i=jimis@gmx.net; bh=zchz2UzJnZTK3M16F2iuGG7za3zaiD6X0COKZS2DiL8=; h=X-UI-Sender-Class:Date:From:To:CC:Subject:In-Reply-To:References: Message-ID:MIME-Version:Content-Type:Content-Transfer-Encoding:cc: content-transfer-encoding:content-type:date:from:message-id: mime-version:reply-to:subject:to; b=hGygE9TjCZbjF1xlW/GZpI6Oo7YSaoaG/DQ0LXceN18GyMxILln+wXBtJMm9AmQs 88U8Ly5TJF04JG1DgMZ4uhQ2JHwCB3JGnKwC+NWwFSJ/nZVJQvonvF7vW4o6NCe8C UKmHzdzPSn0euEAQ2ifWbRZSRKK+5Aml6B8rlWizIOD+bsBe+30riypgWtcSxrMRK pfLP+ZjZADmZund+BhiejjJxjeK1K61JiKPkgc4wPLMHo8qv2wNcTTQTbBaJ+6H0o Or10IzPAMFPi9Rd5d9NNcl6neRZSRlcZ5hiOrwqFSiolc3G3oxLOIA9IQrnWgQfiN 0l5BjKLJBXqH4wIvew== X-UI-Sender-Class: 724b4f7f-cbec-4199-ad4e-598c01a50d3a Received: from ehlo.thunderbird.net ([84.215.109.181]) by mail.gmx.net (mrgmx105 [212.227.17.168]) with ESMTPSA (Nemesis) id 1MEV3C-1ugy2F0jQw-003JDs; Sun, 31 Aug 2025 03:21:53 +0200 Date: Sun, 31 Aug 2025 03:21:50 +0200 From: Dimitrios Apostolou To: Adrian Klaver , Dimitrios Apostolou CC: pgsql-general@lists.postgresql.org Subject: Re: In-order pg_dump (or in-order COPY TO) User-Agent: K-9 Mail for Android In-Reply-To: <0dc17a73-2372-4613-a50e-610ae7d02b93@aklaver.com> References: <3541781s-75o7-26pp-46pp-qs54o4406192@tzk.arg> <4ss66r31-558o-qq24-332q-no351p7n5osr@tzk.arg> <0dc17a73-2372-4613-a50e-610ae7d02b93@aklaver.com> Message-ID: MIME-Version: 1.0 Content-Type: text/plain; charset=utf-8 Content-Transfer-Encoding: quoted-printable X-Provags-ID: V03:K1:nQYK4sIAG77h+wY0HXWUBYvwTK7nGQIzsJyr0u2qeonaWNvBic5 jACXxocPGx4BmejfHW9/afB5J6YhagiP9+HcNFV+BUEMwAyPjZu1LqygQ81yFyODO7a7sYa 2aOBiPQSUKaXGQgt55dWkdyzEQmlmqJpBn3s+gdRM/GWn5JlN2A2ycXtuPfNOcAopQ2gYry Y0IoaOLSpkgCCdfeFwD6Q== X-Spam-Flag: NO UI-OutboundReport: notjunk:1;M01:P0:/SugcJuQZt8=;Z8Qxhlye6WDXh01ScnlQDmKnscZ hhVXD1Fmq8qwCmUVILdqUtRu7akBfI36lXJAMhRaS9X3UzORZeaYUhPLswK9BRTfEzJTgoG2v tv3ijx6A0DBGk5Z2X1bryXmfLn9fGf5Yx4YCfjtNfCW5uIGJo4n7qvqprtiXx4dbAV3VLj2HA fpXulX2C/Peoe6BA2hNhWvdmfiK6/mqiSg+Tx6fd7ZfFPq+32x0yCB7hYdJw6qiismI2lweQE H2Ohg6ceUKSGh/29N3bTG9o+ARKghpBn77EtzvgCASbEWNNtA4rLK5egQfilDoYqSdyK2Z6CO 5AReO8UAtgQRN76PgRLydBWNA2I3TQGrKFopHBdpqgHe4Z4eQGWli+GpOeuwhC9xGLwGZYSeR OFVi+JVxaEq/M5KqK90fd2mTMDX+jBT5/PRX6fDa6YXfdlhgK1D06GL56PKaOMH02YYNYudg+ DP9cx1Kh4u+vnfkAsovJBGD5c+LYemRQET2P5L/2yAD9HKfIQ5ZwkwWZRtpBxkiTjPfdfJhnb qjSB5W8CHLTGELHs9XfR4r9Q93nK6zAF0Lp/wGyyV56roiJwJOz42raK592uHF15/ZYC/iXiq lN+nn/w4230i6jgih9o2LwXOyPO+9CvJ9BJ4Y50eCT9yap+9Isy66noUoBM++IgXqT1kSNQUh 43vOWjrUr59y2VbyGg1ALh9yAC76fIMS3Wgt5r2M71KzW6i7QlHLVTm91bkQNp/ehPWiQw55V E51w0M8/zDp/s7Ejlagqv1ZDpN+I78gQk86gdgJrjq27V0/M7KWrXyjCC68uwVrPde4tT0aaF UKaftDQiuRV57ZTVOuvUBX4N+ypirZycfLiNMcQynCQSymkdHNld2nz/nAVT0Fc6U654GD9tk are5CNK+6+zWF0inw+Twnakdc1hct1sPSGVjzKLZS/AIBdi78tKmTpaGZUR3vqvo0utxC8DkY ZVKLLeAYN8ZrtSAWPD32gBgi6s4at3nm+LZCDSJNmh82jVZmP3Ycztu7xqFb6OJmUyj7CasSX V07fdpDjzqZONosP8jE4zeUeMwoOgslh6x7d8M33liWyzDl3AQVoMX2XRbNDj/Tes4etvtNws fiRXbv0lA/1FFlFHQ3SyjPtxhP2M5UXpSn5+n1WedJ12qK5XEWr8T01ZcMr5D5u1DV9qDEC8m 7TlbcepBb7T2NqQGK0Q9pMSxvVjnG55FJJEbX82jUnW46E4X4hO1gfCHmbhE0146Xp1wWHh7Z YNYLhoy/cAr1+//GN2tIKz+OX2Og3hoe9to2SqNq2ahUnfDY6UVPYiKA2ANJbf73O/rkZGPfq uh6idZFLzslBt6DW64MHT9P51VckMvtVbwcl94YWepW3dPW6AYLZNwnLunHpRMKvEY4bpuZ1x dCvUbWv0vMZFa21P6157Cr/2zG2r82BYezxBzeggdcoJYpdgpEZFhDP1aMU6eP1e3bqBu1O3J 9GLtgf/ntyIVUuvWgpWTBDwcvzM0NfGG8uxNU+kyICDcIzsayBt0dGojySv6rsBwMQzOv81gQ j2GuyyCjn6HhkyahT8Tb+L8U4Oj2LROnntrqZh7YvznQ2Q/55VWjtn/n/FqbEQY4sQ6/4LxTX yD2eldRfMNBPz4X1NfyzdSo4oUOibJehIQQQB1zFwzfsQi2OMNdR/M1n/AHMDxUN6c8u4OFJL vDJt6W+3DcpeW6JpoXG8FNtC5KLtXBuO/3qEipGcEtcmYnu29sn3cGaPB/2tHpBCXh7KJ7Qya XfTx4wJ+nUfLHS52Yx5gJVRoc6SoNaWpyjxz0ki9A0eLoOFXYaCvqXyrY5H4QkfrpwJmVsP7q gSPrukpN6bDhtDubKf7K0ovSp1dIErvyO4gkWAZYaKOKa8UtW0kh0Tx+hHDG6IkP5qdk2U2+I dhvqdst1OfJgIDLJSzOWLgxy69AXDiBdMzwbIxZj9Zg8NAQ5iR3pmED7A9k5AYCvOyR0hvxOj q2xxULmy6LamvfBeKfu6k4McOaHrF09tGXHI+ZtchRhTcYzvnBdF4nKLta2ut7Ri17bEEi7L4 rFVZkRLbuaGII8+ARgYP/fe77nQOHALOU79fNYUS+xaMOqL3vplNSeknorJMUnKKS34iB9avt WIJyTqkvCqh/WUkl0+yXsqZAloKqU7bkIpc5bVhp8m7IY3uhaEWg2wiAM+YWu5bNH4K3Iy37j EW7sQ1lFQe6byFIecXm9WPx9lTKEQE1yZIu6VpzC0L6MZlwnZpcXwWNQDqyGzHXdhvHs6L2Cd 0v4+nefjNCuZhLoYgPGGi7mZj5jS2TJt6sc+SbzVEcVH8xIfvWmdH+TmvJBhhKwznART+DksX 2wI1atNP1kwZGWcqxyd41ry5jxELLrerQoxAEjAVlk+AQxE1Vri5F6/tu9q7c4jPsS2DoaZQ4 GDpK90/75PUcZyfdFPtmXGBhmig0HOP9G9YhK6JkOR8f3wGDghilaNwEj3ZvzI9GfjKHlzUhz rNNGQmxLBgMUKmBhmmd3Kr3QTNbv3etKdm98zla7yInBG2CuD8zApl1NGvJ4PbqKq0b4rfsdP M3h49K6jX80AlQRGHUHQGIHTVJzkVPC5fu0sQIGdjjskdsO67i3EwGc1GmUTemce4JLE2HwcM r1WUAL7C/CuoKGrgBP5LNMb8zKt7aGoHvfTM9ICLN1MzCL9UxX7KRTrjwzAbXIz75nD/S2WAS OT2Ko2NCreB9D3NaQ/0MAxm1hIToFy0O+fTbu2cNRFnh3sd7Cju3j5wyLZZTACy/PlmmBNLtD fCTFteWXIMan9EZz8FWIL6dChFut6EgghKgnk6RJ5c4oOIQTgtfgWEe6pK36WAJBCE+Vb7ygE dYL098ymeI5sXyixtro8onPq2vpC+wLZZffe/JUkMX0Y8TVCvecmVDUZ6R0m83C36yNKjZvVz skx8fpY+eqg/5XzMaRMm53EaCjjZQ16igo6htDMRkmKr9BPyuv9T2kXEcPpUzCG2qzSsjBWBS Ie10XxEtSa36FSpSgB3gCbYKCswhrobXe0ebh+yjGHt5SXagCGk7LQ9ksvJ1NRjiguLWQT+g6 yqRFPTPByJkOLLaJf4hhaYRYnvmPpIociizlLLQWZ2of8kJgcpLIYmVZkP6IeCop/IQVVn46W VOvwQMeVmARQSASwaljB05MaLpdf/WIaW7dxWMMV9vhB7AhZB5vMjZX6E02qaLxO7l4or7YEW pPRBhHaFRXYODblUgnFnKsqeqAdE9+lGWc2KUfc23zWocKoVjs20jJsU2DM9I/jp2fMxgcEcg QlGLaug1CqHmy32RP19DKqUW9O8wbSf0TgWom0no3UMmgw0AZVYdhbSkOGKk5Ao3k6e60P3ay Z/FI1h1ECXOVOkLVPNGAPtu/qjqj2EQbRbd08AwGpheEX1Iuqzyw7kK0hfstRDwrVNBwzavrA rHgsslms30P/tGczgzhbolcIR1ZvP5KEJui0ljR74sGyxNDXlccPtrl8AU4badgWFaEn0kVwo RR9put/SX1HDDY8dhIYHslKQlFvsHwSKUfR8zd5oW3WDU2CXRYo0o5i41VCifxKHdcBVPM+uB 0/G8eKmQD997ezcuoeos9K72X7CwKeA4Ua/2f9SumR7SXm9ybk9i+s+H4M7cIJI50YQkDX8Ux Qfkb6cKCKZ+9e4upcosVPSTFgC97BcPh76LdrnJ1nV9KGp85BU7YbWIDd6vFAsdD+L6dyGGGI HzUT8RaxtVnOzNNuwshIujuYNgcTEvLhEAs+j9PwpiBv84Hyl4ZG1f2FQJeceUSBzcmI98NME a89Cz/7EgY9Xzdm1SxmpZ4MtgDhzh+UcIPxbxZpis8oqLC6WsXHUtC4JK4ecdJbTbCM3tQGyM H9pWisT9I7MdotFuF97UMAUhDFPA1W5rhyh9bLQkY4PXKjoWqkkcFzZr8v/m+/7RR9x0YPPMP JyhfXJDQw2ytvKA5QO+8/n5xqURhfkF9zPA5jaEC822arIo6iMG6P9lI77Gacix42eJwbIkoE /G58liM1Y9zRsayDp5ZPea76yZdz6mtFebl9ZoAipAMDcayAvKNSme0FCBnk0yzEoxs3c067m 7cnc9f8OQOitHs7Itd+XDewdROQKwZv27l0m0tLFfvTk7Z6OEPE1mmLYl1JYEbSWI27+nj6gM IqIeXx7ba0yH6V5drC16m/roCv3kzbSTt54xfuhetS9iFfdIyqZmADGzaQFkMQI/k7HVKxK2N kzs+1OUw6dDuNLF6gq2YKAYwsuOzoKmZhtyZ5FoCv47K2U1rxz3V1b3Ze+QJy1dp6w0d6St1q gU5wt/LaC+Uhkjs2+KjvE5Whr1wHhs76MJCbQpldc4jglUASI6rOMPp7IvQ+fbGVdMRhPDzbE K68yv8iJ+lSE11f6gaXxByoWs56npxB3d2foQJENqNdjNF9wyD+t/RXCA9ml7NRRCXNJoCddD TzANUs3VTJbdvNxzTLUV+4O0W2ej55ebidYtPSTmsQLsaMYgsrZprPEdwdQ0e1VGGGndIi2FK K/NirfzazIq7nYzmQvPSAZyw3IbIdC1Mlo6mFlamgpKB5gRparOlAgCvrEtwFd7iDMpWPYT/0 fbZ3IFFona8JG0buXYjYNBPv2aiIsDbW+eRm4bHy3y1QK4m8vN3ii3vQHgElK5D44qqFbVUsF 5J2br0Dj3xV59KODvztI8CMk4ZAZSBnMNxwuljFHjWiYuHi3h3kswcAGRKxCI2y6ahCo9yl77 IZPjYkzIA+XiiwAHrQU2otB8uBdfGCMERHAmmRLM5W/51772ZqMB6QaF6o+fi9LacQ1EEHxbK YkjGjjDK/zFl5/ooeFR List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk Sorry I was not remembering the details=2E Probably there is a TOC in your = dump file, but it does not contain any positions for the data=2E The pg_res= tore command has to scan the whole file in advance, and fill in the TOC off= sets in memory=2E This scanning happens in a very inefficient way, with many seek calls and = small block reads=2E Try strace to see them=2E This initial phase can take = hours in a huge dump file, before even starting any actual restoration=2E Thank you for testing=2E Dimitris=20 On 30 August 2025 20:19:13 CEST, Adrian Klaver wrote: >On 8/27/25 09:10, Dimitrios Apostolou wrote: >>=20 >> On Wednesday 2025-08-27 17:25, Adrian Klaver wrote: > > >>>=20 >>> For completeness and just in case they may affect the output what do t= he patches do? >>=20 >> Two patches for speeding up scanning an archive without TOC, like the o= ne I'm having (because it is piped into borg, instead of written to file)= =2E These were activated, but shouldn't matter=2E They only build the TOC i= n pg_restore's memory=2E > >Are you sure about that? > >I just did: > >pg_dump -Fc --compress=3Dnone --no-toast-compression -d test -U postgres = | borg create --stats --stdin-name pg_file --stdin-user aklaver --stdin-gr= oup aklaver borg_test/::PgTest - > >Then: > >borg mount borg_test/ mnt_tmp/ >cd mnt_tmp/PgTest/ > >and then: > >pg_restore -l pg_file > >and I got a TOC=2E > >Or are you streaming the data out of the Borg archive? > >>=20 >> https://commitfest=2Epostgresql=2Eorg/patch/5809/ >> https://commitfest=2Epostgresql=2Eorg/patch/5817/ >>=20 >> And two patches for speeding up pg_restore like mentioned above, under = specific arguments that I didn't provide=2E (one speedup needs --clean, and= the other needs --freeze)=2E >>=20 >> https://commitfest=2Epostgresql=2Eorg/patch/5821/ >> https://commitfest=2Epostgresql=2Eorg/patch/5826/ >>=20 >> IIRC I did not activate them (via --clean) because TRUNCATE fails when = foreign keys exist=2E See the discussion threads=2E >>=20 >>=20 >> Dimitris > >