Drop Down MenusCSS Drop Down MenuPure CSS Dropdown Menu

PostgreSQL Killing Long Running Query and Most Resource Taken Process Script

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;
Sample OutPut:
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)

Comments

Popular posts from this blog

ORA-01261: Parameter db_recovery_file_dest destination string cannot be translated ORA-01262: Stat failed on a file destination directory Linux-x86_64 Error: 2: No such file or directory

PostgreSQL pgBadger

How to Get Table Size, Database Size, Indexes Size, schema Size, Tablespace Size, column Size in PostgreSQL Database

PostgreSQL Pgbadger Installation On Linux

Migrating From Oracle to PostgreSQL using ora2pg open source tools