[MARMOTTA-624] Apache Marmotta refusing to load queries - Postgresql No remaining connection

• Type: Bug
• Status: In Progress
• Priority: Major
• Resolution: Unresolved
• Affects Version/s: 3.3.0, 3.4.0
• Fix Version/s: 3.5.0
• Labels: bug postgresql

Environment

Postgresql: 9.3.10
Server version: Apache Tomcat/7.0.52 (Ubuntu)
OS Name: Ubuntu 14.04
OS Version: 3.13.0-74-generic
Architecture: amd64
JVM Version: 1.7.0_91-b02
JVM Vendor: Oracle Corporation
Marmotta: 3.3.0

• Flags: Important
• Assignee: Sergio Fernández
• Reporter: Rinciog Octavian
• Created: 20/Jan/16 11:25
• Updated: 09/Jun/17 10:50

Description

Hello,

I'm running an Apache Marmotta 3.3.0, configured with postgresql 9.3.10 and Tomcat 7, and from time to time, the resources from Marmotta cannot be accessed.

In tomcat logs the following lines appear :

0:10:19.937 ERROR - Error retrieving the resource XXX: database error, could not load URI resource
10:10:27.168 ERROR - database error, could not load URI resource
org.postgresql.util.PSQLException: FATAL: remaining connection slots are reserved for non-replication superuser connections

Right now, in /etc/postgresql/9.3/main/postgresql.conf I have the following setting:

- max_connections = 100

There are 97 opened connection to postgresql, all from tomcat process

root@test# netstat -ap  | grep postgresql 
tcp6       0      0 localhost:33756         localhost:postgresql    ESTABLISHED 22632/java      
tcp6       0      0 localhost:33733         localhost:postgresql    ESTABLISHED 22632/java      
tcp6       0      0 localhost:33757         localhost:postgresql    ESTABLISHED 22632/java      
...

root@test# netstat -ap  | grep postgresql | wc -l
97

root@test# ps -ef | grep 22632
tomcat7  22632     1  0 Jan13 ?        00:14:40 /usr/lib/jvm/default-java/bin/java -Djava.util.logging.config.file=/var/lib/tomcat7/conf/logging.properties -Djava.util.logging.manager=org.apache.juli.ClassLoaderLogManager -Djava.awt.headless=true -Xmx2048m -XX:+UseConcMarkSweepGC -Djava.endorsed.dirs=/usr/share/tomcat7/endorsed -classpath /usr/share/tomcat7/bin/bootstrap.jar:/usr/share/tomcat7/bin/tomcat-juli.jar -Dcatalina.base=/var/lib/tomcat7 -Dcatalina.home=/usr/share/tomcat7 -Djava.io.tmpdir=/tmp/tomcat7-tomcat7-tmp org.apache.catalina.startup.Bootstrap start

Also from pg_top output:

  PID USERNAME PRI NICE  SIZE   RES STATE   TIME   WCPU    CPU COMMAND
23249 postgres  20    0  246M  125M sleep   0:26  0.00%  0.00% postgres: X X 127.0.0.1(33677) idle in transaction                                                         
23217 postgres  20    0  246M  125M sleep   0:20  0.00%  0.00% postgres: X X 127.0.0.1(33675) idle in transaction                                                         
23446 postgres  20    0  247M  129M sleep   0:19  0.00%  0.00% postgres: X X 127.0.0.1(33683) idle in transaction     

But there is no live connection from an outside point to tomcat.

After tomcat restart, all is very good, but in a couple of hours, the problems appears again.

So, how can I fix this problem?

Thank you,