Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

High Availability for PostgreSQL, Batteries Not Included

compose.io

Post reply on HN
Print view View original

21–30 of 64 posts

Re: High Availability for PostgreSQL, Batteries Not Included

#21

Post by imperialWicket »

chucky_z wrote: post #15
imperialWicket wrote: post #6

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?

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

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...

Re: High Availability for PostgreSQL, Batteries Not Included

#22

Post by winsletts »

merb wrote: post #9
Someone wrote: post #7

Earlier quoted context omitted.

But then, isn't it not "If no one has the leader key it runs health checks and takes over as leader." but "If no one has the leader key it takes over as leader, runs health checks, and starts functioning as leader." ? If so, I would do the health checks and then try to become the leader. Or do the 'health checks' involve other nodes?

It simply relies on the Voting feature of ETCD (Raft) it's really simple to use locking with etcd, and etcd is really really stable. However it would be easier to install etcd on every Postgres node and just make a golang library that sets the master of Postgres to the etcd master (etcd also has a leader). Also systemd would keep the overall system healthy. (that's what we at envisia do) Just have repeatedly check if…

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.

Re: High Availability for PostgreSQL, Batteries Not Included

#23

Post by rorr »

jvehent wrote: post #4
pilif wrote: post #2

Personally, I would try to go for a simpler solution. In case of a failover event which is already complicated in itself and happening at a point in time where stuff is already going wrong (there would be no failover otherwise), do you really want to have all this additional infrastructure with etcd and haproxy as a dependency? If you can live with a few minutes of downtime, I would recommend to trigger your failover…

Can keepalived automatically float MAC addresses nowadays? Last time I checked, that didn't work and clients needed an arp flush to use the new master.

keepalived uses vrrp and will issue a gratuitous arp

Re: High Availability for PostgreSQL, Batteries Not Included

#24

Post by teraflop »

jsprogrammer wrote: post #19
teraflop wrote: post #10

Like a lot of designs that use Raft/Zookeeper/Paxos/whatever as a building block, the full system doesn't inherit all of the safety properties of the underlying consensus algorithm. I don't think that makes this code useless by any means, but I think it's important to be aware of the edge cases. Consensus algorithms are popular because they're supposed to solve the difficult problem of guaranteeing consistency while…

If the PostgreSQL leader doesn't reset the leader key, it's no longer leader.

The rest of the cluster doesn't think it's the leader, but the problem is that it still accepts database connections as if it were.

If a client sees a stale value of the leader key (which is possible, either through network hiccups or etcd's normal behavior of allowing reads from followers) then it could contact the old leader and perform updates which won't be visible on the new leader.

Re: High Availability for PostgreSQL, Batteries Not Included

#25

Post by winsletts »

chucky_z wrote: post #15
imperialWicket wrote: post #6

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?

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

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.

Re: High Availability for PostgreSQL, Batteries Not Included

#27

Post by winsletts »

imperialWicket wrote: post #6

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?

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.

Re: High Availability for PostgreSQL, Batteries Not Included

#28

Post by winsletts »

pjungwir wrote: post #11

Since most of the comments are critical, I'll say: thank you for the awesome writeup! I agree this is more complex than HA PG setups I've done in the past, but I'm thrilled to have another perspective. Also doing a thorough writeup like this takes time, and a lot of people would rather jump back into building the next thing. It's a great contribution! I agree with pilif that you almost always want to failover the db…

Take a look at the code we have open sourced: https://github.com/compose/governor

Re: High Availability for PostgreSQL, Batteries Not Included

#29

Post by chousuke »

qaqy wrote: post #26

And with this amazing design you can easily loose committed data and have all sorts of other fun problems.

Is it even possible to guarantee that you won't lose commits with postgresql replication? For many applications, consistency is more important than not losing any data ever. For the other kind of application, you'll need something else.

Re: High Availability for PostgreSQL, Batteries Not Included

#30

Post by imperialWicket »

winsletts wrote: post #25
chucky_z wrote: post #15

Earlier quoted context omitted.

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

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 s…

This is great supplemental data, much appreciated!
Post reply on HN