[pgpool-general: 1063] JDBC, PGPOOL and unexpected EOF on client connection

[Stelios Limnaios]

Hi,

I kindly need your help and expertise on a strange situation we're facing when using PGPool II.

Our setup is 2 Centos 5.3 servers with installed Postgres 9.1 (on port 5432) and PGPool II 3.2.0.0 (on port 9999).
What we want to do, is to establish replication between the 2 servers, with one to serve as the main server and the other as the standby.
To achieve that, we followed the instructions in this tutorial:
http://pgpool.projects.postgresql.org/pgpool-II/doc/tutorial-en.html
and we managed to successfully establish replication.
When we run (as postgres)
  createddb -p 9999 dbname
on the main server then we can see the changes on the standby server.
So far, so good.

The problem we've got is when Java come into play.
For some reason Java applications are not able establish a connection to PGPool but are able to do it to the Postgres server.
For example, connecting to jdbc:postgresql://localhost:9999/dbname  and running SELECT VERSION() throws an error.
But, if we connect to jdbc:postgresql://localhost:5432/dbname we get back some results.

The error in Postgres log is:
LOG:  unexpected EOF on client connection

This is strange, as createdb  -p 9999 connects to PGPool with no problem.

So, we set loglevel=2 to the jdbc url in order to get some more advanced network messages and we got:
PostgreSQL 9.1 JDBC4 (build 901)
Trying to establish a protocol version 3 connection to localhost:9999
FE=> StartupPacket(user=postgres, database=dbname, client_encoding=UTF8, DateStyle=ISO, extra_float_digits=2)
<=BE AuthenticationOk
org.postgresql.util.PSQLException: The connection attempt failed.
        at org.postgresql.core.v3.ConnectionFactoryImpl.openConnectionImpl(ConnectionFactoryImpl.java:152)
        at org.postgresql.core.ConnectionFactory.openConnection(ConnectionFactory.java:66)
        at org.postgresql.jdbc2.AbstractJdbc2Connection.<init>(AbstractJdbc2Connection.java:125)
        at org.postgresql.jdbc3.AbstractJdbc3Connection.<init>(AbstractJdbc3Connection.java:30)
        at org.postgresql.jdbc3g.AbstractJdbc3gConnection.<init>(AbstractJdbc3gConnection.java:22)
        at org.postgresql.jdbc4.AbstractJdbc4Connection.<init>(AbstractJdbc4Connection.java:32)
        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(DriverManager.java:582)
        at java.sql.DriverManager.getConnection(DriverManager.java:185)
        at replication.Main.printVersion(Main.java:41)
        at replication.Main.main(Main.java:23)
SQLException: SQLState(08001)
getConnection failed: org.postgresql.util.PSQLException: The connection attempt failed.

We looked for SQLState (08001) and for Postgres it means that 'sql client unable to establish connection'.
And this is where we ran out of ideas.

So, to summarize, we're in a situation where replication has been established successfully on port 9999.
Java (jdbc) can connect with success to Postgres (5432) but not to PGPool (9999), as the connection gets terminated for some reason right after AuthenticationOK is sent to the client.
(I don't know if that is important to mention but we have opened ports 5432, 9999 and 9898 for tcp connections on both server firewalls)

Do you have any ideas what is causing this problem?
Any help and directions will be much appreciated.


Kind regards,
Stelios


-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://www.sraoss.jp/pipermail/pgpool-general/attachments/20121002/867a6c85/attachment-0001.html>