Horton

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

Julien Rouhaud <rjuju123@gmail.com>

View thread View on postgr.es
From: Julien Rouhaud <rjuju123@gmail.com>
To: Amit Kapila <amit.kapila16@gmail.com>
Cc: Masahiko Sawada <sawada.mshk@gmail.com>, "Hayato Kuroda (Fujitsu)" <kuroda.hayato@fujitsu.com>, PostgreSQL Hackers <pgsql-hackers@lists.postgresql.org>, vignesh C <vignesh21@gmail.com>
Date: 2023-08-07T07:36:17Z
Lists: pgsql-hackers 
On Mon, Aug 07, 2023 at 12:42:33PM +0530, Amit Kapila wrote:
> On Mon, Aug 7, 2023 at 11:29 AM Julien Rouhaud <rjuju123@gmail.com> wrote:
> >
> > Unless I'm missing something I don't see what prevents something to connect
> > using the replication protocol and issue any query or even create new
> > replication slots?
> >
>
> I think the point is that if we have any slots where we have not
> consumed the pending WAL (other than the expected like
> SHUTDOWN_CHECKPOINT) or if there are invalid slots then the upgrade
> won't proceed and we will request user to remove such slots or ensure
> that WAL is consumed by slots. So, I think in the case you mentioned,
> the upgrade won't succeed.

What if new slots are added while the old instance is started in the middle of
pg_upgrade, *after* the various checks are done?

> > Note also that as complained a few years ago nothing prevents a bgworker from
> > spawning up during pg_upgrade and possibly corrupt the upgraded cluster if
> > multixid are assigned.  If publications are preserved wouldn't it mean that
> > such bgworkers could also lead to data loss?
> >
>
> Is it because such workers would write some WAL which slots may not
> process? If so, I think it is equally dangerous as other problems that
> can arise due to such a worker. Do you think of any special handling
> here?

Yes, and there were already multiple reports of multixact corruption due to
bgworker activity during pg_upgrade (see
https://www.postgresql.org/message-id/20210121152357.s6eflhqyh4g5e6dv@dalibo.com
for instance).  I think we should once and for all fix this whole class of
problem one way or another.

Commits

  1. Fix issues in binary_upgrade_logical_slot_has_caught_up().

  2. Fix a random failure in 003_logical_slots.pl.

  3. Fix a test in 003_logical_slots.

  4. Fix uninitialized slot array access during the upgrade.

  5. Fix the test 003_logical_slots.

  6. Commit b195e6d482 forgot to update meson.build.

  7. Use shorter file names in the upgrade logical slots test.

  8. Migrate logical slots to the new node during an upgrade.

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

  10. Prevent possibility of panics during shutdown checkpoint.