I needed a utility to alert our team when any long running queries were running on a production postgres cluster. I came up with the following python code that achieves just that. This would alert slack if an active query exceeds 45 mins. The script takes in user parameters as well, I will demonstrate the way to call it. Hope it helps someone. <\/p>\n
CRON CALL:<\/p>\n
\r\n### postgres long running query check ###\r\n*\/15 * * * * \/usr\/bin\/python2.7 \/home\/postgres\/bin\/pg_long_running_query.py --database proddb --dbhost proddb01 --user postgres --alert_time_mins 45 >> \/home\/postgres\/pg_long_running_query.log 2>&1 \r\n<\/pre>\nCODE:<\/p>\n
\r\n#!\/usr\/bin\/python2.7\r\n\r\n__author__ = \"Jason Ralph\"\r\n\r\n\r\nimport psycopg2\r\nimport psycopg2.extras\r\nimport argparse\r\nimport urllib\r\n\r\n\r\ndef send_message_to_slack(text):\r\n import requests\r\n import json\r\n\r\n webhook_url = 'https:\/\/hooks.slack.com\/services\/--redacted--'\r\n slack_data = {'text': \"%s\" % text}\r\n\r\n response = requests.post(\r\n webhook_url, data=json.dumps(slack_data),\r\n headers={'Content-Type': 'application\/json'}\r\n )\r\n if response.status_code != 200:\r\n raise ValueError(\r\n 'Request to slack returned an error %s, the response is:\\n%s'\r\n % (response.status_code, response.text)\r\n )\r\n\r\n\r\ndef get_long_running_queries():\r\n parser = argparse.ArgumentParser(description='Check long Running '\r\n 'Queries On Postgres '\r\n 'Databases And Alert')\r\n parser.add_argument('--database', help='target database')\r\n parser.add_argument('--dbhost', help='target dbhost')\r\n parser.add_argument('--user', help='database user')\r\n parser.add_argument('--alert_time_mins', help='alert time in mins: e.g 30')\r\n args = parser.parse_args()\r\n\r\n conn = psycopg2.connect(\"dbname='%s' host='%s' user='%s' port=5432\" \r\n % (args.database, args.dbhost, args.user))\r\n\r\n sql = (\"\"\"SELECT pid, usename,\r\n now() - pg_stat_activity.query_start AS duration,\r\n query, state FROM pg_stat_activity \r\n WHERE (now() - pg_stat_activity.query_start) > interval\r\n '\"%s\" minutes';\"\"\") % args.alert_time_mins\r\n\r\n cursor = conn.cursor(cursor_factory=psycopg2.extras.DictCursor)\r\n cursor.execute(sql)\r\n count = 0\r\n while True:\r\n row = cursor.fetchone()\r\n if row is None:\r\n break\r\n if row['usename'] == 'postgres':\r\n continue\r\n if row['state'] == 'idle':\r\n continue\r\n count += 1\r\n pid = row['pid']\r\n user = row['usename']\r\n duration = row['duration']\r\n query = row['query']\r\n state = row['state']\r\n msg_items = ['LONG RUNNING QUERY ON HOST: %s\\n'\r\n % args.dbhost, 'PID: %s\\n' % pid,\r\n 'DURATION: %s\\n' % duration,\r\n 'QUERY: %s\\n' % query,\r\n 'STATE: %s\\n' % state,\r\n 'USER: %s\\n' % user,\r\n 'COUNT: %s\\n' % count] \r\n msg = ''.join(msg_items)\r\n send_message_to_slack(msg)\r\n conn.close()\r\n\r\ndef main():\r\n get_long_running_queries()\r\n\r\nif __name__ == '__main__':\r\n main()\r\n<\/pre>\nSLACK MESSAGE:<\/p>\n
\r\nLONG RUNNING QUERY ON HOST: proddb01\r\nPID: 30270\r\nDURATION: 0:55:02.748624\r\nQUERY: SELECT --redacted--\r\nSTATE: active\r\nUSER: dbuser\r\nCOUNT: 1\r\n<\/pre>\n","protected":false},"excerpt":{"rendered":"I needed a utility to alert our team when any long running queries were running on a production postgres cluster. I came up with the following python code that achieves just that. This would alert slack if an active query exceeds 45 mins. The script takes in user parameters as well, I will demonstrate the […]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[1],"tags":[],"class_list":["post-731","post","type-post","status-publish","format-standard","hentry","category-general-code"],"_links":{"self":[{"href":"https:\/\/jasonralph.org\/index.php?rest_route=\/wp\/v2\/posts\/731","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/jasonralph.org\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/jasonralph.org\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/jasonralph.org\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/jasonralph.org\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=731"}],"version-history":[{"count":3,"href":"https:\/\/jasonralph.org\/index.php?rest_route=\/wp\/v2\/posts\/731\/revisions"}],"predecessor-version":[{"id":735,"href":"https:\/\/jasonralph.org\/index.php?rest_route=\/wp\/v2\/posts\/731\/revisions\/735"}],"wp:attachment":[{"href":"https:\/\/jasonralph.org\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=731"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/jasonralph.org\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=731"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/jasonralph.org\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=731"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}