Date: Thu, 4 Sep 2025 18:08:12 +0200 (CEST)
From: Dimitrios Apostolou <jimis@gmx.net>
To: =?UTF-8?Q?=C3=81lvaro_Herrera?= <alvherre@kurilemu.de>, 
    Greg Sabino Mullane <htamfids@gmail.com>
cc: pgsql-general@lists.postgresql.org
Subject: Re: In-order pg_dump (or in-order COPY TO)
In-Reply-To: <202509041202.ml2xi5yp46yt@alvherre.pgsql>
Message-ID: <sn171540-722q-7o58-9153-541o63rq2s56@tzk.arg>
References: <202509041202.ml2xi5yp46yt@alvherre.pgsql>
MIME-Version: 1.0
Content-Type: multipart/mixed; boundary="0-1516320663-1756997422=:1535142"
UI-OutboundReport: notjunk:1;M01:P0:ZxVXRkH74f4=;tKaRiH4Zttohv394cnG52CiqpI2
 0Mj7QAJMyqBewvJGkMcNAtwju6w6Qmft/uHQ14V9V+0ksHe3CdgS87SNbYUvkRheJw1+bNUEy
 eTWk0Uw/sLwjF1ZZ6X3eXs8TTtRPNMpT4crghtv2WOArZbQogi1KS6/fq8mvXgPCkfRR9qIgM
 +mGo7+HJ5SQgY/2e+i4c6VPcsr2WYUCClFQnJQOqHZChGDcDqsKadePU+bu9s4rUckkENcoaO
 w/Ej6adwxrulXr/cbPscywyQ6JJGkfpyDs8TqMKiGo89LrEdpDr7RIoYt5DQzcuIszMRUYycD
 3zwLnk9+z5Lj0u2cjq3+ceTO7vcfy7NXJZl75OZgoQaJGTuCFcsoT04l/lvXIHe+6Q1ZbbRzC
 KT65521/l+gcPtcbSUOsnNUZ5oAmjlDATDn3jQ7LGvwzdigbJ/7xVcbCa8zdcZQKgrcJuP+J5
 0ECd8wCvNHuUBTzK/LhjPuvb5DMvRT36NuQz/ATmSnyo/u4HIWyvWS8WJWTHcvu86LBASRcyw
 mpP/o9UxzvtxXrPfFC2ZqyAthiBnDY4j51aO3NNUNQyYY+c+NFI0cceijJYe7XJNniz0PGGaW
 romAnFJs1A4fGW2o5iFFklIgPDPAlwP6u7iFHkjPlYiySkzOvgUjaTR2V3jkNlDDNB9UPVI2n
 qL5hCghoBc6oxIj9ZY2OLwdXbn+3MCuyxtAZC1QWdTAeAurpuzP5hfKUQOCiJt6yMokO0oTz4
 B+wB3zUjB4OAu2eYQCxVgAkavDd+Y39ybHSMe9mjVtl3XYAdFvjzFgPqOWpP/aJinjB3dFBl+
 Mwa+obN1As1g5vYKbac7GRQDdCeny2ULJIlj6pQGvHz+ZfBz2jwF+t5O5ekmSthlQTuKzPxzm
 vdD/mzPiHk5QZz4Tmk2/FNQk3a3ll5y60PZZT1UKZHVKc63uiDjGzKbC2Ws/bU+V6zy4x6+W9
 icvZ+A4YR6dR1YmuUtoE1M1QZzSW+5f1duztemGzmkFQ2HkttmxHqttwbC4HEwrmvmFN17Sk8
 ZNY4AQDVPgQXbrtolxVNTX6YraNgTAZnxNaf+2+9R3XZuP9gmiRl6mrURU1gVK47z8vrOia94
 sviH+RhEK1ETRdHhOLzyDilTb5YrxSKE3AfMSOXTVvzABfmEGRGmXtC5XR2IePGXeQXP2iWtt
 vhuMv4eLWikk0MdqV4Bu9ypcOLoR/AC6yWR8oD9lptflevGxCdajkkvV+S1QZGztOH9QP8CBa
 uuC2GZ1/ARjr9doZQvjP0eRsqhe3QjTnGzn6Cw4VT8oKiValSMflsXZOJledKPXgZ4TkUH31/
 6bdx1U+8tIkALMzWJ2t01sacBpbE0B2uHZMyBTzYM9SOlCvlUu1n6FFuPAImG+Pi+mOl2QRss
 yRIf0no3zIP1hnTOTBAWl54yVNecsVUhrvV+ARnDTKOf3Y4atblr8r5qYwemsXr4DdORy2/tV
 vqOGYJQ0kTELUqUadmPjWcouLoaDPdIUp8tsf1QgCA+Q9INR+g7IJ0/bzeqVWxm0iAY9OcnxR
 3ZOY7PB+tz33O9RZo0Q5NmT2nL+BLIsyBLajZhnfgK6NHwv7R3/JPtGsrG2XyA8BaZMDuUoFX
 05p6ARpVXqZT492Xp/FRt3zy8IAX6p+e9vxPW10NPq++gjmvGQno9UhgHQWOhlckh+D+dMuw7
 pnvwMtPNkUZOJFX/CsDVQWR0DtGqztJ8LIK95Wmwb8sQrPzdD63kAutdOWhDipXUF3Yje2uGN
 27ULIS1xRAmmg2laOXnhfSHeSVRikk6EflDP55uJf/LU77HFYyIdoMROOzE4pw/BV7u4nLJiz
 YszaBHZhDMGUabwfMieXSoGvn8f2G5S5G/7kVZNypLr8AMiFD4owUvde4CuZ7BDRO314lEm3n
 repVhIPdSm5TpZ1GIQI7JyoUP+yqIe2HodZh8SwuwnmMay/O8LsPf3hAzEZmxpBEU6Jdl0AvG
 B5b+CWc5tEvL3uvN3VdtJpa1IoyeZjnULSeogRMOFPWSM/jLtYlRNAeXLmWT+RNGAgvMEHcfX
 RGk782poopNqZPm87TdRle+2BvcODd2KYg4GNujSoG9WX96CYazMew/MBZOkhtzNUENYH/9bD
 y/xlpHcbT/WCwts3zvq1tXQOcx55euc8/Kr35NTg6Ug0D/pRxoiROqx2YgBADQFzB3XqSEKDu
 n2lPlfflsFX93JXxaQK4UmI1EWqqMGG/h5q0IxL4VU4ZmrCVBoFCuaxwe+A8sln8WEUXw1cd2
 w94E6wBGZANCswcsZqgZY7farA/VYIH2lNxXGjWLrk9ATcMF1zaOSFV+POQtf3UPcoTMmBsvX
 KRB3OqtZHfCv2cgrTBykMXcY5tQuyQxQaYYk+uiLztvsUGusoqhitynk5zrLMfIUQL2qA+z/1
 8aVwMXd0ob4qFzMBZFcucpZlbcqcmgbaHptwk+tS1SrO1/nVytYvuvKlwxo46ATLPBx9pW9ze
 lM/e/CQuxGISXBb9jFL3/DQcma8h2vQwKgnO7DRctRiYx29X/iC1FatG6w8gtLNfoouE5iS47
 N2TWMSB/8ua9J3OZoCPQOG6YFPkNU7njS20WM/5XVZtsf5nkqExs0iKJ5tRMu7nWidSEKekHy
 9wXnSOALVAMPmBeMuP+NNgaFcy40JsjbNR4AWi/rm9zxfcbeFsHdqO/1z5cWZxSrffbTYW5uB
 Zh3AtSM8iFRrasQHNkOv4/4Lllj6++Pq/Qda+Onfyd5/TqBEV2LmL0qj9xgG1HAdNBo5Tfunl
 wgsHRaf+LVvk/EwhAyGBdpv/RaFqzz1Sa8fZCsGxOPHU+hcV1flAIIojFGTds/iWgtdxQTenP
 E+ExwP0z+n52K9tHHIMhleas/O5LSQJEcz2CAMn0Y+e9N3T6mgZzSLue1JgbQvUVq1xdMPNmt
 Cr/W+ie9nk4eKCxNM1qK8v8saV/6TAj1vMVijdsOK2X41oRg4LqS2dGn/dx/k69PSYkWGFYy6
 pVG8mumjFHTA7LofCPiP/1JXhgNF74Lr1qDSh++Xby0UvONj+GXGLWZEpeRDb2PSkL7VnEaJr
 2mEhGRIdiYZPQygU5EmFGzNI8eIOUJLnumRL/iuY7jtdVB154JK4n5ITBbe6dGhRBpY1qmtf/
 hJQN5OnpRMkllSqJWYah8q/tauk75b5a77o00uEiyh8xL2ByyZWnB1ZikdgQGzD7UROwOtezn
 GpDar+dXMdnT3J2wlIQEBRbTg5MLJJWKYTQU8Sdd8KTX4x94RvwDVRtMtVX3+ARKB9hLf3zat
 vRAitNADUvlORXQcghvKnrC6CCtvM7tp8DxH27+C2nd4epPxL7kH+oGXUVXuuGc0RWUnTkLci
 fbHbTau+aTp+qZTeHp5aiNnO17rAd0pCzkyS+aPkzln/0qU3Uv6yN8IRtGHPSecIyg87t3xHc
 LhhaYeA0fq3R19mP8mv9ceyxIE19m4CeiVnGhh9IaXfK7fxKf26qejytD3lIgNL5S/4Ae4fFo
 YP/p5tW4IgKwMNN0Eos3OC+Ebp+rtOXxnZT1lSqfL2nDCC5WmJcvJnw6IgMjibX5gV9LRPQZQ
 jSSeIFg1ghXUR6wNkKwDyDxszO5sz0i67Faa3Cr5/R26e5mmLUuaWdH4fJgvtabrlCAF4ZGlQ
 WgG7522r0Qn/vINySP/iTGHSa991bjF7iJ55DKSS769ALpa1Zh42jM46rx9pOM96KW8wdHsQT
 P//0GTinKYAzVz5RUynDFirYBTFvNPEijh4UA8TxsDLlFqunA8Wx0ovNQxlxGkEI0bGnSpW8e
 iP563OUfn3/4/y68ph/pPQyU4y9tdUgjmcNBOqLSa+fiHfMFOMdTH55JHIPhZ3Y0JsYc9c6Si
 nx8tYU0Tw4d6iNB+wRv4w8k5h9njDMaHkp7CSruiMvmIfzGmH+apPvE5yXmkkBCXkKarU70m6
 qRb+kyo5Sa9kLyG3lLqEFY3GKwPj47e7ZitOPxyU4DqtDh/5av8AU+EDerl0//kkbM4vFc82V
 N376p2JxO4HECi/SUvHI1DZvgwJw7R2NEpEG3VCJiZ8wf3oJ+YLJXM37mb6BfPn400M1Hdf5l
 ULg5rJnn708X/j1v/0EnJDqSIn5+vHB+F6zA99AtIIuK09EfDP1Ib0HGlnKPVwqQv4WFTJ4Di
 Rs5Rzd3iU8qTLCVedGTLOB6cLQKLFhUsGjkzlnmeCrjliIERKu/1wDB39zQ1Gzq6gzPkTNJlE
 mtsf5+PaWA2MFJcw63XCn6ltJXK27ND2ukLt1bhlkr5iVgnElip3dftaYxPY1pMmT7XXi+dTi
 Mumreidfo6AOsiFdbXvzDTOVF/wBJBpIFIDIESNQiSTJZbck6GkjTsXvljskBuhLirNJmZ3A1
 mmnLUR4stHsoqb4MSQuAAHXevhf8n/rrTTVKBjQ+nbKPvZs/cBJgX3gWNmXLa8LyHwfHjWVwu
 FYOr4M27jupb3ySk2hU+33jBmRGfyz4KypprZId9x7dXGTAQbnsF2SEIF8MxoEA8Rb2r6ALN5
 COHpgTQtWwj3NhOlAK83qTo8nXvWTjJBPgLNyzPBFyZKb5izgnmgO9BFwqSPg2QUVfX0N6PHD
 75w7pPyLrr2NFShsXiFo9s4hGV7kPeQZw4D0GvsllKNB55pH/WJSl+hrm126hh/BRwjcpm0J/
 548u4Dxg5yoVlbhYtr6yYmfAaLXmpaotGiQTq/DEjtx6pIYaUiyHX9Yva43Bu3Fs32/v7Oi3Z
 +zjEMFAIqE4dOq3QvDS45R2RBweDEgRMILBnDfAMs9iFyFzH88nGzpdgqE3CLQS6iHZjV/oVa
 R6MLdqpBhJ8+kWh2ykFuwm1dcxafqjGFfW9yI9AuzTvGrC/Y62CctdDOid2bAHwmpWtVpMUrB
 aPdUcvv4W7
Archived-At: <https://www.postgresql.org/message-id/sn171540-722q-7o58-9153-541o63rq2s56%40tzk.arg>
Precedence: bulk

  This message is in MIME format.  The first part should be readable text,
  while the remaining parts are likely unreadable without MIME-aware tools.

--0-1516320663-1756997422=:1535142
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: quoted-printable

Hi =C3=81lvaro and Greg,

On Thursday 2025-09-04 14:02, =C3=81lvaro Herrera wrote:

> It's generally considered nowadays that pg_dump is not the best option
> to create backups of very large databases.  You may be better served by
> using a binary backup tool -- something like Barman.  With current
> Postgres releases you can create incremental backups, which would
> probably be more effective at deduplicating than playing with pg_dump's
> TOC, because it's based on what actually happens to the data.  Barman
> provides support for hook scripts, which perhaps can be used to transfer
> the backup files to Borg.  (I haven't actually tried to do this, but the
> Barman developers talk about using them to transfer the backups to tape,
> so I imagine getting them to play with Borg it's a Simple Matter of
> Programming.)

On Wed, 27 Aug 2025, Greg Sabino Mullane wrote:

> I suggest looking into pgBackRest, and it's block incremental feature,=
=20
> which sounds similar to what you are doing. But it also does it with=20
> parallel processes, and can do things like grab backup files from your=
=20
> replicas, plus a lot of other features.


if I'm not mistaken, both Barman and pgBackRest are based on physical=20
dumps of the database (pg_basebackup). At the start of this project I=20
had evaluated pg_basebackup, but decided logical backup fitted my needs=20
better.

+ pg_basebackup was slower, measuring speeds of around 10MB/s, because
   of issues with 8KB page size and compressed btrfs (see [1]; situation
   has been improved both on the postgres side and the kernel side;
   I'm not sure how pg_basebackup fares today).

+ pg_basebackup was much bigger, because of including indices etc.  As a
   result of size and speed, pg_basebackup was also taking a longer time.

+ physical dumps would change a lot during maintenance (vacuum full,
   cluster etc) while the data would remain the same. This would
   reduce the effect of deduplication and increase size requirements even
   further. At that point in time I did not expect logical dumps to
   change too, when the data hasn't changed.

+ I use logical dumps as a tool, not only as a backup, to copy the
   database to other servers with different postgresql versions.

+ I also use it to verify the VCS-committed SQL schema: doing pg_restore
   --data-only on an already created database will fail if the SQL schema
   had been modified on the original server without committing the
   changes.

+ Finally I don't really need all the advanced features that physical
   replication offers, like HA, PITR, load balancing.  It's a
   non-mission-critical service that can take a little time off in case
   of disaster recovery.

[1] https://www.postgresql.org/message-id/flat/218fa2e0-bc58-e469-35dd-c5c=
b35906064%40gmx.net

Regards,
Dimitris

--0-1516320663-1756997422=:1535142--