Having a master with a read replica and then making the read replica the master in case the master goes down seems to be a very well known methodology. You can use a virtual ip with both servers behind it (keepalived) or a 0 ttl DNS solution (consul). Is you case more complex than that? With master to master replication I feel there are couple of gotchas and you need to design your data in a way to avoid conflict as much as you can. A solution like bdr seems to be an off-the-shelf one that is proven.
I haven't really upgraded postgres in a rapid fashion, usually just keep with whatever version comes with the OS package manager forever.. or whatever docker image I start it with. I guess the same approach for HA can be used to upgrade one at a time, am assuming postgres doesn't break backwards compatibility very often.
You can either automate the failover or have a system to SMS you when it happens. I like to do it manually personally since I have seen the automated failover fail more times than the manual procedure.
I have probably managed over 30 postgres instances in my services that have grown up to 1/2 PB of data and I've never had a single issue with it... postgres, redis, rabbitmq, consul are technologies that you RTFM, set it up, and it just works!
I wouldn't trust a technology that isn't established and widely adopted which is how I see cockroachdb a the moment.
>You can either automate the failover or have a system to SMS you when it happens.
I don't want to be writing code to make things do automatic failover. I'm not a database engineer. I want a database where its standard setup does this stuff for me.
(I would find it really interesting if there was a tightly-integrated "distribution" of Postgresql plus related services that had a setup process as straight-forward as CockroachDB and had automatic read-replicas and failover.)
Compared to all that, CRDB is just a binary on each server pointing to each other. Operational simplicity matters and that's where CRDB is a great alternative.
None of that is automatic and every step introduces problems compounded by the amount of tools used to handle each step. Because of this, the setup is intensive and usually custom to every environment. This is hard to test and be confident in and the source of much downtime or data loss.
Meanwhile CRDB handles all of this seamlessly. As long as you have a quorum of healthy nodes, your database is alive and working. Point a load balancer at all of the nodes and you will always have a database to contact with no other overhead. Postgres could get there, but it isn't anywhere there today.
We still use Postgresql, and I agree, it's setup and forget. But those aren't HA (and don't need to be).
Amazon does this with RDS, and so can you.
This really isn't very tough to achieve with replicas. And for upgrades you just add a new replica with the latest version and promote it to master and replace the remaining nodes one at a time.
I find it difficult to setup PostgreSQL without having a single point of failure and introducing downtime for things such as upgrades.
We aren't doing life changing stuff, true, but a downtime in our system can result in employees (often at the bottom of the economic ladder) not being able to get to work. That gnaws at me. I guess the better alternative is to go for a hosted approach, but that isn't without its own complications and challenges.