org.postgresql.util.PSQLException: FATAL: remaining connection slots are reserved for non-replication superuser connections

Sakai JIRA | Jonathan Blood | 12 months ago
  1. 0

    I have a DSpace instance which started to encounter the following error when cronjobs started to run: {quote}org.postgresql.util.PSQLException: FATAL: remaining connection slots are reserved for non-replication superuser connections at org.postgresql.core.v3.ConnectionFactoryImpl.readStartupMessages(ConnectionFactoryImpl.java:471) at org.postgresql.core.v3.ConnectionFactoryImpl.openConnectionImpl(ConnectionFactoryImpl.java:112) at org.postgresql.core.ConnectionFactory.openConnection(ConnectionFactory.java:66) at org.postgresql.jdbc2.AbstractJdbc2Connection.<init>(AbstractJdbc2Connection.java:125) at org.postgresql.jdbc3.AbstractJdbc3Connection.<init>(AbstractJdbc3Connection.java:30) at org.postgresql.jdbc3g.AbstractJdbc3gConnection.<init>(AbstractJdbc3gConnection.java:22) at org.postgresql.jdbc4.AbstractJdbc4Connection.<init>(AbstractJdbc4Connection.java:32) at org.postgresql.jdbc4.Jdbc4Connection.<init>(Jdbc4Connection.java:24) at org.postgresql.Driver.makeConnection(Driver.java:393) at org.postgresql.Driver.connect(Driver.java:267) at java.sql.DriverManager.getConnection(DriverManager.java:571) at java.sql.DriverManager.getConnection(DriverManager.java:215) at org.apache.commons.dbcp.DriverManagerConnectionFactory.createConnection(DriverManagerConnectionFactory.java:75) at org.apache.commons.dbcp.PoolableConnectionFactory.makeObject(PoolableConnectionFactory.java:582) at org.apache.commons.pool.impl.GenericObjectPool.borrowObject(GenericObjectPool.java:974) at org.apache.commons.dbcp.PoolingDataSource.getConnection(PoolingDataSource.java:106) at org.dspace.storage.rdbms.DatabaseManager.getConnection(DatabaseManager.java:601) at org.dspace.core.Context.init(Context.java:121) at org.dspace.core.Context.<init>(Context.java:95) at ie.enovation.cli.bulktasks.UpdateFeedCollections.main(UpdateFeedCollections.java:29) at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57) at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) at java.lang.reflect.Method.invoke(Method.java:606) at org.dspace.app.launcher.ScriptLauncher.runOneCommand(ScriptLauncher.java:225) at org.dspace.app.launcher.ScriptLauncher.main(ScriptLauncher.java:77){quote} When this issue occurred I ran: {quote}SELECT * FROM pg_stat_activity{quote} I could see the max_connections limit was reached. I tried to use this query and map times to the DSpace logs and see if I can see any potential issues. From the initial investigation I couldn't see anything which stood out. So I decided to add additional logging to DSpace to count active connections every single time a query is made in DSpace. I did this by modifying _DatabaseManager_ class and reporting this to a separate log file using log4j in order to make it easier to read since it's quite an active DSpace instance. When I deployed this change and restarted Tomcat the issue disappeared as expected. After about two months the issue reappeared again. The logging I added showed the number of active connections was low. But if I did the following query again: {quote}SELECT * FROM pg_stat_activity{quote} I could see number of connections was at the limit. I mapped the timings from the query to DSpace logs and I could see a bit more of a trend. I noticed a lot of new connections were created straight after each other in rapid succession after multiple requests in a short span of time to view a bitstream (crawler activity). This was the most common pattern but it did happen when accessing other pages such as "Browse by title" etc but not as much. In order to try and replicate the issue I set up DSpace locally. Before each test case I restart Tomcat and run SELECT * FROM pg_stat_activity to confirm DSpace has released the connections. * TEST CASE 1* In _dspace.cfg_: db.maxconnections = 80 db.maxwait = 5000 db.maxidle = -1 In postgres configuration I have max connections set to 100. To replicate a number of similar requests that I was seeing from crawler activity I used some bash scripting to do something simple like: {quote} for i in {1..200} do wget localhost:8080/dspace/xmlui/bitstream/handle/10395/2050/test.txt?sequence=2&isAllowed=y done {quote} Before running test case: Query: SELECT * FROM pg_stat_activity Result: 3 rows Custom active connection count logger: 2015-12-08 11:16:26,007 INFO 2015-12-08 11:35:37,830 INFO org.dspace.storage.rdbms.DatabaseManager - Number of active connections 1 After running test case: Query: SELECT * FROM pg_stat_activity Result: 92 rows all in state idle Custom active connection count logger (general pattern): 2015-12-08 11:32:22,523 INFO org.dspace.storage.rdbms.DatabaseManager - Number of active connections 3 2015-12-08 11:32:22,527 INFO org.dspace.storage.rdbms.DatabaseManager - Number of active connections 2 2015-12-08 11:32:22,529 INFO org.dspace.storage.rdbms.DatabaseManager - Number of active connections 3 ... 2015-12-08 11:38:23,431 INFO org.dspace.storage.rdbms.DatabaseManager - Number of active connections 31 2015-12-08 11:38:23,431 INFO org.dspace.storage.rdbms.DatabaseManager - Number of active connections 31 2015-12-08 11:38:23,430 INFO org.dspace.storage.rdbms.DatabaseManager - Number of active connections 31 ...

    Sakai JIRA | 12 months ago | Jonathan Blood
    org.postgresql.util.PSQLException: FATAL: remaining connection slots are reserved for non-replication superuser connections
  2. 0

    I have a DSpace instance which started to encounter the following error when cronjobs started to run: {quote}org.postgresql.util.PSQLException: FATAL: remaining connection slots are reserved for non-replication superuser connections at org.postgresql.core.v3.ConnectionFactoryImpl.readStartupMessages(ConnectionFactoryImpl.java:471) at org.postgresql.core.v3.ConnectionFactoryImpl.openConnectionImpl(ConnectionFactoryImpl.java:112) at org.postgresql.core.ConnectionFactory.openConnection(ConnectionFactory.java:66) at org.postgresql.jdbc2.AbstractJdbc2Connection.<init>(AbstractJdbc2Connection.java:125) at org.postgresql.jdbc3.AbstractJdbc3Connection.<init>(AbstractJdbc3Connection.java:30) at org.postgresql.jdbc3g.AbstractJdbc3gConnection.<init>(AbstractJdbc3gConnection.java:22) at org.postgresql.jdbc4.AbstractJdbc4Connection.<init>(AbstractJdbc4Connection.java:32) at org.postgresql.jdbc4.Jdbc4Connection.<init>(Jdbc4Connection.java:24) at org.postgresql.Driver.makeConnection(Driver.java:393) at org.postgresql.Driver.connect(Driver.java:267) at java.sql.DriverManager.getConnection(DriverManager.java:571) at java.sql.DriverManager.getConnection(DriverManager.java:215) at org.apache.commons.dbcp.DriverManagerConnectionFactory.createConnection(DriverManagerConnectionFactory.java:75) at org.apache.commons.dbcp.PoolableConnectionFactory.makeObject(PoolableConnectionFactory.java:582) at org.apache.commons.pool.impl.GenericObjectPool.borrowObject(GenericObjectPool.java:974) at org.apache.commons.dbcp.PoolingDataSource.getConnection(PoolingDataSource.java:106) at org.dspace.storage.rdbms.DatabaseManager.getConnection(DatabaseManager.java:601) at org.dspace.core.Context.init(Context.java:121) at org.dspace.core.Context.<init>(Context.java:95) at ie.enovation.cli.bulktasks.UpdateFeedCollections.main(UpdateFeedCollections.java:29) at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57) at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) at java.lang.reflect.Method.invoke(Method.java:606) at org.dspace.app.launcher.ScriptLauncher.runOneCommand(ScriptLauncher.java:225) at org.dspace.app.launcher.ScriptLauncher.main(ScriptLauncher.java:77){quote} When this issue occurred I ran: {quote}SELECT * FROM pg_stat_activity{quote} I could see the max_connections limit was reached. I tried to use this query and map times to the DSpace logs and see if I can see any potential issues. From the initial investigation I couldn't see anything which stood out. So I decided to add additional logging to DSpace to count active connections every single time a query is made in DSpace. I did this by modifying _DatabaseManager_ class and reporting this to a separate log file using log4j in order to make it easier to read since it's quite an active DSpace instance. When I deployed this change and restarted Tomcat the issue disappeared as expected. After about two months the issue reappeared again. The logging I added showed the number of active connections was low. But if I did the following query again: {quote}SELECT * FROM pg_stat_activity{quote} I could see number of connections was at the limit. I mapped the timings from the query to DSpace logs and I could see a bit more of a trend. I noticed a lot of new connections were created straight after each other in rapid succession after multiple requests in a short span of time to view a bitstream (crawler activity). This was the most common pattern but it did happen when accessing other pages such as "Browse by title" etc but not as much. In order to try and replicate the issue I set up DSpace locally. Before each test case I restart Tomcat and run SELECT * FROM pg_stat_activity to confirm DSpace has released the connections. * TEST CASE 1* In _dspace.cfg_: db.maxconnections = 80 db.maxwait = 5000 db.maxidle = -1 In postgres configuration I have max connections set to 100. To replicate a number of similar requests that I was seeing from crawler activity I used some bash scripting to do something simple like: {quote} for i in {1..200} do wget localhost:8080/dspace/xmlui/bitstream/handle/10395/2050/test.txt?sequence=2&isAllowed=y done {quote} Before running test case: Query: SELECT * FROM pg_stat_activity Result: 3 rows Custom active connection count logger: 2015-12-08 11:16:26,007 INFO 2015-12-08 11:35:37,830 INFO org.dspace.storage.rdbms.DatabaseManager - Number of active connections 1 After running test case: Query: SELECT * FROM pg_stat_activity Result: 92 rows all in state idle Custom active connection count logger (general pattern): 2015-12-08 11:32:22,523 INFO org.dspace.storage.rdbms.DatabaseManager - Number of active connections 3 2015-12-08 11:32:22,527 INFO org.dspace.storage.rdbms.DatabaseManager - Number of active connections 2 2015-12-08 11:32:22,529 INFO org.dspace.storage.rdbms.DatabaseManager - Number of active connections 3 ... 2015-12-08 11:38:23,431 INFO org.dspace.storage.rdbms.DatabaseManager - Number of active connections 31 2015-12-08 11:38:23,431 INFO org.dspace.storage.rdbms.DatabaseManager - Number of active connections 31 2015-12-08 11:38:23,430 INFO org.dspace.storage.rdbms.DatabaseManager - Number of active connections 31 ...

    Sakai JIRA | 12 months ago | Jonathan Blood
    org.postgresql.util.PSQLException: FATAL: remaining connection slots are reserved for non-replication superuser connections
  3. 0

    WIReDSpace: Internal Server Error

    Google Groups | 10 months ago | Lewatle Johannes Phaladi
    org.postgresql.util.PSQLException: FATAL: remaining connection slots are reserved for non-replication superuser connections
  4. Speed up your debug routine!

    Automated exception search integrated into your IDE

  5. 0

    Can't start hive, some error with hive-metastore-server

    Stack Overflow | 3 years ago | Dave
    org.postgresql.util.PSQLException: FATAL: the database system is shutting down
  6. 0

    hive service is not starting getting error Failed initialising database?

    Stack Overflow | 3 years ago
    org.postgresql.util.PSQLException: FATAL: password authentication failed for user "hive"

  1. silex 13 times, last 21 hours ago
  2. silex 8 times, last 6 months ago
  3. csanyigabor 1 times, last 3 months ago
25 unregistered visitors
Not finding the right solution?
Take a tour to get the most out of Samebug.

Tired of useless tips?

Automated exception search integrated into your IDE

Root Cause Analysis

  1. org.postgresql.util.PSQLException

    FATAL: remaining connection slots are reserved for non-replication superuser connections

    at org.postgresql.core.v3.ConnectionFactoryImpl.readStartupMessages()
  2. PostgreSQL JDBC Driver
    Driver.connect
    1. org.postgresql.core.v3.ConnectionFactoryImpl.readStartupMessages(ConnectionFactoryImpl.java:471)
    2. org.postgresql.core.v3.ConnectionFactoryImpl.openConnectionImpl(ConnectionFactoryImpl.java:112)
    3. org.postgresql.core.ConnectionFactory.openConnection(ConnectionFactory.java:66)
    4. org.postgresql.jdbc2.AbstractJdbc2Connection.<init>(AbstractJdbc2Connection.java:125)
    5. org.postgresql.jdbc3.AbstractJdbc3Connection.<init>(AbstractJdbc3Connection.java:30)
    6. org.postgresql.jdbc3g.AbstractJdbc3gConnection.<init>(AbstractJdbc3gConnection.java:22)
    7. org.postgresql.jdbc4.AbstractJdbc4Connection.<init>(AbstractJdbc4Connection.java:32)
    8. org.postgresql.jdbc4.Jdbc4Connection.<init>(Jdbc4Connection.java:24)
    9. org.postgresql.Driver.makeConnection(Driver.java:393)
    10. org.postgresql.Driver.connect(Driver.java:267)
    10 frames
  3. Java RT
    DriverManager.getConnection
    1. java.sql.DriverManager.getConnection(DriverManager.java:571)
    2. java.sql.DriverManager.getConnection(DriverManager.java:215)
    2 frames
  4. Commons DBCP
    PoolableConnectionFactory.makeObject
    1. org.apache.commons.dbcp.DriverManagerConnectionFactory.createConnection(DriverManagerConnectionFactory.java:75)
    2. org.apache.commons.dbcp.PoolableConnectionFactory.makeObject(PoolableConnectionFactory.java:582)
    2 frames
  5. Commons Pool
    GenericObjectPool.borrowObject
    1. org.apache.commons.pool.impl.GenericObjectPool.borrowObject(GenericObjectPool.java:974)
    1 frame
  6. Commons DBCP
    PoolingDataSource.getConnection
    1. org.apache.commons.dbcp.PoolingDataSource.getConnection(PoolingDataSource.java:106)
    1 frame
  7. DSpace Kernel :: API and Implementation
    Context.<init>
    1. org.dspace.storage.rdbms.DatabaseManager.getConnection(DatabaseManager.java:601)
    2. org.dspace.core.Context.init(Context.java:121)
    3. org.dspace.core.Context.<init>(Context.java:95)
    3 frames
  8. ie.enovation.cli
    UpdateFeedCollections.main
    1. ie.enovation.cli.bulktasks.UpdateFeedCollections.main(UpdateFeedCollections.java:29)
    1 frame
  9. Java RT
    Method.invoke
    1. sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    2. sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57)
    3. sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
    4. java.lang.reflect.Method.invoke(Method.java:606)
    4 frames
  10. DSpace Kernel :: API and Implementation
    ScriptLauncher.runOneCommand
    1. org.dspace.app.launcher.ScriptLauncher.runOneCommand(ScriptLauncher.java:225)
    1 frame