[Postgres-xc-general] a new jdbc driver for postgres-xc?

[Michael V. <mic...@ar...>]

Thanks for the responses.  I'd like to pursue the jdbc driver issue for Postgres-xc a bit more...

Regarding the jdbc driver for Postgresql, it would be neat if we could take a current cut of the postgresql jdbc driver and customize it for postgres-xc, so that the input connection string could contain an array of coordinator addresses, just like oracleRAC, so we could have "automatic failover" at the coordinator node level embedded right into the jdbc driver.  Consider new jdbc pseudo code:

If sql connect error encountered, see if the connection string contains another coordinator address and if so, connect to that coordinator and set its handle globally to replace the original connection handle so other threads will use it as well for future IO requests to the database.

Now of course this doesnt handle failover at the data node level, but is seems like it would sure do the trick at the coordinator node level.

Now let me compare this new postgres-xc jdbc driver to HA failover already embedded in JBOSS AS 7.  It would be much better, because while JBOSS would allow you to input multiple jdbc connection strings (using the vertical bar separator), it does not break down bad connections and automatically reconnect those clients to the other one.  Instead it will just make sure that future connection handle requests are directed to the other one.  Already connected users would have to manually logoff from their applications and log on again.  But if we embed multiple coordinators into a new Postgres-xc jdbc driver, then the driver will break down the bad connections and automatically connect those users to the other cordinators without the users having to do anything!

Although I'm calling this a new jdbc driver for postgres-xc, the concept would work the same for postgresql and postgres-xc: jdbc driver allows specification of multiple database connection parameters.

What you all think?  And if you all know where this would be useful to a postgresql jdbc community email address, please forward this.

Respectfully Yours,
Michael Vitale

________________________________________
From: Michael Paquier [mic...@gm...]
Sent: Friday, June 08, 2012 8:30 AM
To: Michael Vitale
Cc: Postgres-XC mailing list
Subject: Re: [Postgres-xc-general] An article about XC at PgCon

On 2012/06/08, at 20:19, Michael Vitale <mic...@ar...> wrote:

> How do you undo an initdb?  In the case where I want to redefine the initdb differently.  Also, I did the gmake uninstall, but it didn't remove any binary installed files.
Let's take the problems one-by-one.
For initdb, you need to delete the data folder and then recreate it. I don't think Postgres can redefine a data folder with initdb and just returns an error telling that existing data folder is not empty.
gmaje uninstall should work. At least in the environments used here it does.
>
> Also, does postgres-xc work well as a High Availability solution, i.e., automatic failover?
You will need to plug in yourself slave nodes to your master nodes and having  a monitoring engine that performs failover. You got the same problems with Postgres. Its core features cannot perform a failover themselves and you always need to kick something to perform a failover in case of a node failure.
>
> I come from an oracle RAC world, and I am playing with postgres-xc 1.0.0 on 2 centos 6, 64 bit boxes.
>
> Oracle has failover embedded in its listener configuration and in its jdbc driver.  For instance, the oracle jdbc driver for our jboss server is something like this:
>
> <connection-url>jdbc:oracle:thin:@(description=(address_list=(load_balance=on)(failover=off)(address=(protocol=tcp)(host=odb0-vip.arin.net)(port=1521))(address=(protocol=tcp)(host=odb1-vip.arin.net)(port=1521)))(connect_data=(service_name=pjpn.arin.net)(failover_mode=(type=select)(method=basic))))</connection-url>
>
> Note that 2 oracle nodes are specified right within the oracle jdbc driver.  For normal PostgreSQL, the driver would look something like this:
> <connection-url>jdbc:postgresql://dbproto1.arin.net:5432/pjpn</connection-url>
>
> So, I'm wondering if there is already a way to specify more than one cordinator in the jdbc connection string for connecting to postgres-xc?
I am not a specialist of jdbc, but I don't think you can specify multiple connection points in.
>
> If not, then I reckon its up to me to implement a vip switched node that would automatically point to another cordinator if the first vip went down.
Yep think so. The core features of xc should be sufficient for a correct failover.
>
> Comments, suggestions?
>
>
> ------------------------------------------------------------------------------
> Live Security Virtual Conference
> Exclusive live event will cover all the ways today's security and
> threat landscape has changed and how IT managers can respond. Discussions
> will include endpoint security, mobile security and the latest in malware
> threats. http://www.accelacomm.com/jaw/sfrnl04242012/114/50122263/
> _______________________________________________
> Postgres-xc-general mailing list
> Pos...@li...
> https://lists.sourceforge.net/lists/listinfo/postgres-xc-general