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

[Tatsuo Ishii]

Can you connect to 1 th backend by using jdbc or psql?
What are backend_* parameters in pgpool.conf?
--
Tatsuo Ishii
SRA OSS, Inc. Japan
English: http://www.sraoss.co.jp/index_en.php
Japanese: http://www.sraoss.co.jp

> Hi, 
> 
> Please find attached the PGPool log file.
> The error occurred at 07:58:22.
> 
> I think this message:
> 2012-10-03 07:58:22 LOG:   pid 2114: pool_read_kind: error message from 1 th backend:database "notus" does not exist
> is misleading, as the database 'notus' does exist. We can connect to it using the Postgres port (5432) in the jdbc url.
> 
> Again thank you all for your quick responses.
> 
> Regards,
> Stelios
> 
> -----Original Message-----
> From: Tatsuo Ishii [mailto:ishii at postgresql.org] 
> Sent: 03 October 2012 00:06
> To: Stelios Limnaios
> Cc: pgpool-general at pgpool.net
> Subject: Re: [pgpool-general: 1063] JDBC, PGPOOL and unexpected EOF on client connection
> 
> 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
>> 
>>