public inbox for [email protected]
help / color / mirror / Atom feedFrom: Pavel Suderevsky <[email protected]>
To: Postgres General <[email protected]>
Subject: Commit LSN after Redo Done At LSN applied during recovery
Date: Tue, 25 Nov 2025 19:10:13 +0000
Message-ID: <CAEBTBzvtOtwsHuf59DHpBdgFBoJ0UuzUUCbddVArY_i-VPUJSw@mail.gmail.com> (raw)
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
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: [email protected]
Cc: [email protected]
Subject: Re: Commit LSN after Redo Done At LSN applied during recovery
In-Reply-To: <CAEBTBzvtOtwsHuf59DHpBdgFBoJ0UuzUUCbddVArY_i-VPUJSw@mail.gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox