Thread: Connection-fail-over

Connection-fail-over

From
Mikko Tiihonen
Date:
Hi,

I have been thinking of adding connection fail-over support to the jdbc driver. What this means is that there would be
away 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
thejdbc clients. 

Before I implement this I would like to first ask opinions on how the connection url should look like.

Current syntax is:

jdbc:postgresql://hostname:port/database?options

Which of these should we support:

A) jdbc:postgresql://hostname1:port1,hostname2:port2/database?options
B) jdbc:postgresql://hostname1,hostname2:port/database?options
C) jdbc:postgresql://hostname1:port1/database?&failoverhost=hostname2:port2&options
D) some other?

-Mikko

Re: Connection-fail-over

From
Heikki Linnakangas
Date:
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.

--
   Heikki Linnakangas
   EnterpriseDB   http://www.enterprisedb.com

Re: Connection-fail-over

From
Dave Cramer
Date:
On Thu, May 17, 2012 at 1:23 PM, Heikki Linnakangas
<heikki.linnakangas@enterprisedb.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 ?


Dave Cramer

dave.cramer(at)credativ(dot)ca
http://www.credativ.ca

Re: Connection-fail-over

From
Mikko Tiihonen
Date:
On 05/17/2012 08:36 PM, Dave Cramer wrote:
> On Thu, May 17, 2012 at 1:23 PM, Heikki Linnakangas
> <heikki.linnakangas@enterprisedb.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
thecomplexity 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
moreimportant. 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
anyof the common java connection pools. 

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

-Mikko

Re: Connection-fail-over

From
Jesper Pedersen
Date:
On 05/18/2012 07:34 AM, Mikko Tiihonen wrote:
> Thus, I believe that it would be useful to have simple failover support
> in the jdbc driver.

It is true that the application servers out there (we use
<url-delimiter> to separate servers) support this functionality.

However, I think that Mikko's suggestion is valid for applications that
aren't backed by more advanced solutions.

Maybe use special classes that extend the current ones
(driver/datasource/...) that supports this functionality such that users
can explicit choose to enable/disable it.

Best regards,
  Jesper