As far as RDS goes it amazes me that, even with replicas, there are NO options for doing a zero downtime upgrade which is a major gotcha.
By this point they should buffer the writes during the critical section when the switchover happens and make this seamless for users and operators.
Setting up the infrastructure to manually manage your own cluster failover would kinda go against the spirit of using RDS and letting AWS manage infrastructure for you, though.
That has nothing to do with this. All databases are fundamentally the same regardless of data model. The standard process is to start a new version, replicate the existing database, then cutover once the replica is caught up. Advanced systems can seamlessly switch the primary so it'll redirect new queries to the new primary upon that cutoff.
This has been done for decades through external tooling across many systems, including Postgres. However it would be easier if the database included this functionality internally.