'FATAL too many connections' error when using PostgreSQL database due to user-specific connection limit
Platform Notice: Data Center Only - This article only applies to Atlassian products on the Data Center platform.
Note that this KB was created for the Data Center version of the product. Data Center KBs for non-Data-Center-specific features may also work for Server versions of the product, however they have not been tested. Support for Server* products ended on February 15th 2024. If you are running a Server product, you can visit the Atlassian Server end of support announcement to review your migration options.
*Except Fisheye and Crucible
Symptoms
A JIRA application PostgreSQL database may starve for connections through the authenticated applications PostgreSQL user, causing it to keep throwing this error and preventing JIRA applications from functioning properly:
1
2
Could not retrieve dependencies for issue :XXX-1234 : Unable to establish a connection with the database. (FATAL: too many connections for role "jirauser"): Unable to establish a connection with the database. (FATAL: too many connections for role "jirauser")
This error may persist even after changing the maxActive, minIdle, maxIdle Resource tag attributes in server.xml file to (ridiculously) large values.
Cause
PostgreSQL has a per-user (also called role) connections limit, other than the known per-database connection limit. Although by default this connection limit is set to -1 (unlimited), but in few cases (especially when upgrading PostgreSQL database as reported), the maximum connections per user may change to be limited. This attribute of the PostgreSQL users needs to be changed by a PostgreSQL query as will be shown below.
Resolution
Change the connection limit to the PostgreSQL user that the JIRA application uses to authenticate to PostgreSQL database, to be unlimited, using this PostgreSQL query:
1
ALTER ROLE jirauser CONNECTION LIMIT -1;
ℹ️ change jirauser according to the PostgreSQL (role) that your JIRA application instance uses to authenticate to PostgreSQL.
Was this helpful?