Re: Upgrade streaming replication and log-shipping standby servers

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(at)fidonet http://vas.tomsk.ru/