Hacker News Reader

Comment by kodablah | Hacker News Reader

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.

View on HN

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.

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!

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.

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.

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

We are planning to merge this PR today or tomorrow, and it will make Patroni+PostgreSQL first class citizen on Kubernetes.

Thanks for the detailed reply. I see your PR has been merged. The StatefulSet support does look great. Are there any advantages over the CrunchyDB StatefulSet setup here: https://github.com/CrunchyData/crunchy-containers/tree/maste...?

Look at HAProxy, Patroni and Zookeeper

Reply on news.ycombinator.com