On 9/4/25 05:02, Álvaro Herrera wrote:
On 2025-Aug-26, Dimitrios Apostolou wrote:
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 blocks moving by some offset.
Hello,
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
As I understand it the TOC issue was with pg_restore and it having to
generate the offsets as they where not included in the backup file as it
was streamed, not written to a file. The deduplication became an issue
when changing Postgres versions per:
https://www.postgresql.org/message-id/4ss66r31-558o-qq24-332q-no351p7n5osr%40tzk.arg
"
> The problem occurs when you do the pg_dump after this restore, correct?
Correct. The first pg_dump from the restored pg17 is not deduplicated at
all. Most of the tables have not changed (logically at least; apparently
they have changed physically).
"
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.)
--
Adrian Klaver
adrian.klaver@xxxxxxxxxxx
