Drop Down MenusCSS Drop Down MenuPure CSS Dropdown Menu

How to kill idle session and what is the shell script for killing idle connection ?

Kill the idle connection if once reached the postgesql maximum threshold time of 2 minutes
 cat /home/script/kill_idle_conn.sh
DATE=`date +'%d%m%G'`
touch $DETAIL
IDLECONNTIME=`$PSQL -d $DB -U $USER -t -p $PORT -c "select now();"`
MAXCONN=`$PSQL -d $DB -U $USER -t -p $PORT -c "show max_connections;"`
CONN=`$PSQL -d $DB -U $USER -t -p $PORT -c "select count(*) from pg_stat_activity;"`
echo "IDLE IN TRANSACTION Connetions Start Time : $IDLECONNTIME" | tee -a  $DETAIL
echo "Maximum Connections Defined : $MAXCONN" | tee -a  $DETAIL
echo "Current Connection Count    : $CONN"  | tee -a  $DETAIL
IDLECONN=`$PSQL -d $DB -U $USER -p $PORT -t -c "select count(*) from pg_stat_activity where state='idle in transaction' ;"`
echo "Count of IDLE IN TRANSACTION connections  more than 15min: $IDLECONN" |tee -a  $DETAIL
echo "Connections IDLE IN TRANSACTION for more than 15 min" >> $DETAIL
$PSQL -d $DB -U $USER -p $PORT  -c "select * from pg_stat_activity where state='idle in transaction' ;" |tee -a $DETAIL
$PSQL -d $DB -U $USER -p $PORT -c " select pg_terminate_backend(pid)from pg_stat_activity where state='idle in transaction' and now()-query_start > interval '2 minutes';"
echo "IDLE Connetions End Time : $IDLECONNTIME" | tee -a  $DETAIL
exit 0;


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 Installation On Linux

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 Schedule PostgreSQL Jobs using pgAgent on Linux plateform