Re: In-order pg_dump (or in-order COPY TO)

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(dot)klaver(at)aklaver(dot)com