Reconnecting to postgres database after restarting postgres with Java

Question

I am using postgres 9.1, org.apache.commons.dbcp.BasicDataSource (for my connection pool) and Java 1.7. When I restart my postgres server, I get exceptions such as org.postgresql.util.PSQLException: FATAL: terminating connection due to administrator command .

How can I do this so that connections automatically reconnect to a restarted database?

Answer 1

DBCP has an option to request connection validationQuery - validationQuery , according to the docs . You can set it to something like SELECT 1; , and DBCP will run this before returning the connection to verify it.

However, your application really needs to handle this case. SQL queries can fail for various reasons, and you should always run your queries in a retry loop with time and retry limits, as well as some logic to decide which exceptions can be recovered by retrying and which are not (use SQLState to of this).

In particular, validation is subject to a race condition where you can have an order of events, for example:

• Validate
• Skip connection to application
• Server shutdown
• The application launches the first statement

or

• Validate
• Skip connection to application
• The application launches the first statement, opening a transaction
• Server shutdown
• Application launches second statement

... therefore, it is important that your application has the correct retry cycle and good transaction processing.

You can get SQLState from SQLException: SQLException.getSQLState . Codes for PostgreSQL in the PostgreSQL manual .

Answer 2

In this particular case, the PostgreSQL connection tells you that the server was disconnected after the connection was created. The DBCP connection pool does not handle this condition with its default configuration.

Even if you set the validationQuery parameter to something like SELECT 1, it will not be used unless you also set at least one of the testOnXXXX parameters.

I usually set both testOnCreate and testOnBorrow .

Also check the other default values ​​for DBCP (in org.apache.commons.pool2.impl.BaseObjectPoolConfig), because, in my opinion, they are not very suitable for production environments.