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

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

> Dimitrios Apostolou <jimis@gmx.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!