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
