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

From: Dimitrios Apostolou <[email-masked]>
Date: Tue, 26 Aug 2025 21:43:44 +0200 (CEST)

Hello list,

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.

Unfortunately after I did pg_restore to a new server, I notice that the
dumps from the new server are not being de-duplicated, all blocks are
considered new.

This means that the data has been significantly altered. The new dumps 
contain the same rows but probably in very different order. Could the 
row-order have changed when doing COPY FROM with pg_restore? No idea, 
but now that I think about it this can happen by many operations, like 
CLUSTER, VACUUM FULL etc so the question still applies.

A *logical* dump of data shouldn't be affected by on-disk order. 
Internal representation shouldn't affect the output.

This makes me wonder: Is there a way to COPY TO in primary-key order?

If that is possible, then pg_dump could make use of it.

Thanks in advance,
Dimitris

---

From: "David G. Johnston" <[email-masked]>
Date: Tue, 26 Aug 2025 13:31:12 -0700

On Tue, Aug 26, 2025 at 12:43 PM Dimitrios Apostolou <[email-masked]> wrote:

> Could the
> row-order have changed when doing COPY FROM with pg_restore?

There is no reliable, meaningful, row ordering when it comes to the
physical files.  Sure, cluster does make an attempt, but it is quite
limited in practice.

> Internal representation shouldn't affect the output.

The logical dump has no ordering - it will come out however it comes out.
"COPY <table> TO ..." doesn't have an order by clause - there is no way to
make or communicate to it that ordering is important.  For adhoc work you
can use "COPY <query> TO ..." and put and order by in the query.

David J.

---

From: Ron Johnson <[email-masked]>
Date: Tue, 26 Aug 2025 18:00:09 -0400

> rolling checksums which is pretty flexible, it can compensate for blocks

This might be a silly question, but why are you using -Fc to
create multi-TB dumps instead of -Fd?  If nothing else, the parallelization
granted by -Fd will greatly speed up the dumps.

-- 
Death to <Redacted>, and butter sauce.
Don't boil me, I'm still alive.
<Redacted> lobster!

---

From: Tom Lane <[email-masked]>
Date: Tue, 26 Aug 2025 18:08:19 -0400

> row-order have changed when doing COPY FROM with pg_restore?

I'd expect pg_dump/pg_restore to preserve the physical row ordering,
simply because it doesn't do anything that would change that.

However, restoring into an empty table would result in a table with
minimal free space, whereas the original table probably had a
meaningful amount of free space thanks to updates and deletes.  Thus
for example TIDs would not be the same.  If your "rolling checksum"
methodology is at all sensitive to page boundaries, the table would
look quite different to it.

			regards, tom lane

---

From: Ron Johnson <[email-masked]>
Date: Tue, 26 Aug 2025 18:08:39 -0400

On Tue, Aug 26, 2025 at 4:31 PM David G. Johnston <
[email-masked]> wrote:

> make or communicate to it that ordering is important.
>

Doesn't COPY TO copy out records in the order they appeared in the physical
files?  That _seems_ to mean that the records laid down by COPY FROM should
be in the same order as they were in the old dump files.

---

From: Ron Johnson <[email-masked]>
Date: Tue, 26 Aug 2025 18:12:52 -0400

> look quite different to it.

But the rolling checksums are against a pg_dump file, not a pg_basebackup
file.

What probably changed are table OIDs.  Would that change the ordering of
COPY data in post-restore dump files?

---

From: Tom Lane <[email-masked]>
Date: Tue, 26 Aug 2025 18:17:28 -0400

> files?

It emits whatever a sequential-scan plan would emit.  If you set
synchronize_seqscans = off (which pg_dump does), that will match
physical row order.

At least with our standard table AM.  If you're using Aurora or
one of those other PG forks with proprietary storage layers,
you'd have to ask them.

I suspect the OP's problem is not row order per se, but differing
TIDs or XIDs, which are things pg_dump does not endeavor to
replicate.  Or, given that he said something about blocks, maybe
he's actually sensitive to where the free space is.

			regards, tom lane

---

From: Tom Lane <[email-masked]>
Date: Tue, 26 Aug 2025 18:23:45 -0400

> But the rolling checksums are against a pg_dump file, not a pg_basebackup

Oh, that wasn't clear to me.

> COPY data in post-restore dump files?

It would not change the order of data within any one table.  There are
corner cases in which different OID assignments can cause pg_dump to
emit database objects in a different order, see this recent thread:

https://www.postgresql.org/message-id/flat/20250707192654.9e.nmisch%40google.com

			regards, tom lane

---

From: Adrian Klaver <[email-masked]>
Date: Tue, 26 Aug 2025 15:54:52 -0700

On 8/26/25 12:43, Dimitrios Apostolou wrote:
> Hello list,
> 
> 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 

What are the pg_dump/pg_restore commands?

What are the Postgres versions involved?

Are they community versions of Postgres or something else?

What is the depduplication program?

-- 
Adrian Klaver
[email-masked]

---

From: Dimitrios Apostolou <[email-masked]>
Date: Wed, 27 Aug 2025 14:09:39 +0200 (CEST)

On Wednesday 2025-08-27 00:54, Adrian Klaver wrote:

>Date: Wed, 27 Aug 2025 00:54:52
>From: Adrian Klaver <[email-masked]>
>To: Dimitrios Apostolou <[email-masked]>, [email-masked]
>Subject: Re: In-order pg_dump (or in-order COPY TO)
>

> What is the depduplication program?

Dump is from PostgreSQL 16, it's pg_dump writing to stdout:

pg_dump -v --format=custom --compress=none --no-toast-compression --serializable-deferrable db_name  |  borg create ...

As you can see the backup (and deduplicating) program is borgbackup.

Restore is in PostgreSQL 17:

I first create the empty tables by running the DDL commands in version 
control to setup the database. And then I do pg_restore --data-only:

pg_restore -vvvv -j 8 -U db_owner -d db_name --schema=public --section=data  dump_file

Worth noting is that the above pg_restore goes through the WAL, i.e. all 
writes are done by walwriter, not the backend directly.

Postgres is standard open source running on own server. It has a couple 
of custom patches that shouldn't matter in this codepath.

---

From: Dimitrios Apostolou <[email-masked]>
Date: Wed, 27 Aug 2025 14:34:31 +0200 (CEST)

> look quite different to it.

Thanks Tom. I'm not following how the empty space in the tables could 
affect the custom format dumps. Where can I read more about these TIDs? 
Are they stored in the pg_dump custom format archive?

The rolling checksum method should iron out shifting of data chunks that 
are around a couple MB in size. Shorter shifts will not be caught, and 
I assume that the "page boundaries" changes you mentioned would happen 
every 8KB. So that is definitely too fine grained for the deduplicated 
algorithm.

FYI something that I forgot to mention is that pg_restore is --data-only 
and writes go through walwriter. The database with the tables has been 
created from scratch so every table is empty before the pg_restore. Not 
sure how this affects the above.

> simply because it doesn't do anything that would change that.

Regardless of my specific case, it's scary to think that doing VACUUM 
FULL, CLUSTER, or who knows what other maintenance command, will modify 
the logical dumps. Some implicit ordering could be enforced by pg_dump 
if possible, for example when a primary key exists. Does it make sense? 
Is it even possible?

Thanks,
Dimitris

---

From: Dimitrios Apostolou <[email-masked]>
Date: Wed, 27 Aug 2025 14:40:57 +0200 (CEST)

On Tuesday 2025-08-26 22:31, David G. Johnston wrote:

>The logical dump has no ordering - it will come out however it comes out.  "COPY <table> TO ..." doesn't have an order by clause - there is no way to make or communicate to it that ordering is important.  For adhoc work you can use "COPY <query> TO ..." and put and order by in the query.

Thank you, so it's not possible currently.

How would "COPY <query> TO" behave for copying very large tables?
Would it make sense to optionally have that in pg_dump?

Or would it make sense as a new feature, to optionally order "COPY 
<table> TO ..." based on primary key where available, and use that in 
pg_dump option?

Dimitris

---

From: Dimitrios Apostolou <[email-masked]>
Date: Wed, 27 Aug 2025 14:52:39 +0200 (CEST)

On Wednesday 2025-08-27 00:00, Ron Johnson wrote:
>
>This might be a silly question, but why are you using -Fc to create multi-TB dumps instead of -Fd?  If nothing else, the parallelization granted by -Fd will greatly speed up the dumps.

Hi Ron,

the primary reason is space. With -Fc I don't have to store the 10TB 
archive locally. And I don't have to wait for pg_dump to finish. I pipe 
it directly to the "borg create" command which does compression and 
deduplication and sends new chunks to a remote borgbackup server.

Regards,
Dimitris

P.S. please use "Reply-all" when replying, I've missed quite a few
      replies that I found on the list archives. :-)

---

From: Greg Sabino Mullane <[email-masked]>
Date: Wed, 27 Aug 2025 10:06:40 -0400

> rolling checksums which is pretty flexible, it can compensate for blocks

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.

Cheers,
Greg

--
Crunchy Data - https://www.crunchydata.com
Enterprise Postgres Software Products & Tech Support

---

From: Adrian Klaver <[email-masked]>
Date: Wed, 27 Aug 2025 08:25:15 -0700

>> What is the depduplication program?

Comments in line below.

> pg_dump -v --format=custom --compress=none --no-toast-compression -- 
> serializable-deferrable db_name  |  borg create ...
> 
> 

Ok, I use BorgBackup and it is fairly forgiving of normal changes.

FYI, if you ever want to use compression check out gzip --rsyncable, I 
have found it plays well with Borg. For more  information see:

https://beeznest.wordpress.com/2005/02/03/rsyncable-gzip/

> pg_restore -vvvv -j 8 -U db_owner -d db_name --schema=public -- 

If you are using only the --data section why not --data-only in the pg_dump?

Or is the pg_dump output used for other purposes?

> writes are done by walwriter, not the backend directly.

Please explain the above further.

The problem occurs when you do the pg_dump after this restore, correct?

Is it the same pg_dump command as you show above?

> of custom patches that shouldn't matter in this codepath.

For completeness and just in case they may affect the output what do the 
patches do?

---

From: Dimitrios Apostolou <[email-masked]>
Date: Wed, 27 Aug 2025 18:10:58 +0200 (CEST)

On Wednesday 2025-08-27 17:25, Adrian Klaver wrote:

> FYI, if you ever want to use compression check out gzip --rsyncable, I have 
> found it plays well with Borg. For more  information see:
>

Yes, zstd has also --rsyncable. In this case I let borg do per-chunk 
compression after deduplication, it has worked well so far.

> If you are using only the --data section why not --data-only in the pg_dump?

I want the dump to be as complete as possible. Didn't think it would 
create issues.

> Or is the pg_dump output used for other purposes?

It has happened that I have selectively restored user schemas from that 
dump.

> Please explain the above further.

The COPY FROM data is going through the WAL, as usual INSERTS do. The 
writes to disk happen by the walwriter process.

OTOH, If you have configured the server with wal_level=minimal and 
BEGIN a transaction, CREATE or TRUNCATE a table, and then COPY FROM into 
that table, then the backend process writes directly to the table 
without logging to the WAL.

This can be much faster, but most importantly it avoids situations of 
WAL overflow that are very difficult to predict and can mess your server 
up completely. [1]

[1] https://www.postgresql.org/message-id/flat/076464ad-3d70-dd25-9e8f-e84f27decfba%40gmx.net

My patches are for activating that codepath in pg_restore, but they were 
not used on purpose and I took notice that the writes went via WAL.

> 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).

> Is it the same pg_dump command as you show above?

Yes.

>
>>
>>  Postgres is standard open source running on own server. It has a couple of
>>  custom patches that shouldn't matter in this codepath.
>

Two patches for speeding up scanning an archive without TOC, like the 
one I'm having (because it is piped into borg, instead of written to 
file). These were activated, but shouldn't matter. They only build the 
TOC in pg_restore's memory.

https://commitfest.postgresql.org/patch/5809/
https://commitfest.postgresql.org/patch/5817/

And two patches for speeding up pg_restore like mentioned above, under 
specific arguments that I didn't provide. (one speedup needs --clean, 
and the other needs --freeze).

https://commitfest.postgresql.org/patch/5821/
https://commitfest.postgresql.org/patch/5826/

IIRC I did not activate them (via --clean) because TRUNCATE fails when 
foreign keys exist. See the discussion threads.

Dimitris

---

From: Adrian Klaver <[email-masked]>
Date: Sat, 30 Aug 2025 11:19:13 -0700

>>
>> For completeness and just in case they may affect the output what do 
>> the patches do?
> 

Are you sure about that?

I just did:

pg_dump -Fc --compress=none --no-toast-compression -d test -U postgres | 
borg create --stats --stdin-name pg_file  --stdin-user aklaver 
--stdin-group aklaver borg_test/::PgTest -

Then:

borg mount borg_test/ mnt_tmp/
cd mnt_tmp/PgTest/

and then:

pg_restore -l pg_file

and I got a TOC.

Or are you streaming the data out of the Borg archive?

---

From: Dimitrios Apostolou <[email-masked]>
Date: Sun, 31 Aug 2025 03:21:50 +0200

Sorry I was not remembering the details. Probably there is a TOC in your dump file, but it does not contain any positions for the data. The pg_restore command has to scan the whole file in advance, and fill in the TOC offsets in memory.

This scanning happens in a very inefficient way, with many seek calls and small block reads. Try strace to see them. This initial phase can take hours in a huge dump file, before even starting any actual restoration.

Thank you for testing.
Dimitris 

On 30 August 2025 20:19:13 CEST, Adrian Klaver <[email-masked]> wrote:
>>> For completeness and just in case they may affect the output what do the patches do?
>> 
>> Two patches for speeding up scanning an archive without TOC, like the one I'm having (because it is piped into borg, instead of written to file). These were activated, but shouldn't matter. They only build the TOC in pg_restore's memory.
>
>Are you sure about that?
>
>I just did:
>
>pg_dump -Fc --compress=none --no-toast-compression -d test -U postgres | borg create --stats --stdin-name pg_file  --stdin-user aklaver --stdin-group aklaver borg_test/::PgTest -
>
>Then:
>
>> And two patches for speeding up pg_restore like mentioned above, under specific arguments that I didn't provide. (one speedup needs --clean, and the other needs --freeze).
>> 
>> IIRC I did not activate them (via --clean) because TRUNCATE fails when foreign keys exist. See the discussion threads.
>> 
>> 
>> Dimitris
>
>

---

From: Adrian Klaver <[email-masked]>
Date: Sun, 31 Aug 2025 08:41:34 -0700

> This scanning happens in a very inefficient way, with many seek calls and small block reads. Try strace to see them. This initial phase can take hours in a huge dump file, before even starting any…

It may be that my coffee is not strong enough, but I don't understand 
what you are trying to say.

Are you using, from previous post, the following?:

"Two patches for speeding up scanning an archive without TOC, like the 
one I'm having (because it is piped into borg, instead of written to 
file). These were activated, but shouldn't matter. They only build the 
TOC in pg_restore's memory. "

The part I don't see is how you get a dump file without a TOC?

When I do the pg_dump and pipe it to Borg the resulting file has a TOC.

Can you show the rest of the  |  borg create ...  command?

---

From: Dimitrios Apostolou <[email-masked]>
Date: Sun, 31 Aug 2025 19:52:55 +0200

When I first said "dump file without TOC" I actually meant "without offsets in the TOC".

The fact that you get a TOC printed does not prove that the dump file includes a TOC with offsets.

All pg_dump -Fc commands that write to stdout, produce a file without offsets in the TOC. It has nothing to do with borg. ToC offsets must be filled in right before streaming each table, but this is impossible when the whole TOC has already been written to stdout in the beginning.

Dimitris

On 31 August 2025 17:41:34 CEST, Adrian Klaver <[email-masked]> wrote:
>It may be that my coffee is not strong enough, but I don't understand what you are trying to say.
>
>"Two patches for speeding up scanning an archive without TOC, like the one I'm having (because it is piped into borg, instead of written to file). These were activated, but shouldn't matter. They only build the TOC in pg_restore's memory. "
>

---

From: Adrian Klaver <[email-masked]>
Date: Mon, 1 Sep 2025 10:24:15 -0700

> The fact that you get a TOC printed does not prove that the dump file includes a TOC with offsets.

I did some digging in the code and see that the TOC is more then that, 
it stores a range of data. Still have not part where the offsets are 
ignored for writes to stdout, but will keep on digging.

Getting back to your OP:

"Unfortunately after I did pg_restore to a new server, I notice that the
dumps from the new server are not being de-duplicated, all blocks are
considered new."

I ran a test on a much smaller database and I do not see the above. The 
commands and the Borg info for the archive are in attached file.

---

From: Álvaro Herrera <[email-masked]>
Date: Thu, 4 Sep 2025 14:02:15 +0200

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
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.)

-- 
Álvaro Herrera         PostgreSQL Developer  —  https://www.EnterpriseDB.com/
"On the other flipper, one wrong move and we're Fatal Exceptions"
(T.U.X.: Term Unit X  - http://www.thelinuxreview.com/TUX/)