Postgresql snapshot.py

From NailingJello.com
Jump to: navigation, search
import subprocess, time, csv, operator
import popen2

# Script parameters
SLEEPTIME = 1         # Number of seconds between executions
NUMQUERY = 10       # Number of samples, -1 to run until quit (CTRL+C)

# Do not forget to change psql connection parameters if connecting remotely and to change the DB name at the end. 

CMD = """/usr/bin/psql -tc "copy ( select * from pg_stat_activity ) to STDOUT with CSV FORCE QUOTE current_query;" otrs """

OUTPUTFILE = "output.csv"


dct = {}
c = 0 

try:
     while ((NUMQUERY == -1) or (c  <= NUMQUERY)):

          # Works on python 2.7 +
          #output = subprocess.check_output(CMD, shell=True)

          # Alternate version for older version of python
          r,w = popen2.popen2( CMD)
          output = r.read()


          reader = csv.reader(output.split("\n"), delimiter=",", quotechar='"')

          for row in reader:
               if row:
                    query  = row[-1]
                    dct [ query ] = dct.get(query,0 ) + 1 
     
          print "Run: %d, Num: %d \r"%(c, len(dct.keys())) 
          c = c + 1
          time.sleep(SLEEPTIME)
finally:
     # Generate output
     print "Generating output as ", OUTPUTFILE     
     fpw = open( OUTPUTFILE, "w")
     writer = csv.writer(fpw, dialect="excel")
     sortedlist = sorted(dct.iteritems(), key=operator.itemgetter(1) )
     writer.writerows(sortedlist)