Error remaining connection slots are reserved for non replication superuser connections

Wondering how to resolve PostgreSQL “Remaining connection slots are reserved” error? We can help you.

As a part of our Server Management Service, we help our customers to fix PostgreSQL related errors regularly.

Today, let us see how our Support Techs fix this error.

What causes PostgreSQL “Remaining connection slots are reserved” error?

Typical error will look as shown below:

psql: FATAL: remaining connection slots are reserved for non-replication superuser connections

Basically, Insufficient connections allocated for PostgreSQL tasks causes the issue.

Check what your database max connection is.

By default, this value is set to 100.

To do this execute any one of the queries below against a database within your cluster:

show max_connections;

SELECT current_setting(‘max_connections’);

SELECT *
FROM pg_settings
WHERE name = ‘max_connections’;

How to resolve PostgreSQL “Remaining connection slots are reserved” error?

Today, let us see the methods followed by our Support Techs to resolve it.

Method 1:

Firstly, for non-replication superuser connections is installing and configuring connection pooling in your database.

In short, connection pooling is the caching of database connections so that when future requests are made to the database you can reuse same connection.

Next, to do connection pooling in PostgreSQL we can make use of two addons to the database, pgbouncer and pgpool

pgbouncer : pgbouncer is a Lightweight connection pooler for PostgreSQL.

Features of pgbouncer includes: session pooling, transaction pooling, statement pooling.

pgpool : pgpool is a middleware that works between PostgreSQL servers and a database client.

It provides the following features: Connection Pooling, Load Balancing, Limiting Exceeding Connections, Watchdog and In-Memory Query Cache.

Method 2

This method, involves increasing the number of connections to our database, by increasing the max_connections parameter in the postgresql.conf file.

Please note that when changing the max_connections parameter, you also need to increase the shared_buffers parameter as well.

For every connection, the OS needs to allocate memory to the process that is opening the network socket.

The PostgreSQL needs to do its own under-the-hood computations to establish that connection.

Once you change the parameters, restart the database.

Before you make any changes it is good to make note of the total max connections and the current shared buffer size.

show max_connections;
Show shared_buffers;

Using ALTER Command

To change the max connections using SQL execute the command:

ALTER SYSTEM SET max_connections TO ‘150’; — increase the value by 50

To change the shared buffers value using SQL execute the command:

ALTER SYSTEM SET shared_buffers TO ‘256MB’ — Increase the value by 128MB

Write the changes made in the database using the ALTER Command to postgresql.auto.conf file first.

Then, commit the values in files to the postgresql.conf file upon reloading or restarting of database.

Editing the Postgresql.conf file

Next, we can  increase values of the max_connections and shared_buffers parameter is to directly edit postgresql.conf file.

Locate and open your cluster postgresql.conf file.

Once in the file locate parameters that need changing and change them accordingly.

max_connections = 150 # (change requires restart)
shared_buffers = 256MB # min 128kB # (change requires restart)

Restarting the Cluster

After setting new values for parameters, as mentioned above we need to restart database cluster.

Run any of commands below:

Pg_ctl restart -D /path/to/db/directory/
service postgresql-10.service restart (depending on your version)

Method 3

Let us see the steps followed by our Support Techs in this method.

Killing Idle Sessions

In this method, kill idle sessions on the database.

In PostgreSQL A connection is inactive if its state is either idle, idle in transaction, idle in transaction (abort), or disable and if that transaction is in that state for more than 5 minutes it is considered to be old.

Before we kill any idle sessions we need to first check for all idle session in the database that meets the criteria above by running:

SELECT *
FROM pg_stat_activity
WHERE datname = ‘centralauth’
AND pid <> pg_backend_pid()
AND state in (‘idle’, ‘idle in transaction’, ‘idle in transaction (aborted)’, ‘disabled’)
AND state_change < current_timestamp – INTERVAL ’15’ MINUTE; — You can set your own interval

Usually, the query above will check for transactions that have been ‘old’ for 15 minutes and more.

Once we see all the old transactions in the database, we can kill them by running the pg_terminate_backend() function.

SELECT pg_terminate_backend(pid)
FROM pg_stat_activity
WHERE datname = ‘centralauth’
AND pid <> pg_backend_pid()
AND state in (‘idle’, ‘idle in transaction’, ‘idle in transaction (aborted)’, ‘disabled’)
AND state_change < current_timestamp – INTERVAL ’15’ MINUTE;

Next, the query above will collect all the pids for the idle sessions that meet the criteria and pass them to the pg_terminate_backend() function, thus killing them.

Then, set idle in transaction session timeout

Next, terminate sessions that have been idle for a period of time.

Then, we can use the idle_in_transaction_session_timeout parameter to achieve this.

Then, we can set it for a specific role in the database.

alter user username SET idle_in_transaction_session_timeout to 60000; — 1minute

For all connections:

ALTER SYSTEM SET idle_in_transaction_session_timeout to 60000; — 1minute

[Need help to fix PostgreSQL error? We are available 24*7]

Conclusion

Today, we saw the methods followed by our Support Techs to resolve PostgreSQL “Remaining connection slots are reserved” error.

var google_conversion_label = «owonCMyG5nEQ0aD71QM»;

AWS support for Internet Explorer ends on 07/31/2022. Supported browsers are Chrome, Firefox, Edge, and Safari.
Learn more »

I have a PostgreSQL 12.1 database system (I’ll refer to it as PGSQL) running on a remotely hosted VM server (Windows Server 2019). We upgraded the server OS and PGSQL a couple of months ago. Everything has been running more-or-less normally since then, until this morning when I started receiving the above-mentioned database error in pretty much every one of our in-house applications that connect to this PGSQL instance.

To check the connections, I ran SELECT * FROM pg_stat_activity;, which returned 103 rows. My postgresql.conf file has max_connections = 100, so that makes sense, but what doesn’t make sense is that, of those 103 connections, 90+ of them are listed as idle with query text of DISCARD ALL. All of these show as being executed by the same, non-superuser account from the server’s own internal address. However, several of the connections show a query_start date value from a month or more ago.

Now, many of the applications we have in place are unfortunately built with hard-coded credentials (I have a lot of «clean-up» work to do on the code for these applications that I inherited) and are generally being executed from shortcuts pointing to an «Application» share on the server that’s hosting the PGSQL database, so none of this looks particularly «suspicious». I tried to simply kill the processes using SELECT pg_cancel_backend(<pid>); on one of the pid values from the previous query, but requerying pg_stat_activity still shows the same record in the result set (all of the values appear to be exactly the same, from what I can tell).

Perhaps I’m not using the correct function to terminate these «hung» processes or something, but I could not figure out how to clear out these connections individually. Because I needed to get our production environment back to a usable state, I ended up just stopping and restarting the PGSQL service on the server which did clear out all of those old DISCARD ALL statements, but I’m curious if there’s something I could do to prevent this backlog of «hung» statements in the future.

My question here is, how can I prevent this from happening in the future? One thing to note is that, prior to upgrading our PGSQL server to v12.1, we ran v9.4 for a number of years and never once encountered this issue. I’m wondering if there might be something inherent to the newer version of PGSQL, or perhaps even something about running PGSQL in the Windows Server 2019 environment that might be causing this behavior.

EDIT

For reference and consolidation, the following information comes from the comments:

I do not have anything server-side for managing connection pooling (I’ve seen some references in other questions about PgBouncer, but haven’t had an opportunity to look at that for whether or not it would be helpful in our environment). Most of my applications are implementing pooling in the connection string via the Npgsql library. I’ve built a «common library» for managing my applications’ connections — connecting, disconnecting, disposing, etc. — which at least seems to be working normally.

However, it’s certainly possible that some of the «legacy» code I’ve inherited does not have this implemented correctly, but that’s something that’ll take me a bit of time to dig through the code to find all of the connections (there are a lot of issues with some of that code). I’ll investigate that as a potential/likely source of the issue as time permits.

As stated above, I’ve not encountered this issue until we upgraded PGSQL to v12.1 and that same legacy code has been in place for several years. As a 1-man IT Dept, the server’s reboot «schedule» is generally managed by me and I’ve rarely rebooted the server or restarted the PGSQL service due to its nature as a production environment. Hopefully, I’m just being hyper-sensitive about stuff because of the recent upgrade and this whole thing is a «one-off» situation I won’t see again.

I guess what triggered my question was wondering why the database hadn’t dumped these idle connections that have been hanging around for a month or more. I’ll keep an eye on things and, if the problem persists, I’ll look into more aggressive connection pool management options.

UPDATE/EDIT

Since posting this question 4 months ago, I, unfortunately, haven’t been actively checking the status of the idle connections. This morning, however, the error popped back up and I was reminded of this post. Again, executing SELECT * FROM pg_stat_activity; showed over 100 connections (the max defined by my server configuration), of which over 70 were idle / DISCARD ALL with «older» (several weeks/months) values for the backend_start / query_start date.

Understanding that the database itself doesn’t «manage» these, I still find it odd that I never encountered this error in more than a decade of running previous versions of PostgreSQL. It wasn’t until after we upgraded to v12, even though the environment has remained basically the same.

Regardless, because I have so many «legacy» applications that potentially could be leaving these connections open, I’ve decided to implement a check in one of my daily routines that should prevent this issue from reoccurring. I have an application that is run every day to replicate and clean up certain information in the database. In that application, I am adding a small function that will execute the following SQL command to keep the backend clear of these «leftover» idle connections:

SELECT
    pg_terminate_backend(pid)
FROM
    pg_stat_activity
WHERE
    pid <> pg_backend_pid()
    AND backend_start < (SELECT (NOW() - INTERVAL '2 WEEK')::DATE)
    AND state = 'idle';

As you can see, this should look for any idle connections older than 2 weeks and terminate them. Since this application is run daily, I believe this will help to keep the pool as clear as possible. Of course, if there’s a better way — one that does not require the implementation of some new 3rd-party solution (at least, at this time) — I’m all ears, but, for the time being, I believe this will «solve» my problem.

ADDITIONAL REFERENCE / POSSIBLE ALTERNATE SOLUTION

As I was looking around a bit more, I also came across this answer on StackOverflow:

How to close idle connections in PostgreSQL automatically?

The linked answer refers to using the idle_in_transaction_session_timeout setting introduced in PostgreSQL 9.6. As stated in fresko‘s answer, I should be able to set this with a single SQL command sent to the server either as a superuser for the entirety of the server:

ALTER SYSTEM SET idle_in_transaction_session_timeout = '5min';

or for individual user connections on a per-session basis (if needed)

SET SESSION idle_in_transaction_session_timeout = '5min';

I checked this setting on my server and it appears to be disabled (0), so I may look into configuring this setting as well. I’ll be referring to the v12 documentation page for Client Connection Defaults in case anyone wants to «follow along».

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.