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: Install and configure connection pooling

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: Increase the number of connections to the database

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: Kill idle sessions on the database.

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.