Script to killing ALL IDLE Connection In Postgres Database

1.MORE I/O POSTGRESQL KILLING ALL IDLE CONNECTION FOR MONITORING PURPOSES:
This following script is used for killing all idle connection and you can use it as a monitoring puposes like how many idle query is running for a hour and how many ‘idle’, ‘idle in transaction’, ‘idle in transaction (aborted)’ and  ‘disabled’ queries are running for a hour and when it killed and how many hours is taken,schedule this script every one hours don’t schedule it every 2 minutes and 1 minutes because it will be taken more I/O(4 times) than following second script,if you want shedule this script every 1 or 2 minutes use second script it will take less I/O.

cat /home/postgres/idle.sh
#!/bin/bash
HOSTNAME=`hostname`
PSQL="/opt/PostgreSQL/9.3/bin/psql"
PORT=5432
HOST="localhost"
DB="template1"
USER="postgres"
DATE2=`date +'%d%m%G'`
DETAIL2="/tmp/KILL_ALL_IDLE_$DATE2.log"
touch $DETAIL2

IDLECONNTIME2=`$PSQL -d $DB -U $USER -t -p $PORT -c "select now();"`
MAXCONN2=`$PSQL -d $DB -U $USER -t -p $PORT -c "show max_connections;"`
CONN2=`$PSQL -d $DB -U $USER -t -p $PORT -c "select count(*) from pg_stat_activity;"`

echo "ALL IDLE Connetions Start Time (script start Time before killing session) : $IDLECONNTIME2" | tee -a  $DETAIL2
echo "Maximum Connections Defined : $MAXCONN2" | tee -a  $DETAIL2
echo "Current Connection Count    : $CONN2"  | tee -a  $DETAIL2

IDLECONN2=`$PSQL -d $DB -U $USER -p $PORT 
"select count(*) from pg_stat_activity WHERE  pid <> pg_backend_pid() 
AND state in ('idle', 'idle in transaction', 'idle in transaction (aborted)', 'disabled') 
AND state_change < current_timestamp - INTERVAL '5' MINUTE;"`

echo "Count of ALL IDLE  connections  more than 5min: $IDLECONN2" |tee -a  $DETAIL2
echo "Connections ALL IDLE  for more than 5 min" >> $DETAIL2


$PSQL -d $DB -U $USER -p $PORT -c " select datname, pid,now() - query_start as "runtime",state,state_change,query from pg_stat_activity WHERE  pid <> pg_backend_pid() 
AND state in ('idle', 'idle in transaction', 'idle in transaction (aborted)', 'disabled') 
AND state_change < current_timestamp - INTERVAL '5' MINUTE;" >> $DETAIL2



$PSQL -d $DB -U $USER -p $PORT -c " SELECT pg_terminate_backend(pid) FROM pg_stat_activity WHERE  pid <> pg_backend_pid() 
AND state in ('idle', 'idle in transaction', 'idle in transaction (aborted)', 'disabled') 
AND state_change < current_timestamp - INTERVAL '5' MINUTE;" >> $DETAIL2

echo "ALL IDLE Connetions End Time (script End Time After session killed) : $IDLECONNTIME2" | tee -a  $DETAIL2
exit 0;

=========================second script==================================
2.LESS I/O POSTGRESQL KILLING ALL IDLE CONNECTION:

How to schedule a job every 1,2,3 minutes using crontab linux utility?

Ans:
I given below some sample crontab sheduled time,Example for every 1 minutes,every 2 minutes and every 3 minutes want run “ALL_IDLE_CONN.sh” script.

CRONTAB SCHEDULER:

crontab -e
crontab every 1 minute is a commonly used cron schedule:
* * * * * sh /home/postgres/ALL_IDLE_CONN.sh

crontab every 2 minute is a commonly used cron schedule:
*/2 * * * * sh /home/postgres/ALL_IDLE_CONN.sh

crontab every 3 minute is a commonly used cron schedule:
*/3 * * * * sh /home/postgres/ALL_IDLE_CONN.sh

How to write a script for postgreSQL killing ALL IDLE connection ?
Ans:
All idle connection killing script:
#this following script is killed all idle connection ( ‘idle’, ‘idle in transaction’, ‘idle in transaction (aborted)’, ‘disabled’ ) status once idle connectin reached 5 minutes this following script want to run every 3 minutes once so i scheduled it using crontab,i adviced to scheduled it as per your environment transaction idle connection, use following script to find out idle connection timing

vi /home/postgres/ALL_IDLE_CONN.sh

#!/bin/bash

HOSTNAME=`hostname`
PSQL="/opt/PostgreSQL/9.3/bin/psql"
PORT=5432
HOST="localhost"
DB="template1"
USER="postgres"
DATE2=`date +'%d%m%G'`
DETAIL2="/tmp/KILL_ALL_IDLE_$DATE2.log"
touch $DETAIL2

 # this is help to reduces the I/O not beffer sir
$PSQL -d $DB -U $USER -p $PORT -c " SELECT pg_terminate_backend(pid) FROM pg_stat_activity WHERE  pid <> pg_backend_pid() 
AND state in ('idle', 'idle in transaction', 'idle in transaction (aborted)', 'disabled') 
AND state_change < current_timestamp - INTERVAL '5' MINUTE;" >> $DETAIL2
exit 0;

=======================================================================
3.very Less I/O than second script:

vi /home/postgres/ALL_IDLE_CONN.sh

#!/bin/bash

HOSTNAME=`hostname`
PSQL="/opt/PostgreSQL/9.3/bin/psql"
PORT=5432
HOST="localhost"
DB="template1"
USER="postgres"


 # this is help to reduces the I/O not beffer sir
$PSQL -d $DB -U $USER -p $PORT -c " SELECT pg_terminate_backend(pid) FROM pg_stat_activity WHERE  pid <> pg_backend_pid() 
AND state in ('idle', 'idle in transaction', 'idle in transaction (aborted)', 'disabled') 
AND state_change < current_timestamp - INTERVAL '5' MINUTE;"
exit 0;