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 find the server is whether standby (slave) or primary(master) in Postgresql replication ?

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

How to Return a Result Set from a PostgreSQL Stored Procedure

7 Steps to configure BDR replication in postgresql

PostgreSQL Pgbadger Installation On Linux