6 ms·

I started this analysis intending to do just that--it's been difficult, however, to figure out which of the dozens of replication/HA configurations to actually

by aphyr 6y ago

I started this analysis intending to do just that--it's been difficult, however, to figure out which of the dozens of replication/HA configurations to actually test. I settled on Stolon, since it seemed to make the strongest safety claims. However, I found bugs which turned out to be PostgreSQL's fault, so I backed off to investigate those first.

qeternity 6y ago
And herein lies the rub: HA Postgres is an extremely painful proposition. Based on our non-scientific research, Patroni seems to be the most battle tested solution, and as popular if not more so than Stolon.
- aphyr 6y ago
  Patroni's documentation also seems to suggest that even with the strongest settings, it can lose transactions; Stolon makes stronger claims.
  - feike 6y ago
    Patroni does have synchronous_mode_strict setting, which may be what you're looking for: This parameter prevents Patroni from switching off the synchronous replication on the primary when no synchronous standby candidates are available. As a downside, the primary is not be available for writes (unless the Postgres transaction explicitly turns of synchronous_mode), blocking all client write requests until at least one synchronous replica comes up. https://patroni.readthedocs.io/en/latest/replication_modes.html#replication-modes https://patroni.readthedocs.io/en/latest/replication_modes.h... edit: seems I missed this discussion on twitter: https://twitter.com/jepsen_io/status/1265626035380346881 https://twitter.com/jepsen_io/status/1265626035380346881
    - aphyr 6y ago
      Er, again, the docs say "it is still possible to lose transactions even when using synchronous_mode_strict". I've talked about this with some of the Stolon folks on Twitter, and we're not exactly sure how that manifests--possibly an SI or SSI violation.
      - merb 6y ago
        why is stolon more safe than? I tought stolon uses synchronous replication of pg aswell?
  - satyanash 6y ago
    > documentation also seems to suggest that even with the strongest settings, it can lose transactions; Can be reproduced even on a single node postgres. Just hammer it with inserts and maintain a local counter for inserts performed. Then, kill9 the postgres process. You'd expect your local counter to match the actual rows inserted, but you'll find that your counter will always be "less" than the actual rows inserted. Like any "networked" system, it is possible to lose commit acknowledgments even if the commit itself was successful. So yes, you've not "lost" transactions per se. You've "gained" them, but it is still a data issue in either case.
    - anarazel 6y ago
      The classical solution to that is to use 2PC. But often it's not worth it...
      - andreareina 6y ago
        2PC can have unacknowledged writes: https://aphyr.com/posts/282-call-me-maybe-postgres https://aphyr.com/posts/282-call-me-maybe-postgres
  - qeternity 6y ago
    Ah, I presumed you were talking about distributed failure situations (split brain, etc) as opposed the to PG level replication (which most solutions orchestrate anyway).
- LunaSea 6y ago
  It's one of the reasons for which NoSQL databases got a lot of publicity during the early 2010's.
  - threeseed 6y ago
    And are still widely used today. People like to criticise NoSQL databases like MongoDB etc but at least they took on the challenge of making clustering easy enough to use and safe enough to rely on. Especially because it such a complex and error prone challenge.
    - biggestdummy 6y ago
      Odd that you would point out MongoDB as your named example, as it is pretty awful at sharding/clustering. For HA, the more better example would be Cassandra or Scylla. Mongo's success is more tied to the ease of development with a native JSON document DB, rather than any claims to scalability. (Insert "Mongodb is webscale" video here.)
      - threeseed 6y ago
        MongoDB was called out because of its ease of use. You can create replica sets and shards in seconds. And for many use cases it works great. Cassandra is one of if not the best since it's multi-master but it's a little bit more complex to setup.
      - PeterCorless 6y ago
        I lol'ed. (Full disclosure: I work for ScyllaDB.) MongoDB has definitely come a long way in terms of HA, but yes, they are still have a long way to go. A good primer talk on the differences can be found here: https://www.scylladb.com/tech-talk/mongodb-vs-scylla-production-experience-from-both-dev-ops-standpoint/ https://www.scylladb.com/tech-talk/mongodb-vs-scylla-product...
- zozbot234 6y ago
  "HA in Postgres" does not have a very well-defined meaning. The Postgres documentation provides an overview of different viable solutions: https://www.postgresql.org/docs/12/different-replication-solutions.html https://www.postgresql.org/docs/12/different-replication-sol... with features and drawbacks for each. But to call it "extremely painful" seems to be a bit overstated.
- ksec 6y ago
  Is there a proposed roadmap for basic / default solution of HA Postgres? It seems MySQl has this well covered and Postgres continue to think it is not a core part of their DB and relies on third party. ( Not suggesting that is necessary a bad thing )
- mwcampbell 6y ago
  > HA Postgres is an extremely painful proposition. Does anyone here know how Amazon RDS's HA setup, particularly their multi-AZ option, works? That seems to be a switch that the AWS customer can just turn on. Do they have a proprietary implementation, even for non-Aurora Postgres?
  - threeseed 6y ago
    They basically have built a proprietary, distributed block store. And on top of this they have layered PostgreSQL, MySQL, MongoDB, Cassandra etc. I doubt they will never release the code for it since it's very much a competitive advantage.
    - devit 6y ago
      So if the hardware running the database is suddenly destroyed they try to start another instance really fast? That seems inferior to having multiple sync replicas ready to take over without having to start a process and replay the WAL. Also, such an HA block store seems very easy to replicate ( I'd guess there would be something open source already), not much of a competitive advantage.
      - redis_mlc 6y ago
        - using block-level replication allows them to support multiple databases in a common way - block-level replication can be more reliable in the long run operationally than some types of database replication, especially MySQL back in the day - block-level replication has more scalable support staff available than hiring DBAs to fix database replication problems - programming for all the edge cases is something that is a competitive advantage - no licensing required for it - you can probably guess which Open Source project it's based on Source: DBA, worked there.
        hashhar 6y ago
        Just gonna put this here for people who might be wondering. Not too many block-level replication implementations exist. https://www.linbit.com/drbd/ https://www.linbit.com/drbd/ NB: It is Free software, don't be alarmed by the domain name.

by aphyr 6y ago

qeternity 6y ago

And herein lies the rub: HA Postgres is an extremely painful proposition. Based on our non-scientific research, Patroni seems to be the most battle tested solution, and as popular if not more so than Stolon.

aphyr 6y ago
Patroni's documentation also seems to suggest that even with the strongest settings, it can lose transactions; Stolon makes stronger claims.
- feike 6y ago
  Patroni does have synchronous_mode_strict setting, which may be what you're looking for: This parameter prevents Patroni from switching off the synchronous replication on the primary when no synchronous standby candidates are available. As a downside, the primary is not be available for writes (unless the Postgres transaction explicitly turns of synchronous_mode), blocking all client write requests until at least one synchronous replica comes up. https://patroni.readthedocs.io/en/latest/replication_modes.html#replication-modes https://patroni.readthedocs.io/en/latest/replication_modes.h... edit: seems I missed this discussion on twitter: https://twitter.com/jepsen_io/status/1265626035380346881 https://twitter.com/jepsen_io/status/1265626035380346881
  - aphyr 6y ago
    Er, again, the docs say "it is still possible to lose transactions even when using synchronous_mode_strict". I've talked about this with some of the Stolon folks on Twitter, and we're not exactly sure how that manifests--possibly an SI or SSI violation.
    - merb 6y ago
      why is stolon more safe than? I tought stolon uses synchronous replication of pg aswell?
- satyanash 6y ago
  > documentation also seems to suggest that even with the strongest settings, it can lose transactions; Can be reproduced even on a single node postgres. Just hammer it with inserts and maintain a local counter for inserts performed. Then, kill9 the postgres process. You'd expect your local counter to match the actual rows inserted, but you'll find that your counter will always be "less" than the actual rows inserted. Like any "networked" system, it is possible to lose commit acknowledgments even if the commit itself was successful. So yes, you've not "lost" transactions per se. You've "gained" them, but it is still a data issue in either case.
  - anarazel 6y ago
    The classical solution to that is to use 2PC. But often it's not worth it...
    - andreareina 6y ago
      2PC can have unacknowledged writes: https://aphyr.com/posts/282-call-me-maybe-postgres https://aphyr.com/posts/282-call-me-maybe-postgres
- qeternity 6y ago
  Ah, I presumed you were talking about distributed failure situations (split brain, etc) as opposed the to PG level replication (which most solutions orchestrate anyway).
LunaSea 6y ago
It's one of the reasons for which NoSQL databases got a lot of publicity during the early 2010's.
- threeseed 6y ago
  And are still widely used today. People like to criticise NoSQL databases like MongoDB etc but at least they took on the challenge of making clustering easy enough to use and safe enough to rely on. Especially because it such a complex and error prone challenge.
  - biggestdummy 6y ago
    Odd that you would point out MongoDB as your named example, as it is pretty awful at sharding/clustering. For HA, the more better example would be Cassandra or Scylla. Mongo's success is more tied to the ease of development with a native JSON document DB, rather than any claims to scalability. (Insert "Mongodb is webscale" video here.)
    - threeseed 6y ago
      MongoDB was called out because of its ease of use. You can create replica sets and shards in seconds. And for many use cases it works great. Cassandra is one of if not the best since it's multi-master but it's a little bit more complex to setup.
    - PeterCorless 6y ago
      I lol'ed. (Full disclosure: I work for ScyllaDB.) MongoDB has definitely come a long way in terms of HA, but yes, they are still have a long way to go. A good primer talk on the differences can be found here: https://www.scylladb.com/tech-talk/mongodb-vs-scylla-production-experience-from-both-dev-ops-standpoint/ https://www.scylladb.com/tech-talk/mongodb-vs-scylla-product...
zozbot234 6y ago
"HA in Postgres" does not have a very well-defined meaning. The Postgres documentation provides an overview of different viable solutions: https://www.postgresql.org/docs/12/different-replication-solutions.html https://www.postgresql.org/docs/12/different-replication-sol... with features and drawbacks for each. But to call it "extremely painful" seems to be a bit overstated.
ksec 6y ago
Is there a proposed roadmap for basic / default solution of HA Postgres? It seems MySQl has this well covered and Postgres continue to think it is not a core part of their DB and relies on third party. ( Not suggesting that is necessary a bad thing )
mwcampbell 6y ago
> HA Postgres is an extremely painful proposition. Does anyone here know how Amazon RDS's HA setup, particularly their multi-AZ option, works? That seems to be a switch that the AWS customer can just turn on. Do they have a proprietary implementation, even for non-Aurora Postgres?
- threeseed 6y ago
  They basically have built a proprietary, distributed block store. And on top of this they have layered PostgreSQL, MySQL, MongoDB, Cassandra etc. I doubt they will never release the code for it since it's very much a competitive advantage.
  - devit 6y ago
    So if the hardware running the database is suddenly destroyed they try to start another instance really fast? That seems inferior to having multiple sync replicas ready to take over without having to start a process and replay the WAL. Also, such an HA block store seems very easy to replicate ( I'd guess there would be something open source already), not much of a competitive advantage.
    - redis_mlc 6y ago
      - using block-level replication allows them to support multiple databases in a common way - block-level replication can be more reliable in the long run operationally than some types of database replication, especially MySQL back in the day - block-level replication has more scalable support staff available than hiring DBAs to fix database replication problems - programming for all the edge cases is something that is a competitive advantage - no licensing required for it - you can probably guess which Open Source project it's based on Source: DBA, worked there.
      - hashhar 6y ago
        Just gonna put this here for people who might be wondering. Not too many block-level replication implementations exist. https://www.linbit.com/drbd/ https://www.linbit.com/drbd/ NB: It is Free software, don't be alarmed by the domain name.