It's not the same without you

Join the community to find out what other Atlassian users are discussing, debating and creating.

Atlassian Community Hero Image Collage

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

Jimbo H Mar 09, 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

0 votes
Andrew Heinzer Atlassian Team Mar 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.

Jimbo H Mar 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?

Andrew Heinzer Atlassian Team Mar 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
This widget could not be displayed.
This widget could not be displayed.
Community showcase
Published in Jira Software

How to prevent the propagation of unused project schemes, workflows & screens in Jira software

Atlassian ranks project attributes as the third most important factor impacting performance in the category of data. It’s not surprising, since project attributes are precisely the rules used to ma...

1,378 views 1 17
Read article

Community Events

Connect with like-minded Atlassian users at free events near you!

Find an event

Connect with like-minded Atlassian users at free events near you!

Unfortunately there are no Community Events near you at the moment.

Host an event

You're one step closer to meeting fellow Atlassian users at your local event. Learn more about Community Events

Events near you