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 1uqzaG-00GSYq-0x for pgsql-general@arkaria.postgresql.org; Tue, 26 Aug 2025 19:43:57 +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 1uqzaE-009O4S-FW for pgsql-general@arkaria.postgresql.org; Tue, 26 Aug 2025 19:43:55 +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 1uqzaE-009O4J-2U for pgsql-general@lists.postgresql.org; Tue, 26 Aug 2025 19:43:54 +0000 Received: from mout.gmx.net ([212.227.17.22]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.96) (envelope-from ) id 1uqzaC-0020qk-14 for pgsql-general@lists.postgresql.org; Tue, 26 Aug 2025 19:43:54 +0000 DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmx.net; s=s31663417; t=1756237431; x=1756842231; i=jimis@gmx.net; bh=VMQQY0K/49CHxI8QddGA2x2LMgbsVxGVQVyPKlKtM+0=; h=X-UI-Sender-Class:Date:From:To:Subject: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=btUN2Cn7tMe9bTgr7egjT8Yytz80BLH1WshITtLTVLRSnKQv+bPG3SrMuZMJZJkT Knz4nQ0BkCRGqzQxyGrsZEm301wTAc7WRLZck54Cd3K+Dfp7tLPABjx5V8LEk/R0d Q//nPjXY2mn6TdhSqJCeY3xvCmKIxqEwosRBm7TU+R8OR/laeTDLXhGwNlibo13x0 8JZyTM4Q1aMhFJFdq+su/jB9oD2StGAqaACdBIOIU1eLykOCFqMw1/bqY1GjDF4Sv wg5udvitIIjGhokOMAOMgxDfFRU8SXzTgktuqJUH5iG8h3s3+c6+hjw3sjKKYFWP5 FcaJaDvacQSJUBctVQ== X-UI-Sender-Class: 724b4f7f-cbec-4199-ad4e-598c01a50d3a Received: from [10.9.70.59] ([185.55.106.54]) by mail.gmx.net (mrgmx105 [212.227.17.168]) with ESMTPSA (Nemesis) id 1M2f5T-1unP573e2B-00FhwL for ; Tue, 26 Aug 2025 21:43:50 +0200 Date: Tue, 26 Aug 2025 21:43:44 +0200 (CEST) From: Dimitrios Apostolou To: pgsql-general@lists.postgresql.org Subject: In-order pg_dump (or in-order COPY TO) Message-ID: MIME-Version: 1.0 Content-Type: text/plain; format=flowed; charset=US-ASCII X-Provags-ID: V03:K1:IncBhSogVNyMAmDrOI5ifrnXKc9LqRMGRus57QtSi8PZVGAnxB5 r9aC5WZon3jpwIy4UbKUnt5BZs0aviDJp596P4nJS1xs/WGdfFH+ROr3xYVh9lmoANaZ4jU cr93XsR6uqsYI5kt5CXz6bACh0VoDOck9bPK4Q8ZcYhXS3PEmq3oiapto2WYoEtpaNL41Q6 QH1yZ5AQTVdupt4aqsQrg== X-Spam-Flag: NO UI-OutboundReport: notjunk:1;M01:P0:c6mVR5N/Hvc=;4ClTy7zLRL6naZnaZk53C56NFv8 3WRGetoB5qaQzF+ljTQBa2MSk1KEA0OdiMsYDuO0CaR/qFeHLlU/XUejKntDelFhegzHP6rDK WdcnFXR2xmxHfF3H8LmGPPJoT73pXbIxZG1xaWrNvYwI1laohf5h9ULGnJlsG8xsK6NxHeQax xtPza2LWflqgcIP5KERhT1VnfCDGkcmv/CgL5hDQ6rNA3pndqzXuFzWUB3WpoiyVa2b3UxRuz 41q1R/9wXT2oF+WwwSMdzRO7GGzWQu24zMVU8BagP7pEkexRgiBj6ESDde9KPjyd4NNkg/20q BZHSJ+stxxtWUzi7Tqyxo85vgrfNxarCsEZKhJBqjpBe3DlmcabdEZEwG9ySWLhVjDZI8Ggd6 BxKha+pjUswFychOdpxwnsQgAD8iLlQYefNyhuSrgLpfnsVh+ipauSj05/9maJB4IjsHQaJZk u4K+WB9lFglPihxeRSHUsy0NRtlaS+cm8bEuPy1gZiAvgShfl9mvisOwCWbIOFGMy2Zi72v4c B95YMbC3ZOpWPPaw4cIPjwxqgg248xKLm5j/q72/LwNYlBOQwtfiaafAv2pD4qIJhu+XTrjF8 zA70YwpLomr7E8crNwGuIorObQTadAID8PwKM7IEtS7u3LL3q5nMEKyhUNsN5JFAxGDavV9SQ taRbzStkrc3WSO2NQa1rZuQDwEnY7p/dj8hbggCEF6int9uZ+jbZ78a+Wk3PL7KB0YgkDx1OM g+uZ0zOMQP/txZks+WAAm9INajWEqQHrxIt3CbXMWH/Djor6QQocyxr1cnPlj6C30NYMHFQte Ey65MlDOC7QhMZsT9oED+5fcq3VaKKYRxkP8N5Jqmo+OfQ03cTouSARzXz7DbfBMSrudxbYqM BBK9J9O7bkAQ6sHV/+7zS3Dh3ijrOBa+vWNpL24J1pHuPsVF7S/3YW6QhVvxewVAj1hR4ErgX 3ot+JE5FVkdFQuLokvV4ry3EpTE795uWnUyIcxQjk0sdZ0CGooM0my5iwVHCVwThZ6HT6Jv3J 8o6QrP289O9DxBH8m3V11QbJ+9o8HAV84fpkYHsO7M/yvhsbcbBYhMVRM37weNEDHMuevFKrN uIwObTUxRQdQyeEAwLoPMVgR0ji+fSPGOH6/TyfK/SEjI+uPZSbB0ohOWJqhS9GQmj0hvAPyL igYYW9ATFqYVHBfx2gzyJhfqtrAy4NPmz0mFl8xmhdgUwJNxdnnroM48Rq92ppjJTu5teeQ/v +SbZcK6MSKJh9NiL7YGORlEi+3YxZ6WNMXk2DjnSFNChwEdI/R5jRQ5oAUdPI+6qc+Ghaz9et onxv8pyKGD8lKMGgXdCEDoBzm3Y8V9KZ91HB1iEFqWiz+xOl8iY1R1xg68zH6GC5iUNIOUEWm ZeXrbypnqLYC2svl40KBe5EAXnlCSALtmWP9qrDEsPOBQ1zUM3xXx/zuV1Gb2+bwymH0IHk66 qSKGslqU4moMK4N8SkoOjvkb1+m19NDLp9MMUUezkhCMJdi/rdSgD0TO5GkxhcikzS0CMfr07 t5e9eipm90z3n0onwRybnPNyr5t9HrRmJ4Iq0iJ59o+Fqwkzqz1x+82XDFSKiGeLaOlAq8K48 pepeRfAaE58eEzdAAWVnwmI1E9feksnl1XwC6G91n92B8Gi1Cpu5sis+fZaswxyLjtR6kipNR w5ux1uLhU+9pNUDtJDfGkytrgGKEjdjd1YM67o5W6kfw9IPM+zmdEm5oJ6RHEMQk2F94GLbYw 6T2zbXxf6Tekoe+zBzdbxzBuvaEk+Q32dyrkfAEDKCSHrq14Iw73OH/cgs1O7bIBPwjfIDchc woXT2w6GmvhY7pre3JhzPUhuMH39LFDrvQVriBz0R3FNp6nseJPLMIsytlD3JZbQOjDfWnSqx F+EsZU9FpvY9XeH4ELizT0zQGua15/SerrF/HBKTDe0l6e/Ekn3X6XN8IRvwfQwLQ7fUKNbAB n/GRkT8jC+vMOMPJCt/nrztvTuZx9AAoLVJgyBzQ48L8dJS0bPxCBwUp41qlqWmIiQIJ+Xj3D WN87wkLzwBjaWCTykaD566ZHzs05ooWeMyo6dEpXHQSHmPomPD9kXnIC4nJjnD+mao4/jBMM2 ZLz9O6F0TzYuXmCJtA+71gQVCxdngwbueX7wwZC9UNW7dJY0I0AI9KOpqzGE9H5zN/aBPbprd jtQs/lfmlZl3d0jnRc4obsTJcz0zQTavXQ0By6lX7HaVBGAvfEYBYRDGaUTHFxsV9GhM7Vbh3 aJG3PxKIeiBwJHW8B5UG8EDVELs7rYyjb1dTgaB1YbvmiP6gVN02D66dkPVhevgQBBI+u2S5C S/1USqKjiV8tBFu6XUklP3rwTS4nd32rNYrDSyjDjPD7ZUJMGJfknNPsLYRckdDmZaap9uj8w Y+z4L7d72WnNmeL6O/Husm80gqyIR4phOhv8eGCuqN6mqd6v6ruU3OJvb34ZqfSM5XL5R8qhh hjPDdTi/qDEp0jqN/sjcugjbDhpnn4sJN0GfSZVPAlcTzC2/CuO8kqnlBgD/UTNHvWj+6Y4NW dn/Av/VeBDELZLr9tXoBGKgxTOxeTSlFdmoFMeUCGMuCtK8U0AwGvoGZvZ2WT6u2Br1NqFkO/ 7tw7tcLoOiNvE21Dj1G0LJ7tq/yvGaE+8E8SN6mbgJPEab8c0Fgy4r7HJvIJsec3CHDqfIzKx vs/TG+3ndczQavXkwoEUpQLKpuo0MEkwmcVvbJdzZGrK03phxvTvwX2u4tak/gUZmphQSPegb QrHkL5wZuRTyn7Nf9tYOngcXYDmN0VaNm0oWOHrQF0Mp12pwndvIcCMzR14S5ah77OqCKHG3T 0MxkPgV8a4PMLudf8e6gYOfR1U9CxBKIEwYMnl8pNJAwf+q3xJrpxc99jPHdRJOzIfuEeAggP WAgYl3JmM8jmLBc3pvn1TR7FzxuITaBoKNRWOz41Ku65VdWVJBbc/AS+Do50g3F7aCgi2b4+C dVlBgAKZbInzybr2jTbEVq5mqsp5sYUj6xGszsvsR3GPAvvq/D6JJvpU1jc4ZfJosKa5Zexvu e0NF46QJN9z7lNQUtbxy/95qFvETEGce5lywHIA1X4bYuiVbr/Iz5dNBwLFpVp7thkjA1uEOr Wengdv0MbRnq12MOmDZe53nKalAtECnhK8GNxfdKzxGd/FLBHAg302ZTcWwq+tGlPbODP8W2N BATZwxJ0Gyvjt4LncTnJWu0L+84Epglrd6DR91cQdv3KW2vkJOD1erwGGLKBo+UhyX1w8J9Ux 06RdJwyeK1Vd31FROAsm37Ctqz7Zyb2CExN68RDpqzaYht2kzMgsl77MGaPkkSlHTuPK7jbEQ xpVw8E7XJd/F/hIEPM0oPGkggUs2Ej6TaeiDQyIJoGuRShxVk10X7QtviPVOU2zvQqDnK2k+u IAWFZuN3bseAmystMh/yYD/sTW/pgCB912ytMpGrzVPCqqgp1ePZBboZw/5XRdnMpvcPJgAsG 8RmAf7XKClPXniZFC3TsiJBrLXh8RunUERgFpCM5S6ToSiA52Y9Q14EdEH6zyEgjFMJhi0C9j c5iVoAFeHM1A6qcrrzSOyf/1GqPYMmNeRwRpmI9ihHx4R/+NUdgmpHDl6qlLELreOSgLaLktI I3bmlvW2A5GpYzZxtQQle5NXVgKq8GFcO7uAPMiRrnd+x2nFeW7Ta5RzupGYVwski1acoIr5N nO/hhI9TCXzO9tR3m5/zCpGWToF7pydzXwtWwnplqOOmNnLCOR1Ab+278J7L4Z92iZrHg7SlA Z2K22ssqKmrD/PupXLxguMbAuufI7Aatvg1vck09+soWvbpREVVscVLUQEmXEvI9iCQ2hXvjS iZczNPtar4nsg6EIQwfg6TkNjKhu1z/o3oUCZPbPQeF74TF8NjRIWr2dzDxONFPApjuYdg63D djBw5QYzdlQTuGDZLvScXbJMCRAwxWcAFV2Xqr7six32IrL/nmZKV5vFA+q1UEkNXteme71ax 210TCf0tIVQigNuvYFzu5mArPU2Kabm5llKGxVVuSg9IreZ+tkv7oQTbf62SisaJgs0SULkuG 1oomq0/BA+mTUJwRu2f4sNBhK3aKaX7gFE+8a95zy6vsxt5J2ah4Fhm1G38RCdO0Du/42odv7 LBcAlL9LyYD5LKDzDOm2/p4nTn6Dod3VBhEbt/XfqlrSfXGgibuhlzQc+kipzwsT4e1AxKm90 OV+bJIrxI2Fj0uLJN8AbMT/T8NG2H8kt3HWXWNbJsow/SBGl7OFwYYKCGA1Wb/YiDXC1eQBJw LcE+FOZhoJxWsvZrHVI+LV/gq6dsqYU7JXb5Fdt0dd/izKR5qadY9lwKOg/A8LJyUZSjmCX6a VoJVSegAHAoOLz0rG2jPcm+S/8e2G0HdCx3dfTalmmdWfEGT96ksXGLk0FVzt1CJEF8v0SIbu K7Y9pJauc7KqvmNF7i8YZA4k7zCBgh2DUVDmmJscDyLbda8tmF10LwsH1BYsQu33yrpQSx94L F44bIJiGDVbqVmoSi0VGEZyLBEUHMbbNSDZmdGaGJHJDiCco+QRHBO/ErmuXvbe44xQH4I5gD kaScLcdOZrBlXNxDKZSNEwFFbjuA6Rs3HhfoEXg4qZ0Omp Content-Transfer-Encoding: quoted-printable List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk Hello list, I am storing dumps of a database (pg_dump custom format) in a=20 de-duplicating backup server. Each dump is many terabytes in size, so=20 deduplication is very important. And de-duplication itself is based on=20 rolling checksums which is pretty flexible, it can compensate for blocks= =20 moving by some offset. Unfortunately after I did pg_restore to a new server, I notice that the dumps from the new server are not being de-duplicated, all blocks are considered new. This means that the data has been significantly altered. The new dumps=20 contain the same rows but probably in very different order. Could the=20 row-order have changed when doing COPY FROM with pg_restore? No idea,=20 but now that I think about it this can happen by many operations, like=20 CLUSTER, VACUUM FULL etc so the question still applies. A *logical* dump of data shouldn't be affected by on-disk order.=20 Internal representation shouldn't affect the output. This makes me wonder: Is there a way to COPY TO in primary-key order? If that is possible, then pg_dump could make use of it. Thanks in advance, Dimitris