SQL psql：致命：角色连接过多
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/25058649/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
psql: FATAL: too many connections for role
提问by user3286661
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.
如何关闭活动连接？
我没有数据库的管理员权限。我只是一个普通用户。
回答by Erwin Brandstetter
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?
第 22 条：您需要先连接到数据库。也许您可以作为另一个用户连接？
To get detailed information for each connection by this user:
要获取此用户的每个连接的详细信息：
SELECT *
FROM pg_stat_activity
WHERE usename = 'user_name';
As the same useror 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()and pg_terminate_backend()in the manual.
pg_cancel_backend()并pg_terminate_backend()在手册中。
From a Linux shell
从 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 pswhich processes might be at fault:
您可以调查ps哪些进程可能有问题：
ps -aux
ps -aux | grep psql
If you identify a process to kill (better be sure, you do notwant to kill the server):
如果您发现一个进程杀死（最好是肯定的，你就不会想杀死服务器）：
kill 12457689 # pid of process here.
Or with SIGKILLinstead of SIGTERM:
或者用SIGKILL代替SIGTERM：
kill -9 12457689
回答by Christian Meyer
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.
我对 pgAdmin 还很陌生，但到目前为止我还没有使用过命令行。我遇到了同样的问题，我发现解决我的问题的最简单方法是简单地删除仪表板中“数据库活动”中列出的进程。
(just click the X on the left side of the PID)
（只需单击PID左侧的X）
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.
这有点乏味，因为您必须单独删除每个进程，但这样做应该会释放您的可用连接。希望这是有用的。
回答by Dmitry S.
Check pool_sizethis is probably too much or to small set value on local psqlsettings. You should at first check with pool_size = 10(like default). This should fix errors of too_many_connections.
检查pool_size这可能是太多或本地psql设置的小设置值。您应该首先检查pool_size = 10（如默认）。这应该修复too_many_connections.
回答by Walterwhites
You need to connect on your postgresql db and run command:
您需要连接您的 postgresql 数据库并运行命令：
ALTER ROLE your_username CONNECTION LIMIT -1;
