RDBMS - CAST Storage Service - Error - Sorry too many clients

This page provides the solution to the error of Sorry, too many clients that comes up while connecting to CAST Storage Service (i.e. with the CAST Dashboard or with any other CAST application).

Below is the step-by-step scenario leading to the problem:

1. Open Dashboard or any other CAST Application.
2. Get the error of Sorry, too many clients.

The impact of the problem on the analysis or the dashboard is: The users will not be able to connect to CAST Storage Service through the CAST application.

The Sorry, too many clients error message can occur whenever you attempt to connect to the CAST Storage Service (i.e. with the CAST Dashboard or with any other CAST application) and when the maximum number of allowed simultaneous connections is exceeded.

The maximum number of allowed connections is defined in the following file path (assumes you have installed the CAST Storage Service in the default location)

%PROGRAMFILES%\CAST\CASTStorageService\db_data\postgresql.conf

If you or your users are experiencing this error on a regular basis, use the following two recommendations to work around the issue

1. Increase the maximum number of allowed connections 

   1. Modify the postgresql.conf file (with a text editor) to increase the maximum number of allowed connections. Change the max_connections parameter from the default 100 to 250: 

      max_connections = 250
      

      Note that 250 is an absolute maximum - if you configure a higher number, the CAST Storage Service will ignore it and apply the 250 limit.

      Note also that you will need to restart the CAST Storage Service in order for the change to be taken into account.

2. Close idle connections if the error message persists .

   1. If you or your users are still experiencing this error message even after increasing the maximum number of allowed connections, then you can use the following queries to identify and terminate connections and/or processes that have been idle for a long period of time:
      

      1. View all open connections

         select * from pg_stat_activity
         

      2. View processes with long IDLE times

         select pid, usename, now()-query_start
         from pg_stat_activity
         where lower(state) like '%idle%'
         and now()-query_start >  interval '5 minutes'; -- Define the period after which the connection will be timed out
         

      3. Terminate processes with long IDLE times

         select pid, usename, now()-query_start,
         (SELECT pg_terminate_backend(pid)) -- Query to terminate the process
         from pg_stat_activity
         where lower(state) like '%idle%'
         and now()-query_start >  interval '5 minutes'; -- Define the period after which the connection will be timed out
         

         Assuming you put (and adapt) the above script in the KillingScript.sql file, you then could execute it by running a command like:

         psql -h localhost -p 2280 -d postgres -t -f KillingScript.sql

       3. You can also modify the idle_in_transaction_session_timeout parameter in the postgresql.conf file depending on the requirement. This value is to be set in milliseconds. For example, 600000 milliseconds which is equivalent to 10 minutes.

Impact of the solution on the analysis or the dashboard is: The users will be able to connect to CAST Storage Service through CAST Applications.