Thread: Bi-Directional replication client awareness
Hi all! I was wondering if there are any specific load balancing/failover functionality planned for client drivers connection toa BDR group. In my case the jdbc driver, but could be relevant for other drivers as well. Or is the long term plan that we need we need to rely on middleware like pgpool or other third party frameworks for this? The reason I’m asking is because I was reading up on BDR and started thinking about who important the client interfaces alsoare to be able to take full advantage of these features. Having client awareness of the nodes ip, port numbers, status and current load, could probably bring powerful features tothis as a whole. That is some how other database vendors take care of client failover and load balancing and has in myexperience proved to work very well. And not only for BDR, but also streaming replication setups, could use this to do automatic client failovers, in the casethere is a server side failover. Anyone who knows if there is anything in progress regarding this? Kind regards, Martin
On 07/12/2014 02:42 AM, Martin Gudmundsson wrote: > Hi all! > I was wondering if there are any specific load balancing/failover functionality planned for client drivers connection toa BDR group. In my case the jdbc driver, but could be relevant for other drivers as well. PgJDBC actually already supports rudimentary client-based failover. It's not very smart - it doesn't maintain any persistent state, so if a host just vanishes it'll take a long time to connect to the next one as it has to wait for a TCP connection timeout. Making it stateful would be interesting, and may be more useful now that there's more reason to bother with client-side failover. Making it reliable in the face of classloader isolation, load/unload, etc will be "interesting", but it's a problem we eventually need to solve anyway if we're to support asynchronous notification callbacks. AFAIK libpq doesn't have any kind of failover - but if we added something similar, it'd be transparent to drivers like psycopg2, the Pg gem, etc that use libpq. I'm not sure how we'd go about making it stateful though - API changes would likely be needed for that bit. psqlODBC would also need significant changes. > Or is the long term plan that we need we need to rely on middleware like pgpool or other third party frameworks for this? At this point PgBouncer will likely be the way to go if you want to try to make it client transparent, but I don't think that's a good idea. Because BDR is asynchronous multi-master _replication_ though, clients are expected to be aware of some of the anomalies that can occur. A naïve client that just picked a random BDR server and did the next transaction on it would be very likely to cause unwanted replication anomalies, apply conflicts, etc. For example, if the client inserted a row on one server then tried to immediately update it on another, the update would likely fail because the row probably hasn't replicated yet. It is generally a good idea to make clients "sticky" to a given server, but clients also need to be aware of replication anomalies unless each server's data is a self-contained shard that doesn't interact with the others. In which case you probably wouldn't be using BDR. > Having client awareness of the nodes ip, port numbers, status and current load, could probably bring powerful featuresto this as a whole. That is some how other database vendors take care of client failover and load balancing and hasin my experience proved to work very well. Yes, that'd certainly be interesting. > And not only for BDR, but also streaming replication setups, could use this to do automatic client failovers, in the casethere is a server side failover. > > Anyone who knows if there is anything in progress regarding this? Other than the limited failover already in place for PgJDBC I'm not aware of anything. Work on its design, implementation and testing would be greatly appreciated, so polish up your C skills. -- Craig Ringer http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services
On 07/12/2014 02:24 PM, Martin Gudmundsson wrote: > > 12 jul 2014 kl. 04:58 skrev Craig Ringer <craig@2ndquadrant.com>: > >> PgJDBC actually already supports rudimentary client-based failover. > > I’ve seen some mailing list threads regarding this but was that ever implemented? Do you know where it’s documented? I don't think the patch ever got accompanying documentation. It's not mentioned here, anyway: http://jdbc.postgresql.org/documentation/93/connect.html Failover URLs are of the form: jdbc:postgresql://host1:port1,host2:port2/dbnjame with the port being optional. I'll document that now, before I get caught up in working on something else. In case you're looking, the code was added by git bddc05f939ac9227b682e85d1ba0a9b902da814c . > Any ideas giving BDR an option to be synchronous. I mean in ”close quarters” with low latency it should work ok. BDR doesn't do synchronous replication _yet_, but it's on the roadmap. Right now BDR as a whole is in its early days; the feature is being shaped by feedback and the requirements of sponsors and contributors. It's changing quite quickly, which is one good reason most of it is implemented as a plugin to PostgreSQL. Synchronous replication is one of a number of things that the team has looked into but hasn't prioritized implementing yet. Out of interest, what sorts of problems are you interested in solving using BDR? What needs are you trying to meet? Are you looking to use it for scaling? For HA? For data protection/redundancy? For transparent distributed databases (client/node locality)? I'm curious about what part you see synchronous replication support for BDR playing in any deployment you're contemplating. > Great explanation. Making them sticky i most likely the most reasonable approach in our case. But it would be good to havea place to list of servers to try on startup and then stick to one. Perhaps the rudimentary support you mention abovewould do it? If you're using PgJDBC, then you can provide a list of servers in priority order in your JDBC URL, as shown above. It'll connect to the first available server. When time and priorities permit I'd love to tackle things like round-robin server allocation for connections, smart server selection with failover policies, etc. Those are non-trivial projects, though, and must be done for each driver. > Phew, C oh C, that was a while a go :-) Or Java, if you're into that side of things. PgJDBC is a pretty friendly codebase. -- Craig Ringer http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services
On 2014-07-12 18:22:30 +0800, Craig Ringer wrote: > On 07/12/2014 02:24 PM, Martin Gudmundsson wrote: > > Any ideas giving BDR an option to be synchronous. I mean in ”close quarters” with low latency it should work ok. > > BDR doesn't do synchronous replication _yet_, but it's on the roadmap. Just to clarify: There's two things that sometimes are referred to as 'synchronous' in the context of multimaster replication. Just like with HS' builtin synchronous replication it can mean that the client doesn't get a reply until the COMMIT has safely been replicated to other systems. That's supported by BDR today. The more complex thing is support for performing transactions that, when the COMMIT returns successfully, can't have conflicts. That's *not* yet supported, but we're thinking of implementing it for individual transactions. Greetings, Andres Freund -- Andres Freund http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services
On 2014-07-12 13:23:08 +0200, Martin Gudmundsson wrote: > > 12 jul 2014 kl. 12:33 skrev Andres Freund <andres@2ndquadrant.com>: > > > On 2014-07-12 18:22:30 +0800, Craig Ringer wrote: > >> On 07/12/2014 02:24 PM, Martin Gudmundsson wrote: > >>> Any ideas giving BDR an option to be synchronous. I mean in ”close quarters” with low latency it should work ok. > >> > >> BDR doesn't do synchronous replication _yet_, but it's on the roadmap. > > > > Just to clarify: There's two things that sometimes are referred to as > > 'synchronous' in the context of multimaster replication. > > > > Just like with HS' builtin synchronous replication it can mean that the > > client doesn't get a reply until the COMMIT has safely been replicated > > to other systems. That's supported by BDR today. > > Would that be to a streaming replication synchronous standby? I.e replicated to a read only node? It's possible to do it to a streaming replication sync standby, but also to another BDR node. The logical decoding facility added in 9.4 allows logical replication solutions to use the same mechanism as streaming rep does. Greetings, Andres Freund -- Andres Freund http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services
On Sat, Jul 12, 2014 at 7:22 PM, Craig Ringer <craig@2ndquadrant.com> wrote:
> On 07/12/2014 02:24 PM, Martin Gudmundsson wrote:
>> 12 jul 2014 kl. 04:58 skrev Craig Ringer <craig@2ndquadrant.com>:
>>> PgJDBC actually already supports rudimentary client-based failover.
>>
>> I've seen some mailing list threads regarding this but was that ever implemented? Do you know where it's documented?
>
> I don't think the patch ever got accompanying documentation.
> In case you're looking, the code was added by git
> bddc05f939ac9227b682e85d1ba0a9b902da814c .
The documentation has been added with this recent commit, two years
after the feature:
commit c78aa91c82610bd131697d0f4ed152d8017f476b
Author: Patrick Radtke <pradtke@stanford.edu>
Date: Fri May 2 10:42:58 2014 -0700
Apply patch for failover documentation
The website is as well a bit outdated.
--
Michael
On 2014-07-12 14:37:02 +0200, Martin Gudmundsson wrote: > > It's possible to do it to a streaming replication sync standby, but also > > to another BDR node. The logical decoding facility added in 9.4 allows > > logical replication solutions to use the same mechanism as streaming rep > > does. > > > > Sounds interesting, but it does not sound like it’s being bi-directional in that case? It is bidirectional if you configure a bdr node in synchronous_standby_names. Note that streaming replication's synchronous mode probably doesn't give the guarantees you're thinking it does: a) A sent commit might succeed locally on the primary, but the system might fail to send it to the synchronous standby. I.e. postgres' synchronous replication of normal commits is *not* 2-safe. b) Even if a transaction is replicated to the standby it's *not* guaranteed to be applied. All that's guaranteed is that it has been shipped to the synchronous standby. > Ideally I’m looking for a solution where I can run an application and it does not really matter on what node a transactionexecutes, or if it changes data or not. I'd like that too, but I don't think that's going to happen. There's just too many caveats (CAP theorem et al) to make that generally useful/applicable. I think we (as in postgres) will probably get the ability to run individual transactions in such a mode, but you surely wouldn't want to run every transaction in it. Greetings, Andres Freund -- Andres Freund http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services
12 jul 2014 kl. 04:58 skrev Craig Ringer <craig@2ndquadrant.com>: > On 07/12/2014 02:42 AM, Martin Gudmundsson wrote: >> Hi all! >> I was wondering if there are any specific load balancing/failover functionality planned for client drivers connectionto a BDR group. In my case the jdbc driver, but could be relevant for other drivers as well. > > PgJDBC actually already supports rudimentary client-based failover. I’ve seen some mailing list threads regarding this but was that ever implemented? Do you know where it’s documented? > It's not very smart - it doesn't maintain any persistent state, so if a > host just vanishes it'll take a long time to connect to the next one as > it has to wait for a TCP connection timeout. Making it stateful would be > interesting, and may be more useful now that there's more reason to > bother with client-side failover. Making it reliable in the face of > classloader isolation, load/unload, etc will be "interesting", but it's > a problem we eventually need to solve anyway if we're to support > asynchronous notification callbacks. > > AFAIK libpq doesn't have any kind of failover - but if we added > something similar, it'd be transparent to drivers like psycopg2, the Pg > gem, etc that use libpq. I'm not sure how we'd go about making it > stateful though - API changes would likely be needed for that bit. > > psqlODBC would also need significant changes. > > >> Or is the long term plan that we need we need to rely on middleware like pgpool or other third party frameworks for this? > > At this point PgBouncer will likely be the way to go if you want to try > to make it client transparent, but I don't think that's a good idea. > > Because BDR is asynchronous multi-master _replication_ though, clients > are expected to be aware of some of the anomalies that can occur. A > naïve client that just picked a random BDR server and did the next > transaction on it would be very likely to cause unwanted replication > anomalies, apply conflicts, etc. > > For example, if the client inserted a row on one server then tried to > immediately update it on another, the update would likely fail because > the row probably hasn't replicated yet. Any ideas giving BDR an option to be synchronous. I mean in ”close quarters” with low latency it should work ok. > It is generally a good idea to make clients "sticky" to a given server, > but clients also need to be aware of replication anomalies unless each > server's data is a self-contained shard that doesn't interact with the > others. In which case you probably wouldn't be using BDR Great explanation. Making them sticky i most likely the most reasonable approach in our case. But it would be good to havea place to list of servers to try on startup and then stick to one. Perhaps the rudimentary support you mention abovewould do it? >> Having client awareness of the nodes ip, port numbers, status and current load, could probably bring powerful featuresto this as a whole. That is some how other database vendors take care of client failover and load balancing and hasin my experience proved to work very well. > > Yes, that'd certainly be interesting. > >> And not only for BDR, but also streaming replication setups, could use this to do automatic client failovers, in the casethere is a server side failover. >> >> Anyone who knows if there is anything in progress regarding this? > > Other than the limited failover already in place for PgJDBC I'm not > aware of anything. > > Work on its design, implementation and testing would be greatly > appreciated, so polish up your C skills. Phew, C oh C, that was a while a go :-) Thanks for your detailed answer. Kind regards, Martin > -- > Craig Ringer http://www.2ndQuadrant.com/ > PostgreSQL Development, 24x7 Support, Training & Services
Because BDR is asynchronous multi-master _replication_ though, clients
are expected to be aware of some of the anomalies that can occur. A
naïve client that just picked a random BDR server and did the next
transaction on it would be very likely to cause unwanted replication
anomalies, apply conflicts, etc.
For example, if the client inserted a row on one server then tried to
immediately update it on another, the update would likely fail because
the row probably hasn't replicated yet.
Any ideas giving BDR an option to be synchronous. I mean in ”close quarters” with low latency it should work ok.
That way we could get two synch writeable nodes in one datacenter(or 2 datacenter really close to each other. common in Europe), and can be combined with asynch nodes in more distant datacenters. That would enable load balancing between 2 nodes for all type of queries.
Synchrounous Streaming Replication is supported with BDR, but that only gives support for a read-only replica. It does not give a fully writeable node.
Figuring out what queries are readonly to direct to a readonly replica, is not the easiest thing, if I’ve understood documentation for pgpool etc correct.
It is generally a good idea to make clients "sticky" to a given server,
but clients also need to be aware of replication anomalies unless each
server's data is a self-contained shard that doesn't interact with the
others. In which case you probably wouldn't be using BDR
12 jul 2014 kl. 13:45 skrev Andres Freund <andres@2ndquadrant.com>: > On 2014-07-12 13:23:08 +0200, Martin Gudmundsson wrote: >> >> 12 jul 2014 kl. 12:33 skrev Andres Freund <andres@2ndquadrant.com>: >> >>> On 2014-07-12 18:22:30 +0800, Craig Ringer wrote: >>>> On 07/12/2014 02:24 PM, Martin Gudmundsson wrote: >>>>> Any ideas giving BDR an option to be synchronous. I mean in ”close quarters” with low latency it should work ok. >>>> >>>> BDR doesn't do synchronous replication _yet_, but it's on the roadmap. >>> >>> Just to clarify: There's two things that sometimes are referred to as >>> 'synchronous' in the context of multimaster replication. >>> >>> Just like with HS' builtin synchronous replication it can mean that the >>> client doesn't get a reply until the COMMIT has safely been replicated >>> to other systems. That's supported by BDR today. >> >> Would that be to a streaming replication synchronous standby? I.e replicated to a read only node? > > It's possible to do it to a streaming replication sync standby, but also > to another BDR node. The logical decoding facility added in 9.4 allows > logical replication solutions to use the same mechanism as streaming rep > does. > Sounds interesting, but it does not sound like it’s being bi-directional in that case? Is it just a standard streaming synchronous replication you setup for that? Ideally I’m looking for a solution where I can run an application and it does not really matter on what node a transactionexecutes, or if it changes data or not. With BDR in it’s current state, I need to look out for replication lag. That is what I was looking for a solution to whenI mentioned synchronous bdr in the beginning. Don’t get me wrong, BDR can solve a lot of problems for us in it’s current state. You’ve done a tremendous job on this. Butsome scenarios are tricky. > Greetings, > > Andres Freund > > -- > Andres Freund http://www.2ndQuadrant.com/ > PostgreSQL Development, 24x7 Support, Training & Services
>> >> I’ve seen some mailing list threads regarding this but was that ever implemented? Do you know where it’s documented? > > I don't think the patch ever got accompanying documentation. > > It's not mentioned here, anyway: > > http://jdbc.postgresql.org/documentation/93/connect.html > > Failover URLs are of the form: > > jdbc:postgresql://host1:port1,host2:port2/dbnjame > > with the port being optional. > > I'll document that now, before I get caught up in working on something else. > > In case you're looking, the code was added by git > bddc05f939ac9227b682e85d1ba0a9b902da814c . > Great! Will look have a look at the code. >> Any ideas giving BDR an option to be synchronous. I mean in ”close quarters” with low latency it should work ok. > > BDR doesn't do synchronous replication _yet_, but it's on the roadmap. > Right now BDR as a whole is in its early days; the feature is being > shaped by feedback and the requirements of sponsors and contributors. > It's changing quite quickly, which is one good reason most of it is > implemented as a plugin to PostgreSQL. Synchronous replication is one of > a number of things that the team has looked into but hasn't prioritized > implementing yet. > > Out of interest, what sorts of problems are you interested in solving > using BDR? What needs are you trying to meet? > > Are you looking to use it for scaling? For HA? For data > protection/redundancy? For transparent distributed databases > (client/node locality)? > > I'm curious about what part you see synchronous replication support for > BDR playing in any deployment you're contemplating. > One thing I’m thinking about a possible future replacement for other database vendors solutions. Solutions like DB2 DataSharingon z/OS and DB2 PureScale on Linux. They would be somewhat comparable to a synch multimaster setup, but of course only works in low latency environments. Butthey provide online upgrades. Perfromed one node at a time(only z/OS), and every node is equal. But they have centrallocking facilities, which creates other problems of course. But the applications would not need to care about if the transactions have been applied or not. The nodes are always in synch. Scaling is also something that has been on my mind for some apps that has intense write activity at times. BDR gives us thatbut currently we would need to watch out for potential situations where the data might not have been replicated to allnodes. Online scaling up and down a write intense environment without needing to worry if transactions have been appliedor not, would be neat. But I know it would put constraints on the infrastructure, and possibly also not give us complete HA protection in case ofa site failure, since the latency needs to be kept low. But if there could be a mix of synch and async nodes in a BDR group,it could cover more HA. Hope that explains a bit more what I’m thinking about. Currently I’m just looking at what BDR could give us here, so it’s nice to understand your plans. No real projects ongoingcurrently. Thanks for taking your time answering my questions. Best regards, Martin
12 jul 2014 kl. 14:48 skrev Andres Freund <andres@2ndquadrant.com>: > On 2014-07-12 14:37:02 +0200, Martin Gudmundsson wrote: >>> It's possible to do it to a streaming replication sync standby, but also >>> to another BDR node. The logical decoding facility added in 9.4 allows >>> logical replication solutions to use the same mechanism as streaming rep >>> does. >>> >> >> Sounds interesting, but it does not sound like it’s being bi-directional in that case? > > It is bidirectional if you configure a bdr node in > synchronous_standby_names. OK, I will play around with this a bit. Thanks for the hint. > Note that streaming replication's synchronous mode probably doesn't give > the guarantees you're thinking it does: > a) A sent commit might succeed locally on the primary, but the system > might fail to send it to the synchronous standby. I.e. postgres' > synchronous replication of normal commits is *not* 2-safe. > b) Even if a transaction is replicated to the standby it's *not* > guaranteed to be applied. All that's guaranteed is that it has been > shipped to the synchronous standby Very good info! I’m not sure that is so clear in the documentation but I have to revisit it. >> Ideally I’m looking for a solution where I can run an application and it does not really matter on what node a transactionexecutes, or if it changes data or not. > > I'd like that too, but I don't think that's going to happen. There's > just too many caveats (CAP theorem et al) to make that generally > useful/applicable. We are currently using solutions that works like that, DB2 Datasharing on z/OS is one example. But it’s not built around replication, but global filesystems and central locking facilities. That approach also have it’sissues, with performance bottlenecks etc. > I think we (as in postgres) will probably get the ability to run > individual transactions in such a mode, but you surely wouldn't want to > run every transaction in it. That sounds like o good trade off. Looking forward to it. > > Greetings, > > Andres Freund > > -- > Andres Freund http://www.2ndQuadrant.com/ > PostgreSQL Development, 24x7 Support, Training & Services
12 jul 2014 kl. 12:33 skrev Andres Freund <andres@2ndquadrant.com>: > On 2014-07-12 18:22:30 +0800, Craig Ringer wrote: >> On 07/12/2014 02:24 PM, Martin Gudmundsson wrote: >>> Any ideas giving BDR an option to be synchronous. I mean in ”close quarters” with low latency it should work ok. >> >> BDR doesn't do synchronous replication _yet_, but it's on the roadmap. > > Just to clarify: There's two things that sometimes are referred to as > 'synchronous' in the context of multimaster replication. > > Just like with HS' builtin synchronous replication it can mean that the > client doesn't get a reply until the COMMIT has safely been replicated > to other systems. That's supported by BDR today. Would that be to a streaming replication synchronous standby? I.e replicated to a read only node? > The more complex thing is support for performing transactions that, when > the COMMIT returns successfully, can't have conflicts. That's *not* yet > supported, but we're thinking of implementing it for individual > transactions. > > Greetings, > > Andres Freund > > -- > Andres Freund http://www.2ndQuadrant.com/ > PostgreSQL Development, 24x7 Support, Training & Services
> I think we (as in postgres) will probably get the ability to run > individual transactions in such a mode, but you surely wouldn't want to > run every transaction in it. > So, would then these transactions be ”2-phase”? I mean either all nodes commit the transaction or none of them do? Or only to those in the synchronous_standby_names? Perhaps to early to say? Been playing around with BDR all weekend, really interesting. > Greetings, > > Andres Freund > > -- > Andres Freund http://www.2ndQuadrant.com/ > PostgreSQL Development, 24x7 Support, Training & Services
13 jul 2014 kl. 21:54 skrev Martin Gudmundsson <martingudmundsson@gmail.com>: > >> I think we (as in postgres) will probably get the ability to run >> individual transactions in such a mode, but you surely wouldn't want to >> run every transaction in it. >> > > So, would then these transactions be ”2-phase”? Sorry, should be 2-safe, not 2-phase > > I mean either all nodes commit the transaction or none of them do? > Or only to those in the synchronous_standby_names? > > Perhaps to early to say? > > Been playing around with BDR all weekend, really interesting. > >> Greetings, >> >> Andres Freund >> >> -- >> Andres Freund http://www.2ndQuadrant.com/ >> PostgreSQL Development, 24x7 Support, Training & Services >
Hi! I wanted to test synchronous bi-dircetional replication using synchronous_standby_names with bdr. So I set this up as follows: Node alpha has the following settings in postgresql.conf port=5432 wal_level = 'logical' max_replication_slots = 3 max_wal_senders = 4 synchronous_standby_names=’beta' shared_preload_libraries = 'bdr' bdr.connections='beta' bdr.beta_dsn = ’dbname=bdrdemo username=postgres port=5433' track_commit_timestamp = on Node beta has the following settings in postgresql.conf port=5433 wal_level = 'logical' max_replication_slots = 3 max_wal_senders = 4 synchronous_standby_names=’alpha' shared_preload_libraries = 'bdr' bdr.connections=’alpha' bdr.alpha_dsn = ’dbname=bdrdemo username=postgres port=5432' track_commit_timestamp = on§ 1 bdr.alpha_init_replica = on bdr.alpha_replica_local_dsn = 'dbname=bdrdemo user=postgres port=5432’ It seems to work fine. Bringing down one node stalls the other one, just like synch rep should. But the view pg_stat_replication view shows async in sync_state column. Shouldn’t this really be sync? Kind regards, Martin
Hi! Never mind this one. Wrong config of synchronous replication by me. It looks good once I got the replication setup correctly. Kind regards, Martin 13 jul 2014 kl. 23:10 skrev Martin Gudmundsson <martingudmundsson@gmail.com>: > Hi! > I wanted to test synchronous bi-dircetional replication using synchronous_standby_names with bdr. > > So I set this up as follows: > Node alpha has the following settings in postgresql.conf > > port=5432 > wal_level = 'logical' > max_replication_slots = 3 > max_wal_senders = 4 > synchronous_standby_names=’beta' > shared_preload_libraries = 'bdr' > bdr.connections='beta' > bdr.beta_dsn = ’dbname=bdrdemo username=postgres port=5433' > track_commit_timestamp = on > > > Node beta has the following settings in postgresql.conf > > port=5433 > wal_level = 'logical' > max_replication_slots = 3 > max_wal_senders = 4 > synchronous_standby_names=’alpha' > shared_preload_libraries = 'bdr' > bdr.connections=’alpha' > bdr.alpha_dsn = ’dbname=bdrdemo username=postgres port=5432' > track_commit_timestamp = on§ 1 > bdr.alpha_init_replica = on > bdr.alpha_replica_local_dsn = 'dbname=bdrdemo user=postgres port=5432’ > > It seems to work fine. Bringing down one node stalls the other one, just like synch rep should. > > But the view pg_stat_replication view shows async in sync_state column. Shouldn’t this really be sync? > > > Kind regards, Martin > >