Postgres high-availability cluster with auto-failover and cluster recovery | Hacker News Reader

Postgres high-availability cluster with auto-failover and cluster recovery

github.com

215 pointstuyguntn55 comments

Can we just start posting "Jepsen or it didn't happen" on these?

Clustered relational DBs are really remarkably hard to get right. A shocking percentage of projects and vendors don't really even make a serious try (cf GaleraDB.)

This project may or may not have done a reasonable job (no clue,) but the lack of information about how they tested it is a bad sign.

Jepsen isn't a real world test either.

Also PostgreSQL HA has two options, either you have 1 Master and 1 Sync or 1 Async, if you have a Async Secondary it will of course loose data. Also you will have a short downtime on switchover.

I'm pretty sure this is simply active/standby clustering with some level of log shipping, not the active/active clustering that Jepsen is relevant for?

Jepsen is completely relevant to this kind of system, because it doesn't care what words you use to describe your algorithm -- just the patterns of communication it exhibits, and the consistency properties it satisfies. (What Jepsen isn't useful for is databases that don't even try to be consistent -- for example, Cassandra in its default modes. All Jepsen can do for you in that case is confirm what you should have been able to tell from the documentation.)

And arguably, active/standby with automatic failover is just a special case of active/active, with particar tradeoffs about throughput and failover latency.

Well you can run Jepsen tests against single nodes and use it to validate consistency on that single node if you want, but it's not really what it's made for, the github tagline for it is:

"Call Me Maybe: simulating network partitions in DBs"

Running Jepsen against a log shipping automation script is, to my mind, largely redundant.

Running it against this system would be testing Postgres [1], not yoke.

[1] - https://aphyr.com/posts/282-call-me-maybe-postgres

Can't an extra layer of inconsistencies arise if there's a network partition, and each yoke process thinks it should be the primary, so both accept writes?

I guess so yeah, I hadn't considered that :)

Thats true, however in yokes case, each cluster needs a third member called a monitor who only serves to resolve network partitions. a secondary cannot self promote to primary without the monitor agreeing.

two node systems can never work as a primary/secondary failover solution in the presence of a network partition.

disclosure: I helped write yoke.

Can the old master still accept requests if it loses contact with the monitor or secondary? What happens if the monitor goes down? Can there be data loss on failover?

It would be nice if the readme listed what failure scenarios are handled and what guarantees you intend the system to have.

Most (but not all) MySQL / PostgreSQL HA systems have similar semantics of Redis Sentinel. They don't try to achieve any linearizable semantics, but to perform failure detection and fail over to some slave, with heuristics about what slave to pick. All this kind of solutions will fail to pass Jepsen tests since they are not designed to achieve the kind of strong properties Jepsen will test for. The behavior of those systems is acceptable in many production situations (it's up to the product requirements to understand if it is ok that a given set of failure modes produce data loss and/or inconsistencies) but the important bit is that the semantics is very well documented so that users know what tradeoffs they are making.

EDIT: Shameless plug: I believe it was a shame that Sentinel was initially badly received because of the Jepsen test (now tons of users are using Sentinel with success, fortunately), since for what it does, it is very advanced compared to * SQL failover solutions, so a porting of Redis Sentinel to failover those systems could make sense IMHO. A few things Sentinel has that are desirable in * SQL failover systems:

1. It's distributed so Sentinel itself is not a single point of failure.

2. When the partition heals Sentinel is able to have a coherent view of the configuration.

3. It is able to automatically reconfigure the old master and the other slaves to replicate from the new master.

4. It is able to work as a configuration provider for clients, with a well defined handshake in order to trigger the reconfiguration of all the clients.

Generic solutions like corosync/pacemaker also deserve a mention. At some point you need inter-service dependencies, floating IPs, hot service spares and ordered recovery, plus tolerance for network fabric faults. Not every daemon needs to implement this stuff!

A large reason to use Postgres in the first place is because it's a good CP system. Any clustered version is only worthwhile if it preserves consistency.

It's already possible to use more than one Postgres database while not maintaining consistency, but that isn't desirable for most users.

> it's a good CP system

PostgreSQL is not clustered by default so it is only from the point of view of the theory a CP system. Most proper CP systems have the ability to provide a limited form of Availability, which is, they are available in the majority partition (this is not "A" of CAP but is a lot better than, if primary is not available, the whole cluster is not available). If you use PostreSQL with asynchronous replication and a best-effort failover solution to provide some form of Availability, you are doing something that makes sense, assuming your product can tolerate the resulting weak consistency properties.

This is a great example on how CAP does not capture certain important semantics of a system. Technically a single primary node is CP but the real world CP systems have better availability than that, or they are useless (as CP systems).

There are two separate problems:

- clustering to increase performance - clustering for HA

Most people want at least the later one. The former one is a bigger challenge in case of relational databases so I'll ignore it here.

Regarding consistency vs availability typically is not all black and white, and people are willing to make tradeoff. For example losing few transactions as opposed to being completely down. This is not too bad as long as developers are aware of that special case and write code that can handle that.

Having said that, I'm wondering how a failover system based on BookKeeper[1] would work (a relational database is effectively a state machine, so seems like this could fit). The tradeoff here would be most likely reduced performance though.

[1] http://bookkeeper.apache.org/

Would you be willing to run Jepsen again yourself with the latest Sentinel, or ask aphyr/Kyle to run it again himself?

I think you are missing the point.

Sentinel isn't really meant to solve what Aphyr/Jepsen tests.

Redis Sentinel's semantics are an excellent solution for what I use Redis for.

Redis Sentinel's semantics are a terrible solution for things I use relational DBs for.

The fact that clustered relational systems accepts transactions in SQL and then just handle them wrong is completely unacceptable.

Redis has the advantage of making it pretty darn clear that it's best-effort, simple ops, non-transactional. If you spent all your time bragging about ACID semantics for Redis, we'd rake you over the coals at least as hard.

(And you don't. Thanks for that! Also, Redis is awesome.)

> Clustered relational DBs are really remarkably hard to get right.

That is probably be true, but I'd like to point out that clustered non-relational DBs are also really remarkably hard to get right, it's just a hard problem to solve.

The tradeoffs you make with a non relational DB to make this easier are usually also available in relational DBs. It's not like Galera is your only choice.

Many of those choices to make it easier only work if you're willing to disallow a lot of SQL semantics.

Like, tradeoffs in consistency? No transactions? That requires not implementing some important bits of SQL.

You're not forced to use joins and most DBs don't force you to use transactions if you don't need them. Sharding is supported by postgres, oracle, sqlserver and mysql, even if it's not the recommended solution in all cases. A database doesn't magically start to scale by not supporting SQL nor does SQL support magically prevent it from scaling.

Interestly, those tradeoffs have nothing to do with relational or non-relational. They are a part of SQL.

Yes, my first reaction was "auto-failover? what are the odds this will lose data for me".

There are systems that get this right. Just apparently none for Postgres or MySQL :-(

There are remarkably few systems that get this right, and most of the few that remain (like Zookeeper) are key-value stores that don't support complex transactional semantics.

Hey everyone, I work for nanobox and on the yoke project. I just woke up and noticed the thread. The interest is appreciated. There are a lot of great questions here, and I'll try to do my best to answer them individually. Please keep in mind that the nanopack cloud-initiative was just announced friday (https://blog.nanobox.io/nanopack-a-new-vision-for-automated-...), so we're working feverishly to get documentation, tutorials, and demos available. Any help or contributions would be greatly appreciated.

This is inferior to the Manatee design: https://github.com/joyent/manatee

Funnily enough I ran into this yesterday when Mist was posted and I looked at what other projects were under the umbrella.

True it is, however to use manatee you have to build on ZFS, which unfortunately is not as widely adopted as it should be.

Aye but ZFS on Linux is rock solid and definitely the way to go.

Technically you don't need ZFS to use the Manatee state machine. You could swap out the ZFS snapshot transfer service to use pg_basebackup instead.

For anyone interested I have made the appropriate alterations to Manatee to get it to run on Ubuntu with the standard ubuntu-zfs packages.

The fork is here: https://github.com/josephglanville/manatee

Chef cookbook to help you get started along with the other dependencies: https://github.com/josephglanville/cookbook-manatee

Much of the architecture of yoke was inspired by manatee. In fact, we've spent the last 2 years working with Joyent on smartos and illumos, so we were heavily inspired by the great engineering behind manatee.

Manatee didn't work for our use cases, which should not be interpreted as "manatee is bad". Here are a few of the reasons we forged a different solution:

- While node.js is a great language for many things, we didn't want the overhead of running the node.js/v8 runtime alongside postgres. 50M might seem negligible, but with thousands of postgres clusters for our clients every MB adds up.

- We didn't want the administrative overhead of managing a zookeeper cluster, and instead built the cluster management semantics directly into the yoke project.

- This may be different now, but at the time manatee was very heavily integrated into illumos/smartos. We really love smartos, but recognize that not all clients are able to use this tech.

You guys seem to be creating a bunch of great little tools, but I wish you would spend more time on documentation.

For example, in this case it's not at all clear how the cluster management works, or even what the semantics are.

I couldn't agree more. We literally just introduced the nanopack cloud-initiative on friday (https://blog.nanobox.io/nanopack-a-new-vision-for-automated-...). Bear with us, we'll get there!

https://github.com/sorintlab/stolon

I wonder how they compare to yoke.

If I had a penny for every HA postgres solution

I'm no Postgres expert, why isn't something like this built in to the product itself?

the developers "do one thing and do it well"

it's better to have a flexible 'correct' implementation of a DB instead of baking in features which make it more complex and less extensible.

Postgres' motto is "do it right, or don't do it at all". The developers rather let other projects (or postgres addons) figure out the all the hidden traps and then implement a better-than-average solution on the first try a few years later.

Cf. their JSON support, which regularly outperforms dedicated NoSQL databases; they're also slowly integrating building blocks for multi-master replication, while still holding out on actually implementing it – seeing MariaDB's recent track record with their solution ( https://aphyr.com/posts/328-call-me-maybe-percona-xtradb-clu... ), probably a good decision.

PostgreSQL only adds clean, well thought-out, maintainable, and fully reliable implementations of features to their codebase. In stark contrast to many other open source database products, when PostgreSQL adds something, barring often one or two bugs found in the .0 release, you can trust it works now and will continue working pretty much forever. I appreciate this approach, and it is why I have been using PostgreSQL now for almost fifteen years. This particular feature is something that is difficult to make correct (see Jepson complaining about essentially everyone) as well as difficult to come up with a model that supports more than a few narrow use cases. If you need something now it is better than people are being directed to third-party products that can be built on the solid core of PostgreSQL, and which then will have clear expectations associated with them.

The result of this strategy is an extraordinary lack of cruft. Postgres has some obscure unpopular features, but it really doesn't have any legacy behaviour.

If you read the MySQL documentation, it's full of "until version 5.7.7.3 this returned NULL if..." or "this will accept invalid dates unless a certain mode flag is enabled" (I'm paraphrasing, obviously). Postgres has none of this.

They do occasionally deprecate features, such as OIDs, which are supported but not recommended (or particularly useful), or they change some key behaviour (standard_conforming_strings).

Sometimes the development strategy leads to incomplete or overlapping functionality because they prefer discrete, additive changes over big, one-off overhauls. But in general, Postgres' conservative strategy has really paid off.

To discourage people from using something broken. You're talking about a database that waited until 2011 to add a SERIALIZABLE implementation because the existing solutions all sucked, and only did it then because of new research (SSI). They have zero qualms about keeping features out of the database, no matter how useful, if they can't be implemented well.

> They have zero qualms about keeping features out of the database, no matter how useful, if they can't be implemented well.

Another example, INSERT ... ON CONFLICT UPDATE (UPSERT/MERGE) available just now in the 9.5 beta.

Forget serializable, PostgreSQL 7.0 included functional ACID-safe transactions 5 years before MySQL but didn't include LEFT OUTER JOIN (http://www.postgresql.org/docs/7.1/static/release-7-1.html) . That was my first taste of "if you can't do it right, don't ship it."

What about rprmgr? https://github.com/2ndQuadrant/repmgr/blob/master/FAILOVER.r...

There's also a comparison matrix of clustering solutions on official wiki: https://wiki.postgresql.org/wiki/Replication,_Clustering,_an...

repmgr and other projects listed here were considered and heavily tested initially. While each of them are great projects, we needed a solution that was fully automated in both cluster configuration/initialization as well as cluster recovery and migration. Many of these projects were inspirational in the design of yoke. You can read our vision for an "automated, API-driven infrastructure" (http://nanopack.io) for further clarification on why yoke was needed.

I can guarantee that this cannot guarantee both of:

- system stays online when any server goes down

- after confirming a write it will always be returned in corresponding reads following a failover

How does this work?

By skimming the installation instructions I deduce that every time a 'write' call hits the primary server, the primary server sends a message to 'monitor' server which sends a message to primary or secondary server(s) to run rsync?

It's really not documented well (and the generated docs are a joke) but from what I can gather, it's bog-standard PG replication with a go app to monitor the replication and do automated failover.

The monitor node appears to be there to act as neutral witness node so that a secondary is only promoted leader if it can reach the monitor and the monitor cannot reach the current leader. Hopefully, the leader also commits suicide when it cannot reach the monitor.

MS SQL server implements this pattern and calls it "Database Mirroring Witness": https://msdn.microsoft.com/en-us/library/ms175191.aspx

How likely is this to be merged upstream..? There's a glut of reasonable-to-good third-party solutions which I'm always hesitant to commit to as they are just Some Startup's solution.

Not saying it doesn't work for them or it couldn't work for me, but I do feel its justifiable to deduct a few "points" there.

Zero chance. For one, it's written in Go. Postgres is written in C and needs to be portable to a lot more architectures than Go currently supports.

Secondly, this is one possible design. The Postgres team prefers tools like these to complement the core so that people have a choice between different solutions depending on their use case, network topologies, availability tolerances, etc.

This just a helper tool that can control Postgres and ship logs around. A native Postgres solution could do it better.

no docs no party

https://godoc.org/github.com/nanopack/yoke

yep, that's not the kind of docs I expect for this sort of software :) Given how busy this space is, why would anyone ever use this tool?

That is not documentation, that's a bunch of code comments and signatures turned into a web page. You cannot tell from this documentation how the system works, what are the failure modes and how to recover. Those are kind of useful things in a failover management system.

Yeah we hear ya. The nanopack cloud-initiative was just announced last friday (https://blog.nanobox.io/nanopack-a-new-vision-for-automated-...). We're getting there slowly but surely!

ddddddddddddddddddd

Reply on news.ycombinator.com