MIME-Version: 1.0
References: <s0491qrn-343s-0757-8sn5-120rr8610qqq@tzk.arg> <1273426.1756246099@sss.pgh.pa.us>
In-Reply-To: <1273426.1756246099@sss.pgh.pa.us>
From: Ron Johnson <ronljohnsonjr@gmail.com>
Date: Tue, 26 Aug 2025 18:12:52 -0400
Message-ID: <CANzqJaCxNP9TjC9WnhaF3F_FKBmL7UXKRiQzoV5nnDS4vmbnxg@mail.gmail.com>
Subject: Re: In-order pg_dump (or in-order COPY TO)
To: "pgsql-generallists.postgresql.org" <pgsql-general@lists.postgresql.org>
Content-Type: multipart/alternative; boundary="000000000000e2c7b3063d4bf9de"
Archived-At: <https://www.postgresql.org/message-id/CANzqJaCxNP9TjC9WnhaF3F_FKBmL7UXKRiQzoV5nnDS4vmbnxg%40mail.gmail.com>
Precedence: bulk

--000000000000e2c7b3063d4bf9de
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

On Tue, Aug 26, 2025 at 6:08=E2=80=AFPM 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?

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

--000000000000e2c7b3063d4bf9de
Content-Type: text/html; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

<div dir=3D"ltr"><div dir=3D"ltr">On Tue, Aug 26, 2025 at 6:08=E2=80=AFPM T=
om Lane &lt;<a href=3D"mailto:tgl@sss.pgh.pa.us">tgl@sss.pgh.pa.us</a>&gt; =
wrote:</div><div class=3D"gmail_quote gmail_quote_container"><blockquote cl=
ass=3D"gmail_quote" style=3D"margin:0px 0px 0px 0.8ex;border-left:1px solid=
 rgb(204,204,204);padding-left:1ex">Dimitrios Apostolou &lt;<a href=3D"mail=
to:jimis@gmx.net" target=3D"_blank">jimis@gmx.net</a>&gt; writes:<br>
&gt; Unfortunately after I did pg_restore to a new server, I notice that th=
e<br>
&gt; dumps from the new server are not being de-duplicated, all blocks are<=
br>
&gt; considered new.<br>
<br>
&gt; This means that the data has been significantly altered. The new dumps=
 <br>
&gt; contain the same rows but probably in very different order. Could the =
<br>
&gt; row-order have changed when doing COPY FROM with pg_restore?<br>
<br>
I&#39;d expect pg_dump/pg_restore to preserve the physical row ordering,<br=
>
simply because it doesn&#39;t do anything that would change that.<br>
<br>
However, restoring into an empty table would result in a table with<br>
minimal free space, whereas the original table probably had a<br>
meaningful amount of free space thanks to updates and deletes.=C2=A0 Thus<b=
r>
for example TIDs would not be the same.=C2=A0 If your &quot;rolling checksu=
m&quot;<br>
methodology is at all sensitive to page boundaries, the table would<br>
look quite different to it.<br></blockquote><div>=C2=A0</div></div><div>But=
 the rolling checksums are against a pg_dump file, not a pg_basebackup file=
.</div><div><br></div><div>What probably changed are table OIDs.=C2=A0 Woul=
d that change the ordering of COPY data in post-restore dump files?</div><d=
iv><br></div><span class=3D"gmail_signature_prefix">-- </span><br><div dir=
=3D"ltr" class=3D"gmail_signature"><div dir=3D"ltr">Death to &lt;Redacted&g=
t;, and butter sauce.<div>Don&#39;t boil me, I&#39;m still alive.<br><div><=
div>&lt;Redacted&gt; lobster!</div></div></div></div></div></div>

--000000000000e2c7b3063d4bf9de--