Need help?

Our experts have had an average response time of 13.14 minutes in February 2024 to fix urgent issues.

We will keep your servers stable, secure, and fast at all times for one fixed price.

PostgreSQL “Remaining connection slots are reserved” error

by | Jul 7, 2021

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.

PREVENT YOUR SERVER FROM CRASHING!

Never again lose customers to poor server speed! Let us help you.

Our server experts will monitor & maintain your server 24/7 so that it remains lightning fast and secure.

GET STARTED

var google_conversion_label = "owonCMyG5nEQ0aD71QM";

0 Comments

Submit a Comment

Your email address will not be published. Required fields are marked *

Categories

Tags

Privacy Preference Center

Necessary

Necessary cookies help make a website usable by enabling basic functions like page navigation and access to secure areas of the website. The website cannot function properly without these cookies.

PHPSESSID - Preserves user session state across page requests.

gdpr[consent_types] - Used to store user consents.

gdpr[allowed_cookies] - Used to store user allowed cookies.

PHPSESSID, gdpr[consent_types], gdpr[allowed_cookies]
PHPSESSID
WHMCSpKDlPzh2chML

Statistics

Statistic cookies help website owners to understand how visitors interact with websites by collecting and reporting information anonymously.

_ga - Preserves user session state across page requests.

_gat - Used by Google Analytics to throttle request rate

_gid - Registers a unique ID that is used to generate statistical data on how you use the website.

smartlookCookie - Used to collect user device and location information of the site visitors to improve the websites User Experience.

_ga, _gat, _gid
_ga, _gat, _gid
smartlookCookie
_clck, _clsk, CLID, ANONCHK, MR, MUID, SM

Marketing

Marketing cookies are used to track visitors across websites. The intention is to display ads that are relevant and engaging for the individual user and thereby more valuable for publishers and third party advertisers.

IDE - Used by Google DoubleClick to register and report the website user's actions after viewing or clicking one of the advertiser's ads with the purpose of measuring the efficacy of an ad and to present targeted ads to the user.

test_cookie - Used to check if the user's browser supports cookies.

1P_JAR - Google cookie. These cookies are used to collect website statistics and track conversion rates.

NID - Registers a unique ID that identifies a returning user's device. The ID is used for serving ads that are most relevant to the user.

DV - Google ad personalisation

IDE, test_cookie, 1P_JAR, NID, DV, NID
IDE, test_cookie
1P_JAR, NID, DV
NID
hblid

Security

These are essential site cookies, used by the google reCAPTCHA. These cookies use an unique identifier to verify if a visitor is human or a bot.

SID, APISID, HSID, NID, PREF
SID, APISID, HSID, NID, PREF