Drop Down MenusCSS Drop Down MenuPure CSS Dropdown Menu

Script to kill ALL IDLE Connection In postgreSQL

1.MORE I/O POSTGRESQL KILLING ALL IDLE CONNECTION FOR MONITORING PURPOSES:
This following script is used for killing all idle connection and you can use it as a monitoring puposes like how many idle query is running for a hour and how many 'idle', 'idle in transaction', 'idle in transaction (aborted)' and  'disabled' queries are running for a hour and when it killed and how many hours is taken,schedule this script every one hours don't schedule it every 2 minutes and 1 minutes because it will be taken more I/O(4 times) than following second script,if you want shedule this script every 1 or 2 minutes use second script it will take less I/O.
cat /home/postgres/idle.sh
#!/bin/bash
HOSTNAME=`hostname`
PSQL="/opt/PostgreSQL/9.3/bin/psql"
PORT=5432
HOST="localhost"
DB="template1"
USER="postgres"
DATE2=`date +'%d%m%G'`
DETAIL2="/tmp/KILL_ALL_IDLE_$DATE2.log"
touch $DETAIL2

IDLECONNTIME2=`$PSQL -d $DB -U $USER -t -p $PORT -c "select now();"`
MAXCONN2=`$PSQL -d $DB -U $USER -t -p $PORT -c "show max_connections;"`
CONN2=`$PSQL -d $DB -U $USER -t -p $PORT -c "select count(*) from pg_stat_activity;"`

echo "ALL IDLE Connetions Start Time (script start Time before killing session) : $IDLECONNTIME2" | tee -a  $DETAIL2
echo "Maximum Connections Defined : $MAXCONN2" | tee -a  $DETAIL2
echo "Current Connection Count    : $CONN2"  | tee -a  $DETAIL2

IDLECONN2=`$PSQL -d $DB -U $USER -p $PORT 
"select count(*) from pg_stat_activity WHERE  pid <> pg_backend_pid() 
AND state in ('idle', 'idle in transaction', 'idle in transaction (aborted)', 'disabled') 
AND state_change < current_timestamp - INTERVAL '5' MINUTE;"`

echo "Count of ALL IDLE  connections  more than 5min: $IDLECONN2" |tee -a  $DETAIL2
echo "Connections ALL IDLE  for more than 5 min" >> $DETAIL2


$PSQL -d $DB -U $USER -p $PORT -c " select datname, pid,now() - query_start as "runtime",state,state_change,query from pg_stat_activity WHERE  pid <> pg_backend_pid() 
AND state in ('idle', 'idle in transaction', 'idle in transaction (aborted)', 'disabled') 
AND state_change < current_timestamp - INTERVAL '5' MINUTE;" >> $DETAIL2



$PSQL -d $DB -U $USER -p $PORT -c " SELECT pg_terminate_backend(pid) FROM pg_stat_activity WHERE  pid <> pg_backend_pid() 
AND state in ('idle', 'idle in transaction', 'idle in transaction (aborted)', 'disabled') 
AND state_change < current_timestamp - INTERVAL '5' MINUTE;" >> $DETAIL2

echo "ALL IDLE Connetions End Time (script End Time After session killed) : $IDLECONNTIME2" | tee -a  $DETAIL2
exit 0;
=========================second script==================================
2.LESS I/O POSTGRESQL KILLING ALL IDLE CONNECTION:

How to schedule a job every 1,2,3 minutes using crontab linux utility?
Ans:
I given below some sample crontab sheduled time,Example for every 1 minutes,every 2 minutes and every 3 minutes want run "ALL_IDLE_CONN.sh" script.

CRONTAB SCHEDULER:
crontab -e
crontab every 1 minute is a commonly used cron schedule:
* * * * * sh /home/postgres/ALL_IDLE_CONN.sh

crontab every 2 minute is a commonly used cron schedule:
*/2 * * * * sh /home/postgres/ALL_IDLE_CONN.sh

crontab every 3 minute is a commonly used cron schedule:
*/3 * * * * sh /home/postgres/ALL_IDLE_CONN.sh
How to write a script for postgreSQL killing ALL IDLE connection ?
Ans:
All idle connection killing script:
#this following script is killed all idle connection ( 'idle', 'idle in transaction', 'idle in transaction (aborted)', 'disabled' ) status once idle connectin reached 5 minutes this following script want to run every 3 minutes once so i scheduled it using crontab,i adviced to scheduled it as per your environment transaction idle connection, use following script to find out idle connection timing
vi /home/postgres/ALL_IDLE_CONN.sh

#!/bin/bash

HOSTNAME=`hostname`
PSQL="/opt/PostgreSQL/9.3/bin/psql"
PORT=5432
HOST="localhost"
DB="template1"
USER="postgres"
DATE2=`date +'%d%m%G'`
DETAIL2="/tmp/KILL_ALL_IDLE_$DATE2.log"
touch $DETAIL2

 # this is help to reduces the I/O not beffer sir
$PSQL -d $DB -U $USER -p $PORT -c " SELECT pg_terminate_backend(pid) FROM pg_stat_activity WHERE  pid <> pg_backend_pid() 
AND state in ('idle', 'idle in transaction', 'idle in transaction (aborted)', 'disabled') 
AND state_change < current_timestamp - INTERVAL '5' MINUTE;" >> $DETAIL2
exit 0;  
=======================================================================
3.very Less I/O than second script:
vi /home/postgres/ALL_IDLE_CONN.sh

#!/bin/bash

HOSTNAME=`hostname`
PSQL="/opt/PostgreSQL/9.3/bin/psql"
PORT=5432
HOST="localhost"
DB="template1"
USER="postgres"


 # this is help to reduces the I/O not beffer sir
$PSQL -d $DB -U $USER -p $PORT -c " SELECT pg_terminate_backend(pid) FROM pg_stat_activity WHERE  pid <> pg_backend_pid() 
AND state in ('idle', 'idle in transaction', 'idle in transaction (aborted)', 'disabled') 
AND state_change < current_timestamp - INTERVAL '5' MINUTE;"
exit 0;  



Comments

Popular posts from this blog

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

PostgreSQL pgBadger

PostgreSQL Pgbadger Installation On Linux

How to configure Replication Manager (repmgr) ?

How To Schedule PostgreSQL Jobs using pgAgent on Linux plateform