sessionwatch: A small tool to monitor session activities of PostgreSQL

Generally DBA, has to login in system to monitor the activities by querying the pg_stat_activity veiw. However if someone wants to do continous monitor for every 2 seconds. He has to run SQL against pg_stat_activity every 2 seconds.

So, For such activity, I have made following shell script which can be use with watch command on linux to monitor the activities.

#!/bin/bash
## Prepared by Vibhor Kumar (EDB).


Q="select procpid,now() - query_start as long, waiting, current_query from pg_stat_activity where procpid <> pg_backend_pid() limit 10;"
while getopts "b:U:d:p:" opt; do
   case $opt in
     b) BIN=$OPTARG;;
     U) PGUSER=$OPTARG;;
     d) DB=$OPTARG;;
     p) port1=$OPTARG;;
     *) echo "Usage: $0 -b  -d  -U  -mp  -sp ";;
   esac
   done 

if [ -z $BIN ];then
  echo "Usage: $0 -b bin directory -d database name -U user -p port"
  exit
fi
if [ -z $PGUSER ];then
  echo "Usage: $0 -b bin directory -d database name -U user -p port"
  exit
fi

if [ -z $DB ];then
  echo "Usage: $0 -b bin directory -d database name -U user -p port"
  exit
fi
if [ -z $port1 ];then
  echo "Usage: $0 -b bin directory -d database name -U user -p port"
  exit
fi

function verifybin_connect()  
{  
  if [ -f $BIN/psql ];then  
    :  
  else  
      echo "ERROR: psql Not Found!" 
      usage 
      exit 1  
  fi  
  Q1="SELECT 'ping';"  
  $BIN/psql -U $PGUSER -h localhost -p $port1 -c "$Q1" -d $DB -t >/dev/null 2>/dev/null  
   if [ $? -ne 0 ];then  
       echo "ERROR: $DB is not pinging on $host" 
       exit 1  
  fi  
}  

verifybin_connect 
$BIN/psql -c "$Q" -U $PGUSER -d $DB -p $port1

Usage is given below:
vibhore@ubuntu:~$ ./sessionwatch

Usage: ./sessionwatch -b bin directory -d database name -U user -p port

Example of monitoring the activity is given below:

watch ./sessionwatch -d edb -U enterprisedb -p 5444 -b /opt/PostgresPlus/8.4AS/bin

Every 2.0s: ./sessionwatch -d edb -U enterprisedb -p 5444 -b /opt/PostgresPlus/8.4AS/bin                                                                   Sun Apr 24 09:25:50 2011

 procpid |          long          | waiting | current_query
---------+------------------------+---------+---------------
    1292 | 00:00:25.582061        | f       | 
    1314 | 03:13:59.158917        | f       | 
    1172 | 00:00:04.899938        | f       | 
    1324 | 08:18:58.855179        | f       | 
    1330 | 00:00:18.448067        | f       | 
    1819 | 2 days 06:11:09.667475 | f       | 
    1836 | 00:00:00.637558        | f       | 
(7 rows)