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

PostgreSQL pgBadger

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

Migrating From Oracle to PostgreSQL using ora2pg open source tools

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

How to configure Replication Manager (repmgr) ?