7 ms·

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

215 points by tuyguntn 11y ago

secure 11y ago
similar: https://spilo.readthedocs.org/en/latest/DESIGN/ https://spilo.readthedocs.org/en/latest/DESIGN/ https://github.com/sorintlab/stolon https://github.com/sorintlab/stolon I wonder how they compare to yoke.
- navls 11y ago
  If I had a penny for every HA postgres solution
  - Rafert 11y ago
    I'm no Postgres expert, why isn't something like this built in to the product itself?
    - dijit 11y ago
      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.
    - creshal 11y ago
      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-cluster https://aphyr.com/posts/328-call-me-maybe-percona-xtradb-clu... ), probably a good decision.
    - saurik 11y ago
      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.
      - lobster_johnson 11y ago
        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.
    - Jweb_Guru 11y ago
      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.
      - aravindet 11y ago
        > 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.
      - notpeter 11y ago
        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 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."
- anewhnaccount 11y ago
  What about rprmgr? https://github.com/2ndQuadrant/repmgr/blob/master/FAILOVER.rst https://github.com/2ndQuadrant/repmgr/blob/master/FAILOVER.r...
  - sesm 11y ago
    There's also a comparison matrix of clustering solutions on official wiki: https://wiki.postgresql.org/wiki/Replication,_Clustering,_and_Connection_Pooling https://wiki.postgresql.org/wiki/Replication,_Clustering,_an...
    - tylerflint 11y ago
      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 http://nanopack.io) for further clarification on why yoke was needed.
tbarbugli 11y ago
no docs no party
- deleted 11y ago
  [deleted]
- tolmark12 11y ago
  https://godoc.org/github.com/nanopack/yoke https://godoc.org/github.com/nanopack/yoke
  - tbarbugli 11y ago
    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?
  - acveilleux 11y ago
    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.
- tylerflint 11y ago
  Yeah we hear ya. The nanopack cloud-initiative was just announced last friday (https://blog.nanobox.io/nanopack-a-new-vision-for-automated-infrastructure/ https://blog.nanobox.io/nanopack-a-new-vision-for-automated-...). We're getting there slowly but surely!
angelbob 11y ago
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.
- merb 11y ago
  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.
- EwanToo 11y ago
  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?
  - teraflop 11y ago
    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.
    - EwanToo 11y ago
      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 https://aphyr.com/posts/282-call-me-maybe-postgres
FranzSunder 11y ago
ddddddddddddddddddd
deleted 11y ago
[deleted]
jpgvm 11y ago
This is inferior to the Manatee design: https://github.com/joyent/manatee 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.
- deleted 11y ago
  [deleted]
- dan353hehe 11y ago
  True it is, however to use manatee you have to build on ZFS, which unfortunately is not as widely adopted as it should be.
  - jpgvm 11y ago
    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 https://github.com/josephglanville/manatee Chef cookbook to help you get started along with the other dependencies: https://github.com/josephglanville/cookbook-manatee https://github.com/josephglanville/cookbook-manatee
- tylerflint 11y ago
  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.
jdmoreira 11y ago
But is it web scale? http://www.mongodb-is-web-scale.com/ http://www.mongodb-is-web-scale.com/
atmosx 11y ago
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?
- acveilleux 11y ago
  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 https://msdn.microsoft.com/en-us/library/ms175191.aspx
abritishguy 11y ago
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
tylerflint 11y ago
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-infrastructure/ 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.
lamby 11y ago
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.
- lobster_johnson 11y ago
  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.

215 points by tuyguntn 11y ago

similar: https://spilo.readthedocs.org/en/latest/DESIGN/ https://spilo.readthedocs.org/en/latest/DESIGN/ https://github.com/sorintlab/stolon https://github.com/sorintlab/stolon I wonder how they compare to yoke.

navls 11y ago
If I had a penny for every HA postgres solution
- Rafert 11y ago
  I'm no Postgres expert, why isn't something like this built in to the product itself?
  - dijit 11y ago
    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.
  - creshal 11y ago
    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-cluster https://aphyr.com/posts/328-call-me-maybe-percona-xtradb-clu... ), probably a good decision.
  - saurik 11y ago
    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.
    - lobster_johnson 11y ago
      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.
  - Jweb_Guru 11y ago
    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.
    - aravindet 11y ago
      > 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.
    - notpeter 11y ago
      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 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."
anewhnaccount 11y ago
What about rprmgr? https://github.com/2ndQuadrant/repmgr/blob/master/FAILOVER.rst https://github.com/2ndQuadrant/repmgr/blob/master/FAILOVER.r...
- sesm 11y ago
  There's also a comparison matrix of clustering solutions on official wiki: https://wiki.postgresql.org/wiki/Replication,_Clustering,_and_Connection_Pooling https://wiki.postgresql.org/wiki/Replication,_Clustering,_an...
  - tylerflint 11y ago
    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 http://nanopack.io) for further clarification on why yoke was needed.

tbarbugli 11y ago

no docs no party

deleted 11y ago
[deleted]
tolmark12 11y ago
https://godoc.org/github.com/nanopack/yoke https://godoc.org/github.com/nanopack/yoke
- tbarbugli 11y ago
  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?
- acveilleux 11y ago
  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.
tylerflint 11y ago
Yeah we hear ya. The nanopack cloud-initiative was just announced last friday (https://blog.nanobox.io/nanopack-a-new-vision-for-automated-infrastructure/ https://blog.nanobox.io/nanopack-a-new-vision-for-automated-...). We're getting there slowly but surely!

angelbob 11y ago

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.

merb 11y ago
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.
EwanToo 11y ago
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?
- teraflop 11y ago
  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.
  - EwanToo 11y ago
    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 https://aphyr.com/posts/282-call-me-maybe-postgres

FranzSunder 11y ago

ddddddddddddddddddd

deleted 11y ago

[deleted]

jpgvm 11y ago

This is inferior to the Manatee design: https://github.com/joyent/manatee 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.

deleted 11y ago
[deleted]
dan353hehe 11y ago
True it is, however to use manatee you have to build on ZFS, which unfortunately is not as widely adopted as it should be.
- jpgvm 11y ago
  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 https://github.com/josephglanville/manatee Chef cookbook to help you get started along with the other dependencies: https://github.com/josephglanville/cookbook-manatee https://github.com/josephglanville/cookbook-manatee
tylerflint 11y ago
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.

jdmoreira 11y ago

But is it web scale? http://www.mongodb-is-web-scale.com/ http://www.mongodb-is-web-scale.com/

atmosx 11y ago

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?

acveilleux 11y ago
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 https://msdn.microsoft.com/en-us/library/ms175191.aspx

abritishguy 11y ago

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

tylerflint 11y ago

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-infrastructure/ 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.

lamby 11y ago

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.

lobster_johnson 11y ago
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.