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. This comment has been removed by a blog administrator.

    ReplyDelete
  3. This comment has been removed by a blog administrator.

    ReplyDelete
  4. This comment has been removed by a blog administrator.

    ReplyDelete
  5. This comment has been removed by a blog administrator.

    ReplyDelete
  6. This comment has been removed by a blog administrator.

    ReplyDelete
  7. This comment has been removed by a blog administrator.

    ReplyDelete
  8. cerisea medica
    Dieting and training are the known measures but today health and fitness and health goods are becoming increasingly popular. Various organic and non-herbal weight-loss items have come in to market and so you have many choices take into account. In this article, our focus of discussion is on
    https://newsletterforhealth.com/cerisea-medica/

    ReplyDelete
  9. Beta keto
    t strategy strategy. Rotate Activities A wide wide range of new activities chosen with the view of dropping calories while doinle pain. Gradually increasing your participation is the best strategy for
    https://newsletterforhealth.com/beta-keto/

    ReplyDelete
  10. Keto fast information available concerning this design, and as with anything that is misunderstood many myths arise around it. Dreadlocks can and must be washed; otherwise they will smell badly like any other dirty locks. The best way to use to clean dreadlocks is to use a residue-free locks hair shampoo.
    https://newsletterforhealth/keto-fast/

    ReplyDelete
  11. reviva brain
    diminishing, it was regaining a lot of its thickness and the colour had changed. Unfortunately, I then took it upon myself to think that I had crossed the line and that I didn’t need to proceed the therapy so vigorously. From using it day and night I began to only use it once or twice a week. I continued my
    https://newsletterforhealth.com/reviva-brain/

    ReplyDelete
  12. rapid slim bodyweight. It wasn't the burden she was hoping to lessen , but it was a start. A start she knew was slow and steady; the right way to get rid of fat. She understood that this was going to be a life-style change and that she would hit her target bodyweight decrease soon enough and keep it off. Her big Apr Fool's
    https://newsletterforhealth.com/rapid-slim/

    ReplyDelete
  13. keto blast
    Unfortunately, this research does not lay that out as the kynurenic acidity was administered orally rather than induced directly via execute out. A little digging however, and another paper provided exactly that information: endurance execute out.3 Ah, I knew it! I had a suspicion that a lot of initiatives
    https://topwellnessblog.com/keto-blast/

    ReplyDelete
  14. Thanks for all the tips mentioned in this article! it’s always good to read things you have heard before and are implementing, but from a different perspective, always pick up some extra bits of information. office.com/setup | norton.com/setup | norton.com/setup

    ReplyDelete
  15. provexum
    being said, many men do not think of the consequences of tobacco in terms of their loving way of life. Smoking constricts the blood vessels vessels all over one's whole personal body, and the penis is filled with a program of very small blo • invaders. It functions to protect the more sensitive systems inside
    https://newsletterforhealth.com/provexum/

    ReplyDelete
  16. Get Step-by-Step guide for Norton.com/setup – Activate, Download & complete installation from norton.com/setup and get the best security setup for any of your preferred devices just by visiting norton.com/setup & mcafee.com/activate. Also try our step by step guide for mcafee.com/activate & office.com/setup.
    Related link - mcafee.com/activate

    ReplyDelete
  17. Thanks for sharing valuable information.It will help everyone.keep Post.
    Kerala Lottery guessing

    ReplyDelete

Post a Comment

Popular posts from this blog

PostgreSQL pgBadger

PostgreSQL Pgbadger Installation On Linux

How To Schedule PostgreSQL Jobs using pgAgent on Linux plateform

How to configure Replication Manager (repmgr) ?