[imperialWicket]

This seems robust, but feels like more moving parts than are necessary.

I feel like HAProxy with PostgreSQL + Bucardo (multi-master + at least one slave) would achieve this, and net you fewer moving parts. Under what circumstances does this fail where the etcd-dependent solution succeeds?

[winsletts]

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.

[chucky_z]

Can I ask why HAProxy seems to be a more popular choice than the very, very simple (and robust) pgbouncer?

[winsletts]

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.

[chucky_z]

Interesting. I got around this by just recreating the users file for pgbouncer and issuing a reload every 15 minutes or so. This was only needed for one setup as the rest were just static, or the user was very low volume so I asked them to connect directly. pgbouncer handled reloads very well (on-par with nginx) so it worked for me. Thanks for the great insight with your answer.

[imperialWicket]

This is great supplemental data, much appreciated!

[imperialWicket]

Lots of people are already using it, so it has familiarity (I was thinking more than pgbouncer, pure conjecture though).

I think if you wanted to have the simplest possible solution, pgbouncer and postgresql-specific replication mechanism would be perfect. This is along the same lines as my question - I don't really see how these alternate solutions could be construed as lacking...