'FATAL too many connections' error when using PostgreSQL database due to user-specific connection limit
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:
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:
ALTER ROLE jirauser CONNECTION LIMIT -1;
change jirauser according to the PostgreSQL (role) that your JIRA application instance uses to authenticate to PostgreSQL.