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

51–60 of 119 posts

Re: PostgreSQL HA cluster failure: a post-mortem

#51

Post by cromantin »

merb wrote: post #48
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…

why not just use https://github.com/zalando/patroni

Patrons is great. Running dockerised Postgres with consul backend for years without a hitch. Haproxy as lb. What that? A replica need reboot. Just reboot. Primary? Just failover to replica and reboot. Undesired reboots recovers in under 10 seconds. During which just primary is not available but replicas are.

Re: PostgreSQL HA cluster failure: a post-mortem

#52

Post by takeda »

merb wrote: post #48
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…

why not just use https://github.com/zalando/patroni

My understanding is that the problem is not really with pacemaker/corosync. Those tools also are always consistent as ZK/etcd/Consul. There is also SONITH to make sure the node that goes down can't cause damage once it is back.

The problem is not these tools, but implementing what is the right thing to do during an outage or even properly detecting one (what happened with github). Your solution might work 99 cases out of 100 but that remaining 1 case might cause your data loss.

When there is a human required to do the switch it typically he/she can investigate what happened and make the right decision.

It's theoretically possible to have a foolproof solution that always works right, but that's extremely hard to implement, because you need to know in advance what kind of issues you will have, and if you miss something, that's one case where your tool might make a wrong decision.

Re: PostgreSQL HA cluster failure: a post-mortem

#53

Post by jskrablin »

barkingcat wrote: post #44
jskrablin wrote: post #28

Pacemaker is known to wreak havoc if it gets angry. The usual path to quick recovery when the cluster goes crazy like this is to make really sure what's the most up to date replica, shut down Pacemaker completely, assign VIP manually to a healthy replica and promote it manually. Then once you're up and back in the business figure out how to rebuild the cluster.

If this is indeed true, doesn't this negate the purpose of pacemaker to begin with? It's like anti-software. When you run with it in your environment, to recover from a failure (which seems to me what HA software should be about) you have to turn it off first or else it will destroy your recovery attempts. It's like a perverse version of chaos-monkey, except you want it to destroy you when you are most vulnerable.

It's great when it works as expected. When it doesn't... then the fun begins. I've found it quite fragile, components versions sensitive, configuration sensitive, etc. Most of the time I've seen Pacemaker gone crazy Pg itself was happy to cooperate once the Pacemaker was out of the way. The unknown/weird Pacemaker failure modes were a real (and scary) problem.

I guess the lesson here is not to rely entirely on some HA black magic and always have procedures in place for the 'HA black magic failed us' moments. And team trained to deal with situation like this. It's only software so it will break sooner or later.

Re: PostgreSQL HA cluster failure: a post-mortem

#54

Post by liveoneggs »

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…

your findings are supported by Baron Schwartz as well (https://www.xaprb.com/blog/2012/09/17/is-automated-failover-... and others)

Re: PostgreSQL HA cluster failure: a post-mortem

#55

Post by ahoka »

"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. :-)

Re: PostgreSQL HA cluster failure: a post-mortem

#56

Post by qaq »

Sinjo wrote: post #32
derefr wrote: post #31

Earlier quoted context omitted.

It's still "respectable" to run your own n=1 Postgres instance, maybe with WAL-E backup. It's sensible, as well, to create your own read-replicas to scale OLAP; and even to do your own shared-nothing sharding across regions. These are all "set and forget" enough that they can be the responsibility of your regular devops. But, when you get to the point where you need multi-master replication, you're making a mistake i…

Right! A thing that scares me is anyone saying they're running their own HA cluster (not single instance) for cost reasons. Infra people are not cheaper than the hosted solutions (Amazon RDS, Google Cloud SQL, Heroku Postgres).

Cause AWS infra is managed by magic monkeys.

Re: PostgreSQL HA cluster failure: a post-mortem

#57

Post by mbrynard »

clarkdave wrote: post #29
kodablah wrote: post #3

I too am coming up on a need for no-downtime HA failover for Postgres. I too am not allowed to use a hosted PaaS-ish solution like RDS. I was considering Citus's multi master impl (I don't need to spread the load, just need HA). I had not considered Pacemaker. Has GoCardless investigated this option and have any insight to give? HA has traditionally been a real pain point for traditional RDBMS's in my experience.

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.

Re: PostgreSQL HA cluster failure: a post-mortem

#58

Post by hinkley »

jskrablin wrote: post #28

Pacemaker is known to wreak havoc if it gets angry. The usual path to quick recovery when the cluster goes crazy like this is to make really sure what's the most up to date replica, shut down Pacemaker completely, assign VIP manually to a healthy replica and promote it manually. Then once you're up and back in the business figure out how to rebuild the cluster.

Unfortunately appropriate project name? (A maulfunctioning pacemaker can kill you)

Re: PostgreSQL HA cluster failure: a post-mortem

#59

Post by pjungwir »

morrbo wrote: post #40
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…

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.

Re: PostgreSQL HA cluster failure: a post-mortem

#60

Post by luke0016 »

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. :-)

> People seem to forget that adding a RAID controller creates a single point of failure instead of removing one. :-)

At worst it does both. In most cases it really does just remove a single point of failure (a disk). Other non-RAID configurations likely use a shared controller too. Moving that single point of failure to a different controller doesn't make it any worse.

Post reply on HN