On Tue, Aug 26, 2025 at 6:08 PM Tom Lane <t...@sss.pgh.pa.us> wrote:
> Dimitrios Apostolou <ji...@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!
