PostGreSQL Error "remaining connection slots are reserved for non-replication superuser connections"

James H March 9, 2018

We are using Jira v7.8.0 with Jira Service Desk 3.11.0 and PostGreSQL 9.6.6.  We are in pre-prod at the moment so max 2-3 users at any one time.  Periodically when doing something in Jira the error "FATAL: remaining connection slots are reserved for non-replication superuser connections" is thrown.

Anyone able to advise how to troubleshoot this.  Max connections in PostGreSQL is set at default value of 100 which should be plenty.

 

1 answer

2 votes
Andy Heinzer
Atlassian Team
Atlassian Team members are employees working across the company in a wide variety of roles.
March 12, 2018

I typically would not expect 2-3 users to consume so many connections at once.   But the error you are seeing is an indication that postgres does not have any more connections available.

I would recommend trying to run this command on your SQL database to see if you can better understand what commands/users are using up so many connections

SELECT * FROM pg_stat_activity;

Granted this won't show you Jira users, this will only show SQL users.   And in turn Jira is connected to your database by a single login account to SQL.   But still this can tell us more about what commands are being run, what the state of the connection is (idle/active), if there are queued queries.

 

Are there other databases on this SQL server?  Or is this just Jira's database?

Are you running any other plugins in this Jira instance, like nfeed, scriptrunner, eazybi, etc?   Some of these and more, could be configured to create a large number of database connections either via scripting some commands on a large batch of issues, or for the sake of creating reports with very large datasets.

I would also be interested to see what parameters you have in your $JIRAHOME/dbconfig.xml  (we don't need to see your dbuser or dbpassword contained in that file though).  This file governs how Jira connects to your database.   I would expect it to look closely similar to the sample on Connecting JIRA applications to PostgreSQL.

It might also help to take a look at Monitoring database connection usage.  This utility built into Jira can sometimes give you a better graphical understanding of the number of connections at any given time.

James H March 13, 2018

Hi Andrew, thank you for getting back to me.  The database server is being shared with Confluence, and we did a bit of investigation and found its actually Confluence hogging all the connections, Jira was only running about 7 of them.  Even bumping up the total connection from 100 to 150 just caused Confluence to grab a load more.

Is there an equivalent to the  $JIRAHOME/dbconfig.xml for Confluence?

Like Gustavo Honorato likes this
Andy Heinzer
Atlassian Team
Atlassian Team members are employees working across the company in a wide variety of roles.
March 13, 2018

In most cases of Confluence that equivalent is in the $CONFHOME/ directory and is called 'confluence.cfg.xml'

I say most because it is possible you could be using a JNDI defined connector with the configuration being in the $CONFINSTALL/conf/server.xml file instead.  More details on that setup in Configuring a datasource connection

Suggest an answer

Log in or Sign up to answer