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?

Use a try-catch to catch the exception (checking e.getMessage() for it having actually been a server restart), and then reconnect to the database from within the catch statement or another block of code triggered by it. — FThompson
– FThompson, Commented Jun 4, 2013 at 19:40
@Vulcan Do not check the error message; use the SQLSTATE you can get from the PSQLException to check the cause of the issue. Otherwise you'll get fun problems when the message is reworded in a new version or someone's running Pg in a different language. — Craig Ringer
– Craig Ringer, Commented Jun 4, 2013 at 23:48
@CraigRinger Good point. I wasn't aware that PSQLException had such a field (I've never used PSQL before). It should definitely be used instead of the message, you're right. — FThompson
– FThompson, Commented Jun 4, 2013 at 23:54
@Vulcan getSQLState() is an SQLException method, and is standard across all DBs. never parse the error message - for any DB, not just Pg. That's what the SQLState is for. The message classes are fairly standard, too. — Craig Ringer
– Craig Ringer, Commented Jun 4, 2013 at 23:55
@CraigRinger Ah, my bad. I've only used JDBC briefly in the past. I never parse exception messages in any case, but I wasn't aware of getSQLState(). Thanks. — FThompson
– FThompson, Commented Jun 4, 2013 at 23:57

Craig Ringer · Accepted Answer · 2013-06-06 12:51:47Z

5

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:

Validate
Pass connection to app
Server shutdown
App runs first statement

or

Validate
Pass connection to app
App runs first statement, opening a transaction
Server shutdown
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.

edited Jun 6, 2013 at 12:51

answered Jun 4, 2013 at 23:45

Craig Ringer

329k84 gold badges742 silver badges820 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

three-cups Over a year ago

thanks for your answer. Will this test (the validationQuery) remove connections from the pool?

Craig Ringer Over a year ago

@three-cups Yes, otherwise it'd serve no purpose. It'll cause a connection to be discarded and replaced with a new one. Your app still needs to correctly handle retries - since you might shut the server down halfway through a transaction or between validation and the start of a transaction - but validation is a useful way of discarding known-bad connections.

three-cups Over a year ago

The only thing I don't like about it is that it adds an extra database query to on every connection borrow from the pool. I'd rather handle the exceptional case when it comes up.

Craig Ringer Over a year ago

@three-cups That's my view too - since you have to handle the exceptional cases anyway, why have the validation query? You seemed to be asking for how to validate the connections, though, so that's what I explained.

Oleg Muravskiy · Accepted Answer · 2015-04-09 09:03:55Z

3

In this particular case the PostgreSQL connection is telling you that the server was shut down after the connection was created. DBCP connection pool does not handle this condition with it's default configuration.

Even if you set 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 to true.

Also check other defaults of DBCP (in the org.apache.commons.pool2.impl.BaseObjectPoolConfig), because in my opinion they are not well suited for production environments.

answered Apr 9, 2015 at 9:03

Oleg Muravskiy

7856 silver badges8 bronze badges

Collectives™ on Stack Overflow

Reconnecting to a postgres database after postgres restart from Java

2 Answers 2

4 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related