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

Oracle DBMS SCHEDULER Examples

How to find the server is whether standby (slave) or primary(master) in Postgresql replication ?

7 Steps to configure BDR replication in postgresql

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

vacuumlo - removing large objects orphans from a database PostgreSQL