High Availability for PostgreSQL, Batteries Not Included
1–10 of 64 posts
Re: High Availability for PostgreSQL, Batteries Not Included
#2Personally, 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 using human intervention once you have ascertained that the failover would actually help (you never, ever want to fail over if master doesn't respond in time due to high load - at that point, failing over will only make things worse due to cold caches).
See https://github.com/blog/1261-github-availability-this-week for a nice story of automated DB failover going wrong.
In our case, we're running keepalived to share the IP address of the postgres master, but we don't actually automatically act on PG availability changes.
In a situation that actually warrants the failover, a human will kill the master node by shutting it down and keepalived will select another master and trigger the failover (which is then automated using `trigger_file` in `recovery.conf`).
In this case we have only one additional piece of infrastructure (keepalived) and we can be sure that we don't accidentally make our lives miserable with automated failovers.
The cost is, of course, potential additional downtime while somebody checks the situation, does minimal emergency root cause analysis and then shuts down the failed master.
In the even rarer case of hardware failure, keepalived would of course fail over automatically, but let's be honest: Most failures are caused by application or devops issues and in these cases it pays off to be diligent instead of panicing.
Re: High Availability for PostgreSQL, Batteries Not Included
#3"If no one has the leader key it runs health checks and takes over as leader."
I'm no expert at all on this stuff, but I do smell either a race condition (if other nodes comes alive and 'goes to see who owns the leader key in etcd' before the node 'takes over as leader') or a longer-than-needed time without a leader (where the new node knows it wants to become the leader, but is running health checks)
Re: High Availability for PostgreSQL, Batteries Not Included
#4Personally, 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.
Re: High Availability for PostgreSQL, Batteries Not Included
#5"If no one has the leader key it runs health checks and takes over as leader." I'm no expert at all on this stuff, but I do smell either a race condition (if other nodes comes alive and 'goes to see who owns the leader key in etcd' before the node 'takes over as leader') or a longer-than-needed time without a leader (where the new node knows it wants to become the leader, but is running health checks)
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.
The functionality in the code is here: https://github.com/compose/governor/blob/master/helpers/etcd...
The documentation for etcd is here: https://coreos.com/etcd/docs/latest/api.html#atomic-compare-...
Re: High Availability for PostgreSQL, Batteries Not Included
#6This 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?
Re: High Availability for PostgreSQL, Batteries Not Included
#7"If no one has the leader key it runs health checks and takes over as leader." I'm no expert at all on this stuff, but I do smell either a race condition (if other nodes comes alive and 'goes to see who owns the leader key in etcd' before the node 'takes over as leader') or a longer-than-needed time without a leader (where the new node knows it wants to become the leader, but is running health checks)
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. The functionality in the code is here: https://github.com/compose/governor/blob/master/helpers/etcd... The documentation for etcd is here: https://coreos.com/etcd/docs/latest/api.html#atomic-compare-...
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?
Re: High Availability for PostgreSQL, Batteries Not Included
#8Personally, 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…
If you are running in Microsoft Azure you need two VM instances to get any form of availability SLAs. Microsoft can reboot/migrate single instances whenever they feel like it. With manual failover you would only have a few minute downtime if someone is there to trigger it. That honestly sounds like a crappy solution 2015..
Re: High Availability for PostgreSQL, Batteries Not Included
#9Earlier quoted context omitted.
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. The functionality in the code is here: https://github.com/compose/governor/blob/master/helpers/etcd... The documentation for etcd is here: https://coreos.com/etcd/docs/latest/api.html#atomic-compare-...
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 machine is the leader and if yes it sets the url of the currently running machine to a etcd key. So overall we need to use 3 Postgres machines and 1 could fail and we would still have voting, however thats just for a single master where we don't need to read from the slaves, however thats easily extendable.
Oh and here is the Compare and Swap (Atomic) functionality of etcd that he described: https://github.com/coreos/etcd/blob/master/Documentation/api...
Re: High Availability for PostgreSQL, Batteries Not Included
#10Like 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 attempting to provide liveness, in the presence of arbitrary node or connection failures. Etcd itself can provide this for operations on its own datastore, but that doesn't mean it can be used as a perfect failure detector for another system (which is impossible in the general case). In particular, if the database master becomes partitioned from the etcd leader for more than 30 seconds but is still accessible to clients, boom -- split brain.
(You can attempt to mitigate this with timeouts, but that's not foolproof if your system can experience clock skew or swapping/GC delays. Exactly this kind of faulty assumption has caused critical bugs in e.g. HBase in the past, turning what would otherwise be a temporary period of unavailability into data loss.)
EDIT: If I'm reading the code correctly, compose.io doesn't make any attempt to mitigate this failure scenario. If the Postgresql master can't contact etcd, it continues acting as a master indefinitely, even after 30 seconds have expired and another server might have taken over. This appears to be what happens in the "no action. not healthy enough to do anything." case in ha.py. I'd be happy to be corrected if there's something I'm missing.