Script for Finding & killing postgres long running query

This script is mostly used for will display  what are the postgresql query is running more than one minutes and it will display query with PID using this PID you can kill what query is taken more CPU utilization and time.
Before running this script you can monitor Using “top -c” command and you can kill particular process using top command PID for example if unwanted backup is running mistaken schedule crontab then kill that job/process using this Linux shell script

As a dba I suggested don’t use kill -9 instead of pg_cancel and pg_terminate command

cat kill.sh
echo " "
echo " "
HOSTNAME=`hostname`
PSQL=" /opt/PostgreSQL/9.3/bin/psql"
PORT=5432
HOST="localhost"
DB="template1"
USER="postgres"
echo "------***WHAT ARE THE QUERY IS RUNING MORE THAN 1 MINUTES***------"
$PSQL -d $DB -U $USER -p $PORT <<EOF
\pset format wrapped
SELECT pid, now() - query_start as "runtime", usename, datname, state, query
  FROM  pg_stat_activity
  WHERE now() - query_start > '1 minutes'::interval
 ORDER BY runtime DESC;

EOF

echo "Enter the value of PID which process you want to kill";
read pid
$PSQL -d $DB -U $USER -p $PORT <<EOF
SELECT pg_terminate_backend(pid) FROM pg_stat_activity WHERE pid= $pid;
EOF
exit 0;

bash-4.1$ sh kill.sh


------***WHAT ARE THE QUERY IS RUNING MORE THAN 1 MINUTES***------
Output format is wrapped.
  pid  |     runtime     |  usename  | datname  | state |   query
-------+-----------------+-----------+----------+-------+------------
 30432 | 00:02:22.036876 | emutation | mhrordhu | idle  | unlisten *
 31761 | 00:02:21.739505 | emutation | mhrordhu | idle  | unlisten *
 

Enter the value of PID which process you want to kill
30432
 pg_terminate_backend
----------------------
 t
(1 row)