Automatic pg_upgrade orchestration

Howdy –

I have a product that uses stolon to coordinate cluster assembly, backup and restore in a completely hands-free way (no customer ops-team interaction). It’s been great! Several years in, I’m now trying to automate upgrade of the underlying PostgreSQL release, and it’s not immediately obvious how best to do that.

My initial thought is to set up a completely separate consul namespace (by overriding store-prefix) and backup store location for the newer PostgreSQL release; read the metadata from the old version to determine which node was most recently primary; perform pg_upgrade there, and essentially set up a whole new cluster with this as the initial node. There’s a lot of hand-waving in that one-paragraph description, though – if anyone has actually done this, and nailed down the steps that need to be automated, it would be very welcome. (Even better if there’s a more efficient, or safer, approach than that described above – having a single point of failure during the migration process is not great, and neither is needing to reload the entire database from backup on every node other than the most recent primary).

Thoughts? Experience?