Bucardo with multi-master is fantastic when a DBA can configure the multi-master and manage future changes. Bucardo requires each table to have proper Bucardo configuration and each table on each host to have the proper schema, since Bucardo does not replicate schema changes.
Compared to streaming replication, during high load, Bucardo sync is also quite expensive for a replication mechanism.
As a service, Bucardo's requirements did not scale for us. It created to many caveats. The limitations of Bucardo for our service became obvious quickly.
We tested with PGPool and PgBouncer in various iterations.
PGPool failed at basic failover. It worked fine while the leader remained leader. It would failover to the follower who became leader, but after the first failover, it would stall on connections. We worked through various settings and attempts at making it more stable, but in the end we were not happy with the stability.
PGBouncer requires a connection to a single database and requires a user store associated at the PGBouncer level. One of our internal requirements for our Postgres service is give customers full access to Postgres capabilities. PGBouncer would either limit customer functionality or require us to build more tools for customers to use Postgres's complete functionality. For instance, if a customer ran `CREATE USER foo WITH LOGIN …` from the Postgres connection, the customer would not be authenticate as foo user because PGBouncer would not have immediate knowledge of the new user.
In the end, HAProxy offered the stability and enabled the base functionality of Postgres we wanted. In tests, it failed over quickly and reliably. The only caveat with HAProxy + Postgres is that you have to rely on TCP passthrough with SSL termination at Postgres. We'd have preferred the SSL termination at HAProxy, but Postgres engineered it's own connecting procedure to listen for standard and SSL connections on the same port. SSL termination at the HAProxy was causing issues for drivers that were built to use that procedure and cannot use a standard SSL connection.
The problem with etcd members on every Postgres node is that clusters fixed nodes or members. etcd doesn't function well in an environment where you could tear down / build up new nodes. Most of our Postgres service runs on AWS, and thus we must expect that any single node may vanish, and our system must replace that node. We tried running etcd alongside Postgres in an early prototype, but ran into issues with etcd cluster stability when destroying and recreating nodes. Thus, we opt for a stand alone etcd cluster distinct from the Postgres cluster.
The code relies on functionality in etcd to prevent a race condition. Using `prevExist=false` on acquiring the leader key, the set will fail if another node wins the race.
I imagine this is one of those situations where the truth will never be available. All participates have taken sides, and have skewed / omitted / shaped their own stories for their own interests.
I wish we had a black box recorder for organizational and cultural failures.