27 ms·

PostgreSQL HA cluster failure: a post-mortem

322 points by stevedomin 9y ago

sandGorgon 9y ago
>Fortunately, as part of some unrelated work we'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. this is the coolest part of this story. Any chance these scripts are opensource ?
- Sinjo 9y ago
  We plan to open source it as soon as we can. Tiny bit more work to do, then review from a couple of people in the team, then we make it public. :)
  - sandGorgon 9y ago
    Thanks for this !
kodablah 9y ago
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.
- Sinjo 9y ago
  To be honest we've not looked into Citus in any depth. My early impression of it (can't speak for the rest of the team) was that it was mostly aimed at sharding analytics workloads, but parts of the docs (e.g. https://docs.citusdata.com/en/v7.1/admin_guide/cluster_management.html#worker-node-failures https://docs.citusdata.com/en/v7.1/admin_guide/cluster_manag...) make it sound like it handles OLTP workloads too. Maybe I've been ignoring it for bad reasons! EDIT: Managing Postgres clusters is something that a lot of people are working on. Thought I'd mention two projects that have me excited right now: - Patroni https://github.com/zalando/patroni - Stolon https://github.com/sorintlab/stolon Stolon's client proxy approach in particular looks interesting, and reminds me of how people are using Envoy (https://github.com/envoyproxy/envoy https://github.com/envoyproxy/envoy), albeit as a TCP proxy rather than one that understands and can do fun stuff with the database's protocol. I wonder if we'll start to see more Envoy filters for different databases!
  - craigkerstiens 9y ago
    Craig from Citus here. Since we grew transactional support a couple of years ago and a number of the features we've supported since then much of our traction has come from those outgrowing single node Postgres and needing more performance. So in short we're very much focused on handling and supporting OLTP workloads. We do also support some analytics workloads, less so data warehousing, when there is a need for end user facing analytics where higher concurrency and real-time responsiveness is key.
- craigkerstiens 9y ago
  Craig from Citus here. Unfortunately, at this time Citus isn't really focused solely on solving the HA implementation for single node Postgres. Rather, we're focused on when you need to scale for performance issues. Our multi-master setup is targeting use cases that need higher throughput of 500,000+ single wrote inserts per second or say higher than 5 million writes per second when using ingestion with Postgres \copy.
- mdekkers 9y ago
  Look at HAProxy, Patroni and Zookeeper
- clarkdave 9y ago
  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.
  - mbrynard 9y ago
    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.
    - CyberDem0n 9y ago
      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 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.
      - mbrynard 9y ago
dboreham 9y ago
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.
- quicklyfrozen 9y ago
  And wary of using options that might be outside typical usage (such as -INF for the backup VIP).
  - king_phil 9y ago
    I don't see how using infinity in a pacemaker config would be outside typical usage
- Klathmon 9y ago
  I always advocate for monthly "pull the plug on the box" tests. If you don't need "high availability", then it will test your backups and restore process, and if you do need "high availability", it will ensure your failover processes are running smoothly. Not to mention it trains everyone involved what to do in an emergency since it should be second nature by the time it really happens. If you can't go "Full netflix" and unleash a chaos monkey on your servers, at least setup a maintenance period where downtime is somewhat expected, and do it then.
- rosser 9y ago
  "Your backups are only as good as their last successful restore."
  - CodeWriter23 9y ago
    https://feedbin.com/blog/2017/05/05/testing-huge-postgresql-backups-for-37-cents-a-day/ https://feedbin.com/blog/2017/05/05/testing-huge-postgresql-...
- seanwilson 9y ago
  > This is why you should be extremely wary of anything that is only run once in a a blue moon. I find this similar to when you launch a project that hasn't been used in production yet. Bugs should be expected because it hasn't been battle tested.
notyourday 9y ago
Stop pretending that there's a magic bullet called "multi-master" and "transparent promotion". Your apps are super simple. Their DB interactions are super simple. Learn how to do federations and all these problems will go away.
- vemv 9y ago
  Interesting. Recommended reads?
- rosser 9y ago
  It's curious that you decry some kinds of "magic bullet" in favor of another. How about "there are no magic bullets, full stop"?
echelon 9y ago
I'm told that MySQL replication blows Postgres out of the water by my company's data team, but they could just be biased since that is their area of expertise. I work on server code and don't really have much familiarity with the operations of running replica chains. Postgres seems like a better choice for personal projects since it has a lot of nifty features. I'm also wary of Oracle, but that'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? Serious (naive) question; not wanting to start a flame war.
- YorickPeterse 9y ago
  PostgreSQL's replication itself is perfectly fine, albeit a bit bare-bones (e.g. it's just replication plus the ability to trigger a failover). For something like cluster management and automated failovers you'll need e.g. https://repmgr.org/ https://repmgr.org/.
- dijit 9y ago
  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 uses statement based replication (in a weird binary format with log positions and stuff) and postgresql does logical replication (as in, you transmit the binary differences of what you'll be doing to the replica's database files directly and the replica just follows along) Both of these approaches have trade-offs depending on what you want. Statement based replication is great if you want to have _different_ datasets on each side, You can transform the data or remove huge chunks of it on a slave and use it for a dedicated purpose. However that applies the other way, you can never really be 100% sure that your replica looks exactly like your master. this bit me a few times with MySQL when I assumed that because the replica was 'up to date' with the master and it was set to read only, that the data had integrity- it absolutely did not.
  - 9y ago
testplzignore 9y ago
Good write-up. I'm curious about two more things: 1. What caused the crash on the synchronous replica? Was it just a coincidence and completely unrelated to the primary failure? 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?
- Sinjo 9y ago
  1. Unfortunately the logs don't give any detail there. Most likely something arrived down the replication connection that the process couldn't handle, and it crashed. 2. Our understanding now is that INF is the strongest preference, whereas -INF is a veto. It would be very cool to have this confirmed 100% by someone who works on Pacemaker!
qaq 9y ago
There are specialized tools like Patroni (https://github.com/zalando/patroni https://github.com/zalando/patroni)
- emXdem 9y ago
  I like Stolon: https://github.com/sorintlab/stolon https://github.com/sorintlab/stolon
tomc1985 9y ago
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 :/
- rosser 9y ago
  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.
  - nh2 9y ago
    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.
    - rosser 9y ago
      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.
  - majidazimi 9y ago
    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.
jskrablin 9y ago
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.
- barkingcat 9y ago
  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.
  - jskrablin 9y ago
    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.
- hinkley 9y ago
  Unfortunately appropriate project name? (A maulfunctioning pacemaker can kill you)
foobarbazetc 9y ago
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’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/takeover script then it is to rely on automation. pgbouncer makes this easy enough. That said, there was a lot of bad luck involved in this incident.
- morrbo 9y ago
  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!
  - snuxoll 9y ago
    PgBouncer makes it easy to do a controlled failover without having to deal with floating IP's on your postgresql cluster.
  - fake-name 9y ago
    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.
    - scurvy 9y ago
      How much effort is required to reconfigure pgbouncer across hundreds of app containers vs moving a single IP? (not using pacemaker/corosync)
Erwin 9y ago
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? So far I'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.
- pjungwir 9y ago
  Here are a couple gotchas I've seen on RDS: - If you are running a MultiAZ instance, it is supposed to fail over automatically, but if the problem is in the networking, then you can still effectively lose service. One way around that is to run a read replica in another AZ, and use a Route53 entry with a health check to send traffic to the read replica if the primary isn't reachable. You'll still need to promote the read replica to a master though. - If you restore from a snapshot, the new EBS volume only pulls blocks of data from S3 as they are requested. So these reads are a lot slower than normal. If you have a large database you could have degraded performance for days. Here is some more info about this: http://docs.aws.amazon.com/AWSEC2/latest/UserGuide/ebs-restoring-volume.html http://docs.aws.amazon.com/AWSEC2/latest/UserGuide/ebs-resto...
linker3000 9y ago
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 we have put to both companies, but if anyone else can comment from experience that would be fantastic: * Is the product a fork of PostgreSQL or a wrapper round the current version? * Will the DB engine keep in lock-step with new PostgreSQL releases or might they diverge? * 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? * 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?
- lattepiu 9y ago
  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.
devit 9y ago
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.
- Sinjo 9y ago
  Pretty much number 1 on my Postgres wishlist.
- tracker1 9y ago
  I cannot upvote this enough... it's the single most glaring feature missing from PostgreSQL at this point. I know it's where all the support companies are making their money, but it really keeps the majority of SMBs from adopting postgres. As much as I hate mySQL.
ahoka 9y ago
"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. :-)
- luke0016 9y ago
  > 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.
- tensor 9y ago
  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.
- majidazimi 9y ago
  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.
  - fb03 9y ago
    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 :-)
  - king_phil 9y ago
    And the SATA/SAS controller of your Mainboard is invincible? Or the Mainboard itself? Or the CPU? What's your backup solution if they are faulty?
    - 9y ago
emXdem 9y ago
Stolon with etcd or consul is a far superior solution for HA postgres.
derekmhewitt 9y ago
I'm seeing the term "HA" being used a lot in the comments here, what does it mean?
- inimino 9y ago
  high availability
cavisne 9y ago
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. 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.
ncmncm 9y ago
I am astonished that, in the two years, you had not already handled 100+ scheduled failovers. If your HA is good, customers don't notice, and if not, you find out when there are fewer of them (and in daytime!), and fix it. 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. Not doing frequent production failure drills is just irresponsible.

322 points by stevedomin 9y ago

>Fortunately, as part of some unrelated work we'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. this is the coolest part of this story. Any chance these scripts are opensource ?

Sinjo 9y ago
We plan to open source it as soon as we can. Tiny bit more work to do, then review from a couple of people in the team, then we make it public. :)
- sandGorgon 9y ago
  Thanks for this !

kodablah 9y ago

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.

Sinjo 9y ago
To be honest we've not looked into Citus in any depth. My early impression of it (can't speak for the rest of the team) was that it was mostly aimed at sharding analytics workloads, but parts of the docs (e.g. https://docs.citusdata.com/en/v7.1/admin_guide/cluster_management.html#worker-node-failures https://docs.citusdata.com/en/v7.1/admin_guide/cluster_manag...) make it sound like it handles OLTP workloads too. Maybe I've been ignoring it for bad reasons! EDIT: Managing Postgres clusters is something that a lot of people are working on. Thought I'd mention two projects that have me excited right now: - Patroni https://github.com/zalando/patroni - Stolon https://github.com/sorintlab/stolon Stolon's client proxy approach in particular looks interesting, and reminds me of how people are using Envoy (https://github.com/envoyproxy/envoy https://github.com/envoyproxy/envoy), albeit as a TCP proxy rather than one that understands and can do fun stuff with the database's protocol. I wonder if we'll start to see more Envoy filters for different databases!
- craigkerstiens 9y ago
  Craig from Citus here. Since we grew transactional support a couple of years ago and a number of the features we've supported since then much of our traction has come from those outgrowing single node Postgres and needing more performance. So in short we're very much focused on handling and supporting OLTP workloads. We do also support some analytics workloads, less so data warehousing, when there is a need for end user facing analytics where higher concurrency and real-time responsiveness is key.
craigkerstiens 9y ago
Craig from Citus here. Unfortunately, at this time Citus isn't really focused solely on solving the HA implementation for single node Postgres. Rather, we're focused on when you need to scale for performance issues. Our multi-master setup is targeting use cases that need higher throughput of 500,000+ single wrote inserts per second or say higher than 5 million writes per second when using ingestion with Postgres \copy.
mdekkers 9y ago
Look at HAProxy, Patroni and Zookeeper
clarkdave 9y ago
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.
- mbrynard 9y ago
  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.
  - CyberDem0n 9y ago
    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 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.
    - mbrynard 9y ago

dboreham 9y ago

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.

quicklyfrozen 9y ago
And wary of using options that might be outside typical usage (such as -INF for the backup VIP).
- king_phil 9y ago
  I don't see how using infinity in a pacemaker config would be outside typical usage
Klathmon 9y ago
I always advocate for monthly "pull the plug on the box" tests. If you don't need "high availability", then it will test your backups and restore process, and if you do need "high availability", it will ensure your failover processes are running smoothly. Not to mention it trains everyone involved what to do in an emergency since it should be second nature by the time it really happens. If you can't go "Full netflix" and unleash a chaos monkey on your servers, at least setup a maintenance period where downtime is somewhat expected, and do it then.
rosser 9y ago
"Your backups are only as good as their last successful restore."
- CodeWriter23 9y ago
  https://feedbin.com/blog/2017/05/05/testing-huge-postgresql-backups-for-37-cents-a-day/ https://feedbin.com/blog/2017/05/05/testing-huge-postgresql-...
seanwilson 9y ago
> This is why you should be extremely wary of anything that is only run once in a a blue moon. I find this similar to when you launch a project that hasn't been used in production yet. Bugs should be expected because it hasn't been battle tested.

notyourday 9y ago

Stop pretending that there's a magic bullet called "multi-master" and "transparent promotion". Your apps are super simple. Their DB interactions are super simple. Learn how to do federations and all these problems will go away.

vemv 9y ago
Interesting. Recommended reads?
rosser 9y ago
It's curious that you decry some kinds of "magic bullet" in favor of another. How about "there are no magic bullets, full stop"?

echelon 9y ago

I'm told that MySQL replication blows Postgres out of the water by my company's data team, but they could just be biased since that is their area of expertise. I work on server code and don't really have much familiarity with the operations of running replica chains. Postgres seems like a better choice for personal projects since it has a lot of nifty features. I'm also wary of Oracle, but that'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? Serious (naive) question; not wanting to start a flame war.

YorickPeterse 9y ago
PostgreSQL's replication itself is perfectly fine, albeit a bit bare-bones (e.g. it's just replication plus the ability to trigger a failover). For something like cluster management and automated failovers you'll need e.g. https://repmgr.org/ https://repmgr.org/.
dijit 9y ago
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 uses statement based replication (in a weird binary format with log positions and stuff) and postgresql does logical replication (as in, you transmit the binary differences of what you'll be doing to the replica's database files directly and the replica just follows along) Both of these approaches have trade-offs depending on what you want. Statement based replication is great if you want to have _different_ datasets on each side, You can transform the data or remove huge chunks of it on a slave and use it for a dedicated purpose. However that applies the other way, you can never really be 100% sure that your replica looks exactly like your master. this bit me a few times with MySQL when I assumed that because the replica was 'up to date' with the master and it was set to read only, that the data had integrity- it absolutely did not.
- 9y ago

testplzignore 9y ago

Good write-up. I'm curious about two more things: 1. What caused the crash on the synchronous replica? Was it just a coincidence and completely unrelated to the primary failure? 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?

Sinjo 9y ago
1. Unfortunately the logs don't give any detail there. Most likely something arrived down the replication connection that the process couldn't handle, and it crashed. 2. Our understanding now is that INF is the strongest preference, whereas -INF is a veto. It would be very cool to have this confirmed 100% by someone who works on Pacemaker!

qaq 9y ago

There are specialized tools like Patroni (https://github.com/zalando/patroni https://github.com/zalando/patroni)

emXdem 9y ago
I like Stolon: https://github.com/sorintlab/stolon https://github.com/sorintlab/stolon

tomc1985 9y ago

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 :/

rosser 9y ago
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.
- nh2 9y ago
  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.
  - rosser 9y ago
    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.
- majidazimi 9y ago
  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.

jskrablin 9y ago

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.

barkingcat 9y ago
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.
- jskrablin 9y ago
  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.
hinkley 9y ago
Unfortunately appropriate project name? (A maulfunctioning pacemaker can kill you)

foobarbazetc 9y ago

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’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/takeover script then it is to rely on automation. pgbouncer makes this easy enough. That said, there was a lot of bad luck involved in this incident.

morrbo 9y ago
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!
- snuxoll 9y ago
  PgBouncer makes it easy to do a controlled failover without having to deal with floating IP's on your postgresql cluster.
- fake-name 9y ago
  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.
  - scurvy 9y ago
    How much effort is required to reconfigure pgbouncer across hundreds of app containers vs moving a single IP? (not using pacemaker/corosync)

Erwin 9y ago

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? So far I'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.

pjungwir 9y ago
Here are a couple gotchas I've seen on RDS: - If you are running a MultiAZ instance, it is supposed to fail over automatically, but if the problem is in the networking, then you can still effectively lose service. One way around that is to run a read replica in another AZ, and use a Route53 entry with a health check to send traffic to the read replica if the primary isn't reachable. You'll still need to promote the read replica to a master though. - If you restore from a snapshot, the new EBS volume only pulls blocks of data from S3 as they are requested. So these reads are a lot slower than normal. If you have a large database you could have degraded performance for days. Here is some more info about this: http://docs.aws.amazon.com/AWSEC2/latest/UserGuide/ebs-restoring-volume.html http://docs.aws.amazon.com/AWSEC2/latest/UserGuide/ebs-resto...

linker3000 9y ago

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 we have put to both companies, but if anyone else can comment from experience that would be fantastic: * Is the product a fork of PostgreSQL or a wrapper round the current version? * Will the DB engine keep in lock-step with new PostgreSQL releases or might they diverge? * 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? * 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?

lattepiu 9y ago
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.

devit 9y ago

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.

Sinjo 9y ago
Pretty much number 1 on my Postgres wishlist.
tracker1 9y ago
I cannot upvote this enough... it's the single most glaring feature missing from PostgreSQL at this point. I know it's where all the support companies are making their money, but it really keeps the majority of SMBs from adopting postgres. As much as I hate mySQL.

ahoka 9y ago

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

luke0016 9y ago
> 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.
tensor 9y ago
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.
majidazimi 9y ago
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.
- fb03 9y ago
  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 :-)
- king_phil 9y ago
  And the SATA/SAS controller of your Mainboard is invincible? Or the Mainboard itself? Or the CPU? What's your backup solution if they are faulty?
  - 9y ago

emXdem 9y ago

Stolon with etcd or consul is a far superior solution for HA postgres.

derekmhewitt 9y ago

I'm seeing the term "HA" being used a lot in the comments here, what does it mean?

inimino 9y ago
high availability

cavisne 9y ago

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

ncmncm 9y ago

I am astonished that, in the two years, you had not already handled 100+ scheduled failovers. If your HA is good, customers don't notice, and if not, you find out when there are fewer of them (and in daytime!), and fix it. 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. Not doing frequent production failure drills is just irresponsible.