psql: FATAL: too many connections for role
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
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.
You need to connect on your postgresql db and run command:
ALTER ROLE your_username CONNECTION LIMIT -1;