High Availability for PostgreSQL, Batteries Not Included • Hacker News Board

Re: High Availability for PostgreSQL, Batteries Not Included

#61

Post by matrixritter » Sat, Jul 18, 2015, 8:14 PM UTC

I really wonder why people go for implementing their own HA stack when there's Pacemaker or rgmanager available?

First: There's a functional resource agent available to handle PostgreSQL. It handles single instances or multiple ones.

Second: Zhe whole cluster-thing can be very complex. You can have a LOT of fail scenarios and I wouldn't recommend to anyone to try to catch them all.

Re: High Availability for PostgreSQL, Batteries Not Included

#62

Post by chucky_z » Sun, Jul 19, 2015, 7:51 AM UTC

winsletts wrote: post #25

chucky_z wrote: post #15

Earlier quoted context omitted.

Can I ask why HAProxy seems to be a more popular choice than the very, very simple (and robust) pgbouncer?

We tested with PGPool and PgBouncer in various iterations. PGPool failed at basic failover. It worked fine while the leader remained leader. It would failover to the follower who became leader, but after the first failover, it would stall on connections. We worked through various settings and attempts at making it more stable, but in the end we were not happy with the stability. PGBouncer requires a connection to a s…

Interesting. I got around this by just recreating the users file for pgbouncer and issuing a reload every 15 minutes or so. This was only needed for one setup as the rest were just static, or the user was very low volume so I asked them to connect directly. pgbouncer handled reloads very well (on-par with nginx) so it worked for me. Thanks for the great insight with your answer.

Re: High Availability for PostgreSQL, Batteries Not Included

#63

Post by chucky_z » Sun, Jul 19, 2015, 7:51 AM UTC

winsletts wrote: post #25

chucky_z wrote: post #15

Earlier quoted context omitted.

Can I ask why HAProxy seems to be a more popular choice than the very, very simple (and robust) pgbouncer?

We tested with PGPool and PgBouncer in various iterations. PGPool failed at basic failover. It worked fine while the leader remained leader. It would failover to the follower who became leader, but after the first failover, it would stall on connections. We worked through various settings and attempts at making it more stable, but in the end we were not happy with the stability. PGBouncer requires a connection to a s…

Interesting. I got around this by just recreating the users file for pgbouncer and issuing a reload every 15 minutes or so. This was only needed for one setup as the rest were just static, or the user was very low volume so I asked them to connect directly. pgbouncer handled reloads very well (on-par with nginx) so it worked for me. Thanks for the great insight with your answer.

Re: High Availability for PostgreSQL, Batteries Not Included

#64

Post by SEJeff » Sun, Jul 26, 2015, 2:25 PM UTC

merb wrote: post #59

robszumski wrote: post #54

Earlier quoted context omitted.

You can set up a local etcd proxy to mitigate this. You'd run the proxy listening on localhost, and then have it connected to the stable etcd cluster elsewhere. The proxy can find the cluster manually or use SRV records. Autoscale the Postgres machines as much as you want after that while leaving etcd on stable machines.

That's what we basically trying to do in the future, however that's really hard to do if you want to have a running etcd cluster with 5 nodes all the time. You would need to check if one etcd died, and then either promote a proxy to a etcd master or run a new machine (the later is only possible in clouds or virtual environments)

You can do that trivially with Mesos and have it always ensure 5 instances are running. Bonus points that it will run identically on bare metal and cross cloud which means less vendor lock in for you.