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 1urEye-002sxK-LM for pgsql-general@arkaria.postgresql.org; Wed, 27 Aug 2025 12:10: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 1urEye-00EmbC-2g for pgsql-general@arkaria.postgresql.org; Wed, 27 Aug 2025 12:10: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 1urEyd-00Emb4-IU for pgsql-general@lists.postgresql.org; Wed, 27 Aug 2025 12:10:08 +0000 Received: from mout.gmx.net ([212.227.17.22]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.96) (envelope-from ) id 1urEyb-0020rS-1S for pgsql-general@lists.postgresql.org; Wed, 27 Aug 2025 12:10:07 +0000 DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmx.net; s=s31663417; t=1756296603; x=1756901403; i=jimis@gmx.net; bh=kkqWKXz2sstlPkmGTmQxzgDnxLxMUUsOWbtpx7WcBp4=; h=X-UI-Sender-Class:Date:From:To:cc:Subject:In-Reply-To:Message-ID: References:MIME-Version:Content-Type:Content-Transfer-Encoding:cc: content-transfer-encoding:content-type:date:from:message-id: mime-version:reply-to:subject:to; b=jCCmrCJ0fm2hRPes4pynuzeuT9VUEtQW79fsVRRXVELLP+j4K77dhRoIi/U9Nxdw TbnO+h8OYxoJAwI8ecrjjXfTaFwjl3mtg83vFt7AJuvrM7sRPUQsJoBMCKMx0PfqB iFuqiTo+lVVGqiAR7FRpeeWIVHpdAkikXDJteWNwzA/Kdq8kWVP6k3oPA7r9T9e7s wGkNJnLWJeavboGy8Gfc84lmwByUx4+xP1miRal/Fngr+qINfL+iHf4eTMTCSUiwJ 0FgAO2icF4GkdCyXoxbj/CWfHg8WIBiOhjRh2yCqYqBzjz97h1+HWVZcIwgNp8IbH Ds4rga2mG6I7ijfsFQ== 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 1MiJZO-1uKdRd0Jkc-00iC58; Wed, 27 Aug 2025 14:10:03 +0200 Date: Wed, 27 Aug 2025 14:09:39 +0200 (CEST) From: Dimitrios Apostolou To: Adrian Klaver cc: pgsql-general@lists.postgresql.org Subject: Re: In-order pg_dump (or in-order COPY TO) In-Reply-To: Message-ID: <3541781s-75o7-26pp-46pp-qs54o4406192@tzk.arg> References: MIME-Version: 1.0 Content-Type: text/plain; charset=US-ASCII; format=flowed X-Provags-ID: V03:K1:w0bnlF0QwGnRwlanLRVd9ID3lgvboElc1DbisnjSHLl4WlNVjEE GNDZwaL2NAmcoTheIQKpiHKvBtaY6T+JqqwWGoP+JoUNocA1F8X93mSEwIZSDmKX2Vdqta8 C4wkbHrOHb7wUG9jtEz7u3jvJu7gCimLPvSmmwkoJ1KL+G+NR8xqY3++9n1xiZG7fjiw2Qk ymY7y4z7VVI0fdT4tIlfw== X-Spam-Flag: NO UI-OutboundReport: notjunk:1;M01:P0:oj+knyrFAlE=;7/Y/tsH/3uVgKEEADx7sJG3vuzm pAV5K8bCRPvelGat6C3oJoHeZJvNM68PSWifsn7XTJOwx647pdiVP548ljnqc+01ELOhnAYnE P1Gy15UV1NlBHYAo9HJw3YZu7dYVwdgqJI9nFil++K0Hlh9dl+tGgWG35watLUXmvEjMkitpD NaXOqcaNh8p/6xROAWqGpRpI2Rwb8/lD0m7MxVbjVpjmuyYatmXF+5w7MryDc1t4FdcMvmDaW J8SLgZyNZi7lA4Pg1Hvg5c6BTkOFPz1pn4zBWgJLdA3G81a504BK30cwKmGk5FELMaut+xk9A LsDa+ZaRmivr14mf2/xUUeb3e77qTmQKdWK7BjTus5RJa1Vh5VMqcIeIBbICNoMmy4YAcLWTE JQJ2TDrnFlW5kUxRleKTyFbtYezkUTZdGjata9CEY1CI7Wv6lpCTgDDoA6/V2u+zFUSAikmJm 84Ut6pndki+mA1kTrlY+RlkxnFhdKIA70SGqAeF/jx4Cj3EI5n2KZqvsZCsfpObDtaXmHmQye yfy+jdciq6+DUbe6gRxzqdHrOeF558k7uD2e4Ra4VJi6w3P9+ak3f2qT0IfTS+MEDvGvr/nfI hTiNeXen2N33sTrYboj1gphaPIvvNyUBbDmzFoOm+H0COCbjcKu5RhZq98nEL6F5t8idqmbV5 W8MhbIUQwNt+nteMsmPVagqLp+zvq+Nmi1MlOi+dLDxadi2fmZrZ1Rni8IVe2TkOw7Gl4htCX 6zJtxtE+ZNp7NU4iNyn3dIZcD3flBdgRPnAzhAjN9p/yIxSqd+V3XdCkS3xhbQKdC+psY1O6C cKOfmua7MGXWq3P1bBcYYx9PZsyxJlD6t29HmI2cNAShUwOjCGZcNIosgKgrkyPOMaitAmQ32 9RwfDx0If3VdpL4/w9zaQPli/kjInmoCp6uQlv1IIyKpdIQPK1zuRmDwjZPmfNvQ2Ct+mPwpC GQpi5tmJRp+jlehTVlvpk5AuxhuWp4TVvnGjVJiE3v48KT/kTXCoHf8PzfcjDEHK4R/zW3wT/ JKCjTTRbxcZV3/PWehA+GgKiL+V0D/SEV/TRkxooibNMsr3CVtZSDOZDR5jRHy5vnG2GdIwmQ IwZqH3Tj6ilUvukyycSsBaUNMno9wCc/bQ/6KEPy6S3ac8bA5Ql42psOsWRxudjHbitaVZRaF 9LLXr70OH3vnWFPImiOOqRfBoVwLcr51V1ym5mnzQOr65tjxkUgRkK3LoBmi0oLqrW4kRZqIJ 3GQ5QeIPNmR7D1Omd7pyQMwOhOiR1ASKi3ZmcDqDwrMDbecZpxNJJ42Pwhym+Ih63UMVcNk4y bkWvxn5toRuPL3kA2moL9keg52p284LlAIdh+Gl8hvJYsvcpvBxUNovN+FC/zZDLFG5pQc8PY U2z530wtzyb1naYNV0IVCIWrdMkTOIOF2ZhQp/IrzrBrkl4DM9G9vpfcZmEP+gHcbZxdYDANw VkR9v/70TMpyQI/N8VihU6wqfErdKa8+/vOAo3ccETKGvOofo4EkD3DsURI84CWrs16M7WQSf eZsFar7AcOovH3b/iW+42fux+dcb2Op0RiyejgHnA9pWiFmq/2wBOcZNsN87fBxN8QDl0WYyY BSO3h9CZBQoDXCIeW2pFNZ56H8I1EmAe32xNRWXRJhDEnNTdQWhG8wdzXzmICWzllmk7iT2ln U1EoeRxd04r3j7cGvM9jARiXugJnbi4zESsPNrlQO6Q0/Yyv5PNtv5TUOIc4ZUH8hc0WOSJlv 94ChagaUOqw+Uk2Lh4P3G1A6Sr6u4Q7gFQq9Azm7U0lgCkklBDsGS363itYtkmNhVukmYDC84 z6BAUZYsMkZP0qBHQKCfPu0FVOhwlaRkc88uM7Z0oh39TXFl5K5SDyFgWWmu5Cd5M/FhCacfI nEJG/YHb8J/9HXHj4DGu8zTmxJM77yywTy48O534sO1wNq9k1KBuqzTLwg1HIc70Zbil/old6 cf/1nlsRuVpcs495LQc+DCsIqvEsJWp029YV8EXDJl6caqC9h3ojRKa0PZV+U9G4+KPBTD4ia fg4LY5d3HyxpiyRBfWtERL0LIRGfeRaTC57xmeNvc5NVOgE9Ij3qGqR6Awdas44iIm7yea+jj vgqdKpxQjwRVwirmn4zCBxmFLaCuatKWeb1NMnKRvfp81JKUPX3L48knEiofz2uqZIk0m/LWj IFOzgdpa9IcX6oM7+eyGyAsUggCAyLx2unRnZfiFLvFacuwp22v7dvb10WT6k6TdF54ML8D7Q rEdT6DXMRmfmIDOC4eiPToMgYSWAvoM+jOl0OcNlF3DqzDYgdl+DK6kMdXkk4QSpsIiOoND0D UpqThvgO3tiW8IXP61APBjH8CqTcCb2hCaUgPtvd+PPOy+oFaluuj/mOD4OnYQ8fD/EoXuHgb olQIRyDaxME6lkSqCqLxqYJQsq/BG4o+jHkMy5hEZH5LzF3bE8YXmrXZQWD1K7qWpa7lTn5Nt tEa0Pylni7Q+BYbB3+HyxJKgDXb4/UkZOYcIqv11p9xBGbctcvAooFruPG/H51yQsG/F+Ki3b cezFrrtlVsXni/EgC0AVfFyPbH4Acr3kH/FeSpjNrfgor+NWmO/rSan8ht2dhw0IgNm9Molk7 3pgcTbUsl0S+J4EzSy1q2JtzpFz6Lt/hc2Y1wIdOq+6ZKMSozMExWcHceGTQdE3nHColMbdLJ OJ2KxTNJZEd3blIHzywMfuj2uTdvMEYBh1XBILIE/yEKUfrdD3bip38NzfpR+868sFriIewti 4ofDz3uPsfmbx1oU5lwyDEHAcZYPVlEs/vQ7JrKtAplRQmvvm6m5F22su3e/5s5cDZHAscgwN cG4YfgUju3dLxUjBGA1UX6GTSX87N4FPJlPVwWzeN4tpJ5ihE8vfs4rZ2u03dw7ZbDREBMmK6 ftm6NEMc93GD1BpDzYr0z5+MWKFynwQI+Yqery6p7xW7TsC8OU+iCG6XvAn2iftpz49vplOz/ 6UdmJ4PcajvXHWrS03CCYRfZeQMTRDzfbnswZHJiOrKnlpQzdtiEMXZf51628Tmx7AV7hLc1C HaGRYQ2tWLUlgZmE6faq31kXunXh6T4+KucY1kicHzMwKgaylslnuHbqRR/17UwWzDsRZ8EOp ljP692tkFb0rhIiR7171breXCSeEgMqcuhiUzVfOKfAf85bbVqj4FqMYEsFVRosqBRuNFUs0T 7JhAEaias4igwJGxFxIHa9EkAuMM24Low7RH8kzzmshsEKe3mrLwQRiLlw8Ge4R030E4i4ahZ GOnDqACvLnhjsMAkrdWAKhfKdfNX1CDmKAxiLmCt9OGFsS6r4GLDyZT9FCTGXmp5n35Ih6M2g WJFfTQbTxAyjkTCNSX+qz+rnVJM+Z4nQzpsmx7LQd4BGTLwDaaO+O/OmEC5h+FwFW8mnCBXVN 8qdcSvoPHpzA7ojuK4giJRBduteU+jA0kMFArJSMVXZ7KdJknJAXfJnx9q8Ph4ASrql8Nh5qq /yKUqEro5r4CARxfWhAMC1pM14xfFz31HoeMYiekQQNc3WwqJ2oEXiq/ftXwHGiw/E2030+UP rwgnQwmDC3MkTYy32PFWM+2NEoOtz72SZEe/QWKW6Gwzt3S7VoCeHcFKRjduL/rDfa9kn05aX Vb++RtPwFK6lUPif2s71SbkvvMkTWa8UILpmJA98kLYp/gE1N/wbNR+uvKUb/lacq803ap507 BsxkdiuRfZ4i22M/XTe1w/kwjtCY9GsBwwZ/O1e8RALvD84a+fu+4FyHTGuNPh85f0lOB4q68 DVTm5eTnYPJ/x6b/m3u/u570rjpkKrtlDb54+6c1lgotBw2UNocz7a7Q2cPLuB/A7twcoHdpk IOZcHwXdjv/+VbI7DMdDRIQtppNLWjN4lUzbmG4rI8XAkgJTEKzC2e5YnaXEBIeo2n26HXTZ9 AhVu6VBA/hN+EmRn/T6snm3GrvutPJTFYYcMhAn+tlKs6v0oXK5sDlxAwQAd7JqApVqykhh0A CIey2QAsnVHl9N6sZfbrriNcOX+OCBbOFB5NYBbS+G3e6ueJ3TceO3HB0Iey39SkHlKCw2yLb gTBTfgZlB2GKuzDigUEFhXPMjnWgQ32bJ5zVagQjNH7knX8HKYnjlM/ozohGQz7rYLgL4ocqz nlo8kTX7fqmTwJ51H7wybdysSOcIGHqHRJRKdXsTMsJTq4z9242LnYvKH5kjKmfD3zLUAr3Um daEVkE78Il3x30PQPkz6V6KoG4N99EdnFQ1XANQ9aOtzZv1r48HCl46LjqpEt9U+00ooAROfs F4B8nIN6r/szk/I/0B06qX8a4bbCnfjpelycBFytX6xv75pwe5FNplA8WGjTYJeDOaCwIAV79 NW0j+1LZkH46Pepz8ZAsZw2OyA/kuvW/0M3a85/JNRZl/kgiHfi+WOZzZ4g7FZJlG6yHfK3A1 sp0Q88LB8umH//CyTA5Iw1inQowDu3L3SY+lJcq2RLoB1EGcrnl9UlIqvlYsc9AuYA5Sx37// bpfGZnjkVfCtaavWmVtxPv5oiKk3Pp1dqNLlS7JGAkKUGDGn7DpdiiHttl393TPBRHJpTT5OH khLTPaC4WJiAG9Kn+/DJRyx9q1ya+2k30wE2WnEN+rQaNCHiBYHKZd/HtvGDgzcfByfzW/nRj q96anz3VTBjUMgAopVggik1feZmBcosJ6p6C54RHY54i7+Sfj3ZQbF8cS1A9/AMv8AmgR0g/O 1Uky+1o= Content-Transfer-Encoding: quoted-printable List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk On Wednesday 2025-08-27 00:54, Adrian Klaver wrote: >Date: Wed, 27 Aug 2025 00:54:52 >From: Adrian Klaver >To: Dimitrios Apostolou , pgsql-general@lists.postgresql.o= rg >Subject: Re: In-order pg_dump (or in-order COPY TO) > > On 8/26/25 12:43, Dimitrios Apostolou wrote: >> Hello list, >> >> I am storing dumps of a database (pg_dump custom format) in a de- >> duplicating backup server. Each dump is many terabytes in size, so >> deduplication is very important. And de-duplication itself is based on >> rolling checksums which is pretty flexible, it can compensate for bloc= ks >> moving by some offset. >> >> Unfortunately after I did pg_restore to a new server, I notice that th= e >> dumps from the new server are not being de-duplicated, all blocks are >> considered new. > > What are the pg_dump/pg_restore commands? > > What are the Postgres versions involved? > > Are they community versions of Postgres or something else? > > What is the depduplication program? > > Dump is from PostgreSQL 16, it's pg_dump writing to stdout: pg_dump -v --format=3Dcustom --compress=3Dnone --no-toast-compression --se= rializable-deferrable db_name | borg create ... As you can see the backup (and deduplicating) program is borgbackup. Restore is in PostgreSQL 17: I first create the empty tables by running the DDL commands in version=20 control to setup the database. And then I do pg_restore --data-only: pg_restore -vvvv -j 8 -U db_owner -d db_name --schema=3Dpublic --section= =3Ddata dump_file Worth noting is that the above pg_restore goes through the WAL, i.e. all= =20 writes are done by walwriter, not the backend directly. Postgres is standard open source running on own server. It has a couple=20 of custom patches that shouldn't matter in this codepath. >> Thanks in advance, >> Dimitris