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

31–40 of 64 posts

Re: High Availability for PostgreSQL, Batteries Not Included

#31

Post by geertj »

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.

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

Re: High Availability for PostgreSQL, Batteries Not Included

#32

Post by gshx »

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…

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.

Re: High Availability for PostgreSQL, Batteries Not Included

#33

Post by MichaelGG »

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.

Shouldn't sending a gratuitous ARP work to update clients?

Re: High Availability for PostgreSQL, Batteries Not Included

#34

Post by rosser »

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.

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.

Re: High Availability for PostgreSQL, Batteries Not Included

#35

Post by dap »

chousuke wrote: post #29
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.

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 acknowledged data. If I PUT data into an application and fetch it back and it's not there, that's not consistent.

Re: High Availability for PostgreSQL, Batteries Not Included

#36

Post by dap »

Thanks for writing this up!

At Joyent, we built a similar system for automated postgresql failover called Manatee. I'm sure today we would have used a Raft-based system, but that was not available when we did this work, so we used ZooKeeper. We haven't spent much time polishing Manatee for general consumption, but there's a write-up on how it maintains consistency[1]. The actual component is available here[2], and it's also been ported to Go as part of Flynn[3].

Edit: Manatee uses synchronous replication, not async, so it does not lose data on failover.

[1] https://github.com/joyent/manatee-state-machine

[2] https://github.com/joyent/manatee

[3] https://github.com/flynn/flynn

Re: High Availability for PostgreSQL, Batteries Not Included

#38

Post by mrkurt »

qaqy wrote: post #26

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

It's relatively easy to adjust this setup to use synchronous replication. In fact, it's something that I expect we'll be offering to customers in the future. Most people we talk to aren't willing to sacrifice the write availability or performance synchronous replication requires. We mostly try to give them the tools to do it right, and then educate them on the tradeoffs.

(disclaimer, I work for Compose)

Re: High Availability for PostgreSQL, Batteries Not Included

#39

Post by mrkurt »

gshx wrote: post #32
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…

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.

Re: High Availability for PostgreSQL, Batteries Not Included

#40

Post by mingmecca »

rosser wrote: post #34
jsprogrammer wrote: post #19

Earlier quoted context omitted.

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

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?
Post reply on HN