Re: [PoC] pg_upgrade: allow to upgrade publisher node

On Mon, Sep 25, 2023 at 12:32 PM Dilip Kumar <dilipbalaut(at)gmail(dot)com> wrote:
>
> > > Is there anything else that stops this patch from supporting migration
> > > of logical replication slots from PG versions < 17?
> >
> > IMHO one of the main change we are doing in PG 17 is that on shutdown
> > checkpoint we are ensuring that if the confirmed flush lsn is updated
> > since the last checkpoint and that is not yet synched to the disk then
> > we are doing so. I think this is the most important change otherwise
> > many slots for which we have already streamed all the WAL might give
> > an error assuming that there are pending WAL from the slots which are
> > not yet confirmed.
> >
>
> You might need to refer to [1] for the change I am talking about
>
> [1] https://www.postgresql.org/message-id/CAA4eK1%2BLtWDKXvxS7gnJ562VX%2Bs3C6%2B0uQWamqu%3DUuD8hMfORg%40mail.gmail.com

I see. IIUC, without that commit e0b2eed [1], it may happen that the
slot's on-disk confirmed_flush LSN value can be higher than the WAL
LSN that's flushed to disk, no? If so, can't it be detected if the WAL
at confirmed_flush LSN is valid or not when reading WAL with
xlogreader machinery?

What if the commit e0b2eed [1] is treated to be fixing a bug with the
reasoning [2] and backpatch? When done so, it's easy to support
upgradation/migration of logical replication slots from PG versions <
17, no?

[1]
commit e0b2eed047df9045664da6f724cb42c10f8b12f0
Author: Amit Kapila <akapila(at)postgresql(dot)org>
Date: Thu Sep 14 08:56:13 2023 +0530

Flush logical slots to disk during a shutdown checkpoint if required.

[2]
It can also help avoid processing the same transactions again in some
boundary cases after the clean shutdown and restart. Say, we process
some transactions for which we didn't send anything downstream (the
changes got filtered) but the confirm_flush LSN is updated due to
keepalives. As we don't flush the latest value of confirm_flush LSN, it
may lead to processing the same changes again without this patch.

--
Bharath Rupireddy
PostgreSQL Contributors Team
RDS Open Source Databases
Amazon Web Services: https://aws.amazon.com