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
