psql: FATAL: too many connections for role

Question

I tried connecting to the database server using the command:

psql -h host_ip -d db_name -U user_name --password

It displays the following line and refuses to connect.

psql: FATAL:  too many connections for role "user_name".

How to close the active connections?
I do not have admin rights for the database. I am just an ordinary user.

Solution 1

From inside any DB of the cluster:

Catch 22: you need to be connected to a database first. Maybe you can connect as another user? (By default, some connections are reserved for superusers with the superuser_reserved_connections setting.)

To get detailed information for each connection by this user:

SELECT *
FROM   pg_stat_activity
WHERE  usename = 'user_name';

As the same user or as superuser you can cancel all (other) connections of a user:

SELECT pg_cancel_backend(pid)     -- (SIGINT)
    -- pg_terminate_backend(pid)  -- the less patient alternative (SIGTERM)
FROM   pg_stat_activity
WHERE  usename = 'user_name'
AND    pid <> pg_backend_pid();

Better be sure it's ok to do so. You don't want to terminate important queries (or connections) that way.

pg_cancel_backend() and pg_terminate_backend() in the manual.

From a Linux shell

Did you start those other connections yourself? Maybe a hanging script of yours? You should be able to kill those (if you are sure it's ok to do so).

You can investigate with ps which processes might be at fault:

ps -aux
ps -aux | grep psql

If you identify a process to kill (better be sure, you do not want to kill the server):

kill  123457689 # pid of process here.

Or with SIGKILL instead of SIGTERM:

kill -9 123457689

Solution 2

I'm pretty new to pgAdmin, but so far I have not utilized the command line. I had the same issue and I found the easiest way to resolve the issue in my case was to simply delete the processes listed in "Database Activity" in the Dashboard.

(just click the X on the left side of the PID)

It's a bit tedious since you must delete each process individually, but doing so should free up your available connections. Hope this is useful.

Solution 3

You need to connect on your postgresql db and run command:

ALTER ROLE your_username CONNECTION LIMIT -1;

Solution 4

Check pool_size this is probably too much or to small set value on local psql settings. You should at first check with pool_size = 10 (like default). This should fix errors of too_many_connections.

View more solutions