Feedback-ID: i76984098:Fastmail
Message-ID: <fe1900fa-f2e0-4fc5-ad06-215530eb2b64@aklaver.com>
Date: Thu, 4 Sep 2025 07:49:43 -0700
MIME-Version: 1.0
User-Agent: Mozilla Thunderbird
Subject: Re: In-order pg_dump (or in-order COPY TO)
To: =?UTF-8?Q?=C3=81lvaro_Herrera?= <alvherre@kurilemu.de>,
 Dimitrios Apostolou <jimis@gmx.net>
Cc: pgsql-general@lists.postgresql.org
References: <202509041202.ml2xi5yp46yt@alvherre.pgsql>
Content-Language: en-US
From: Adrian Klaver <adrian.klaver@aklaver.com>
In-Reply-To: <202509041202.ml2xi5yp46yt@alvherre.pgsql>
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 8bit
Archived-At: <https://www.postgresql.org/message-id/fe1900fa-f2e0-4fc5-ad06-215530eb2b64%40aklaver.com>
Precedence: bulk

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@aklaver.com