MIME-Version: 1.0
From: Pavel Suderevsky <psuderevsky@gmail.com>
Date: Tue, 25 Nov 2025 19:10:13 +0000
Message-ID: <CAEBTBzvtOtwsHuf59DHpBdgFBoJ0UuzUUCbddVArY_i-VPUJSw@mail.gmail.com>
Subject: Commit LSN after Redo Done At LSN applied during recovery
To: Postgres General <pgsql-general@postgresql.org>
Content-Type: multipart/alternative; boundary="0000000000002b1631064470083f"
Archived-At: <https://www.postgresql.org/message-id/CAEBTBzvtOtwsHuf59DHpBdgFBoJ0UuzUUCbddVArY_i-VPUJSw%40mail.gmail.com>
Precedence: bulk

--0000000000002b1631064470083f
Content-Type: text/plain; charset="UTF-8"

Hi all,

Migrating a large database from 13 to 17 using a logical replication.
Postgres - SaaS (GCP).
Subscriber - 17.7.
Publisher - 13.22.

Plan:
1. create publication [pub]
2. create logical replication slot [pub]
3. copy instance [pub -> sub]
4. extract the LSN of the "redo done" point from the log [sub]
5. upgrade the copied instance to version 17 [sub]
6. adjust the LSN of the replication slot (pg_replication_slot_advance)
[pub]
7. create subscription [sub]

There is a constant load on the Primary with inserts and updates.

The problem is:
After creating a Subscription, a transaction that had already been
committed on the Subscriber during the restore, is applied again. As a
result, getting PK violation on INSERT.

If you compare LSNs, "redo done at" point is indeed before the COMMIT of
the transaction. But the conflicting row already exists on the Subscriber.
Meanwhile, if move *confirmed_flush_lsn* of the replication slot by 1 byte,
replication goes further without issues. But again, formally this LSN is
still before the COMMIT.

Example: redo done at - *276/8FEE68E0*, which is before the last DML (
*276/8FEE6458*) and COMMIT (*276/8FEE6910*).
Records around this COMMIT (*xid 13347755*) ordered by lsn:

| lsn | older_than_redo | xid | operator | cmt |
| 276/8FEE4BB0 | false | 13347755 | INSERT | |
| 276/8FEE5FC0 | false | 13347755 | UPDATE | |
| 276/8FEE6458 | false | 13347755 | UPDATE | |
| 276/8FEE6910 | true | 13347755 | COMMIT | COMMIT 13347755 |
| 276/8FEE6910 | true | 13347756 | BEGIN | BEGIN 13347756 |
| 276/8FEE6910 | true | 13347756 | UPDATE | |
| 276/8FEE6980 | true | 13347756 | INSERT | |

(the second column is just "*lsn > '276/8FEE68E0'::pg_lsn*")
And advanced by 1 byte slot position is "*276/8FEE68E1*"*.*

Please help to understand what is going on:
*1.* Why after copying the instance a transaction that has COMMIT LSN lower
than *"Redo Done At*" point LSN appears to be already committed?
*2.* Why shifting the *confirmed_flush_lsn* of the replication slot by *1
byte* fixes the problem?
*3.* From the consistency perspective, how safe is this trick with
advancing the replication slot position by 1 byte?

Thank you,
--
Pavel Suderevsky

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

<div dir=3D"ltr"><div>Hi all,<br><br>Migrating a large database from 13 to =
17 using a logical replication. Postgres - SaaS (GCP).<br>Subscriber - 17.7=
.<br>Publisher - 13.22.<br><br>Plan:<br>1. create publication [pub]<br>2. c=
reate logical replication slot [pub]<br>3. copy instance [pub -&gt; sub]<br=
>4. extract the LSN of the &quot;redo done&quot; point from the log [sub]<b=
r>5. upgrade the copied instance to version 17 [sub]<br>6. adjust the LSN o=
f the replication slot (pg_replication_slot_advance) [pub]<br>7. create sub=
scription [sub]<br><br>There is a constant load on the Primary with inserts=
 and updates.<br><br>The problem is:<br>After creating a Subscription, a tr=
ansaction that had already been committed on the Subscriber during the rest=
ore, is applied again. As a result, getting PK violation on INSERT.<br><br>=
If you compare LSNs, &quot;redo done at&quot; point is indeed before the CO=
MMIT of the transaction. But the conflicting row already exists on the Subs=
criber.<br>Meanwhile, if move <i>confirmed_flush_lsn</i> of the replication=
 slot by 1 byte, replication goes further without issues. But again, formal=
ly this LSN is still before the COMMIT.<br><br>Example: redo done at - <i>2=
76/8FEE68E0</i>, which is before the last DML (<i>276/8FEE6458</i>) and COM=
MIT (<i>276/8FEE6910</i>).<br>Records around this COMMIT (<i>xid 13347755</=
i>) ordered by lsn:<br><br>| lsn | older_than_redo | xid | operator | cmt |=
<br>| 276/8FEE4BB0 | false | 13347755 | INSERT | |<br>| 276/8FEE5FC0 | fals=
e | 13347755 | UPDATE | |<br>| 276/8FEE6458 | false | 13347755 | UPDATE | |=
<br>| 276/8FEE6910 | true | 13347755 | COMMIT | COMMIT 13347755 |<br>| 276/=
8FEE6910 | true | 13347756 | BEGIN | BEGIN 13347756 |<br>| 276/8FEE6910 | t=
rue | 13347756 | UPDATE | |<br>| 276/8FEE6980 | true | 13347756 | INSERT | =
|<br><br>(the second column is just &quot;<i>lsn &gt; &#39;276/8FEE68E0&#39=
;::pg_lsn</i>&quot;)</div>And advanced by 1 byte slot position is &quot;<i>=
276/8FEE68E1</i>&quot;<i>.</i><div><br></div><div>Please help to understand=
 what is going on:<br><b>1.</b> Why after copying the instance a transactio=
n that has COMMIT LSN lower than <i>&quot;Redo Done At</i>&quot; point LSN =
appears to be already committed?<br><b>2.</b> Why shifting=C2=A0the <i>conf=
irmed_flush_lsn</i> of the replication slot by <u>1 byte</u> fixes the prob=
lem?<br><b>3.</b> From the consistency perspective, how safe is this trick =
with advancing the replication slot position by 1 byte?<br><br>Thank you,<b=
r>--<br>Pavel Suderevsky</div></div>

--0000000000002b1631064470083f--