public inbox for [email protected]
help / color / mirror / Atom feedFrom: Achilleas Mantzios <[email protected]>
To: [email protected]
Subject: Re: In-order pg_dump (or in-order COPY TO)
Date: Thu, 4 Sep 2025 19:59:43 +0300
Message-ID: <[email protected]> (raw)
In-Reply-To: <[email protected]>
References: <[email protected]>
<[email protected]>
On 9/4/25 19:08, Dimitrios Apostolou wrote:
> Hi Álvaro and Greg,
>
> On Thursday 2025-09-04 14:02, Álvaro 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, which sounds similar to what you are doing. But it also does
>> it with parallel processes, and can do things like grab backup files
>> from your replicas, plus a lot of other features.
>
>
> if I'm not mistaken, both Barman and pgBackRest are based on physical
> dumps of the database (pg_basebackup). At the start of this project I
> had evaluated pg_basebackup, but decided logical backup fitted my
> needs 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-c5cb35906064%40gmx.net
>
if that helps , this is a writeup I had some some years ago comparing
pgbackrest , barman and probackup :
https://severalnines.com/blog/current-state-open-source-backup-management-postgresql/
>
> Regards,
> Dimitris
view thread (2+ messages)
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: [email protected]
Cc: [email protected], [email protected]
Subject: Re: In-order pg_dump (or in-order COPY TO)
In-Reply-To: <[email protected]>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox