Re: Connection-fail-over

On 05/17/2012 08:36 PM, Dave Cramer wrote:
> On Thu, May 17, 2012 at 1:23 PM, Heikki Linnakangas
> <heikki(dot)linnakangas(at)enterprisedb(dot)com> wrote:
>> On 17.05.2012 20:00, Mikko Tiihonen wrote:
>>>
>>> Hi,
>>>
>>> I have been thinking of adding connection fail-over support to the jdbc
>>> driver. What this means is that there would be a way to specify more
>>> than host:port pair to connect to. This will become handy with
>>> high-availability installations.
>>>
>>> Currently the limitation of hostname means that there has to be a
>>> virtual ip address that is moved with the active backend, which is a
>>> complex solution that can break.
>>>
>>> If we have list of hostname:port pairs we just have to make sure that
>>> only the master database allows connections from the jdbc clients.
>>
>>
>> I'd suggest doing this as a generic wrapper or proxy that would work with
>> any JDBC driver, not just PostgreSQL's. Maybe there is such a thing out
>> there already? I believe many application servers at least can do that, and
>> then there's http://ha-jdbc.sourceforge.net/, which I think can do that too.
>>
>
> I would agree with Heikki here that it makes more sense to have a
> wrapper. I noticed that ha-jdbc has an lpgl license. How important is
> it to have a bsd like license ?

I did some googling and found out that

* all of the 'competitors' have decided to build failover support into their jdbc drivers
* big JavaEE servers have connection pool configurations that support failover
* none of common the open source connection pools or web servers support connection failover

Jdbc drivers that support connection failover:
- mysql: http://dev.mysql.com/doc/refman/5.0/en/connector-j-reference-configuration-properties.html
- oracle: http://docs.oracle.com/cd/B28359_01/java.111/e10788/rac.htm
- mssql: http://msdn.microsoft.com/en-us/library/aa342332%28v=sql.90%29.aspx
- db2: http://publib.boulder.ibm.com/infocenter/db2luw/v9/index.jsp?topic=%2Fcom.ibm.db2.udb.apdv.java.doc%2Fdoc%2Fcjvclrrt.htm

Of the normal jdbc connection pools
- c3p0: no support for failovers
- dbcp: no support for failovers
- bonecp: no support for failovers
- tomcat: no support for failovers

Application servers:
- Weblogic: has MultiPools that support failover
- Websphere: has Automatic Client Reroute
- JBoss: has failover support
- Glassfish: I could not find any built-in support for failover

About HA-JDBC: Yes, looking at the documentation it can be configured for the simple connection failover use case, but the complexity is 21k lines of Java code
which it uses to wrap the whole JDBC API. The latests discussions on the site were about deadlocks.

Now that postgresql 9.x has nice built-in replication, the need for simple setup for client applications will become more important. And I believe that with
less than 50 lines of code we can have a simple solution that works in most setups and allows the developer to choose any of the common java connection pools.

Thus, I believe that it would be useful to have simple failover support in the jdbc driver.

-Mikko