We're in a similar boat. We currently lean on (mostly custom) ansible scripting to automate all the repeatable tasks we can, but automation of major PG version upgrades is something we are yet to tackle -- although we plan to start this effort in the short term.
Would you mind sharing a bit more about your own current upgrade process? What's your standard, a dump+restore, a pg_upgrade, or replication-based? Also if you are able to share any lessons learned (e.g. common pitfalls you've run into) will all be useful information to identify ahead of time when drafting an automation strategy.
Our upgrades (pg_upgrade) have been pretty smooth so far. We have not encountered any issues other than the occasional missing OS packages. The issue is I do not know what problems to expect during a major upgrade which makes automating the process difficult.
Every year we spent a lot of time planning and manually performing major PostgreSQL upgrade on many of our on-prem and RDS instances. I’m wondering if there is a better way of managing these upgrades through automation. Can anyone share their experiences?