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：您需要先连接到数据库。

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?

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

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 that way.

pg_cancel_backend（）和 pg_terminate_backend（）

您自己启动了其他连接吗？也许你的悬挂脚本？

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

如果你确定一个进程要杀死（更好的确定， / em>想杀死服务器）：

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

kill  12457689 # pid of process here.

或使用 SIGKILL $ c> SIGTERM ：

Or with SIGKILL instead of SIGTERM:

kill -9 12457689

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