Message-ID: <e48a1211-ca90-4944-958d-d8fb29ab944c@cloud.gatewaynet.com>
Date: Thu, 4 Sep 2025 19:59:43 +0300
MIME-Version: 1.0
Subject: Re: In-order pg_dump (or in-order COPY TO)
To: pgsql-general@lists.postgresql.org
References: <202509041202.ml2xi5yp46yt@alvherre.pgsql>
 <sn171540-722q-7o58-9153-541o63rq2s56@tzk.arg>
Content-Language: en-US
From: Achilleas Mantzios <a.mantzios@cloud.gatewaynet.com>
In-Reply-To: <sn171540-722q-7o58-9153-541o63rq2s56@tzk.arg>
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: quoted-printable
Archived-At: <https://www.postgresql.org/message-id/e48a1211-ca90-4944-958d-d8fb29ab944c%40cloud.gatewaynet.com>
Precedence: bulk


On 9/4/25 19:08, Dimitrios Apostolou wrote:
> 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.=C2=A0 You may be better ser=
ved by
>> using a binary backup tool -- something like Barman.=C2=A0 With curren=
t
>> 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 transf=
er
>> the backup files to Borg.=C2=A0 (I haven't actually tried to do this, =
but the
>> Barman developers talk about using them to transfer the backups to tap=
e,
>> 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=20
>> feature, which sounds similar to what you are doing. But it also does=20
>> it with parallel processes, and can do things like grab backup files=20
>> from your 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=20
> needs better.
>
> + pg_basebackup was slower, measuring speeds of around 10MB/s, because
> =C2=A0 of issues with 8KB page size and compressed btrfs (see [1]; situ=
ation
> =C2=A0 has been improved both on the postgres side and the kernel side;
> =C2=A0 I'm not sure how pg_basebackup fares today).
>
> + pg_basebackup was much bigger, because of including indices etc.=C2=A0=
 As a
> =C2=A0 result of size and speed, pg_basebackup was also taking a longer=
 time.
>
> + physical dumps would change a lot during maintenance (vacuum full,
> =C2=A0 cluster etc) while the data would remain the same. This would
> =C2=A0 reduce the effect of deduplication and increase size requirement=
s even
> =C2=A0 further. At that point in time I did not expect logical dumps to
> =C2=A0 change too, when the data hasn't changed.
>
> + I use logical dumps as a tool, not only as a backup, to copy the
> =C2=A0 database to other servers with different postgresql versions.
>
> + I also use it to verify the VCS-committed SQL schema: doing pg_restor=
e
> =C2=A0 --data-only on an already created database will fail if the SQL =
schema
> =C2=A0 had been modified on the original server without committing the
> =C2=A0 changes.
>
> + Finally I don't really need all the advanced features that physical
> =C2=A0 replication offers, like HA, PITR, load balancing.=C2=A0 It's a
> =C2=A0 non-mission-critical service that can take a little time off in =
case
> =C2=A0 of disaster recovery.
>
> [1]=20
> https://www.postgresql.org/message-id/flat/218fa2e0-bc58-e469-35dd-c5cb=
35906064%40gmx.net=20
>

if that helps , this is a writeup I had some some years ago comparing=20
pgbackrest , barman and probackup :

https://severalnines.com/blog/current-state-open-source-backup-management=
-postgresql/

>
> Regards,
> Dimitris