10 ms·
Bi-Directional Replication for PostgreSQL v1.0
- koolba 10y agoAnybody in the HN crowd have experience using this? How does it perform on a WAN?
- eloycoto 10y agoWe used it for a few months now, it works. we have 3 datacenters with ~10ms of delay between them and no problems. Over WAN will work, but not sure how reliable will be. Regards.
- baq 10y agoHave you tried PostgresXL and if yes, can you make a high level comparison?
- eloycoto 10y agoNo, I didn't try. So I can't compare. I saw the contributors/support from postgres, and I chose BDR.
- pgaddict 10y agoWell, we're also Postgres-XL contributors, so ... ;-)
- pgaddict 10y agoThose are solutions for very different types of deployments. BDR is asynchronous multi-master, without global transaction management etc. XL is a distributed database with global transaction management, strong consistency, etc. Say you have geographically distributed application (managing customers in US, EU, ...). You can deploy a node in each area and direct local users to this node with low latency etc. It also means "no conflicts" (which is a major concern in multi-master), and the changes will asynchronously replicate to the other nodes. XL would perform poorly in this scenario (at least I believe so), and it's meant more for local deployments, when hitting vertical scalability limits.
- alfalfasprout 10y agoIf you're interested, I'm trying just this over the weekend. Standard streaming replication over WAN was pretty awful. It would work reliably for a few weeks then cause both the master and slave to block writes when they got out of sync because something was dropped.
- pgaddict 10y agoWhat do you mean by "got out of sync"? That's a fairly strange issue with physical streaming replication.
- rvanlaar 10y agoI use on it to keep my postgres data in sync in a high available setup. I host two nodes, each in a different DigitalOcean datacenters (AMS2 and AMS3). It's fast enough to not notice delays for users. As in, have a user log in on host1, switch the user to host2 and the session is there. Disclaimer, we're not write heavy and have around one hundred active users. https://rvanlaar.github.io/Django-HA-presentation/#/ https://rvanlaar.github.io/Django-HA-presentation/#/
- eloycoto 10y agoyou should create a new node. Less than 3 is a bit dangerous ;-)
- martinmarques 10y agoActually with two nodes, if one node goes down, you don't have a BDR cluster anymore ;-)
- therealmarv 10y agoWhen reading this I wonder how to do this: OS or Postgres updates with one normal Postgres (or a typical master+slave setup) without downtime and without using Postgres BDR. Does somebody now?
- andruby 10y agoWe have one master PG database and handle upgrades during "planned downtime" during off-peak hours (during weekend nights our B2B app is relatively empty). We prep a new instance with the upgrades in place, and then load over the production data during downtime.
- colanderman 10y agoOS upgrades, sure, just use two systems with one of the existing async rep solutions between them. You'll need to make the master read-only for a brief period before failover as the slave "catches up" with the master. Online PG upgrades aren't (until now) possible because the binary log format changes. But pglogical (which underpins BDR) solves this issue: https://2ndquadrant.com/en/resources/pglogical/ https://2ndquadrant.com/en/resources/pglogical/ Process would be the same as an OS upgrade (brief period of read-only availability required), but you can do it on one machine.
- pgaddict 10y agoSomething like that is possible by using UDR (think BDR but only in one direction): https://wiki.postgresql.org/images/a/a8/Udr-pgconf.pdf https://wiki.postgresql.org/images/a/a8/Udr-pgconf.pdf
- jimktrains2 10y ago> I’m pleased to say that we’ve just released Postgres-BDR 1.0, based on PostgreSQL 9.4.9. While I'm still excited to play with it, I can't wait until pglogical comes into mainline.
- reitanqild 10y agoWow, that sounds like a really nice open-source company. They offer sources, repos for both apt and yum as well as this: > Need more help? > Click here for pglogical's documentation. > Click here to look at answers to some of the most frequently asked questions. > Want to know even more? Need some help to implement pglogical? As the developers we're probably the best people in the world to help you get up and running with pglogical. We have consultants available to provide assistance, plus our unique 24/7 Production Support subscription service also covers pglogical. That's a company I'd want to deal with. (Not using postgres at the moment, but hired Tomitribe for some minor work and the whole experience of dealing with the people who actually write and maintain stuff instead of marketing drones etc is totally awesome.)
- colanderman 10y agoThanks for pointing out pglogical. I've never heard of it before, and it's exactly what we need to support online upgrade.
- continuations 10y agoHow does pglogical differ from PostgreSQL's built-in streaming or log-shipping replication?
- pinaraf 10y agoPostgreSQL streaming : send disk diffs over network, raw binary, absolutely unusable if you don't have the same postgresql version on the other side Logical : send data diffs over network. Could be used for replication, but also audit or sending to different databases...
- martinmarques 10y agopglogical works over the logical decoding code introduced in 9.4, so instead of sending over binary changes of the data files (SR just sends a change that says change these bytes on page X -- well, not super exact, but it gives you a picture of whats done), while logical decoding will read the WAL log, decode the changes so you can apply them else were by some plugin (pglogical would serve as a plugin in this case). I think the best way to show how it would work (and this is not what pglogical does as it would be inefficient) is by writing a plugin that feeds the logical decoded changes from the WAL and builds SQL statements from them UPDATE, INSERT, DELETE, etc. You could easily apply those on any other PostgreSQL server, or maybe even some other SQL server. Very handy for online upgrades.
- brightball 10y agoCan't wait to try this out. I wonder how long it will take Heroku, RDS or another PG provider to make this available?
- Artemis2 10y agoSurprisingly I haven't found a lot of HA-ready (actual HA, no failover) hosted PostgreSQL. There's Citus Data but that's a fork and they're expensive.
- Judson 10y agoJust wanted to point out that Citus Data is no longer a fork of Postgres and is now open source[0]. (Though, I'm sure their managed service is 'expensive' compared to unmanaged). [0]: https://www.citusdata.com/blog/2016/03/24/citus-unforks-goes-open-source/ https://www.citusdata.com/blog/2016/03/24/citus-unforks-goes...
- nubela 10y agoAny reasons why I should not use this? Any other (Postgres-esqe) alternatives for such a solution?
- mason55 10y ago> Any reasons why I should not use this? If your use case can't handle eventually consistent data or you have no way to successfully resolve conflicts/never cause conflicts then this product is not for you. They've chosen availability over consistency, so when the network between your DB servers partitions your applications will still function. But partition A won't see what's happening on partition B and vice versa and when the partition heals you might have conflicts to resolve. One great use case I can think of for this is a case where you have geographically clustered clients that need to read globally but only write locally. As long as you model your data correctly you could set up a master in each region so that your writes were fully ACID compliant then have the masters replicate to each other so that data from other regions was available for reading. You'd get the benefit of distributed writes, your local clusters could continue working even if there was a partition or one of the other clusters went down, and you'd assure yourself that you wouldn't have conflicts.
- pabl0rg 10y agocitus data may interest you. it automates sharding and will soon help with High Availability
- sinatra 10y agoAs the linked page doesn't describe what it is: Bi-Directional Replication for PostgreSQL (Postgres-BDR, or BDR) is an asynchronous multi-master replication system for PostgreSQL, specifically designed to allow geographically distributed clusters. Supporting more than 48 nodes, BDR is a low overhead, low maintenance technology for distributed databases. [0] [0]: https://2ndquadrant.com/en/resources/bdr/ https://2ndquadrant.com/en/resources/bdr/
- rattray 10y ago> Supporting more than 48 nodes Hmm, on the mailing list announcement they claim 2-48 nodes. EDIT: perhaps they're referring to support for read-replica's that can go beyond the 48 primaries. https://www.postgresql.org/message-id/CAH%2BGA0qO0q9NJhKxaoo8CV-QR9Cuw6LtDGDb33pq16rU88F_dA%40mail.gmail.com https://www.postgresql.org/message-id/CAH%2BGA0qO0q9NJhKxaoo...
- pgaddict 10y agoAFAIK there's no explicit limit per se - the BDR developers tested it with 48 nodes, but it will work with more nodes. But there are things you'll probably run into - for example this only supports full mesh topology, i.e. each node speaks to every other node. So the more nodes you have, the higher the number of management connections.
- baq 10y agoIt's not immediately clear for me when to use BDR and when would I choose XL. Can somebody do a quick comparison? I like this quote in the announcement email: --- IS Postgres-BDR RIGHT FOR YOU? BDR is well suited for databases where: - Data is distributed globally - Majority of data is written to from only one node at a time (For example, the US node mostly writes changes to US customers, each branch office writes mostly to branch-office-specific data, and so on.) - There is a need for SELECTs over complete data set (lookups and consolidation) - There are OLTP workloads with many smaller transactions - Transactions mostly touching non overlapping sets of data - There is partition and latency tolerance However, this is not a comprehensive list and use cases for BDR can vary based on database type and functionality. In addition, BDR aids business continuity by providing increased availability during network faults. Applications can be closer to the data and more responsive for users, allowing for a much more satisfying end-user experience. --- But I can't seem to find something similar for XL and especially a diff between the two.
- brian_cloutier 10y agoI'm by no means authoritative, but XL lets you distributed postgresql and keep the transactionality. That's great if you're a bank but necessarily slow and not Available, so running it over a wan might be a bad idea. BDR lets you distribute your database but throws transactionality out of the window. You have two independent databases which stream changes to each other. A lot faster, and well suited for wans, but not Consistent.
- pgaddict 10y agoXL has a global transaction manager, keeping the whole cluster consistent as a whole, and allows you to query the whole database for analytics, etc. BDR on the other hand doesn't have such global component, so each node is consistent on it's own, but the nodes may be at slightly different states. Still, the replicated changes are applied transactionally (i.e. you won't see just a partial update from another node, etc.)
- gleenn 10y ago
- therealmarv 10y agoWould not this be an ideal Docker database?! But I could not find a good maintained Docker image in the hub: https://hub.docker.com/search/?isAutomated=0&isOfficial=0&page=1&pullCount=0&q=postgres+bdr&starCount=0 https://hub.docker.com/search/?isAutomated=0&isOfficial=0&pa...
- cookiecaper 10y agoDocker and database do not mix.
- thom_nic 10y agoSource? I have not seen strong argument against running postgres or other DBs in a container. Data directory should be mapped to a persistent volume, of course.
- superuser2 10y agoThen why would you run it in a container? You're not encapsulating anything or making anything portable of the interesting part of the container is a directory on the host.
- therealmarv 10y agoYou can make the whole binary/running part interchangeable... think of OS updates, Postgres updates etc. just replace the container with a new one.
- superuser2 10y ago>just Why is building, distributing, and cutting over to a new container easier than "sudo apt-get upgrade postgres"?
- therealmarv 10y ago
- zzzeek 10y agoum, LICENSE? am I missing something https://github.com/2ndQuadrant/bdr https://github.com/2ndQuadrant/bdr
- kej 10y agoThe link at the top of the repo goes to the product page, which says: >Postgres-BDR, an extension to PostgreSQL is free and open source licensed under the [same terms] as PostgreSQL. So it's under the PostgreSQL license, which is similar to MIT.
- DelaneyM 10y agoI wonder if it's plausible for AWS to release a customized variant on this targeted to their data centers and designed for ease of multi-zone deployment? Something like Azure (which optimized and facilitates MySQL). I realize that redshift already loosely meets that definition, but it doesn't quite work as a globally distributed regionally clustered web service back end. This is ideal.
- neuronexmachina 10y agoMinor point: Did you mean to say "Aurora" (AWS's MySQL-ish DB) instead of "Azure" (Microsoft's cloud service)?
- DelaneyM 10y agoYes. That is what I meant. :P Thank you.
- Klathmon 10y agoSo this might not be the right place for this, but i'm curious. How do people deal with "eventual consistency"? In my head once a transaction is done, it's everywhere and everyone has access to it. What happens if 2 nodes try to modify the same data at the same time? Or what happens if you insert on one node, then query on another before it propagates? And if the answer to those questions are what I think they are (that bad stuff happens), how do you setup your application to avoid doing it?
- hinkley 10y agoAlways wondered this myself. There seems to be a general fondness for writing to the database and then immediately reading back from it to make a decision. Some variant of that show up in the requirements quite often, usually in the form of confirmation pages or overview screens following right after an update, so the user knows what they did took effect. And almost as often you'll find that some member or members of your team have made the assumption that the whole interaction will occur on one machine talking to one database instance. With session affinity you might be able to create a convincing illusion that this is working, but now with microservices you're intentionally hitting many servers. If checkout and order history are on two different servers, how does eventual consistency work?
- LoSboccacc 10y ago>There seems to be a general fondness for writing to the database and then immediately reading back from it to make a decision one way I saw banking software do it was keeping deltas of the changes thorough a wizard like process and then commit it all at once in a single transaction, at which point if there was a conflict the wizard restarted with up to date data. conflict detection was done by a sequential id keyed on the accounts touched on the operation, so that any operation modifying the account before your delta was applied would knock your commit off
- hinkley 10y agoBanking and the credit industry are a good example of eventually consistent systems. But you can see the inconsistencies and you're expected to just ignore them. I guess what I'm asking is how do you push back on requirements and inattentive coworkers that make these compromises unworkable, because it seems so pervasive to me.
- onderkalaci 10y agoIs there a documentation on "How BDR works?" or so?
- srparish 10y agohttp://bdr-project.org/docs/stable/index.html http://bdr-project.org/docs/stable/index.html
- rattray 10y agoThey claim performance comparable to Hot Standby: https://2ndquadrant.com/en/resources/bdr/bdr-performance/ https://2ndquadrant.com/en/resources/bdr/bdr-performance/ and, in some cases, ~1.5x over Slony.
- rattray 10y agoIt seems like this is very much a (FOSS) product of 2nd Quadrant. Does anyone here have experience with them? What is their reputation like?
- Tostino 10y agoNot worked with them directly, but have followed the pg-hackers mailing list and they are have a bunch of contributions back to core Postgres, and have some core maintainers on staff. They seem very open with everything they do, as opposed to EDB, which do contribute back but also have a bunch of closed source tools and their own closed source Postgres fork.
- looneysquash 10y agoWhat I want to know is the timeframe of supporting PostgreSQL 9.5 and/or 9.6. (Though 9.6 is still in beta.) Also, my understanding is that they're feeding patches back to postgres, and want it to eventually run on stock postgres. But it's not clear to me how progress on that is going. I was also surprised that UDR was removed, I didn't even realize it was deprecated. I'm not actually using the product at all right now, but I've been watching on the website, because I want to use it eventually. I'm kind of hoping it works with stock postgres before I jump in. But if not that, I think I at least want to wait for 9.6 support.
- jlgaddis 10y agoIf memory serves, they're planning on it being "in core" on 10.0.
- rattray 10y agoWould be amazing if anyone could come up with a citation for this.
- martinmarques 10y agoUDR has been deprecated in favor of pglogical, which is based on better code already available on PostgreSQL (and production ready... actually we are using it with customers on production systems). There will not be any BDR release for 9.5. That has been commented may many times on the pgsql-general list. There will most likely be a 9.6 version, but it won't run on stock postgres (you'll still need to have a patched version of postgres). If the rest of the features BDR relies on get in for the PG 10 release, then there will be only a BDR extension which you'd have to install (no patching postgres)
- bonesmoses 10y agoThat may be the case, but they need to update the documentation to reflect that. This is what it currently says: "Note: All the new features required have been submitted for inclusion in PostgreSQL 9.5. Many have already been accepted and included. If all the functionality BDR requires is added to PostgreSQL 9.5 then the need for a modified PostgreSQL will go away in the next version." Well, PostgreSQL 9.5 has been out for several months now, and there's no note at all for 9.6. For a 1.0 announcement, that's a pretty strange omission. So we get something that only really works with a patched version of 9.4 when 9.6 is going to be released soon, and talks about 9.5 like it isn't out yet? No thanks.
- petepete 10y agoTitle makes it sound like people are still writing extensions for PostgreSQL 1.0