Summary

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

Environment

All versions of Confluence.

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 Confluence, Synchrony (which is the engine that drives Confluence's Collaborative Editing feature, any other applications running on the server that require a Postgres server connection, plus a buffer of 20% for Postgres overhead activities.

Confluence

To begin calculating the total number of connections needed, we'll first need to look at the main Confluence application.  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.  It should look something like this: 

 <property name="hibernate.c3p0.max_size">60</property>

  Note:  if this is a Data Center instance, this number needs to be multiplied by the total number of DC nodes that are running.  This will then total the number of connections required to be available by the main Confluence application.

Synchrony

Next, we'll now need to include the 15 connections that are configured by default within Synchrony.   Like the main Confluence application, this number needs to be multiplied by the number of nodes in use if Data Center is in use and Synchrony is managed by Confluence.  If Synchrony has been configured to run as Synchrony standalone in a cluster, then multiply the number of Synchrony nodes in the cluster by 15.

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 Jira uses the same Postgres instance, then we'll want to inspect Jira's dbconfig.xml file which is located in its home/data directory.

Buffer

And finally, we'll want to add a 20% buffer to the total of the connections required by the main Confluence application, Synchrony 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.