Re: Upgrade streaming replication and log-shipping standby servers

On Mon, Jun 15, 2020 at 4:46 AM Victor Sudakov <vas(at)sibptus(dot)ru> wrote:

> Dear Colleagues,
>
> 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.

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. :)

-Greg