Re: Bi-Directional replication client awareness

On 07/12/2014 02:24 PM, Martin Gudmundsson wrote:
>
> 12 jul 2014 kl. 04:58 skrev Craig Ringer <craig(at)2ndquadrant(dot)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 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?

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