PostgreSQL HA cluster failure: a post-mortem

#1

Post by stevedomin » Wed, Dec 06, 2017, 3:47 PM UTC

PostgreSQL HA cluster failure: a post-mortem
gocardless.com

Re: PostgreSQL HA cluster failure: a post-mortem

#2

Post by sandGorgon » Wed, Dec 06, 2017, 4:32 PM UTC

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

Re: PostgreSQL HA cluster failure: a post-mortem

#3

Post by kodablah » Wed, Dec 06, 2017, 4:32 PM UTC

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.

Re: PostgreSQL HA cluster failure: a post-mortem

#4

Post by Sinjo » Wed, Dec 06, 2017, 4:44 PM UTC

sandGorgon wrote: post #2

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

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

Re: PostgreSQL HA cluster failure: a post-mortem

#5

Post by sandGorgon » Wed, Dec 06, 2017, 4:52 PM UTC

Sinjo wrote: post #4

sandGorgon wrote: post #2

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

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

Thanks for this !

Re: PostgreSQL HA cluster failure: a post-mortem

#6

Post by Sinjo » Wed, Dec 06, 2017, 4:53 PM UTC

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.

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_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), 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!

Re: PostgreSQL HA cluster failure: a post-mortem

#7

Post by craigkerstiens » Wed, Dec 06, 2017, 5:02 PM UTC

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.

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.

Re: PostgreSQL HA cluster failure: a post-mortem

#8

Post by mdekkers » Wed, Dec 06, 2017, 5:07 PM UTC

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.

Look at HAProxy, Patroni and Zookeeper

Re: PostgreSQL HA cluster failure: a post-mortem

#9

Post by craigkerstiens » Wed, Dec 06, 2017, 5:09 PM UTC

Sinjo wrote: post #6

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.

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_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 tha…

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.

Re: PostgreSQL HA cluster failure: a post-mortem

#10

Post by dboreham » Wed, Dec 06, 2017, 5:15 PM UTC

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.