A confusing thing said in the PostgreSQL manual:
I think the technical term for this sort of thing is "Covering your Behind". 

All it's saying is that upgrades will work in the vast majority of cases but acknowledges that there's an outside chance that somebody, somewhere has got some convoluted set-up that means an extra step or two might be needed.  If the PostgreSQL team think that's a possibility for any given upgrade, then they'll tell us so and we can work out whether or not the problem case(s) apply to us and we can do the extra work, if necessary. 

I would never contemplate completely automating database upgrades.
Databases are complex Beasties and can be "upset" by any number of things.  Having them "upset" in the middle of an upgrade is a really Bad Place to be.  

Of course, you want to script up as much as you can, but you have to accept that you cannot cater for every possible eventuality.  You should always have somebody around during the upgrade, just in case.