FATAL: too many connections for role PostgreSQL

If we receive error “too many connections for role” when, we try to connect to PostgreSQL database. Then it may be allowed connections list is set or connection limit set to zero.

FATAL  too many connections for role PostgreSQL

Why this error throughs?

1. Check the number of active connection for the user with which are trying to login.

Query for that:

SELECT * FROM pg_stat_activity WHERE usename = 'user_name';

In my case:

SELECT * FROM pg_stat_activity WHERE usename = 'elchur';

Output:

2. The above output says, there are no active connections for that user, Then, check connection limit might be configured for that particular user or not.

SELECT rolname, rolconnlimit from pg_authid where rolname='elchur';

Output:

postgres=# SELECT rolname, rolconnlimit from pg_authid where rolname='elchur'; rolname | rolconnlimit ---------+-------------- elchur | 0 (1 row)

Which means connection limit is configured for the user.

Solution:

Increase connection limit by running below query. We are giving maximum connections to 5. If we want a user to log into server unlimited, then give option as -1 instead of 5.

To verify:

postgres=# alter user elchur connection limit 5; ALTER ROLE

For proof:

postgres=> select current_user; current_user -------------- elchur (1 row)

Note: The connection limit is applied during session connection. Raising this limit would never affect any connected users. Lowering the limit doesn’t have any effect either, unless they try to disconnect and reconnect. Check any connections exists or not. If exists, quit them and configure the connections limit.

SELECT count(*)
FROM pg_stat_activity
WHERE usename = ‘elchuru’;
count
——-
1
(1 row)

Related Posts

Responses are currently closed, but you can trackback from your own site.

Comments are closed.

Powered by k2schools
%d bloggers like this: