Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

High Availability for PostgreSQL, Batteries Not Included

compose.io

Post reply on HN
Print view View original

11–20 of 64 posts

Re: High Availability for PostgreSQL, Batteries Not Included

#11

Post by pjungwir »

Since most of the comments are critical, I'll say: thank you for the awesome writeup! I agree this is more complex than HA PG setups I've done in the past, but I'm thrilled to have another perspective. Also doing a thorough writeup like this takes time, and a lot of people would rather jump back into building the next thing. It's a great contribution!

I agree with pilif that you almost always want to failover the db manually.

I agree with teraflop that just because etcd gives strong guarantees, that doesn't mean your application logic built on top of etcd primitives shares them. So you have to be careful about your reasoning there.

I'm curious if you're doing anything to mitigate haproxy being a single point of failure?

One thing I've had to fix in other people's HA PG setups is ease of getting back to HA after a failover. You lose the master and promote the slave, and now you've just got a master. Ideally it should be easy to just launch another db instance and everyone keeps going. I think this setup achieves that, and that's great!

Re: High Availability for PostgreSQL, Batteries Not Included

#12

Post by nickpsecurity »

pilif wrote: post #2

Personally, I would try to go for a simpler solution. In case of a failover event which is already complicated in itself and happening at a point in time where stuff is already going wrong (there would be no failover otherwise), do you really want to have all this additional infrastructure with etcd and haproxy as a dependency? If you can live with a few minutes of downtime, I would recommend to trigger your failover…

VMS clusters on VAXen were doing fail-overs perfectly in the 80's. All kinds of products and software (even FOSS) do it today. You're telling me that, in 2015, you are doing manual failovers despite tons of free tools to automate it reliably?

Better to do an assessment of each thing that can fail, how to isolate/detect it, how to recover from it, how to implement that with available tools, and implement it. Test it in a number of situations on same hardware, network, and apps you'll use in production. Once it's solid, put them into production. Then, never worry about that stuff again past monitoring and maintenance.

Btw, Netflix employs Monkeys to do this. Open-sources their tools with blog writeups on their use, too. I'm sure you Humans will be able to handle it. ;)

Re: High Availability for PostgreSQL, Batteries Not Included

#13

Post by chucky_z »

nitramnitram wrote: post #8
pilif wrote: post #2

Personally, I would try to go for a simpler solution. In case of a failover event which is already complicated in itself and happening at a point in time where stuff is already going wrong (there would be no failover otherwise), do you really want to have all this additional infrastructure with etcd and haproxy as a dependency? If you can live with a few minutes of downtime, I would recommend to trigger your failover…

If you are running in Microsoft Azure you need two VM instances to get any form of availability SLAs. Microsoft can reboot/migrate single instances whenever they feel like it. With manual failover you would only have a few minute downtime if someone is there to trigger it. That honestly sounds like a crappy solution 2015..

What? In the case of Microsoft rebooting/migrating an instance causing a failure, Keepalived will automatically failover.

The manual failover is in case of something going horribly wrong (outside of hardware failure), in which case a human steps in, looks at the situation, determines the best solution... and if it's failover, they initiate the failover.

I've personally used this procedure in the past and it worked 100% of the time there was a failure in a production environment. The tricky part is then notifying the hell out of everyone who needs to be notified that something really bad has happened, a failover occurred, everything is OK, but it needs some attention ASAP.

In PGSQL world, there are even a handful of tools to help you turn the old (failed) master into a slave, and correctly escalate the old (promoted) slave into a master; all in a single command on each side (which can be kicked off through keepalived).

Re: High Availability for PostgreSQL, Batteries Not Included

#15

Post by chucky_z »

imperialWicket wrote: post #6

This seems robust, but feels like more moving parts than are necessary. I feel like HAProxy with PostgreSQL + Bucardo (multi-master + at least one slave) would achieve this, and net you fewer moving parts. Under what circumstances does this fail where the etcd-dependent solution succeeds?

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

Re: High Availability for PostgreSQL, Batteries Not Included

#16

Post by merb »

chucky_z wrote: post #13
nitramnitram wrote: post #8

Earlier quoted context omitted.

If you are running in Microsoft Azure you need two VM instances to get any form of availability SLAs. Microsoft can reboot/migrate single instances whenever they feel like it. With manual failover you would only have a few minute downtime if someone is there to trigger it. That honestly sounds like a crappy solution 2015..

What? In the case of Microsoft rebooting/migrating an instance causing a failure, Keepalived will automatically failover. The manual failover is in case of something going horribly wrong (outside of hardware failure), in which case a human steps in, looks at the situation, determines the best solution... and if it's failover, they initiate the failover. I've personally used this procedure in the past and it worked 10…

And you can do this 24/7 since you are awoke everyday and night?

Re: High Availability for PostgreSQL, Batteries Not Included

#17

Post by rachbelaid »

dgreensp wrote: post #14

What does "Batteries Not Included" mean for a highly available database? Is it a good thing?

"Batteries Not Included" in this article refers to the fact that PostgreSQL doesn't offer any built-in solution yet to do HA (automatic fail-over) and you have to depend of 3rd party solution.

Re: High Availability for PostgreSQL, Batteries Not Included

#18

Post by stonemetal »

dgreensp wrote: post #14

What does "Batteries Not Included" mean for a highly available database? Is it a good thing?

"Batteries Not Included" is a phrase found on the box of children's toys, so that parents know they will need to buy batteries before giving it to their kids. It has become an expression for something being incomplete and will require effort before it will work properly.

In this case the author is stating that Postgres doesn't come with a high availability capability. He then goes on to explain the high availability setup he put together.

Re: High Availability for PostgreSQL, Batteries Not Included

#19

Post by jsprogrammer »

teraflop wrote: post #10

Like a lot of designs that use Raft/Zookeeper/Paxos/whatever as a building block, the full system doesn't inherit all of the safety properties of the underlying consensus algorithm. I don't think that makes this code useless by any means, but I think it's important to be aware of the edge cases. Consensus algorithms are popular because they're supposed to solve the difficult problem of guaranteeing consistency while…

If the PostgreSQL leader doesn't reset the leader key, it's no longer leader.

Re: High Availability for PostgreSQL, Batteries Not Included

#20

Post by chucky_z »

merb wrote: post #16
chucky_z wrote: post #13

Earlier quoted context omitted.

What? In the case of Microsoft rebooting/migrating an instance causing a failure, Keepalived will automatically failover. The manual failover is in case of something going horribly wrong (outside of hardware failure), in which case a human steps in, looks at the situation, determines the best solution... and if it's failover, they initiate the failover. I've personally used this procedure in the past and it worked 10…

And you can do this 24/7 since you are awoke everyday and night?

"The tricky part is then notifying the hell out of everyone who needs to be notified that something really bad has happened, a failover occurred, everything is OK, but it needs some attention ASAP."

If there is a need for multiple 9's of uptime, there should be an escalation process for these kinds of events, which will probably include 24/7 on-call rotations.

Even if the problem is entirely self-resolving, it should still be looked at by more than one system. It should be noted, observed, documented, and confirmed it's truly resolved. That system is usually a human, but it doesn't necessarily have to be.

Post reply on HN