Confluence encounters Postgres error: "FATAL: remaining connection slots are reserved for non-replication superuser connections"
Platform Notice: Server and Data Center Only - This article only applies to Atlassian products on the server and data center platforms.
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.
All versions of Confluence.
All versions of Postgres.
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)
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.
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:
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.
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.
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.
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.