问题描述
我尝试使用以下命令连接到数据库服务器:
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:致命:角色的连接太多的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!