• Blog posts

    Bio

    by David Crowther

    DC_7th_A01.png

    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.

    DC_7th_A02.jpg

    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.

    DC_7th_A03.jpg

    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.

    DC_7th_A04.jpg

    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.

    DC_7th_A05.jpg

    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.

     DC_7th_A06.jpg

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

    DC_7th_A07.jpg

    … 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!

    DC_7th_A08.jpg

    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.

    DC_7th_A09.jpg

    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';

    DC_7th_A10.jpg

    … 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.

    DC_7th_A11.jpg

    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';

    DC_7th_A12.jpg

    … 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.

     DC_7th_A13.jpg

    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();

    DC_7th_A14.jpg

    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.

    DC_7th_A15.jpg

    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.

    DC_7th_A16.jpg

    DC_7th_A17.jpg

    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.

    DC_7th_A18.jpg