Drop Down MenusCSS Drop Down MenuPure CSS Dropdown Menu

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

Ans:
Kill the idle connection if once reached the postgesql maximum threshold time of 2 minutes
 cat /home/script/kill_idle_conn.sh
#!/bin/bash
HOSTNAME=`hostname`
PSQL="/opt/PostgreSQL/9.3/bin/psql"
PORT=5432
HOST="localhost"
DB="template1"
USER="postgres"
DATE=`date +'%d%m%G'`
DETAIL="/tmp/kill_idle_in_tran_detailed-$DATE.log"
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;


















Comments

Popular posts from this blog

PostgreSQL Pgbadger Installation On Linux

PostgreSQL Sequence

Postgresql maximum size

How to configure Replication Manager (repmgr) ?

PostgreSQL pgBadger