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)

4 Comments

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s