[pgpool-general: 1168] Re: JDBC sessions close when PGPool switchs between primary/standby

[Tatsuo Ishii]

> Hi again,
> 
> This is probably a question that needs some good knowledge of PGPool.
> 
> We have a Java web application running on a Postgres database.
> We installed PGPool II (3.2 Stable checked out directly from the repository) and setup Master/Slave mode and Failover with Streaming Replication (we use 2 Postgres servers).
> We can tell from PGPoolAdmin that the above setup works, as we can promote the standby server when the primary one goes down.
> Finally, we changed the jdbc url to connect to the PGPool port.
> 
> The problem we face has to do with the client sessions to PGPool that our Java application utilizes.
> When a server node is detached, these sessions are closed, and the application ends up using terminated by the server connections.
> Once we restart our application it works fine.
> 
> So, we wonder if there is a way that sessions to pgpool stay alive while pgpool switches between servers? I guess that, even if we change our application to use one new connection for each database operation, then there is still the danger that some transactions hang.
> 
> Thank you in advanced for you time on this,

This is many times requested but I don't see any resonable techinique
to enable it so far. The issue you pointed is one of the concerns. The
other is, session variable set by SET command. When pgpool reconnects
to PostgreSQL pgpool should restore those session variables set in the
previous session.
--
Tatsuo Ishii
SRA OSS, Inc. Japan
English: http://www.sraoss.co.jp/index_en.php
Japanese: http://www.sraoss.co.jp