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"

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

1 vote
Andy 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.

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 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
Community showcase
Published in Jira Software

Early Access: If you use Jenkins and Jira Software Cloud, you need to read this!

The Jira Software Cloud Team has been busy working on a simple, secure, and reliable way to integrate your build and deployment information from Jenkins with Jira Software Cloud. This means you don’t...

2,120 views 3 19
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