Re: [PATCH] Preserve replication origin OIDs in pg_upgrade

On Thu, Oct 15, 2026 at 4:26 PM Hayato Kuroda (Fujitsu)
<kuroda(dot)hayato(at)fujitsu(dot)com> wrote:
>
> Hi Ajin,
>
> 1. the pg_dumpall command reads the remote_lsn from the old source. the value is
> LSN_1.
> 2. the apply worker on the old source receives and applies changes.
> 3. the worker also updates the replication origin. The value is LSN_2.
> 4. then the source is stopped and heap is copied by the pg_upgrade command.
> The data is replicated till LSN_2, but the remote_lsn would be LSN_1.
> 5. the apply worker requests replicating data from LSN_1.
>
> It's not problematic if the confirmed_flush has already been updated to LSN_2 on
> publisher side, the publisher ignores the requested startpoint (LSN_1) and starts
> for LSN_2. remote_lsn can be eventually corrected.
> If not replicated yet, the publisher may send the same data thus can cause the
> data duplication error. But IIUC it seems possible even for HEAD. The apply worker
> does not request the specific startpoint (i.e., 0/0), and the walsender replicates
> after the confirmed_flush.

Hi Kuroda-san,

Thanks for the review.

You’re right that for a PG16-or-older source the apply worker can run
during pg_upgrade, so the dumped remote_lsn (LSN_1) can be behind what
has actually been applied (LSN_2). But I don’t think the patch changes
the outcome compared with HEAD.

When the worker starts on the new cluster, the walsender never streams
from earlier than the slot’s confirmed_flush. With the patch,
replication therefore starts from max(LSN_1, confirmed_flush). On HEAD
the origin is recreated with no remote_lsn for pre-17 sources, so
replication starts from confirmed_flush. The patch’s start point is
never earlier than HEAD’s, so whatever the patch might re-send is a
subset of what HEAD would re-send. Duplicates are possible in both
cases when confirmed_flush lags behind the changes already applied on
the old subscriber; the patch doesn’t introduce them. I checked this
with a script that runs pg_upgrade from a PG16 subscriber while the
publisher is under load. In both cases the new subscriber hit an
insert_exists conflict. With the patch, the first re-applied row was
198, because replication resumed at LSN_1. On HEAD it was row 144,
because replication resumed from the older confirmed_flush. As you
say, the only visible difference is that the origin can show LSN_1 for
a while before the worker advances it again. Pre-17 subscribers are
also restored disabled and without their pg_subscription_rel entries,
so replication doesn’t resume automatically anyway. HEAD’s docs
already say subscription dependencies are not migrated for clusters
before 17.0. One problem with the current patch is the docs:
“Migration of the subscription’s replication origin is supported for
all cluster versions”, right after “continue from where the old
subscriber was up to”, suggests that a pre-17 subscriber also resumes
from the correct position.

So I think a documentation fix is enough here. The attached v20
changes that paragraph to:

... When the old cluster is version 17.0 or later, this allows logical
replication on the new subscriber to continue from where the old
subscriber was up to. Replication origins, including their IDs, are
migrated for all cluster versions. However, for clusters before
version 17.0, the subscription's table information is not migrated and
subscriptions are restored in a disabled state, so replication does
not resume automatically after the upgrade....

There are no code changes from v19.

regards,
Ajin Cherian
Fujitsu Australia