使用python脚本监控postgres中超时的僵尸连接并将其释放
#coding:utf-8
from db.SqlUtil import *
import psycopg2.extras
from datetime import datetime,timedelta
import time
import os,sys
from subprocess import *
sql_conn=Driver().getConn()
cursor = sql_conn.cursor(cursor_factory=psycopg2.extras.DictCursor)
cursor.execute("select * from pg_stat_activity WHERE state='idle' ORDER BY backend_start")
now=time.time()
for obj in cursor.fetchall():
t=obj["query_start"]
spT=now-time.mktime(t.timetuple())
spTH=spT/3600
if spTH>5:
pid=obj["pid"]
cmd="kill %s" % pid
Popen(cmd, shell=True, stdout=PIPE)
cursor.close()
sql_conn.close()
将上段代码保存为pgMonitor.py并添加到linux的corntab中定时执行即可