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

71–80 of 119 posts

Re: PostgreSQL HA cluster failure: a post-mortem

#71

Post by nh2 »

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.

On the other hand:

With hosted Postgres, when a failure does happen, isn't it much harder to get at the log files? They seem extremely useful to diagnose the problem and make sure it doesn't happen again, as the article shows. What's your experiene here, can you get at logs easily with hosted Posgres offerings?

And it seems the only way to get reliable Postgres HA for everyone, and to weed out the bugs, is if more people run Posgres HA themselves. For example, I find Stolon and Patroni great, but I would be more relaxed about them if they had 100x more users.

Re: PostgreSQL HA cluster failure: a post-mortem

#72

Post by devit »

I think the root issue is that PostgreSQL does not offer an HA solution that works out of the box with minimal configuration, resulting in people using broken third-party ones and/or configuring them incorrectly.

They should either provide one or "bless" an external solution as the official one (after making sure it works correctly).

The other problem is that GoCardless setup an asynchronous and a synchronous replica instead of 2 synchronous replicas (or preferably 4+), resulting in only two points of failure, which is not enough.

Re: PostgreSQL HA cluster failure: a post-mortem

#73

Post by rosser »

nh2 wrote: post #71
rosser wrote: post #23

Earlier quoted context omitted.

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.

On the other hand: With hosted Postgres, when a failure does happen, isn't it much harder to get at the log files? They seem extremely useful to diagnose the problem and make sure it doesn't happen again, as the article shows. What's your experiene here, can you get at logs easily with hosted Posgres offerings? And it seems the only way to get reliable Postgres HA for everyone, and to weed out the bugs, is if more pe…

We aren't using hosted postgres (much, yet). We provision EC2 instances and self-manage it. Failover is scripted, and manually invoked as needed.

None of us trust any of the automated failover solutions enough to use them. We want human judgement in that loop, even if it means being woken at 3AM to push the button. It's that hard to get right.

Just one incident like The Fine Article's is well more than our entire infrastructure's total downtime for the rolling year, and we have hundreds of postgres instances.

Done wrong, automated failover is a net increase in risk. And, in case my thesis is somehow unclear, it's hard to get right.

Re: PostgreSQL HA cluster failure: a post-mortem

#74

Post by lattepiu »

linker3000 wrote: post #39

Since I am investigating HA with PostgreSQL right now and have bitter experience of Pacemaker 'HA' instances that have been anything but, I am looking at Amazon Aurora and Microsoft's (in preview) Azure database for PostgreSQL offerings. I would really appreciate any insight from others who are already using them (we intend to do some PoC work shortly). Our dev team also came up with some pertinent questions, which w…

I can comment on Amazon Aurora:

> Is the product a fork of PostgreSQL or a wrapper round the current version?

Aurora is a fork: they've re-written a significant chunk of the engine. Note that Amazon also offers RDS PostgreSQL, which is a managed version of the "regular" PostgreSQL engine. RDS PostgreSQL also offers a HA setup (no version upgrade without downtime, however). It works quite well.

> Will the DB engine keep in lock-step with new PostgreSQL releases or might they diverge?

Amazon promises to keep it in lock-step. How soon they will release an upgrade to a major version remains to be seen.

> When new versions of Amazon Aurora/Azure DB for PostgreSQL are released will our live instance get automatically updated or will we be able to choose a version?

Minor version upgrades are applied automatically. For major version upgrades, it's unclear at this time (there hasn't been one yet for Aurora PostgreSQL), but I think it's unlikely they will be applied automatically.

Re: PostgreSQL HA cluster failure: a post-mortem

#75

Post by Sinjo »

devit wrote: post #72

I think the root issue is that PostgreSQL does not offer an HA solution that works out of the box with minimal configuration, resulting in people using broken third-party ones and/or configuring them incorrectly. They should either provide one or "bless" an external solution as the official one (after making sure it works correctly). The other problem is that GoCardless setup an asynchronous and a synchronous replica…

Pretty much number 1 on my Postgres wishlist.

Re: PostgreSQL HA cluster failure: a post-mortem

#76

Post by thijsvandien »

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.

Re: PostgreSQL HA cluster failure: a post-mortem

#77

Post by nh2 »

merb wrote: post #68
Sinjo wrote: post #67

Earlier quoted context omitted.

It's a little lost in another comment thread ( https://news.ycombinator.com/item?id=15862584 ), but I'm definitely excited about solutions like Patroni and Stolon that have come along more recently.

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 "owns" the postgres processes and controls what they do, so I suspect there is less chance to accidentally mis-configurations in the fashion of what the article describes.

I currently prefer Stolon over Patroni because statically typed languages make it easier to have less bugs (Stolon is Go, Patroni is Python), 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, and if the Postgres fails over, it ensures to disconnect me so that I'm not accidentally connected to a replica.

In general, the Stolon/Patroni approach feels like the "right way" (in absence of failover being built directly into the DB, which would be great to have in upstream postgres).

Cons:

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.

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.

Re: PostgreSQL HA cluster failure: a post-mortem

#78

Post by amigoingtodie »

antoncohen wrote: post #30
dijit wrote: post #16

Earlier quoted context omitted.

Having ran MySQL in prod for a decade and PostgreSQL in prod for half a decade I can say without doubt that your data team is telling fibs. Firstly we consider that there are multiple replication possibilities of both technologies- however I'm going to assume the defaults because that's pretty much what everyone uses except if there's an actual case for using something else. It's the exception. But by default MySQL u…

I don't think the claim of MySQL replication being better is related to statement vs. row vs. binary diff. I think it is about the tooling and community knowledge about replication, and about running MySQL in large scale production environments in general. MySQL is run more often at extremely large scale (Facebook, YouTube, Twitter, Dropbox, etc.) than Postgres. That results in very battle tested and/or featureful to…

What are anybody's real-world use case for proxysql?

I have been toying with the idea of using it for local devs to access our prod DB for reads (for accurate data) and using a local DB on their machines for writes.

Not sure how to handle UPDATEs, though.

Re: PostgreSQL HA cluster failure: a post-mortem

#79

Post by scurvy »

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…

Pacemaker/Corosync is a good tool if you know exactly how it works. However, very few people know exactly how it works. This is a very old problem for that team if you check the mailing lists. If they offered a few "blessed" configurations or even an online config generator, I'm sure that people would have nicer things to say about it.

I've used it heartbeat/pacemaker/corosync with DRBD for over a decade and I'm pretty pleased with it...now. But it took a bit of trial by fire to get it right. Luckily I never lost any data and found the issues in testing. Which gets at the heart of any failover mechanism -- it's all useless unless you test it on a regular basis (just like your backups).

Re: PostgreSQL HA cluster failure: a post-mortem

#80

Post by tensor »

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

I suppose it depends on which is more reliable, HDDs or the controller. If the likelihood of a HHD failure is much higher than a controller failure, then it still makes sense to go with RAID.
Post reply on HN