DSpace, JDBC PostgreSQL Connector and tcpKeepAlive

[Patrick Rynhart]

Our University data network has recently been segmented, and there is now a firewall between our frontend DSpace Servers and the backend PostgreSQL DB Server.  The problem that we're seeing is that the firewall will drop/close connections after a period of inactivity (60 minutes), but the client (DSpace) seems to think they are still open.  Our DSpace servers are low volume and we are reaching this timeout overnight, which is resulting in intermittent performance problems with pages taking minutes to load the next morning.  (A tomcat thread dump shows WAITING on org.postgresql.core.v3.QueryExecutorImpl.execute when this issue occurs.)

Ideally we would like to send a keep-alive from the client side.  It seems that the JDBC PostgreSQL connector used by DSpace can do this (https://jdbc.postgresql.org/documentation/head/connect.html) with the tcpKeepAlive parameter.  However, we are not sure whether we can embed / include this somehow in the db.url option of dspace.cfg ?  (A look at dspace-api/src/main/java/org/dspace/core/HibernateDBConnection.java suggests that DSpace will only allow a small number of properties, such as "max connections" to be sent.)

Is there any way we can specify this value ?

With Thanks,

Patrick Rynhart

[Mark Wood]

You could try adding it to the db.url value:  "db.url = jdbc:postgresql://localhost:5432/DS-3176?tcpKeepAlive=true"

I'd be more inclined to configure the connection pool to test connections before use, but that is a bit more involved:  you'd need to explicitly declare to Tomcat a <Context> for DSpace (if you are not already doing that) and enclose a <Resource> element which describes the connection pool.  Then you can configure the pool using the full range of its properties, including testOnBorrow and validationQuery.  If this all sounds completely new to you, I'd try the URL method first.