TE
科技回声
首页24小时热榜最新最佳问答展示工作
GitHubTwitter
首页

科技回声

基于 Next.js 构建的科技新闻平台,提供全球科技新闻和讨论内容。

GitHubTwitter

首页

首页最新最佳问答展示工作

资源链接

HackerNews API原版 HackerNewsNext.js

© 2025 科技回声. 版权所有。

PostgreSQL HA cluster failure: a post-mortem

322 点作者 stevedomin超过 7 年前

18 条评论

foobarbazetc超过 7 年前
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.<p>I’ve never in that time seen a pacemaker&#x2F;corosync&#x2F;etc&#x2F;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.<p>The lesson I’ve taken from this is it’s better to have 15-20 minutes of downtime in the unlikely event a primary goes down and run a manual failover&#x2F;takeover script then it is to rely on automation. pgbouncer makes this easy enough.<p>That said, there was a lot of bad luck involved in this incident.
评论 #15866726 未加载
评论 #15866688 未加载
评论 #15866254 未加载
评论 #15870390 未加载
评论 #15864775 未加载
评论 #15864206 未加载
评论 #15864450 未加载
sandGorgon超过 7 年前
&gt;<i>Fortunately, as part of some unrelated work we&#x27;d done recently, we had a version of the cluster that we could run inside Docker containers. We used it to help us build a script that mimicked the failures we saw in production. Being able to rapidly turn clusters up and down let us iterate on that script quickly, until we found a combination of events that broke the cluster in just the right way.</i><p>this is the coolest part of this story. Any chance these scripts are opensource ?
评论 #15862513 未加载
linker3000超过 7 年前
Since I am investigating HA with PostgreSQL right now and have bitter experience of Pacemaker &#x27;HA&#x27; instances that have been anything but, I am looking at Amazon Aurora and Microsoft&#x27;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).<p>Our dev team also came up with some pertinent questions, which we have put to both companies, but if anyone else can comment from experience that would be fantastic:<p>* Is the product a fork of PostgreSQL or a wrapper round the current version?<p>* Will the DB engine keep in lock-step with new PostgreSQL releases or might they diverge?<p>* If the DB engine keeps in lock-step, what’s the period between a new version of PostgreSQL being released before its incorporated in the live product?<p>* When new versions of Amazon Aurora&#x2F;Azure DB for PostgreSQL are released will our live instance get automatically updated or will we be able to choose a version?
评论 #15865969 未加载
devit超过 7 年前
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&#x2F;or configuring them incorrectly.<p>They should either provide one or &quot;bless&quot; an external solution as the official one (after making sure it works correctly).<p>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.
评论 #15866076 未加载
评论 #15875502 未加载
tomc1985超过 7 年前
Makes me sad that running your own instances is now an &quot;elephant in the room.&quot; No pride in old-school do-it-yourself nerditry these days :&#x2F;
评论 #15863450 未加载
评论 #15863846 未加载
评论 #15864533 未加载
dboreham超过 7 年前
This is why you should be extremely wary of anything that is only run once in a a blue moon. And very wary of such things that when run, are being run to save your bacon.
评论 #15863480 未加载
评论 #15864274 未加载
评论 #15863651 未加载
评论 #15862889 未加载
ahoka超过 7 年前
&quot;The RAID controller logged the simultaneous loss of 3 disks from the array. All subsequent read and write operations against it failed.&quot;<p>People seem to forget that adding a RAID controller creates a single point of failure instead of removing one. :-)
评论 #15865054 未加载
评论 #15866704 未加载
评论 #15869073 未加载
jskrablin超过 7 年前
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&#x27;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&#x27;re up and back in the business figure out how to rebuild the cluster.
评论 #15864321 未加载
评论 #15864832 未加载
testplzignore超过 7 年前
Good write-up. I&#x27;m curious about two more things:<p>1. What caused the crash on the synchronous replica? Was it just a coincidence and completely unrelated to the primary failure?<p>2. Given the three conditions necessary for the cluster to break, was the behavior of the Pacemaker software expected? I.e., was this a gotcha that should be in the Pacemaker documentation, or a bug?
评论 #15863640 未加载
cavisne超过 7 年前
The end of this post mortem was a bit handwavy TBH. I feel like they didnt dig deep enough, and the problem was the backup VIP, not the two processes crashing at once and the backup VIP.<p>I think by still allowing the backup VIP to run on the sync replica the same mistake is being repeated, there will always be the possibility of a situation where the VIP cannot be moved when promotion is required. That replica should be doing nothing but sitting there waiting to save the day, and if they want the backup VIP to be highly available they should provision 2 async replicas.
kodablah超过 7 年前
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&#x27;s multi master impl (I don&#x27;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&#x27;s in my experience.
评论 #15862681 未加载
评论 #15862584 未加载
评论 #15863790 未加载
评论 #15862741 未加载
echelon超过 7 年前
I&#x27;m told that MySQL replication blows Postgres out of the water by my company&#x27;s data team, but they could just be biased since that is their area of expertise. I work on server code and don&#x27;t really have much familiarity with the operations of running replica chains.<p>Postgres seems like a better choice for personal projects since it has a lot of nifty features. I&#x27;m also wary of Oracle, but that&#x27;s my own attitude talking. For a startup eventually wanting to scale, would the better choice be to use MySQL out of the gates? Am I being mislead about Postgres clusters and availability?<p>Serious (naive) question; not wanting to start a flame war.
评论 #15863285 未加载
评论 #15863067 未加载
评论 #15865217 未加载
评论 #15864374 未加载
评论 #15863092 未加载
qaq超过 7 年前
There are specialized tools like Patroni (<a href="https:&#x2F;&#x2F;github.com&#x2F;zalando&#x2F;patroni" rel="nofollow">https:&#x2F;&#x2F;github.com&#x2F;zalando&#x2F;patroni</a>)
评论 #15865194 未加载
Erwin超过 7 年前
If you are running HA in AWS RDS, how would you compare your experience with the above? What are the types of RDS failures modes that you have experienced?<p>So far I&#x27;ve discovered that TCP keepalives are quite important, otherwise your queries may hang forever after failover (or at least for the default timeout which is like 30 minutes). The connection does not get broken otherwise by the failover.
评论 #15865081 未加载
ncmncm超过 7 年前
I am astonished that, in the two years, you had not already handled 100+ scheduled failovers. If your HA is good, customers don&#x27;t notice, and if not, you find out when there are fewer of them (and in daytime!), and fix it.<p>Probably by now Pacemaker would have been abandoned. A hundred drills would have been enough to flush out these behaviors. If you are afraid to run drills on production equipment, you should be running them on a full-scale production testbed, ideally with mirrored production traffic. With a production-scale testbed, two years is enough to run thousands of risk-free failovers.<p>Not doing frequent production failure drills is just irresponsible.
derekmhewitt超过 7 年前
I&#x27;m seeing the term &quot;HA&quot; being used a lot in the comments here, what does it mean?
评论 #15867427 未加载
emXdem超过 7 年前
Stolon with etcd or consul is a far superior solution for HA postgres.
notyourday超过 7 年前
Stop pretending that there&#x27;s a magic bullet called &quot;multi-master&quot; and &quot;transparent promotion&quot;. Your apps are super simple. Their DB interactions are super simple. Learn how to do federations and all these problems will go away.
评论 #15863610 未加载
评论 #15862984 未加载