Date: Tue, 16 Jun 2020 21:50:49 +0700
From: Victor Sudakov <vas@sibptus.ru>
To: pgsql-admin@lists.postgresql.org
Subject: Re: Upgrade streaming replication and log-shipping standby servers
Message-ID: <20200616145049.GB62975@admin.sibptus.ru>
References: <20200615104619.GA97072@admin.sibptus.ru>
 <CAEtnbpWtn442AJ084rg32OweT9NwuuvOMfAEwipb3tPsgrJPtQ@mail.gmail.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
In-Reply-To: 
 <CAEtnbpWtn442AJ084rg32OweT9NwuuvOMfAEwipb3tPsgrJPtQ@mail.gmail.com>
Precedence: bulk

Greg Spiegelberg wrote:
> 
> >
> > When upgrading to a new major version, pg_upgrade documentation
> >
> > https://www.postgresql.org/docs/current/pgupgrade.html#PGUPGRADE-STEP-REPLICAS
> > states that pg_upgrade can be used on a master server. After upgrading
> > the PostgreSQL version on standbys, however, one must either pull the
> > data from scratch with pg_basebackup, or run rsync from master to
> > standby.
> >
> > Is running pg_upgrade on replicas not supported and why?
> >
> >
> Hi Victor,
> 
> I agree it would be extraordinarily convenient if pg_upgrade could operate
> on standbys.
> 
> Via experience and a quick code read of pg_upgrade source confirms that the
> new, initialized and as yet unmodified cluster is modified in preparation
> for an upgrade.  Some of those new cluster modifications include execution
> of analyze, freezing rows, resetting WAL, modification of
> controldata(?) and restoration of the old schema.  These and others are
> evident running the utility in verbose mode. Setting aside a standby must
> be promoted for these modifications, there is no current way to
> guarantee these modifications will be consistently done on both primary and
> all standbys. A standby inconsistent with the primary is no standby at all.

Hi Greg,

Thank you for the detailed explanation.
> 
> Perhaps not the right place but I would argue that IF pg_upgrade could
> 1) pause after all these new cluster modifications at or soon after
> "Restoring database schemas in the new cluster",
> 2) permit standbys to replicate the new cluster,
> 3) continue on primary, and
> 4) run on standbys picking up from where it left off at step 2 perhaps in
> coordination with primary
> then it would be very nice but I'm making assumptions and painting with a
> wide brush.  :)
> 

Sounds very tricky.

-- 
Victor Sudakov,  VAS4-RIPE, VAS47-RIPN
2:5005/49@fidonet http://vas.tomsk.ru/