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

51–60 of 64 posts

Re: High Availability for PostgreSQL, Batteries Not Included

#51

Post by eropple »

rosser wrote: post #48
mingmecca wrote: post #40

Earlier quoted context omitted.

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.

I'll second this. Getting your data store right is way too important to leave to the inexperienced (and I say that being, if I had to judge, the semi-experienced; I've refused gigs centered around HA data stores because I won't put my name on something I am not 100% sure will work for them without issues).

Re: High Availability for PostgreSQL, Batteries Not Included

#52

Post by danieltillett »

rosser wrote: post #48
mingmecca wrote: post #40

Earlier quoted context omitted.

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.

Isn’t the major problem knowing that the expert actually know what they are doing and not just think they know what they are doing? Any tips on how you can separate the true experts from the deluded?

Re: High Availability for PostgreSQL, Batteries Not Included

#53

Post by rosser »

danieltillett wrote: post #52
rosser wrote: post #48

Earlier quoted context omitted.

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.

Isn’t the major problem knowing that the expert actually know what they are doing and not just think they know what they are doing? Any tips on how you can separate the true experts from the deluded?

For someone who doesn't already have a background in and depth of understanding of this stuff, I'd first probably look for any relevant blog posts or articles written by the folks with whom you're considering a consulting arrangement. If they do exist, you can do some research on the things they're talking about and hopefully get at least a first-pass approximation of their full of shit factor.

Any consultant worth their day (let alone week) rate should be also able to refer you to previous clients, from whom you can hopefully get some sense of how satisfied people are with the candidate's work.

And, ultimately, there's an intuition factor at work here. It's been my consistent experience that if a candidate gives you some kind of hinky vibe, don't use them. When folks I've worked with haven't followed that, the results have pretty reliably been poor, at best.

Re: High Availability for PostgreSQL, Batteries Not Included

#54

Post by robszumski »

winsletts wrote: post #22
merb wrote: post #9

Earlier quoted context omitted.

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

You can set up a local etcd proxy to mitigate this. You'd run the proxy listening on localhost, and then have it connected to the stable etcd cluster elsewhere.

The proxy can find the cluster manually or use SRV records. Autoscale the Postgres machines as much as you want after that while leaving etcd on stable machines.

Re: High Availability for PostgreSQL, Batteries Not Included

#55

Post by danieltillett »

rosser wrote: post #53
danieltillett wrote: post #52

Earlier quoted context omitted.

Isn’t the major problem knowing that the expert actually know what they are doing and not just think they know what they are doing? Any tips on how you can separate the true experts from the deluded?

For someone who doesn't already have a background in and depth of understanding of this stuff, I'd first probably look for any relevant blog posts or articles written by the folks with whom you're considering a consulting arrangement. If they do exist, you can do some research on the things they're talking about and hopefully get at least a first-pass approximation of their full of shit factor. Any consultant worth t…

Thanks for this. As you rightly point out setting up these systems can involve knowing all the rare edge cases. I am sure I could put together a system that functioned - that is until it ran straight into an edge case I have not thought of. Getting this right is not easy.

Re: High Availability for PostgreSQL, Batteries Not Included

#56

Post by technion »

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've seen multiple deployments (not necessarily specific to PostgresSQL) engineer themselves into a corner with what people feel will be a highly available roll your own solution, complete with convincing sounding blog posts.

In every case, at some point, there were implementation/software bug related issues that ultimately caused more unplanned outages than I've ever seen a single, well run server experience.

Re: High Availability for PostgreSQL, Batteries Not Included

#57

Post by rdtsc »

technion wrote: post #56
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've seen multiple deployments (not necessarily specific to PostgresSQL) engineer themselves into a corner with what people feel will be a highly available roll your own solution, complete with convincing sounding blog posts. In every case, at some point, there were implementation/software bug related issues that ultimately caused more unplanned outages than I've ever seen a single, well run server experience.

Based on experience is there a common bug or scenario that you see overlooked often? Like say what happens during the transition between leaders, or handling multiple failures (multiple netsplits..)?

Re: High Availability for PostgreSQL, Batteries Not Included

#59

Post by merb »

robszumski wrote: post #54
winsletts wrote: post #22

Earlier quoted context omitted.

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

You can set up a local etcd proxy to mitigate this. You'd run the proxy listening on localhost, and then have it connected to the stable etcd cluster elsewhere. The proxy can find the cluster manually or use SRV records. Autoscale the Postgres machines as much as you want after that while leaving etcd on stable machines.

That's what we basically trying to do in the future, however that's really hard to do if you want to have a running etcd cluster with 5 nodes all the time. You would need to check if one etcd died, and then either promote a proxy to a etcd master or run a new machine (the later is only possible in clouds or virtual environments)

Re: High Availability for PostgreSQL, Batteries Not Included

#60

Post by technion »

rdtsc wrote: post #57
technion wrote: post #56

Earlier quoted context omitted.

I've seen multiple deployments (not necessarily specific to PostgresSQL) engineer themselves into a corner with what people feel will be a highly available roll your own solution, complete with convincing sounding blog posts. In every case, at some point, there were implementation/software bug related issues that ultimately caused more unplanned outages than I've ever seen a single, well run server experience.

Based on experience is there a common bug or scenario that you see overlooked often? Like say what happens during the transition between leaders, or handling multiple failures (multiple netsplits..)?

I can't really identify a common problem. Things I've seen include:

* After a complete, planned shutdown, neither server is happy to start until it sees the other one online. In the end, neither ends up booting. * A failover occurs, at which point you find out the hard way there is state being stored in a non-replicate file. I've seen this with several different Asterisk HA solutions in particular. * A failover occurs, and non-database aware storage snapshots leave the redundant server with a non-mountable mirror of the database.

Post reply on HN