#!/usr/bin/python
#
# script for PG stats like iostat
# 2009 Kenny Gorman; www.kennygorman.com
# v1.01

import psycopg2
import time
import datetime
import commands
from optparse import OptionParser
import sys

# get command line input
parser = OptionParser()
parser.set_defaults(database="postgres",hostname="localhost",username="postgres",password="")
parser.add_option("-d","--database", dest="database", help="database name to connect to")
parser.add_option("-n","--hostname", dest="hostname",help="hostname to connect to")
parser.add_option("-u","--username", dest="username",help="username")
parser.add_option("-p","--password", dest="password",help="password")
parser.add_option("--port",dest="port",help="port")
parser.add_option("-s","--sleep",dest="sleeptime",help="time to sleep between fetches")
(options, args) = parser.parse_args()

# works in linux. need to change for solaris.
def getload():
  cmd="uptime"
  out=commands.getstatusoutput(cmd)
  load=out[1].split(':')[3].split(',')[0]
  return load

def thetime():
  thedate=datetime.datetime.now().strftime("%d-%m-%Y.%H:%M:%S")
  return thedate	

# make sure there are reasonable defaults
if not options.port:
  options.port=str(5432)
if not options.database:
  options.database="postgres"
if not options.sleeptime:
  options.sleeptime=10

try:
  connectstr="host="+options.hostname+" dbname="+options.database+" port="+options.port+" user="+options.username+" password="+options.password
  handle=psycopg2.connect(connectstr)
except psycopg2.DatabaseError,info:
  print info
  sys.exit(1)

sql="""
     select max(xact_commit), max(xact_rollback), max(blks_read), max(blks_hit), max(numbackends), sum(seq_scan),sum(seq_tup_read),
     sum(idx_scan), sum(idx_tup_fetch), sum(n_tup_ins), sum(n_tup_upd), sum(n_tup_del), max(c.locks) as locks, max(d.sess) as active
     from pg_stat_database a, pg_stat_user_tables b, (select count(*) as locks from pg_locks) c, (select count(*) as sess from pg_stat_activity where current_query !~ '.*<IDLE>*') d
     where datname = '"""+options.database+"""'
    """
i=0
o=[0,0,0,0,0,0,0,0,0,0,0,0]
while(1):
 curs=handle.cursor()
 curs.execute(sql)
 handle.commit()
 r=curs.fetchone()
 d2=r[2]-o[2]+0.0
 d3=r[3]-o[3]+1.0
 hit=round((d3/(d2+d3))*100,2)
 if (i % 25 == 0):
   print "%1s%28s%12s%12s%12s%12s%12s%12s%12s%12s%12s%12s%12s%12s%12s%12s%12s" % ("time","commits","rollbks","blksrd","blkshit","bkends","seqscan","seqtprd","idxscn","idxtrd","ins","upd","del","locks","hit","load","active")
 print "%1s%12s%12s%12s%12s%12s%12s%12s%12s%12s%12s%12s%12s%12s%12s%12s%12s" % (thetime(),r[0]-o[0],r[1]-o[1],r[2]-o[2],r[3]-o[3],r[4],r[5]-o[5],r[6]-o[6],r[7]-o[7],r[8]-o[8],r[9]-o[9],r[10]-o[10],r[11]-o[11],r[12],hit,getload(),r[13])
 i+=1
 o=r
 time.sleep(int(options.sleeptime))

handle.close()
sys.exit(0)

