FATAL: connection limit exceeded for non-superusers

Products

• VMware Tanzu Greenplum
• VMware Tanzu Greenplum / Gemfire
• VMware Tanzu Data Suite

Issue/Introduction

The error "connection limit exceeded for non-superusers" is an indication that all allowed connections to the database have been used up.

Administrators may still be able to connect, depending on the source of the issue, but ultimately the result is the same: users cannot connect

Environment

Greenplum Database.

Resolution

The number of non-superusers allowed in the database is the difference between max_connections and superuser_reserved_connections:

You can check the current values by running: gpconfig -s max_connections gpconfig -s superuser_reserved_connections

The default value for max_connections is 250 for the master and 750 for segments and 3 for superuser_reserved_connections.

The quickest solution is to increase the value and allow more connections, but there may be an underlying issue explaining why available connections have already been filled. It is recommended that you verify this before proceeding, as a higher connection parameter will mean an increased memory demand, so ensure there is enough memory available beforehand.

gpconfig -c max_connections -v VALUE_FOR_SEGMENTS -m VALUE_FOR_MASTER

To see current connections, use the following command:
select * from pg_stat_activity

Check the number of connections in the master and segments:
ps -ef | grep postgres | grep con[0-9] | grep -v grep | wc -l

Check the number of superuser connections:
SELECT usesuper, count(*) FROM pg_stat_activity JOIN pg_user USING (usesysid) GROUP BY usesuper;

Role connection limit:
select rolname,rolconnlimit from pg_roles;

View orphaned queries:

SELECT 
   w.relation::regclass AS "Table"
   , w.mode AS "Waiters Mode"
   , w.pid AS "Waiters PID"
   , w.mppsessionid AS "Waiters SessionID"
   , b.mode AS "Blockers Mode"
   , b.pid AS "Blockers PID"
   , b.mppsessionid AS "Blockers SessionID"
   , (select 'Hostname: ' || c.hostname ||' Content: '|| c.content || ' Port: ' || port from gp_segment_configuration c where c.content=b.gp_segment_id and 
      role='p') AS "Blocking Segment" 
FROM 
     pg_catalog.pg_locks AS w
    , pg_catalog.pg_locks AS b 
Where 
       ((w."database" = b."database" AND w.relation = b.relation) OR w.transactionid = b.transaction) AND w.granted='f' AND b.granted='t' AND w.mppsessionid