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

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

PostgreSQL Pgbadger Installation On Linux

PostgreSQL pgBadger

How to configure Replication Manager (repmgr) ?

Migrating From Oracle to PostgreSQL using ora2pg open source tools