Summary

By default, the max_connections setting within Postgres is set to 100 connections.  If Jira is configured to use more connections than this setting allocates, this can result in a Jira outage. 

Environment

All versions of Jira.

All versions of Postgres.

Diagnosis

If an outage has occurred, this issue shows itself by way of a message like this appearing in the application logs:

2020-12-10 02:45:06,432 ERROR [C3P0PooledConnectionPoolManager[identityToken->1bqqkxuad1lwglwv1y8yakz|5660fd1e]-HelperThread-#1] [org.postgresql.Driver] connect Connection error: 
org.postgresql.util.PSQLException: FATAL: remaining connection slots are reserved for non-replication superuser connections
	at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2476)
	at org.postgresql.core.v3.QueryExecutorImpl.readStartupMessages(QueryExecutorImpl.java:2602)
	at org.postgresql.core.v3.QueryExecutorImpl.<init>(QueryExecutorImpl.java:125)
	at org.postgresql.core.v3.ConnectionFactoryImpl.openConnectionImpl(ConnectionFactoryImpl.java:227)
	at org.postgresql.core.ConnectionFactory.openConnection(ConnectionFactory.java:49)
	at org.postgresql.jdbc.PgConnection.<init>(PgConnection.java:194)
	at org.postgresql.Driver.makeConnection(Driver.java:450)
	at org.postgresql.Driver.connect(Driver.java:252)
	at com.mchange.v2.c3p0.DriverManagerDataSource.getConnection(DriverManagerDataSource.java:175)

Cause

To verify the cause of this issue, a quick inventory of the available connections versus the required connections by the application is necessary.

To determine the number of connections available within Postgres, execute the following SQL:

SELECT name, current_setting(name) 
FROM pg_settings 
WHERE name = 'max_connections';

Now, we'll need to determine how many connections are required by the applications running on the server. This includes Jira and any other applications (such as analytics) running on the server that require a Postgres server connection, plus a buffer of 20% for Postgres overhead activities.

Other Applications that use the Postgres instance

If there are any other applications that use the Postgres instance, we'll need to account for those connections as well.  For instance, if Confluence uses the same Postgres instance, we do so by first locating the <confluence-home-folder>/confluence.cfg.xml file and examining the value set to the hibernate.c3p0.max_size property.

Buffer

And finally, we'll want to add a 20% buffer to the total of the connections required by the main Jira application and any other application using the Postgres service.

If the total required connections are greater than what's configured on the Postgres server, then we'll need to increase the max_connections setting.

Solution

While we'll need to increase the max_connections setting within the Postgres configuration file, the shared_buffer setting may need to be adjusted as well.  For more information, please see Tuning Your PostgreSQL Server or contact a DBA to assist.