On 08/14/2014 05:45 PM, Craig Ringer wrote:
> Wouldn't that force client drivers - libpq, psqlODBC, PgJDBC, etc - to
> all watch for explicit "COMMIT"s sent by the application and rewrite them?
Realistically, users are going to need new drivers to take advantage of
any automated connection failover anyway.
> Full automatic transparent failover _will_ be complex on the client. No
> denying that. The hard parts are picking which node to connect to when
> one goes away, the decision making around what to do when the new node
> fails to catch up to the last committed state on the old node, and
> tracking session state.
Frankly, I'd love to see just the simplest version of this implemented
in libpq as a start: the ability for client drivers to take a list of
hosts instead of a singe hostaddr (this was discussed at the 2013
clustering meeting).
> There are some quite simple uses too though. The main one of interest to
> me is an app that routes read-only queries to an async read-replica and
> wants to guarantee that some of them see a state consistent with the
> last commit on the master.
>
> It's the first thing many people have asked me about BDR, though. "How
> does client-side failover work". This is a priority for a lot of people.
> As far as I can see, if you have client-side failover with asynchronous
> replication of any form, the client _must_ have some way to reliably
> connect to a new node and ask it "are you caught up to the state of the
> last node I was connected to yet?". Or "Please wait until the last
> transaction I committed elsewhere is visible here".
There are quite a few use-cases where this information isn't required;
even for BDR, I'd love to see the ability to disable this check.
There's also cases where it's not adequate; the user may not have
committed anything on the master, but they still don't want to connect
to a replica which is hours behind the last node they queried.
There's also use-cases for which automated connection failover without a
managed proxy is a Seriously Bad Idea. For one thing, you're setting up
a strong risk of split-brain.
--
Josh Berkus
PostgreSQL Experts Inc.
http://pgexperts.com
