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

On Tue, Aug 26, 2025 at 6:08 PM Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> Dimitrios Apostolou <jimis(at)gmx(dot)net> writes:
> > 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?
>
> 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.
>

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?

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