12 jul 2014 kl. 04:58 skrev Craig Ringer <craig@xxxxxxxxxxxxxxx>: > 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 to 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 have a place to list of servers to try on startup and then stick to one. Perhaps the rudimentary support you mention above would do it? >> Having client awareness of the nodes ip, port numbers, status and current load, could probably bring powerful features to this as a whole. That is some how other database vendors take care of client failover and load balancing and has in 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 case there 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
