Ask HN: Best Tools for High Availability PostgreSQL?

https://news.ycombinator.com/item?id=42006972

by cloudnewbie • 2 years ago

7 8 2 years ago

news.ycombinator.com

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• 2 years ago

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.

cloudnewbie3• 2 years ago

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.

Malidir• 2 years ago

Run in Docker?

ahoka• 2 years ago

Do you really need HA?

cloudnewbie3• 2 years ago

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• 2 years ago

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

cloudnewbie3• 2 years ago

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• 2 years ago

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.

Ask HN: Best Tools for High Availability PostgreSQL?

Related Stories