I'm reading about automated Postgres failover. So you can do it with a tool like repmgr... But you still have the task of notifying the application to re-connect to the new primary. Can't find any guides on how you could do that. (I use Consul for service discovery for what it's worth)
Consul service check definitions hardcode tags... Like technically you are supposed to tag services with stuff like "primary" or "replica" to be able to filter during service discovery. You can override them with a CLI command. If repmgr has an option to execute something after automatic failover is triggered, then you could update the service tags; then the rest could be done with consul-template.
@Gargron it’s risky
Lot’s of network partition/STONITH edge cases that makes it impractical. Unless you engineer a lot around it.
@gargron did you had a look at patroni?