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

PostgreSQL HA cluster failure: a post-mortem

gocardless.com

Post reply on HN
Print view View original

91–100 of 119 posts

Re: PostgreSQL HA cluster failure: a post-mortem

#91

Post by CyberDem0n »

nh2 wrote: post #77
merb wrote: post #68

Earlier quoted context omitted.

Well you should definitly look into them. In the past we used corosync/pacemaker a lot (even for different things than just database-ha) but trust me... it was never a sane system. if it ain't broke it worked. if something broke it was horrible to actually get back to any sane state at all. we migrated to patroni (yeah stolon is cool aswell, but since it's a little bit bigger than we need to we used patroni). the har…

I am currently using Stolon with synchronous replication for a setup, and overall it's great. It gives me automated failover, and -- perhaps more imporatantly -- the opportunity to exercise it a lot: I can reboot single servers willy-nilly, and do so regularly (for security updates every couple days). I picked the Stolon/Patroni approach over Corosync/Pacemaker because it seems simpler and more integrated; it fully "…

> I currently prefer Stolon over Patroni because statically typed languages make it easier to have less bugs (Stolon is Go, Patroni is Python)

Sounds like a holy-war topic :) But lets be serious. How statically typed language helps you to avoid bugs in algorithms you implement? The rest is about proper testing.

> and because the proxy it brings out of the box makes it convenient: On any machine I connect to localhost:5432 to get to postgres

It seems like you are running a single database cluster. When you'll have to run and support hundreds of them you will change your mind.

> if the Postgres fails over, it ensures to disconnect me so that I'm not accidentally connected to a replica.

HAProxy will do absolutely the same.

> Bugs. While Stolon works great most of the time, every couple months I get some weird failure. In one case it was that a stolon-keeper would refuse to come back up with an error message, in another that a failover didn't happen, in a third that Consul stopped working (I suspect a Consul bug, the create-session endpoint hung even when used via plain curl) and as a result some stale Stolon state accidentally accumulated in the Consul KV store, with entries existing that should not be there and thus Stolon refusing to start correctly.

Yeah, it proves one more time: * don't reinvent wheel: HAProxy vs stolon-proxy * using statically typed language doesn't really help you to have less bugs

> I suspect that, as with other distributed systems that are intrinsically hard to get right, the best way to get rid of these bugs is if more people use Stolon.

As I've already told before. We are running a few hundred Patroni clusters with etcd and a few dozen with ZooKeeper. Never had such strange problems.

Re: PostgreSQL HA cluster failure: a post-mortem

#92

Post by CyberDem0n »

mbrynard wrote: post #57
clarkdave wrote: post #29

Earlier quoted context omitted.

We’ve been using Patroni in production and it has been great. We use it with consul & pgbouncer and it can failover in under a minute with a small number of dropped requests (mostly bound by how many clients your pgbouncer can hold at once while the new master gets going). Controlled failover for upgrades or maintenance can be as quick as 10 seconds.

When deciding to go with Patroni, did you have a look at CruncyDB? We're deciding between the two and kubernetes support on CrunchyDB and documentation seems to be more comprehensive.

It looks like you are talking here about postgres-operator by Crunchy

Yeah, crunchy was a little faster with releasing it than Zalando, but try you look closer how they deploy postgres on kubernetes. Somehow it feels that they are trying to map 1 to 1 the same approach how folks used to run postgres on bare metal. That is: deploy master pod, wait until it's up and running, deploy a replica pod, and so on...

It doesn't really look cloud-k8s-native.

In my opinion such deployment should look absolutely different. You just need to deploy k8s manifest, which will create Secrets, StatefulSet and Service which will be used to connect to the master. The rest should happen automatically: * StatefulSet will start N pods with postgres * pods (Patroni) will elect leader. * elected leader will initialize (initdb) a new cluster * all other pods will get basebackup from the leader and become replicas * if the master(leader) pod die - other pods will elect a new leader * StatefulSet will start a replacement of failing pod and it will join the cluster as a new replica

And more important all it should happen without connection to Etcd, ZooKeeper or Consul. It should just use Kubernetes API.

Basically all this "magic" is already supported by Patroni https://github.com/zalando/patroni/pull/500

We are planning to merge this PR today or tomorrow, and it will make Patroni+PostgreSQL first class citizen on Kubernetes.

Re: PostgreSQL HA cluster failure: a post-mortem

#93

Post by merb »

CyberDem0n wrote: post #91
nh2 wrote: post #77

Earlier quoted context omitted.

I am currently using Stolon with synchronous replication for a setup, and overall it's great. It gives me automated failover, and -- perhaps more imporatantly -- the opportunity to exercise it a lot: I can reboot single servers willy-nilly, and do so regularly (for security updates every couple days). I picked the Stolon/Patroni approach over Corosync/Pacemaker because it seems simpler and more integrated; it fully "…

> I currently prefer Stolon over Patroni because statically typed languages make it easier to have less bugs (Stolon is Go, Patroni is Python) Sounds like a holy-war topic :) But lets be serious. How statically typed language helps you to avoid bugs in algorithms you implement? The rest is about proper testing. > and because the proxy it brings out of the box makes it convenient: On any machine I connect to localhost…

> > if the Postgres fails over, it ensures to disconnect me so that I'm not accidentally connected to a replica.

> HAProxy will do absolutely the same.

well I think that is not the same what stolon-proxy actually provides. (actually I use patroni) but if your network gets split and you end up with two masters (one application writes to the old master) there would be a problem if one application would still be connected to the splitted master.

however I do not get the point, because etcd / consul would not allow to still hold the master role which means that the splitted master would lose the master role and thus either die, because it can not connect to the new master or just be a read slave and the application would than probably throw errors if users are still connected to the splitted application. highly depends how big your etcd/consul is and how good your application detects failures. (since we are highly dependent on our database we actually kill hikaricp (java) in case of too many master write failures and just restart it after a special amount of time. well we also look in creating a small lightweight async driver based on akka, where we do this in a little bit more automated fashion.)

Re: PostgreSQL HA cluster failure: a post-mortem

#94

Post by majidazimi »

scurvy wrote: post #89
fake-name wrote: post #43

Earlier quoted context omitted.

I'd assume it's because you can just reconfigure to point pgbouncer to redirect everything to the secondary, rather then having to update all the applications using pgbouncer. It centralizes the configuration of which database is active.

How much effort is required to reconfigure pgbouncer across hundreds of app containers vs moving a single IP? (not using pacemaker/corosync)

Zero. Update configuration in a central location. Push an update to configuration manager agent on each node (using a dead simple gossip protocol). The agent wakes up, pulls new configuration, writes it and reloads pgbouncer. Using an aggressive gossip protocol, the whole process shouldn't take more than 5 seconds on a large cluster, let alone couple of nodes.

Re: PostgreSQL HA cluster failure: a post-mortem

#95

Post by majidazimi »

rosser wrote: post #23
tomc1985 wrote: post #22

Makes me sad that running your own instances is now an "elephant in the room." No pride in old-school do-it-yourself nerditry these days :/

Getting HA right is hard . DIY-ing it incurs risk, possibly deliberately, out of Not-Invented-Here-ism. Source: PostgreSQL DBA for over a decade; have built multiple HA environments; have seen many ways of "doing it wrong", and how those can end up biting their creators.

It's not hard. The problem is operation team rarely exercise failures. Configure a test HA cluster in lab, and test it. If it works, push it to production. The production system, should be continuously tested with real failures to see whether fail over mechanisms actually taking over the responsibility or not.

OF COURSE every thing is going to work in the lab, BUT MAY BE there is some other corner case in the production that you haven't considered yet. --- Louis C.K. after a sleepless night of switching primary DB to secondaries.

Re: PostgreSQL HA cluster failure: a post-mortem

#96

Post by majidazimi »

ahoka wrote: post #55

"The RAID controller logged the simultaneous loss of 3 disks from the array. All subsequent read and write operations against it failed." People seem to forget that adding a RAID controller creates a single point of failure instead of removing one. :-)

Always use software RAID. I'd rather loose 30% on efficiency than creating a single point of failure with a random hardware. Software RAID on modern OSes (Linux, FreeBSD, ... ) are pretty darn reliable and fast.

Re: PostgreSQL HA cluster failure: a post-mortem

#97

Post by koffiezet »

pjungwir wrote: post #59
morrbo wrote: post #40

Earlier quoted context omitted.

How does pgbouncer make this process easy? Just because there are less connections to go to the final DB? I've also got a random question you might be able to answer (having a hard time googling)... when using pgsql in streaming replication mode, are created functions replicated/updated to everything else as well? (just learning about postgres and saw an opportunity to ask someone in the know) Cheers!

> are created functions replicated/updated to everything else as well? Yes. Note if those functions have an implementation compiled from C, you do need to install the .so on the standbys though.

You need the .so's on the replica's simply for the database structure, but afaik, it's only the on-disk format that's being synchronized, functions are not executed on the replicas. I'm not even sure this would throw errors if the .so's would be missing - but it will give problems when a replica is promoted to the new master.

Re: PostgreSQL HA cluster failure: a post-mortem

#98

Post by fb03 »

cookiecaper wrote: post #90
illumin8 wrote: post #50

Earlier quoted context omitted.

I love nerding out over this personally, but if you're a startup, given the plethora of well managed offerings, you're frankly foolish to invest resources on this. Even if you eventually reach the point where it makes financial sense to hire a full time ops person or DBA, the opportunity cost of having a smart engineer (and it does take a smart engineer to manage a multi-master database) work on infrastructure instea…

The danger here is differentiating between "the infrastructure" and "the product". Useful database and/or infrastructure work _is_ "building the product". There's nothing necessarily wrong with using pre-baked or hosted components when they fit the bill, but pretending like they're unrelated concerns is going down a bad road. A lot of recent fads are based on this self-centered, lazy fantasy from devs that $LANGUAGE_…

Your comment make me warm and fuzzy, all I can see these days is $UNNEEDED_ADDED_COMPLEXITY. People genuinely want to get their jobs done but they don't pause for the first second to analyze if that extra lib is gonna pull a bunch of other dependencies which might break the whole thing in a million different ways down the road.

Next month a new super magical JS router component-ized and gulp-ified sass-y library is gonna come out and then I'm gonna be again in that position of the "old guy in the shop who is always convincing everyone not to upgrade to the bleeding edgiest version available". And I feel it's an epidemic.

Re: PostgreSQL HA cluster failure: a post-mortem

#99

Post by fb03 »

majidazimi wrote: post #96
ahoka wrote: post #55

"The RAID controller logged the simultaneous loss of 3 disks from the array. All subsequent read and write operations against it failed." People seem to forget that adding a RAID controller creates a single point of failure instead of removing one. :-)

Always use software RAID. I'd rather loose 30% on efficiency than creating a single point of failure with a random hardware. Software RAID on modern OSes (Linux, FreeBSD, ... ) are pretty darn reliable and fast.

And if your OS breaks you can always boot from another one and recover your cluster.

Now try that with an obscure Adaptec card which has been EOL for the last 4 years :-)

Re: PostgreSQL HA cluster failure: a post-mortem

#100

Post by pmuk »

thijsvandien wrote: post #76
foobarbazetc wrote: post #33

I’ve been involved in various Postgres roles (developer on it, consultant for it for BigCo, using it at my startup now) for around 18 years. I’ve never in that time seen a pacemaker/corosync/etc/etc configuration go well. Ever. I have seen corrupted DBs, fail overs for no reason, etc. The worst things always happen when the failover doesn’t go according to plan and someone accidentally nukes the DB at 2am. The lesson…

I'd generalize this to many kinds of failover setups, DB or non-DB. Practically each and every one I've seen is in itself less reliable than the thing it's covering for.

It amazes me that in 2017 HA for RDBs is still so brittle and complicated. I had hoped this problem would have been solved by now!
Post reply on HN