Resolving: Remaining connection slots are reserved for non-replication superuser connections in PostgreSQL

Resolving Remaining connection slots are reserved for non-replication superuser connections in PostgreSQL

The error: remaining connection slots are reserved for non-replication superuser connections, can be resolved in many ways. In this post, we will be looking at least three ways to resolve this error so that connections can be restored to your database.

Before we dive into resolving this error, it is best if we check what our database max connection is. By default, this value is set to 100. To do this we simply execute any one of the queries below against a database within our cluster:

show max_connections;

SELECT current_setting('max_connections');

SELECT *
FROM   pg_settings
WHERE  name = 'max_connections';

Show max_connections PostgreSQL

Method 1

The first step that we should consider taking when trying to resolve The error: remaining connection slots are reserved for non-replication superuser connections is installing and configuring connection pooling in our database. In short, connection pooling is the caching of database connections so that when future requests are made to the database the same connection can be reused. To do connection pooling in PostgreSQL we can make use of two addons to the database, pgbouncer and pgpool

pgbouncer
As described on its official website, pgbouncer is a Lightweight connection pooler for PostgreSQL. Features of pgbouncer includes: session pooling, transaction pooling, statement pooling.

pgpool
As described on its official website, 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.

In a follow-up post, we will be installing and configuring both pgbouncer and pgpool for PostgreSQL.

The diagram below gives a pictorial example of connection pooling.

Connection Pooling in Postgresql

Method 2

Our second solution, which by no means is the best, involves increasing the number of connections to our database, by increasing the max_connections parameter in the postgresql.conf file. It’s important to note that when changing the max_connections (default is 100) parameter, you also need to increase the shared_buffers (default is 128M) parameter as well.

For every connection that is created, the OS needs to allocate memory to the process that is opening the network socket, and PostgreSQL needs to do its own under-the-hood computations to establish that connection. Please note that once these parameters are changed, in order for them to take effect, a restart of the database is needed. Before we 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

Changes made in the database using the ALTER Command are written to the postgresql.auto.conf file first, the values in that file are then committed to the postgresql.conf file upon reloading or restarting of the database in some cases.

Editing the Postgresql.conf file
A next method we can use to increase the values of the max_connections and shared_buffers parameter is to directly edit the postgresql.conf file.
Locate and open your cluster postgresql.conf file. Onece in the file locate the 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 the new values for the parameters, as mentioned above we need to restart the database cluster. To do this we can issue any of the commands below:

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

Method 3

Killing Idle Sessions
The next method we can use to resolve the error: remaining connection slots are reserved for non-replication superuser connections, is to kill idle sessions on the database. In PostgreSQL A connection is considered inactive if its state is either idle, idle in transaction, idle in transaction (aborted), or disabled 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

 

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;

 

As you might have guessed, 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.

Set idle in transaction session timeout
The next option is to terminate sessions that have been idle for a period of time. We can use the idle_in_transaction_session_timeout parameter to achieve this. The beauty of this parameter is that 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

 

Other posts

Step by step guide on how To Install PostgreSQL 12 on Ubuntu 16.04/18.04/20.04 (2020)

PostgreSQL 12 Streaming replication on Ubuntu easy step-by-step guide

Understanding the pg_hba conf file in PostgreSQL