Drop Down MenusCSS Drop Down MenuPure CSS Dropdown Menu

Script to kill ALL IDLE Connection In postgreSQL

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;  



Comments

  1. im not able to view pages and i can view only few of them. please assist onthis

    ReplyDelete
    Replies
    1. Clear Your browser cache & Cookies Or use CTRL+F5

      Delete
  2. Thank you for sharing! I hope you will continue to have great articles like this to share with everyone!
    Mobile app development company in toronto

    ReplyDelete
  3. Can I simply say what a relief to uncover somebody who really knows what they are discussing on the internet.
    You actually understand how to bring an issue to light and make it important.
    More people have to read this and understand this side of your story.
    It’s surprising you’re not more popular given that you surely possess the gift. If anyone is looking for weight loss medicines at cheaper price you can immediately call on +1-661-2217635 and get the best prescription by the experts.
    Buy Reductil 15MG
    Buy Meridia 15MG
    Buy adipex 37.5mg
    Buy Ambien 10mg
    Buy Klonopin 1mg
    Buy Valium 5mg
    Pain Relief Medicines Online
    Reduce Fat now with pills
    Erectile dysfunction solution is here
    Best anxiety medicine


    ReplyDelete

Post a Comment

Popular posts from this blog

PostgreSQL pgBadger

PostgreSQL Pgbadger Installation On Linux

How to configure Replication Manager (repmgr) ?

How To Schedule PostgreSQL Jobs using pgAgent on Linux plateform