postgresql Heroku Postgres：连接太多。我如何杀死这些连接？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/19814740/
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
Heroku Postgres: Too many connections. How do I kill these connections?
提问by Jasper Schulte
I have an app running on Heroku. This app has an Postgres 9.2.4 (Dev) addon installed. To access my online database I use Navicat Postgres. Sometimes Navicat doesn't cleanly close connections it sets up with the Postgres database. The result is that after a while there are 20+ open connections to the Postgres database. My Postgres installs only allows 20 simultanious connections. So with the 20+ open connections my Postgress database is now unreachable (too many connections).
我有一个在 Heroku 上运行的应用程序。此应用程序安装了 Postgres 9.2.4 (Dev) 插件。要访问我的在线数据库，我使用 Navicat Postgres。有时 Navicat 不会完全关闭它与 Postgres 数据库建立的连接。结果是一段时间后有 20 多个打开的连接到 Postgres 数据库。我的 Postgres 安装只允许 20 个同时连接。因此，由于有 20 多个打开的连接，我的 Postgress 数据库现在无法访问（连接太多）。
I know this is a problem of Navicat and I'm trying to solve this on that end. But if it happens (that there are too many connections), how can I solve this (e.g. close all connections).
我知道这是 Navicat 的一个问题，我正在努力解决这个问题。但是如果发生（连接太多），我该如何解决这个问题（例如关闭所有连接）。
I've tried all of the following things, without result.
我已经尝试了以下所有事情，但没有结果。
- Closed Navicat & restarted my computer (OS X 10.9)
- Restarted my Heroku application (
heroku restart) - Tried to restart the online database, but I found out there is no option to do this
- Manually closed all connections from OS X to the IP of the Postgres server
- Restarted our router
- 关闭 Navicat 并重新启动我的电脑 (OS X 10.9)
- 重新启动我的 Heroku 应用程序 (
heroku restart) - 尝试重新启动在线数据库，但我发现没有选项可以执行此操作
- 手动关闭从 OS X 到 Postgres 服务器 IP 的所有连接
- 重新启动我们的路由器
I think it's obvious there are some 'dead' connections at the Postgres side. But how do I close them?
我认为很明显 Postgres 端存在一些“死”连接。但是我如何关闭它们？
回答by John Beynon
Maybe have a look at what heroku pg:killcan do for you? https://devcenter.heroku.com/articles/heroku-postgresql#pg-ps-pg-kill-pg-killall
也许看看heroku pg:kill能为你做什么？https://devcenter.heroku.com/articles/heroku-postgresql#pg-ps-pg-kill-pg-killall
回答by mgojohn
heroku pg:killallwill kill all open connections, but that may be a blunt instrument for your needs.
Interestingly, you can actually kill specific connectionsusing heroku's dataclips.
heroku pg:killall将杀死所有打开的连接，但这可能是满足您需求的生硬工具。有趣的是，您实际上可以使用 heroku 的数据剪辑来终止特定的连接。
To get a detailed list of connections, you can query via dataclips:
要获得详细的连接列表，您可以通过 dataclips 进行查询：
SELECT * FROM pg_stat_activity;
In some cases, you may want to kill all connections associated with an IP address (your laptop or in my case, a server that was now destroyed).
在某些情况下，您可能想要终止与 IP 地址相关的所有连接（您的膝上型电脑，或者在我的情况下，是现在已损坏的服务器）。
You can see how many connections belong to each client IP using:
您可以使用以下命令查看属于每个客户端 IP 的连接数：
SELECT client_addr, count(*)
FROM pg_stat_activity
WHERE client_addr is not null
AND client_addr <> (select client_addr from pg_stat_activity where pid=pg_backend_Tid())
GROUP BY client_addr;
which will list the number of connections per IP excluding the IP that dataclips itself uses.
这将列出每个 IP 的连接数，不包括 dataclips 本身使用的 IP。
To actually kill the connections, you pass their "pid" to pg_terminate_backend(). In the simple case:
要真正终止连接，您可以将它们的“pid”传递给 pg_terminate_backend()。在简单的情况下：
SELECT pg_terminate_backend(1234)
where 1234 is the offending PID you found in pg_stat_activity.
其中 1234 是您在 pg_stat_activity 中找到的有问题的 PID。
In my case, I wanted to kill all connections associated with a (now dead) server, so I used:
就我而言，我想终止与（现在已死的）服务器相关的所有连接，所以我使用了：
SELECT pg_terminate_backend(pid), host(client_addr)
FROM pg_stat_activity
WHERE host(client_addr) = 'IP HERE'
回答by Robert H
From the Heroku documentation(emphasis is mine):
从Heroku 文档（重点是我的）：
FATAL: too many connections for role
致命：角色连接过多
FATAL: too many connections for role "[role name]" This occurs on Starter Tier (dev and basic) plans, which have a max connection limit of 20 per user. To resolve this error, close some connections to your database by stopping background workers, reducing the number of dynos, or restarting your application in case it has created connection leaks over time.A discussion on handling connections in a Rails application can be found here.
致命：角色“[角色名称]”的连接太多这发生在 Starter Tier（开发和基本）计划中，每个用户的最大连接限制为 20。要解决此错误，请通过停止后台工作程序、减少 dyno 数量或重新启动应用程序来关闭与数据库的某些连接，以防它随着时间的推移造成连接泄漏。可以在此处找到有关在 Rails 应用程序中处理连接的讨论。
Because Heroku does not provide superuser accessyour options are rather limited to the above.
因为 Heroku不提供超级用户访问权限，所以您的选择仅限于上述内容。
回答by Ebin Joy
Restart server
重启服务器
heroku restart --app <app_name>
It will close all connection and restart.
它将关闭所有连接并重新启动。
回答by Neeraj Sewani
1).First login into Heroku with your correct id (in case you have multiple accounts) using heroku login.
2).Then, run heroku appsto get a list of your apps and copy the name of the one which is having the PostgreSQL db installed.
3).Finally, run heroku pg:killall --app appnameto get all the connections terminated.
1）。首先使用正确的 ID 登录 Heroku（如果您有多个帐户）heroku login。
2）。然后，运行heroku apps以获取您的应用程序列表并复制安装了 PostgreSQL 数据库的应用程序的名称。
3）。最后，运行heroku pg:killall --app appname以终止所有连接。
回答by bma
As the superuser (eg. "postgres"), you can kill every session but your current one with a query like this:
作为超级用户（例如“postgres”），您可以使用如下查询终止除当前会话之外的所有会话：
select pg_cancel_backend(pid)
from pg_stat_activity
where pid <> pg_backend_pid();
If they do not go away, you might have to use a stronger "kill", but certainly test with pg_cancel_backend()first.
如果它们没有消失，您可能必须使用更强的“杀戮”，但一定pg_cancel_backend()要先进行测试。
select pg_terminate_backend(pid)
from pg_stat_activity
where pid <> pg_backend_pid();
