Live data from Hacker News

Patroni: A Template for PostgreSQL HA with ZooKeeper, Etcd, or Consul

github.com

11–20 of 50 posts

Re: Patroni: A Template for PostgreSQL HA with ZooKeeper, Etcd, or Consul

#11
I wonder how much things like this are needed going forward. From my (rudimentary) knowledge about Kubernetes and stateful sets, I think that Kubernetes is able to solve a lot of the issues surrounding failover, recovery of the old master, and partitioning; providing that we use Kubernetes in combination with networked storage that guarantees reliability.

It appears that the way to setup PostgreSQL (or any replicated database) with Kubernetes is to treat index 0 in the stateful set as the master, and everything else as slaves. Each pod is to be connected to some networked storage. If the master goes down, then Kubernetes detects that through the health check, and simply reschedules the master on another node. The storage volume that the old master was using, is simply reattached to this other node. The master going down does not imply storage failure. Storage reliability then becomes a separate problem which is solved in another system (e.g. the RAID system or whatever).

In this kind of setup, there is no failover support in the database itself. From the point of view of the database itself, it looks as if the underlying hardware/OS "automatically" recovered from failure. This way we don't have to mess with promoting slaves and stuff.

Kubernetes already assigns static IPs to services, so pgpool and similar tools -- in so far they are only used to provide a stable network address for PostgreSQL -- become redundant. Network partitioning is "solved" by treating the Kubernetes state as the single authoritative description of the network state.

What do people think about this? Obviously this setup won't work if you don't have networked storage that can be reattached to another node, but I'm thinking that maybe reattachable networked storage should be the future.

Re: Patroni: A Template for PostgreSQL HA with ZooKeeper, Etcd, or Consul

#12

I wonder how much things like this are needed going forward. From my (rudimentary) knowledge about Kubernetes and stateful sets, I think that Kubernetes is able to solve a lot of the issues surrounding failover, recovery of the old master, and partitioning; providing that we use Kubernetes in combination with networked storage that guarantees reliability. It appears that the way to setup PostgreSQL (or any replicated…

> providing that we use Kubernetes in combination with networked storage that guarantees reliability

What kind of networked storage do people like with kubernetes? I've recently set up a small cluster not in any cloud, and persistent cross-node storage is a concern. There's quite a few options such as glusterfs, but I'd be curious to know if anyone here knows about the tradeoffs.

Re: Patroni: A Template for PostgreSQL HA with ZooKeeper, Etcd, or Consul

#13

Earlier quoted context omitted.

As per my understanding, Postgres-XL is not comparable to Patroni. Postgres-XL is comparable to PostgreSQL. Postgres-XL shards the data across multiple data nodes. Where as Patroni uses etcd, consul or zookeeper to provide HA for any Postgres cluster using replication (Data is written only to a single instance and replicated further). I think you meant Stolon. Stolon is quite comparable to Patroni and has more featur…

> Stolon is quite comparable to Patroni and has more features than Patron. Let me as a question, what feature Stolon has and Patroni doesn't? Can you give an example? From my side I can provide list of features available in Patroni, but not in Stolon. For example: * there is no way to do a controlled failover (switchover) in Stolon. * in Patroni it's possible to exclude some nodes from a leader race. * Patroni suppor…

Thanks for this. As I mentioned in previous comment, I couldn't find much about Stolon. One of things I likee in stolon is the proxy, it enforce connections to the right PostgreSQL master and forcibly closes connections to unelected masters. Which is not present in Patroni, but I guess it should be doable using consul's service discovery and dynamic DNS. (I am not sure if this can be done using etcd and zookeper).

Re: Patroni: A Template for PostgreSQL HA with ZooKeeper, Etcd, or Consul

#14

Earlier quoted context omitted.

> Stolon is quite comparable to Patroni and has more features than Patron. Let me as a question, what feature Stolon has and Patroni doesn't? Can you give an example? From my side I can provide list of features available in Patroni, but not in Stolon. For example: * there is no way to do a controlled failover (switchover) in Stolon. * in Patroni it's possible to exclude some nodes from a leader race. * Patroni suppor…

Thanks for this. As I mentioned in previous comment, I couldn't find much about Stolon. One of things I likee in stolon is the proxy, it enforce connections to the right PostgreSQL master and forcibly closes connections to unelected masters . Which is not present in Patroni, but I guess it should be doable using consul's service discovery and dynamic DNS. (I am not sure if this can be done using etcd and zookeper).

Doesn't HAProxy provide such functionality? Patroni has REST API which can be used by HAProxy for a health-check.

patroni:8008/master will return http status code 200 only if the node running as elected master

patroni:8008/replica will return http status code 200 if node running as replica.

And final missing bit is a automation of generation of haproxy.cfg - it could be done with confd: https://github.com/kelseyhightower/confd

And here is an example of template file: https://github.com/zalando/patroni/blob/master/extras/confd/...

Re: Patroni: A Template for PostgreSQL HA with ZooKeeper, Etcd, or Consul

#15

I wonder how much things like this are needed going forward. From my (rudimentary) knowledge about Kubernetes and stateful sets, I think that Kubernetes is able to solve a lot of the issues surrounding failover, recovery of the old master, and partitioning; providing that we use Kubernetes in combination with networked storage that guarantees reliability. It appears that the way to setup PostgreSQL (or any replicated…

I think the largest disadvantage is, that you won't get real high availability. Promoting a slave to a master is done in a second. Restarting the master can take some time (especially when it crashed previously). There is also the problem of losing the storage volume of your master node (ebs block storage do neither have 100% availability, nor 100% durability). In this case you can't recover your master.

Re: Patroni: A Template for PostgreSQL HA with ZooKeeper, Etcd, or Consul

#16

Earlier quoted context omitted.

Thanks for this. As I mentioned in previous comment, I couldn't find much about Stolon. One of things I likee in stolon is the proxy, it enforce connections to the right PostgreSQL master and forcibly closes connections to unelected masters . Which is not present in Patroni, but I guess it should be doable using consul's service discovery and dynamic DNS. (I am not sure if this can be done using etcd and zookeper).

Doesn't HAProxy provide such functionality? Patroni has REST API which can be used by HAProxy for a health-check. patroni:8008/master will return http status code 200 only if the node running as elected master patroni:8008/replica will return http status code 200 if node running as replica. And final missing bit is a automation of generation of haproxy.cfg - it could be done with confd: https://github.com/kelseyhight…

I am not sure, will HAProxy forcibly close connections to old master in case of a failover?

Re: Patroni: A Template for PostgreSQL HA with ZooKeeper, Etcd, or Consul

#17

Earlier quoted context omitted.

Thanks for this. As I mentioned in previous comment, I couldn't find much about Stolon. One of things I likee in stolon is the proxy, it enforce connections to the right PostgreSQL master and forcibly closes connections to unelected masters . Which is not present in Patroni, but I guess it should be doable using consul's service discovery and dynamic DNS. (I am not sure if this can be done using etcd and zookeper).

Doesn't HAProxy provide such functionality? Patroni has REST API which can be used by HAProxy for a health-check. patroni:8008/master will return http status code 200 only if the node running as elected master patroni:8008/replica will return http status code 200 if node running as replica. And final missing bit is a automation of generation of haproxy.cfg - it could be done with confd: https://github.com/kelseyhight…

[deleted]

Re: Patroni: A Template for PostgreSQL HA with ZooKeeper, Etcd, or Consul

#18

Earlier quoted context omitted.

Doesn't HAProxy provide such functionality? Patroni has REST API which can be used by HAProxy for a health-check. patroni:8008/master will return http status code 200 only if the node running as elected master patroni:8008/replica will return http status code 200 if node running as replica. And final missing bit is a automation of generation of haproxy.cfg - it could be done with confd: https://github.com/kelseyhight…

I am not sure, will HAProxy forcibly close connections to old master in case of a failover?

on-marked-down shutdown-sessions

should do the trick

Re: Patroni: A Template for PostgreSQL HA with ZooKeeper, Etcd, or Consul

#20

I wonder how much things like this are needed going forward. From my (rudimentary) knowledge about Kubernetes and stateful sets, I think that Kubernetes is able to solve a lot of the issues surrounding failover, recovery of the old master, and partitioning; providing that we use Kubernetes in combination with networked storage that guarantees reliability. It appears that the way to setup PostgreSQL (or any replicated…

Relaunching crashed processes is the visible part of HA and yes, Kubernetes and comparable systems do this well.

I don't have direct experience with building HA data systems, but some other teams at my employers (Pivotal) do. We ship some of these as BOSH releases intended to allow service injection into Cloud Foundry (and pretty soon OpenShift and Kubernetes too[0]). For the purposes of what you describe, BOSH and k8s are comparable.

Given that we have multiple teams that work continuously on building fully-packaged, fully automated HA data systems, I suspect that this stuff is harder than it looks.

[0] https://www.openservicebrokerapi.org/

Post reply on HN