Kill long running sql queries on postges server

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

"""
    Utility to check for idle running queries over a long time

    Can 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.

    Params:
    -s = Simulate the kill (outputs the queries and some info without touching the pid)
    -k = Kill the long runners pids, send a mail

    If both params are set (-s and -k) the -s param is dominant (which means no killing)
    for security reasons

"""

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


maxMinutes = 240 # 4h
reportMail = 'error@myserver.com'
host = socket.getfqdn()
sender = 'longrunner@'+ host
smtpserver = 'smtp.myserver.com'
usetls = True
smtpusername = ''
smtppassword = ''


# no config beyond this point needed
debug = 0
kill = 0


def usage():
    print("\n\nUsage:\n python %s [-s|-k]\n" % (sys.argv[0]))
    print("Kill the long running sql queries (over %s minutes) and \nsend a mail to %s\n" % (maxMinutes, reportMail))
    print("Params:")
    print("-s = simulate (no kill)")
    print("-k = kill long running query pids\n")
    sys.exit(0)


def terminate(msg):
    msg = "%s [%s]: ERROR - %s" % (time.ctime(), sys.argv[0], msg)
    syslog.syslog(syslog.LOG_ERROR, msg)
    sys.exit(1)


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:
        terminate("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:
        terminate("Querying db failed")

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

    counter = 0
    mailbody = ""
    cmdLine = "Manual command on host %s:\nsudo kill " % (host)

    for row in rows:
        if row[7] <= maxMinutes:
            continue

        # build line with infos for query
        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
        cmdLine += " %s" % (row[0])
        if debug==1:
            print(line)

        # only if kill flag is set kill the pid
        if kill==1:
            os.kill(row[0], signal.SIGTERM) #if not working try: SIGKILL

        counter += 1

    # only if we have killed long runners we send a mail
    if counter > 0:
        # in debug mode output the cmdline to kill the pids manually
        if debug==1:
            print("\n%s\n" % (cmdLine))

        if kill==1:
            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__':
    if (len(sys.argv)==1):
        usage()
    else:
        for opt in sys.argv[1:]:
            if opt == '-k':
                kill=1
                debug=0

            if opt == '-s':
                debug=1
                kill=0

        main()