是否有任何工具可以在没有更多连接插槽时重新启动PostgreSQL [英] Is there any tool that allows to restart PostgreSQL when there is no more connection slots

问题描述

有没有自动工具可以重启PostgreSQL或在没有更多连接插槽时杀死所有阅读器上的所有挂起查询？
问题是有时由于未知原因（可能是缓慢的查询），正在使用所有与读取器的连接，并且我们需要快速而又肮脏的解决方案来监视读取器并在事件发生时杀死所有查询。
我知道它更有可能掩盖实际问题，但目前我的任务是编写脚本来执行此操作，但是我敢肯定，已经有我可以使用的工具了。
我所做的研究使我得出以下结果：Nagios。
不幸的是，我仍在等待服务器支持团队处理我的票。

Is there any automated tool that allows to restart PostgreSQL or just kill all pending queries on all readers when there is no more connection slots? The problem is that sometimes all connections to the readers are in use because of unknown reason (slow query may be) and we need quick and dirty solution to monitor the readers and kill all queries when such as event occurs. I know it's more likely workaround masking the real problem but currently the task I have is to write a script doing this, but I am pretty sure there is already tool I can use instead. The research I've done led me to the following result: Nagios. Unfortunately, I'm still waiting my ticket to be processed by the server support team.

推荐答案

Postgresql版本9.4

Postgresql Version 9.4

参考统计收集器

select pid, query from pg_stat_activity where waiting='t';

参考功能管理：pg_cancel_backend

select pg_cancel_backend(pid of the postgres process); 

一个简单的bash脚本。您甚至可以从命令行传入参数

a Simple bash script. You could even pass in paramters from the command line

#!/bin/bash
dbname="Test"
username="postgres"
vartest=`psql -d $dbname -U $username -h localhost -p 5432 -t -c "select  pid from pg_stat_activity where waiting='f';"`
for p_var in ${vartest[@]}
do
# This is an admin function please read more on function-admin in the postgres documentation

# q_result= `psql -d $dbname -U $username -h localhost -p 5432 -t -c "SELECT pg_cancel_backend('$p_var');"`

# sanitiy check just grabs the pid and related query

q_result=`psql -d $dbname -U $username -h localhost -p 5432 -t -c "select pid, query  from pg_stat_activity where  pid='$p_var';"`
done

这篇关于是否有任何工具可以在没有更多连接插槽时重新启动PostgreSQL的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！