Fail-over PG server in connection string - syntax ?

Hi all.

We have 2 datacenters in diferent locations with public IP addresses each from different subnets. We would like to use Posgtres JDBC driver fail-over capability to switch over java client to secondary datacenter if the first one is unavailable. We need only basic functionality. We cannot use pgpool-II or something similar at the server side because all these type of softwares use one virtual IP address which in many cases is from one subnet.

There should be fail-over capability available in the JDBC version 9.2-1000 according to changelog: http://jdbc.postgresql.org/changes.html#version_9.2-1000

The documentation doesn’t mention how to build fail-over connection string at all. I’ve found in following mailing list thread mentioning the structure of the connection string:

http://postgresql.1045698.n5.nabble.com/Re-Failover-Datasource-td4496411.html#a5736270

I tried fail-over by using the version 9.2-1002 in the following example.

I tried to build easy test java script according to this site: http://www.dotnetfunda.com/Blogs/Niladri.Biswas/3743/connect-to-postgresql-from-java-using-jdbc-driver

The java file then looks like this…

import java.sql.*;

public class JavaPostgreSQLConnectivityTest

{

    public static void main(String[] args)

    {

                String connectionString = "jdbc:postgresql://pgsqldc1:5432/test,pgsqldc2:5432/test";

                String userName = "postgres";

                String password = "postgres";

               try

        {

            Class.forName("org.postgresql.Driver"); //specify the JDBC driver for PostgreSQL

            Connection conn = DriverManager.getConnection(connectionString, userName, password);

            System.out.println("Connection properly established");

            Statement st = conn.createStatement();

                                                ResultSet rs = st.executeQuery("SELECT * FROM mytable ORDER BY created DESC LIMIT 5");

                                                                while (rs.next()) {

                                                                                System.out.print("Column 1 returned ");

                                                                                System.out.println(rs.getString(1));

                                                                }

                                                rs.close();

                                                st.close();

        }

        catch (Exception e)

        {

            e.printStackTrace();

        }

    }

}

When I ran the java compiled class I got this…

D:\temp\Scripts>java -cp postgresql-9.2-1003.jdbc4.jar; JavaPostgreSQLConnectivityTest

org.postgresql.util.PSQLException: FATAL: database "test,pgsqldc2:5432/test" does not exist

        at org.postgresql.core.v3.ConnectionFactoryImpl.readStartupMessages(ConnectionFactoryImpl.java:572)

        at org.postgresql.core.v3.ConnectionFactoryImpl.openConnectionImpl(ConnectionFactoryImpl.java:177)

        at org.postgresql.core.ConnectionFactory.openConnection(ConnectionFactory.java:64)

        at org.postgresql.jdbc2.AbstractJdbc2Connection.<init>(AbstractJdbc2Connection.java:136)

        at org.postgresql.jdbc3.AbstractJdbc3Connection.<init>(AbstractJdbc3Connection.java:29)

        at org.postgresql.jdbc3g.AbstractJdbc3gConnection.<init>(AbstractJdbc3gConnection.java:21)

        at org.postgresql.jdbc4.AbstractJdbc4Connection.<init>(AbstractJdbc4Connection.java:31)

        at org.postgresql.jdbc4.Jdbc4Connection.<init>(Jdbc4Connection.java:24)

        at org.postgresql.Driver.makeConnection(Driver.java:393)

        at org.postgresql.Driver.connect(Driver.java:267)

        at java.sql.DriverManager.getConnection(Unknown Source)

        at java.sql.DriverManager.getConnection(Unknown Source)

        at JavaPostgreSQLConnectivityTest.main(JavaPostgreSQLConnectivityTest.java:15)

What is the correct syntax for building connection string? Should the fail-over within postgres-jdbc driver work?

Best regards,

Michal Mistina