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.

Answer 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