Killing long running idle postgres pids

#!/usr/bin/env python
# -*- coding: utf-8 -*-

"""
    Utility to check for idle running queries over a long time
    Will kill the pid and send an email to error-account with
    some infos concerning the killed pids/queries

    Place it on db server. Best used with crontab.

    For test purposes debug can be set to 1
    In debug-mode the queries are only displayed - no killing occurs/ no mail (for manual call)

"""

import os
import signal
import psycopg2
from email.mime.text import MIMEText
import smtplib
import sys
import socket


debug = 0
maxMinutes = 240 # 4h
reportMail = 'error@myserver.com'

# no config beyond this point needed

host = socket.gethostbyaddr(socket.gethostname())[0]
sender = 'longrunner@'+ host
smtpserver = 'smtp.myserver.com'
usetls = True
smtpusername = ''
smtppassword = ''


def sendmail(recipient, subject, content):
    msg = MIMEText(content)
    msg['From'] = sender
    msg['To'] = recipient
    msg['Subject'] = subject
    server = smtplib.SMTP(smtpserver)
    if usetls:
        server.starttls()
    if smtpusername and smtppassword:
        server.login(smtpusername, smtppassword)
    server.sendmail(sender, recipient, msg.as_string())
    server.quit()



def main():
    try:
        conn = psycopg2.connect("dbname='postgres' host='127.0.0.1' user='postgres' password=''")
    except:
        print "Could not connect to local database [127.0.0.1]"

    cur = conn.cursor()
    sql = """SELECT
        /* 00 */ pid,
        /* 01 */ datname,
        /* 02 */ client_addr,
        /* 03 */ substring(query,0,160),
        /* 04 */ state,
        /* 05 */ waiting,
        /* 06 */ (now()-query_start) AS elapsed,
        /* 07 */ (extract(EPOCH FROM (now()-query_start))/60)::INTEGER AS minutes
        FROM pg_stat_activity
        WHERE query!='<IDLE>' AND query!='DISCARD ALL'
        ORDER BY elapsed ASC;"""

    try:
        cur.execute(sql)
    except:
        print "Querying db failed"

    try:
        rows = cur.fetchall()
    except:
        print "Could not fetch resultset"

    counter = 0
    mailbody = ""
    for row in rows:
        if row[7] <= maxMinutes:
            continue

        line = """pid=%s minutes=%s state=%s db=%s ip=%s\n\tquery=%s\n\n""" % (row[0], row[7], row[4], row[1], row[2], row[3])
        mailbody += line
        if debug==1:
            print line

        if debug==0:
            os.kill(row[0], signal.SIGQUIT) #if not working try: SIGKILL
            counter += 1

    if counter > 0:
        mailheader = """The following %s pids have been killed [limit= %s minutes]:\n\n""" % (counter, maxMinutes)
        mailbody = mailheader + mailbody
        sendmail(reportMail, 'LongRunner: '+ host, mailbody)
    sys.exit(0)

if __name__ == '__main__':
    main()