• Home
  • Services
    • HR Services
      • HR Advisory Services
      • Contract Staffing
      • HR Outsourcing Services
      • Manpower Supply Services
      • Payroll Processing
      • Permanent Placement
      • Recruitment and Placement Services
      • Recruitment Process Outsourcing
      • Staffing Agency Services
    • DBA Support
      • DBA Consultancy Services
      • PostgreSQL Support
    • Website Maintenance
    • Company Registration Services
    • Virtual Office Space Address
  • Company
    • FAQs
    • About Us
    • Contact
  • Office Space
    • Virtual Space
    • Co-working Space
    • Private Space
    • Meeting Room
    • Locations
    • Add Listing
    • Dashboard
  • Blogs
  • Careers
    • Jobseeker
    • Employer
  • Courses

    About Courses

    • List Of Courses
    • Become an Instructor
    Greenplum

    Greenplum

    $1,500.00
    Read More
    Have any question?
    (+91)8148383856
    info@rayafeel.com
    Login
    RayaFeeL
    • Home
    • Services
      • HR Services
        • HR Advisory Services
        • Contract Staffing
        • HR Outsourcing Services
        • Manpower Supply Services
        • Payroll Processing
        • Permanent Placement
        • Recruitment and Placement Services
        • Recruitment Process Outsourcing
        • Staffing Agency Services
      • DBA Support
        • DBA Consultancy Services
        • PostgreSQL Support
      • Website Maintenance
      • Company Registration Services
      • Virtual Office Space Address
    • Company
      • FAQs
      • About Us
      • Contact
    • Office Space
      • Virtual Space
      • Co-working Space
      • Private Space
      • Meeting Room
      • Locations
      • Add Listing
      • Dashboard
    • Blogs
    • Careers
      • Jobseeker
      • Employer
    • Courses

      About Courses

      • List Of Courses
      • Become an Instructor
      Greenplum

      Greenplum

      $1,500.00
      Read More

      Blog

      • Home
      • Blog
      • Blog
      • Script to killing ALL IDLE Connection In Postgres Database

      Script to killing ALL IDLE Connection In Postgres Database

      • Posted by bushra.rayafeel
      • Categories Blog
      • Date May 28, 2022
      • Comments 0 comment

      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;

       

      Tag:postgresql, script

      • Share:
      bushra.rayafeel

      Previous post

      Clear RAM Memory Cache, Buffer and Swap Space on Linux
      May 28, 2022

      Next post

      Postgres 9.6 parameter idle_in_transaction_session_timeout
      May 28, 2022

      Leave A Reply

      You must be logged in to post a comment.

      Connect with



      Search

      ADVERTISEMENT

      Latest Posts

      HOW VIRTUAL OFFICE IS GOING TO CHANGE YOUR BUSINESS STRATEGIES
      21Jun2022
      Take the best advantage of your Virtual Office
      21Jun2022
      Let’s discuss how corporates can improve their productivity through virtual office spaces?
      21Jun2022
      Virtual Office Space & Address Services
      21Jun2022
      HOW VIRTUAL OFFICES ARE TRANSFORMING BUSINESSES
      21Jun2022
      PostgreSQL

      PostgreSQL

      $800.00 $500.00
      Greenplum

      Greenplum

      $1,500.00
      Oracle Database

      Oracle Database

      $350.00
      2ndquadrant.in

      (+91) 8838953252

      ITsupport@rayafeel.com

      Company

      • About Us
      • Our Team

      COURSES

      • List Of Course
      • Become An Instructor

      Support

      • DBA Support
      • Consultancy Services

      Recommend

      • Login
      • Contact Us

      IT Services by rayafeel.com. Powered by Rayafeel Technologies Pvt Ltd.

      • Privacy
      • Terms

      Become An Instructor?

      Join thousand of instructors and earn money hassle free!

      Get Started Now

      Connect with

      Login with Google

      Login with your site account

      Connect with



      Lost your password?