High Availability for PostgreSQL, Batteries Not Included • Hacker News Board

Re: High Availability for PostgreSQL, Batteries Not Included

#41

Post by Xorlev » Fri, Jul 17, 2015, 8:05 PM UTC

mrkurt wrote: post #39

gshx wrote: post #32

Earlier quoted context omitted.

Agree and that's a great point about human failover. It can become a challenge for distributed databases running on a large number of instances (like bigtable) but if we're talking only about master HA, then yes, that can still do with human intervention though automation is still preferable. For smaller db setups, much easier to just let a human/dba intervene.

Smaller DB setups rarely have the ops/DBA support required to do manual failover. I think having an as-consistent-as-feasible, automatic failover is something of a default expectation for databases these days, at any size.

You need a larger team to do automatic failover because getting it right is a massive PITA. Either that or pay someone to do it right for you, e.g. RDS, managed solutions.

Manual failover is often a lot safer, automatic systems have a nasty habit of not doing what you expect them to and trashing your database / losing data.

Re: High Availability for PostgreSQL, Batteries Not Included

#42

Post by ninkendo » Fri, Jul 17, 2015, 8:09 PM UTC

Where I am we have a similar setup for leader election and failover (using etcd and haproxy) but we add an additional step: a standby instance that does not participate in master election, and always follows the elected master.

Then we turn on confirmed writes on the master so that the non-participating standby (called the "seed") has to receive and confirm your write before the transaction can commit.

This has the bonus of preventing split brain... If the wrong instance thinks it's master, writes will block indefinitely because the seed isn't confirming them. If the seed is following the wrong machine, same thing. And if clients and the seed and the master are all "wrong", then that's ok because at least they all "consistently" disagree with etcd.

The seed instance can run anywhere, and is responsible for receiving WAL snapshots from the master and archiving them (to shared storage) so it can crash too and be brought up elsewhere and catch up fine. The writes just block until this converges.

It's worked quite well for us for a few months on a hundred or so Postgres clusters, we haven't seen an issue yet. I'd love for somebody knowledgeable about this stuff to point out any flaws.

Re: High Availability for PostgreSQL, Batteries Not Included

#43

Post by snuxoll » Fri, Jul 17, 2015, 8:37 PM UTC

dap wrote: post #35

chousuke wrote: post #29

Earlier quoted context omitted.

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.

With postgresql synchronous replication, in order to lose writes that have been acknowledged to the postgresql client, you'd have to lose filesystem data on both the primary and the synchronous standby. (I believe the way postgresql uses the term "committed", you can lose data that's "committed", but not once postgresql has acknowledged it to the client.) For many applications, consistency includes not losing acknowl…

> For many applications, consistency includes not losing acknowledged data. If I PUT data into an application and fetch it back and it's not there, that's not consistent.

Durability and consistency are two separate concepts.

Re: High Availability for PostgreSQL, Batteries Not Included

#44

Post by ozgune » Fri, Jul 17, 2015, 8:54 PM UTC

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…

Great write-up, thanks for sharing!

I'm curious about HAProxy being a single point of failure as well. What happens when it fails?

Re: High Availability for PostgreSQL, Batteries Not Included

#45

Post by dap » Fri, Jul 17, 2015, 8:55 PM UTC

ninkendo wrote: post #42

Where I am we have a similar setup for leader election and failover (using etcd and haproxy) but we add an additional step: a standby instance that does not participate in master election, and always follows the elected master. Then we turn on confirmed writes on the master so that the non-participating standby (called the "seed") has to receive and confirm your write before the transaction can commit. This has the b…

That's interesting. We do something pretty similar in the Manatee component that I mentioned elsewhere in this thread, except that the designated synchronous standby can takeover if the primary goes away. But it can only do so when another peer is around to become the new synchronous standby, so we maintain the write-blocking behavior that avoids split-brain.

Re: High Availability for PostgreSQL, Batteries Not Included

#46

Post by gshx » Fri, Jul 17, 2015, 9:00 PM UTC

mrkurt wrote: post #39

gshx wrote: post #32

Earlier quoted context omitted.

Agree and that's a great point about human failover. It can become a challenge for distributed databases running on a large number of instances (like bigtable) but if we're talking only about master HA, then yes, that can still do with human intervention though automation is still preferable. For smaller db setups, much easier to just let a human/dba intervene.

Smaller DB setups rarely have the ops/DBA support required to do manual failover. I think having an as-consistent-as-feasible, automatic failover is something of a default expectation for databases these days, at any size.

I should have clarified that I meant small in the context of less scaled out and more vertically scaled like the traditional rdbms running on big iron.

Re: High Availability for PostgreSQL, Batteries Not Included

#47

Post by jsprogrammer » Fri, Jul 17, 2015, 9:01 PM UTC

teraflop wrote: post #24

jsprogrammer wrote: post #19

Earlier quoted context omitted.

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.

Those updates don't count though, since they weren't sent to the leader. Might as well pipe them to /dev/null. There is no need to track them.

A client shouldn't use a stale value. If a DB does not hold a valid key, it shouldn't accept new connections, or signal that data was committed.

Re: High Availability for PostgreSQL, Batteries Not Included

#48

Post by rosser » Fri, Jul 17, 2015, 9:02 PM UTC

mingmecca wrote: post #40

rosser wrote: post #34

Earlier quoted context omitted.

Fencing isn't quite that simple, unfortunately. I've been doing database, and specifically PostgreSQL, administration and HA setups for a long time now. This stuff is a lot harder than people think it is. People who roll their own solutions, thinking "Oh, this will totes be good enough!" tend to find themselves very painfully surprised that it isn't.

I'm just wading into the HA waters with Postgres. I somewhat understand the tradeoffs between simplicity and robustness, but what would be your recommendation on how to proceed for someone who is a newbie?

This is going to sound cynical and self-serving (even though I'm not actually available for hire right now), but find someone who knows what they're doing and buy their time. It probably won't be cheap, but it will almost without doubt be cheaper than what you'll do to yourself if you try to hand-roll database HA.

Re: High Availability for PostgreSQL, Batteries Not Included

#49

Post by teraflop » Fri, Jul 17, 2015, 9:07 PM UTC

geertj wrote: post #31

jsprogrammer wrote: post #19

Earlier quoted context omitted.

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

I haven't look at the code, but the failover should ensure that the HAproxy isolates the failed master ("fencing" in HA terminology).

I won't pretend to understand all the details after only a quick glance at the code, but it looks to me like HAproxy is invoking a script[1] that checks each PostgreSQL instance directly (bypassing the governor process) and enables it as an endpoint iff the instance is a replication master, as opposed to a follower. In which case, if multiple instances are in master mode, the proxy might forward connections to either one. It has no way of knowing which one is considered to be the "real" one by etcd; and even if it tried to check, that check would itself be subject to race conditions.

[1]: https://github.com/compose/governor/blob/master/haproxy_stat...

Re: High Availability for PostgreSQL, Batteries Not Included

#50

Post by teraflop » Fri, Jul 17, 2015, 9:12 PM UTC

jsprogrammer wrote: post #47

teraflop wrote: post #24

Earlier quoted context omitted.

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.

Those updates don't count though, since they weren't sent to the leader. Might as well pipe them to /dev/null. There is no need to track them. A client shouldn't use a stale value. If a DB does not hold a valid key, it shouldn't accept new connections, or signal that data was committed.

> Those updates don't count though, since they weren't sent to the leader.

But that's exactly the problem! If you were to run this whole system under a tool like Jepsen, this would show up as "acknowledged but lost writes". It's not generally considered acceptable to connect to your database, issue an UPDATE and a COMMIT, and have everything appear to work successfully, only for the data to disappear into the aether because it got sent to the wrong replica.

> If a DB does not hold a valid key, it shouldn't accept new connections, or signal that data was committed.

Exactly, and the problem with this implementation (again, unless I'm missing something) is that it may violate this contract.