@Gargron at work we decided to only do manual failover of postgres databases, because usually the circumstances are best evaluated by a human, but we have 24/7 on-call to react to whatever alert condition.

On the other hand not sure you’d be better off trying to automate this, as the investment to get it working in a way that is a benefit more than a source of problems is large.