4 ms·
Ask HN: Best Tools for High Availability PostgreSQL?
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.
- yen223 2y agoI 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 2y agoRDS 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.
- Malidir 2y agoRun in Docker?
- ahoka 2y agoDo you really need HA?
- cloudnewbie 2y agoMy 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 2y agoDo 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 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 https://github.com/patroni/patroni
- cloudnewbie 2y agoMinimizing 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 2y agoThe 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.