Re: Advice Needed: Simultaneous Upgrade of Two-Node PostgreSQL 11 Cluster

On Tue, 2025-01-07 at 01:59 +0200, kasem adel wrote:
> Dear Laurenz Albe
> في الاثنين، ٦ يناير ٢٠٢٥ ٧:١٣ م Laurenz Albe <laurenz(dot)albe(at)cybertec(dot)at> كتب:
> > On Sun, 2025-01-05 at 19:57 +0200, kasem adel wrote:
> > > Proposed Upgrade Approach:
> > >
> > > Simultaneous Upgrade Process:
> > >
> > > Install new PostgreSQL version packages on both nodes
> > > Run pg_upgrade with --check flag on both nodes
> > > Execute pg_upgrade with --link option on both nodes simultaneously
> > > Update Patroni configuration for new version on both nodes
> >
> > No, that won't work.
> >
> > You have to upgrade the primary, then rebuild the standby with
> > "patronictl reinit".  There is no safe way to upgrade the standby.
>
> Our application for reporting is dependent on the replica node for handling
> read-only operations. If we proceed with the proposed changes, it would result
> in a one-month application downtime, which would significantly impact our
> operations and is not a feasible option for us.
>
> We would greatly appreciate your insights and support in identifying
> alternative solutions [...]

One month to rebuild a standby?
Before I discuss options, let me ask how you plan to survive the next time you
have to restore your database from a backup.

There are two paths you can consider:
1. Logical replication into a new Patroni cluster.
That is not simple.

2. Following the steps in
https://www.postgresql.org/docs/current/pgupgrade.html#PGUPGRADE-STEP-REPLICAS
to upgrade the standby server.
That is not for the faint of heart either.

In both cases, you'd have to test the procedure well, and it might be a good idea
to get professional assistance.

Yours,
Laurenz Albe