Обсуждение: Connection-fail-over
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
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
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
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
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