4 ms·

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'

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

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