Reconnecting to a postgres database after postgres restart from Java

Question

I'm 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 like org.postgresql.util.PSQLException: FATAL: terminating connection due to administrator command.

How can I make it so the connections automatically re-connect to the restarted database?

Answer 1

DBCP has a connection validation query option - validationQuery, according to the docs. You can set it to something like SELECT 1; and DBCP will run that before returning a connection to test it.

That said, your app really should handle this case. SQL queries can fail for all sorts of reasons and you should always do your queries in a retry loop with a time back-off and retry limit, plus some logic to decide what exceptions are recoverable on retry and which aren't (use the SQLState for that).

In particular, validation is subject to a race condition where you can have event orderings like:

1. Validate
2. Pass connection to app
3. Server shutdown
4. App runs first statement

or

1. Validate
2. Pass connection to app
3. App runs first statement, opening a transaction
4. Server shutdown
5. App runs second statement

... so it remains important for your app to have a proper retry loop and good transaction handling.

You can get the SQLState from the SQLException: SQLException.getSQLState. The codes for PostgreSQL are in the PostgreSQL manual.