psql:致命:角色的连接太多 [英] psql: FATAL: too many connections for role

问题描述

我尝试使用以下命令连接到数据库服务器:

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.

推荐答案

从集群的任意 DB 内部:

Catch 22:您需要先连接到数据库.也许您可以以其他用户身份连接?(默认情况下，某些连接是为具有 superuser_reserved_connections 设置.)

要获取此用户的每个连接的详细信息:

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() 和 pg_terminate_backend() 在手册中.

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).

您可以使用 ps 调查哪些进程可能有问题:

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.

或者用 SIGKILL 代替 SIGTERM:

kill -9 123457689

这篇关于psql:致命:角色的连接太多的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！