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.
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.
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;
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.
Please check how many connections are allowed on that user and you can just kill other connections for that user and log in again. But it's better to just increase the connection limit for that user.
This issue mostly arises on PgAdmin. It seems like after all these years still this issue persists.
This will drop existing connections except for yours:
Query pg_stat_activity and get the pid values you want to kill, then issue SELECT pg_terminate_backend(pid int) to them.
PostgreSQL 9.2 and above:
SELECT pg_terminate_backend(pg_stat_activity.pid)
FROM pg_stat_activity
WHERE pg_stat_activity.datname = 'TARGET_DB' -- ← change this to your DB
AND pid <> pg_backend_pid();
PostgreSQL 9.1 and below:
SELECT pg_terminate_backend(pg_stat_activity.procpid)
FROM pg_stat_activity
WHERE pg_stat_activity.datname = 'TARGET_DB' -- ← change this to your DB
AND procpid <> pg_backend_pid();
from https://stackoverflow.com/a/5408501/13813241 (duplicate)
I was getting this for a specific incident. Django Development. I had a shell open to query django models. I also have the dev server running. I was using elephantsql for testing/prototyping. So it threw error. Once I exited out of django manage.py shell, it started working.
