Re: PoC: true in-place major version upgrades

On Mon, Oct 26, 2026 at 08:29:01AM -0400, Robert Haas wrote:
> I don't really like that conclusion, because I've always felt that
> pg_upgrade is a pretty half-baked version of upgrade-in-place. Both
> the rather high possibility of failures requiring manual correction
> and also the time it takes to run are real downsides, and I would like
> to have something better. But I suspect this proposal is not the way.
> I do wonder if some more limited version of it might make sense.

I'm a bit hesitant to chime in because I don't want to derail John's
thread, but pg_upgrade downtime is something I've thought about and worked
on a lot over the years. I've witnessed or at least heard about many
attempts at $subject, and thus far I'm not convinced it's viable. Sure,
it's just software and almost anything is possible, but in this case I
suspect it comes with a huge amount of complexity, ongoing maintenance
burden, and really weird trade-offs. Re-creating the catalogs with SQL
keeps things at least somewhat reasonable, at the expense of lots of
downtime for folks with big catalogs. (I should say that I would be very
happy for John to prove me wrong and get $subject committed.)

I've long wondered (and even prototyped at some point) if we can address
the downtime problem by doing the dump and restore while the old cluster is
running. The restore specifically contributes the majority of the
downtime, so allowing folks to use the old cluster up until the new
cluster's schema is ready would be a huge win. We'd need to block most
catalog changes (except for perhaps a few crucial ones like relfrozenxid
updates), so users wouldn't be able to run DDL commands, but that still
seems substantially better to me than being able to run _no_ commands.

Maybe that's similarly complicated and unmaintainable, but ISTM that it
could be a good incremental step that helps minimize downtime while
retaining most of the battle-tested infrastructure of pg_upgrade.

--
nathan