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

[Tatsuo Ishii]

Can you show us pgpool log?
--
Tatsuo Ishii
SRA OSS, Inc. Japan
English: http://www.sraoss.co.jp/index_en.php
Japanese: http://www.sraoss.co.jp

> 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
> 
>