HN Explorer

Ask HN: Best Tools for High Availability PostgreSQL?

7 points by cloudnewbie ↗ HN
PostgreSQL has lots of different tools available for replication, though in various states of support and maintenance. What are your current recommendations for a small-scale Postgres cluster (e.g. 3 node on modest VMs)?

My use case is a relatively standard web app with database backend. One write master is fine, but it should have automatic failover if the primary is unavailable. As a solo dev, I'd like to avoid the overhead that comes with tools like Kubernetes, if there are suitable alternatives.

8 comments

[ 4.4 ms ] story [ 34.7 ms ] thread
yen223
I used Amazon RDS in my past job, which supports automatic failover and read replication. It was fine. Fairly low-maintenance on my part, which is always good.
cloudnewbie
RDS is on my list. It seems like the easiest, most hands-off solution, but has a price premium compared to running my own pg on EC2 or similar.
ahoka
Do you really need HA?
cloudnewbie
My use will be for a small SaaS. Having automated failover for the database would make me feel much more at ease about uptime and reliability for clients.
moehm
Do you need HA, or do you want to minimize downtime? At work we have something like an "error budget", were we accept downtime but try to minimize it. As such we have two nodes with one floating ip and a shared disk. The switch over takes as long as stopping the database on the first node, starting up the database on the second one and switching over the ip. Stuff like kernel updates takes us <1 minute of scheduled downtime, which is good enough for us.

Here is a good talk which resonated with me from the last pgconf: https://www.youtube.com/watch?v=_rYP6xVymtI

If you want more, I think Patroni (by Zalando) is the current best option for you. Patroni handles automatic leader election if the master goes down, and it is open source. Read here more:

https://github.com/patroni/patroni

cloudnewbie
Minimizing unintended downtime is the primary intent. Having a second server automatically take over in less than a minute or two would be good enough for me. I'll look in to shared disks and Patroni. Thank you for the pgconf video too.
hitpointdrew
The best stack I found for this is:

Keepalived -> pgbouncer -> postgresql

Then repmgr for managing replication and barman for backups.

The stack is nice because keepalived gives you a virtual ip that you point your apps to, then you can promote a standby to primary (or have one auto promote on a failure) and the VIP will flip to the new primary. All in all you get like 5-10 seconds of “down” time when it flips (depending on how aggressive or conservative you want to be with the rise and fall settings).

Edit: caveat you won’t get keepalived to work if you are using AWS and spread your Postgres servers across AZ’s, they would have to be in the same AZ.

Edit 2: You can simplify the setup if you don’t need connection pooling, in that case skip pgbouncer.