PostgreSQL データベースの使用時にユーザー固有の接続制限が原因で「致命的な接続が多すぎます」エラーが発生する
症状
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.
原因
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.
ソリューション
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.