PostgreSQL High Availability Solutions – Part 1: Jepsen Test and Patroni

> If the PostgreSQL backend is cancelled while waiting to acknowledge replication (as a result of packet cancellation due to client timeout or backend failure) transaction changes become visible for other backends. Such changes are not yet replicated and may be lost in case of standby promotion.

This sounds like the two generals problem, which has no solution. But I may be misunderstanding.

The best way I have found is to setup keepalived -> pgbouncer -> Postgres. Use repmgr to manage replication and barman for backups. Setup a VIP with keepalived with a small script that checks if the server is primary. You loose about 7-9 pings during a failover, have keepalived check about every 2 seconds and flip after 3 consecutive failures.

Is anyone here using YugabyteDB for high-availability Postgres?

It seems like a compelling option:

* Much closer to Postgres compatibility than CockroachDB.

* A more permissive license.

* Built-in connection manager [1], which should simplify deployment.

* Supports both high availability and geo-distribution, which is useful if scaling globally becomes necessary later.

That said, I don't see it mentioned around here often. I wonder if anyone here has tried it and can comment on it.

--

1: https://docs.yugabyte.com/preview/explore/going-beyond-sql/c...

MySQL has had first class replication and failover built into it for years. You deploy a server, enable binlogs, clone the server (percona's xtranackup can live-clone a server locally or remotely) and start the new instance. Then you point the replica at the master using 'CHANGE MASTER TO ...' and it starts pulling binlogs and applying them. On the master, you can repeat this last step, making the replica it's master. This means that you have multi-master replication. And it just works. There are some other tools you can use to detect failure of the current master and switch to another, that is up to you.

There are also solutions like MySQL Cluster and Galera which provide a more cluster-like solution with synchronous replication. If you've got a suitable use case (low writes, high reads and no gigantic transactions) this can work extremely well. You bootstrap a cluster on node 1, and new members automatically take a copy of the cluster data when they join. You can have 3 or 5 mode clusters, and reads are distributed across all nodes since it's synchronous. Beware though, operating one of these things requires care. I've seen people suffer read downtime or full cluster outages by doing operations without understanding how they work under the hood. And if you're cluster is hard-down, you need to pick the node with "most recent transaction" to re-bootstrap the cluster or you can lose transactions.

We use proxysql (https://proxysql.com) which works very well. We have not seen any downtime for years. We wrote our own master promotion code a very long time ago; it has proven to be very robust.