PostGIS - Sorry Too Many Clients Already

by David Crowther

Question:

Why am I getting this error message – Sorry Too Many Clients Already – and how can I resolve it?

Answer:

Postgres Database Instances have a default maximum number of Connections that can be made and when this is exceeded the following error message will be shown in your client application.

To better understand these Connections we can use some simple commands in PGAdmin to investigate the issue.

1 – Identify the Maximum Connections Allowed for Postgres

At your Database level open a Query Tool and write the following syntax.

SHOW max_connections;

The query will run and in the Data Output pane the result is shown, in this case the Postgres Instance allows up to a maximum of 100 Connections.

2 – View the Details of these Connections

Add the following syntax which will list all of the Connections as records from the pg_stat_activity table.

SELECT * FROM pg_stat_activity;

The query will run and in the Data Output pane the result is shown, in this case the pg_stat_activity table is shown, where each record details the Connections to each of your Databases. This includes the Database Name, Application_Name, Client IP Address of the Connection, Time and Date of the Connection.

3 – View the Count of Connections

By using a COUNT command you can identify the total number of Connections to your Postgres Instance.

select count(*) from pg_stat_activity;

The query will run and in the Data Output pane the result is shown, in this case the total number of Connections to your Postgres Instance.

By using a Count and Group Query on the pg_stat_activity table you can then identify the number of Connections from different Applications. In this case we can see that PGAdmin and DBeaver are being used as Client Applications to connect to our PostGIS databases.

If we now Connect via QGIS and Add a PostGIS Table as a Layer…..

… when we re-run the Select and Count query we can see that the number of Connections has risen from 31 previously to now be 32!

And if we re-run the Count and Group query we can see that QGIS is now also listed as a Client Application in the results list.

4 – Close Connections

Now that we have investigated the Connections to our PostGIS Databases, we may wish to start to Disconnect Client Applications or possibly individual Users. The example below uses the ID (pid) of the Connection from the pg_stat_activity table to terminate the Connection for the DBeaver User.

Select pg_terminate_backend(pid)
from pg_stat_activity
where pid = '16393';

… when we re-run the Select and Count query we can see that the number of Connections has decreased from 32 previously to now be 31.

We can also choose to Terminate connections for all Users that are using a specific Application. In this example we can terminate Connections for all QGIS Users.

select pg_terminate_backend(pid)
from pg_stat_activity
where application_name = 'QGIS';

… when we re-run the Select and Count query we can see that the number of Connections has decreased from 31 previously to now be 30.

5 – Close ALL Connections

Finally…. You may wish to close all Connections to your Database to flush out multiple Connections. In this example you can use the syntax below to close Connections to one specific Database. Removing the WHERE clause will close Connections to ALL PostGIS Databases.

SELECT
pg_terminate_backend(pg_stat_activity.pid)
FROM
pg_stat_activity
WHERE
pg_stat_activity.datname = 'TryMapThat'
AND pid <> pg_backend_pid();

After running the Terminate All command, if we now view the number of Connections to just the ‘TryMapThat’ database, we can see the number has markedly dropped as only your current PGAdmin session will be listed.

NB: Each new connection or QUERY Window open within PGAdmin will add a new connection to your Database. So be careful with the number of TABS/Queries you have open in PGADMIN.

By manually closing these TABS/Queries in PGAdmin (clicking the X to close them) you dramatically reduce the number of Connections to your PostGIS Database.