终止挂起的查询（在事务中空闲）

Question

我使用Crane Postgres选项在Heroku上运行，并且当我的本地机器崩溃时，我正在从我的本地机器上对数据库运行查询。如果我运行

select * from pg_stat_activity

其中一条记录具有

<IDLE> in transaction

在 current_query_text 列中运行的查询已经被终止，因此我不能删除正在被写入的表。我尝试使用 pg_cancel_backend(N)，它返回 True，但似乎没有任何变化。

我该如何终止这个进程，以便能够删除这张表?

- alan

---

也许问题应该重新表述为“当没有postgres服务器的root访问权限和数据库的超级用户访问权限时，如何终止自己的查询”。这似乎确实是一个非常好的问题……而我不知道答案。 - tobixen

Answer 1

这是一个关于PostgreSQL的通用回答，与Heroku无关。

---

（对于这个问题的简单愚蠢的答案可能是...只需重新启动PostgreSQL！假设这不是可取的或不是一个选项...）

通过运行以下SQL语句找到PID：

SELECT pid , query, * from pg_stat_activity
  WHERE state != 'idle' ORDER BY xact_start;

（根据Postgres的版本，查询可能需要修复 - 最终，只需选择* from pg_stat_activity）。您将在第一列（左侧）找到pid，并且第一行（顶部）很可能是您想要终止的查询。我假设下面的pid是1234。

只要查询是您自己的或您具有超级用户访问权限，您可以通过SQL（即无需shell访问）取消查询：

select pg_cancel_backend(1234);

这是一个“友好”的请求，取消1234查询，希望它会在一段时间后消失。如果需要，以下是一个更加“强制终止”的命令，可以更快地取消它：

select pg_terminate_backend(1234);

如果您具有shell访问权限和root或postgres权限，您也可以通过shell来执行此操作。要“取消”，可以执行以下操作：

kill -INT 1234

而要“终止”，只需简单地：

kill 1234

请勿：

kill -9 1234

...这样经常会导致整个Postgres服务器崩溃，那么最好重启Postgres。Postgres非常强大，所以数据不会损坏，但无论如何，我建议不要使用"kill -9"命令 :-)

---

长时间的“事务空闲”通常意味着事务没有通过“提交”或“回滚”终止，这意味着应用程序存在错误或者没有正确设计以适应事务性数据库的工作方式。应该避免长时间的“事务空闲”，因为它可能会（根据您的使用模式）导致严重的性能问题。

- tobixen

---

我尝试了使用 pg_cancel_backend，但无济于事。我没有 shell 访问权限，也不是超级用户，因此无法使用 pg_terminate_backend 发送 SIGKILL 信号。 - alan

你正在使用哪个版本的Postgres？（提示：select version()）。当使用pg_cancel_backend时，是否收到任何错误消息？ - tobixen

尝试使用pg_cancel_backend，但出现错误消息“必须是超级用户才能信号化其他服务器进程”...意味着显然您需要在服务器上拥有root访问权限或通过某些postgres超级用户（即postgres用户）对数据库进行访问以杀死自己的查询。看起来有点烦人 :-( - tobixen

事实证明，进程被 pg_cancel_backend 取消了，但查询在 pg_stat_activity 中仍然显示一段时间。 - alan

也许这只适用于Heroku。据我所见，在普通的postgres下，需要超级用户才能杀死卡住的进程（我正在测试pg 8.4上的“select pg_sleep(3600);”，并且我得到“ERROR：必须是超级用户才能信号其他服务器进程”）。不过，“空闲事务”又不完全相同。 - tobixen

Answer 2

试一下这个:

select pg_terminate_backend(pid int)

更详细的信息可以在这里找到。这应该是比通过系统杀死进程更 "清洁" 的解决方案。

- evgenek

---

请在您的回答中添加如何获取您的 PID。 - mountainclimber11

Answer 3

你可以安装heroku-pg-extras插件并运行以下命令来获取PID：

heroku pg:locks --app <your-app>

那么就执行：

heroku pg:kill <pid> --app <your-app> 

注意：可以使用--force选项来发出pg_terminate_backend，从而为该查询删除整个连接。

如果heroku pg:locks没有列出任何内容，请尝试heroku pg:ps。

了解更多信息，请查看：
https://devcenter.heroku.com/articles/heroku-postgresql#pg-ps-pg-kill-pg-killall

- davidfrancisco

---

谢谢。尽管如此，我仍然无法终止事务/PID...在导入过程中，我的计算机发生了硬件冻结，我无法终止PID。 :( - dimitarvp

Answer 4

终止所有正在运行的查询：

SELECT pg_cancel_backend(pid) FROM pg_stat_activity WHERE state = 'active'; 

from here

- rogerdpack