__author__ = 'kaceymiriholston'
#!/usr/bin/python

####################### copyright ########################

"""Datacollection.py is copyright 2014 PostgreSQL Experts Inc.
All rights reserved."""

import sys, commands
from time import strftime, localtime, sleep
import os, re, string, platform, shutil, glob, subprocess
from shutil import move, copyfile
from optparse import OptionParser

######################## functions ########################

### prerequisite functions ###
def check_installed(prog):
#checks if program is installed
    if not os.system("which %s"%prog):
        mention("%s is on path, great!"%prog)
    else:
        complain("%s is not on the path, the program can't run without it."%prog)

def complain(message):
#prints complain message and quits program
    mention(message)
    sys.exit(1)

def mention(message):
#prints complain message, does not quit
    sys.stderr.write(message + "\n")

def check_writable(path):
#checks if path is writeable
    if not os.access(path, os.W_OK):
        complain("This file must be readable by the postgres user. The path used is  %s. Please ensure this is correct and that the permissions are set correctly." %path)
    else:
        mention("We have write access to the %s file, great!"%path)

def clear():
    #removes old run data
    try:
        if os.path.exists(DIRECTORY + "project"):
            shutil.rmtree(DIRECTORY + "project")
        files = glob.glob(DIRECTORY + "/*")
        for f in files:
            if f == DIRECTORY + "check_databases.txt":
                continue
            os.remove(f)
        if os.path.isfile("datacollection.sar"):
            move("datacollection.sar", "%sdatacollection%s.sar"%(DIRECTORY, strftime("%b%d%Y_%H:%M:%S", localtime())))
    except:
        print "COULD NOT REMOVE OLD FILES IF THIS IS IN ERROR CTL^C NOW>"


### End prerequisite functions ###

### Begin main script functions ###
def make_database_list():
    #When there are more than one databases, this parses the txt file, removes default databases and appends them to an array.
    with open("%scheck_databases.txt"%DIRECTORY, 'r') as database_file:
        for db in database_file:#go through each database
            if "datname" not in db and "------------------" not in db and "template1" not in db and "template0" not in db and "postgres" not in db:
                if not re.search(r'(\d+ rows)', db) and re.search(r'\w+', db):
                    database_list.append(db.strip())
    database_file.close()
    print_databases()

def print_databases():
    print "The following databases will be checked:(Hit CTRL-C if you wish to make changes)"
    for database in database_list:
       print ("\t%s"%database.upper())

### End main script function ###

### Start Postgres functions ###

def begining_database_collection(database, req_sleep):
    # this starts data collection
    if req_sleep:
        sleep(.3)
    try:
        path = os.getcwd()+ "/OUTPUT/project/monitor/" + database
        print path
        if not os.path.exists(path):
            os.makedirs(path)
        if password:
            os.system('''PGPASSWORD=%s psql -c "COPY(select current_database() as dbname, * from pg_stat_user_tables) TO '%s/%s.%s.csv' with delimiter ',' csv header" %s >/dev/null 2>&1 &'''%(password, path, database,'pg_stat_user_tables_start', database))
            os.system('''PGPASSWORD=%s psql -c "COPY(select current_database() as dbname, * from pg_stat_sys_tables) TO '%s/%s.%s.csv' with delimiter ',' csv header" %s >/dev/null 2>&1 &'''%(password, path, database,'pg_stat_sys_tables_start', database))
            os.system('''PGPASSWORD=%s psql -c "COPY(select current_database() as dbname, * from pg_statio_user_tables) TO '%s/%s.%s.csv' with delimiter ',' csv header" %s >/dev/null 2>&1 &'''%(password, path, database,'pg_statio_user_tables_start', database))
            os.system('''PGPASSWORD=%s psql -c "COPY(select current_database() as dbname, * from pg_statio_sys_tables) TO '%s/%s.%s.csv' with delimiter ',' csv header" %s >/dev/null 2>&1 &'''%(password, path, database,'pg_statio_sys_tables_start', database))
            os.system('''PGPASSWORD=%s psql -c "COPY(select current_database() as dbname, * from pg_statio_user_indexes) TO '%s/%s.%s.csv' with delimiter ',' csv header" %s >/dev/null 2>&1 &'''%(password, path, database,'pg_statio_user_indexes_start', database))
            os.system('''PGPASSWORD=%s psql -c "COPY(select current_database() as dbname, * from pg_statio_sys_indexes) TO '%s/%s.%s.csv' with delimiter ',' csv header" %s >/dev/null 2>&1 &'''%(password, path, database,'pg_statio_sys_indexes_start', database))
            os.system('''PGPASSWORD=%s psql -c "COPY(select current_database() as dbname, * from pg_stat_user_indexes) TO '%s/%s.%s.csv' with delimiter ',' csv header" %s >/dev/null 2>&1 &'''%(password, path, database,'pg_stat_user_indexes_start', database))
            os.system('''PGPASSWORD=%s psql -c "COPY(select current_database() as dbname, * from pg_stat_sys_indexes) TO '%s/%s.%s.csv' with delimiter ',' csv header" %s >/dev/null 2>&1 &'''%(password, path, database,'pg_stat_sys_indexes_start', database))
            os.system('''PGPASSWORD=%s psql -c "COPY(select current_database() as dbname, oid, nspname, nspowner from pg_namespace) TO '%s/%s.%s.csv' with delimiter ',' csv header" %s >/dev/null 2>&1 &'''%(password, path, database,'pg_namespace_start', database))
            os.system('''PGPASSWORD=%s psql -c "COPY(select current_database() as dbname, schemaname, tablename, attname, inherited, null_frac, avg_width, n_distinct, most_common_freqs, correlation from pg_stats) TO '%s/%s.%s.csv' with delimiter ',' csv header" %s >/dev/null 2>&1 &'''%(password, path, database,'pg_stats_start', database))
            os.system('''PGPASSWORD=%s psql -c "COPY(select current_database() as dbname, * from pg_stat_user_functions) TO '%s/%s.%s.csv' with delimiter ',' csv header" %s >/dev/null 2>&1 &'''%(password, path, database,'pg_stat_user_functions_start', database))
            os.system('''PGPASSWORD=%s psql -c "COPY(select relid, schemaname, relname, pg_relation_size(relid), pg_total_relation_size(relid) from pg_stat_user_tables ) TO '%s/%s.%s.csv' with delimiter ',' csv header" %s >/dev/null 2>&1 &'''%(password, path, database,'table_size_start', database))
            os.system('''PGPASSWORD=%s psql -c "COPY(select oid, relname, relnamespace, reltype, reloftype, relowner, relam, relfilenode, reltablespace, relpages, reltuples, reltoastrelid, reltoastidxid, relhasindex, relisshared, relpersistence, relkind, relnatts, relchecks, relhasoids, relhaspkey, relhasrules, relhastriggers, relhassubclass, relfrozenxid, reloptions, pg_relation_size(oid) from pg_class) TO '%s/%s.%s.csv' with delimiter ',' csv header" %s >/dev/null 2>&1 &'''%(password, path, database,'pg_class_start', database))
            os.system('''PGPASSWORD=%s psql -c "COPY(select current_database() as dbname, * from pg_indexes) TO '%s/%s.%s.csv' with delimiter ',' csv header" %s >/dev/null 2>&1 &'''%(password, path, database,'pg_indexes_start', database))
            os.system('''PGPASSWORD=%s psql -c "COPY(select indexrelid, indrelid, indnatts, indisunique, indisprimary, indisexclusion, indimmediate, indisclustered, indisvalid, indcheckxmin, indisready, indkey, indcollation, indclass, indoption, indexprs::text, indpred::text from pg_index) TO '%s/%s.%s.csv' with delimiter ',' csv header" %s >/dev/null 2>&1 &'''%(password, path, database,'pg_index_start', database))
            os.system('''PGPASSWORD=%s psql -c "COPY(select indexrelid, schemaname, relname, indexrelname, pg_relation_size(indexrelid) as index_size from pg_stat_user_indexes) TO '%s/%s.%s.csv' with delimiter ',' csv header" %s >/dev/null 2>&1 &'''%(password, path, database,'pg_index_size_start', database))
            os.system('''PGPASSWORD=%s psql -c "COPY(select 'start'::text, now() + '5 minute'::INTERVAL as now) TO '%s/%s.%s.csv' with delimiter ',' csv header" %s >/dev/null 2>&1 &'''%(password, path, database,'times.start', database))
            os.system('''PGPASSWORD=%s psql -c "COPY(select 'server start time'::text as name, to_char(pg_postmaster_start_time(), 'YYYY-MM-DD HH24:MI:SS') as info) TO '%s/%s.%s.csv' with delimiter ',' csv header" %s >/dev/null 2>&1 &'''%(password, path, database,'other_stats_start.ssd', database))
            os.system('''PGPASSWORD=%s psql -c "COPY(select 'version'::text as name, version() as info) TO '%s/%s.%s.csv' with delimiter ',' csv header" %s >/dev/null 2>&1 &'''%(password, path, database,'other_stats_start.v', database))
            os.system('''PGPASSWORD=%s psql -c "COPY(select 'current_setting'::text as name, current_setting('block_size')::numeric as info) TO '%s/%s.%s.csv' with delimiter ',' csv header" %s >/dev/null 2>&1 &'''%(password, path, database,'other_stats_start.cs', database))
            os.system('''PGPASSWORD=%s psql -c "COPY(select 'current xid'::text as name, txid_current()::TEXT as info) TO '%s/%s.%s.csv' with delimiter ',' csv header" %s >/dev/null 2>&1 &'''%(password, path, database,'other_stats_start.cx', database))
            os.system('''PGPASSWORD=%s psql -c "COPY(select 'database size'::text as name, pg_database_size(current_database()) as info) TO '%s/%s.%s.csv' with delimiter ',' csv header" %s >/dev/null 2>&1 &'''%(password, path, database,'other_stats_start.ds', database))
            os.system('''PGPASSWORD=%s psql -c "COPY(select current_database() as dbname, * from information_schema.columns)TO '%s/%s.%s.csv' with delimiter ',' csv header" %s >/dev/null 2>&1 &'''%(password, path, database,'information_schema_columns_start', database))
            os.system('''PGPASSWORD=%s psql -c "COPY(select current_database() as dbname, pg_relation_size(attrelid) as relation_size, * from pg_attribute) TO '%s/%s.%s.csv' with delimiter ',' csv header" %s >/dev/null 2>&1 &'''%(password, path, database,'pg_attribute_start', database))
            os.system('''PGPASSWORD=%s psql -c "COPY(select current_database() as dbname, * from pg_statistic) TO '%s/%s.%s.csv' with delimiter ',' csv header" %s >/dev/null 2>&1 &'''%(password, path, database,'pg_statistic_start', database))

        else:
            os.system('''psql -c "COPY(select current_database() as dbname, * from pg_stat_user_tables) TO '%s/%s.%s.csv' with delimiter ',' csv header" %s >/dev/null 2>&1 &'''%( path, database,'pg_stat_user_tables_start', database))
            os.system('''psql -c "COPY(select current_database() as dbname, * from pg_stat_sys_tables) TO '%s/%s.%s.csv' with delimiter ',' csv header" %s >/dev/null 2>&1 &'''%( path, database,'pg_stat_sys_tables_start', database))
            os.system('''psql -c "COPY(select current_database() as dbname, * from pg_statio_user_tables) TO '%s/%s.%s.csv' with delimiter ',' csv header" %s >/dev/null 2>&1 &'''%( path, database,'pg_statio_user_tables_start', database))
            os.system('''psql -c "COPY(select current_database() as dbname, * from pg_statio_sys_tables) TO '%s/%s.%s.csv' with delimiter ',' csv header" %s >/dev/null 2>&1 &'''%( path, database,'pg_statio_sys_tables_start', database))
            os.system('''psql -c "COPY(select current_database() as dbname, * from pg_statio_user_indexes) TO '%s/%s.%s.csv' with delimiter ',' csv header" %s >/dev/null 2>&1 &'''%( path, database,'pg_statio_user_indexes_start', database))
            os.system('''psql -c "COPY(select current_database() as dbname, * from pg_statio_sys_indexes) TO '%s/%s.%s.csv' with delimiter ',' csv header" %s >/dev/null 2>&1 &'''%( path, database,'pg_statio_sys_indexes_start', database))
            os.system('''psql -c "COPY(select current_database() as dbname, * from pg_stat_user_indexes) TO '%s/%s.%s.csv' with delimiter ',' csv header" %s >/dev/null 2>&1 &'''%( path, database,'pg_stat_user_indexes_start', database))
            os.system('''psql -c "COPY(select current_database() as dbname, * from pg_stat_sys_indexes) TO '%s/%s.%s.csv' with delimiter ',' csv header" %s >/dev/null 2>&1 &'''%( path, database,'pg_stat_sys_indexes_start', database))
            os.system('''psql -c "COPY(select current_database() as dbname, oid, nspname, nspowner from pg_namespace) TO '%s/%s.%s.csv' with delimiter ',' csv header" %s >/dev/null 2>&1 &'''%( path, database,'pg_namespace_start', database))
            os.system('''psql -c "COPY(select current_database() as dbname, schemaname, tablename, attname, inherited, null_frac, avg_width, n_distinct, most_common_freqs, correlation from pg_stats) TO '%s/%s.%s.csv' with delimiter ',' csv header" %s >/dev/null 2>&1 &'''%( path, database,'pg_stats_start', database))
            os.system('''psql -c "COPY(select current_database() as dbname, * from pg_stat_user_functions) TO '%s/%s.%s.csv' with delimiter ',' csv header" %s >/dev/null 2>&1 &'''%( path, database,'pg_stat_user_functions_start', database))
            os.system('''psql -c "COPY(select relid, schemaname, relname, pg_relation_size(relid), pg_total_relation_size(relid) from pg_stat_user_tables) TO '%s/%s.%s.csv' with delimiter ',' csv header" %s >/dev/null 2>&1 &'''%( path, database,'table_size_start', database))
            os.system('''psql -c "COPY(select oid, relname, relnamespace, reltype, reloftype, relowner, relam, relfilenode, reltablespace, relpages, reltuples, reltoastrelid, reltoastidxid, relhasindex, relisshared, relpersistence, relkind, relnatts, relchecks, relhasoids, relhaspkey, relhasrules, relhastriggers, relhassubclass, relfrozenxid, reloptions, pg_relation_size(oid) from pg_class) TO '%s/%s.%s.csv' with delimiter ',' csv header" %s >/dev/null 2>&1 &'''%( path, database,'pg_class_start', database))
            os.system('''psql -c "COPY(select current_database() as dbname, * from pg_indexes) TO '%s/%s.%s.csv' with delimiter ',' csv header" %s >/dev/null 2>&1 &'''%( path, database,'pg_indexes_start', database))
            os.system('''psql -c "COPY(select indexrelid, indrelid, indnatts, indisunique, indisprimary, indisexclusion, indimmediate, indisclustered, indisvalid, indcheckxmin, indisready, indkey, indcollation, indclass, indoption, indexprs::text, indpred::text from pg_index) TO '%s/%s.%s.csv' with delimiter ',' csv header" %s >/dev/null 2>&1 &'''%( path, database,'pg_index_start', database))
            os.system('''psql -c "COPY(select indexrelid, schemaname, relname, indexrelname, pg_relation_size(indexrelid) as index_size from pg_stat_user_indexes) TO '%s/%s.%s.csv' with delimiter ',' csv header" %s >/dev/null 2>&1 &'''%( path, database,'pg_index_size_start', database))
            os.system('''psql -c "COPY(select 'start'::text, now() + '5 minute'::INTERVAL as now) TO '%s/%s.%s.csv' with delimiter ',' csv header" %s >/dev/null 2>&1 &'''%( path, database,'times.start', database))
            os.system('''psql -c "COPY(select 'server start time'::text as name, to_char(pg_postmaster_start_time(), 'YYYY-MM-DD HH24:MI:SS') as info) TO '%s/%s.%s.csv' with delimiter ',' csv header" %s >/dev/null 2>&1 &'''%( path, database,'other_stats_start.ssd', database))
            os.system('''psql -c "COPY(select 'version'::text as name, version() as info) TO '%s/%s.%s.csv' with delimiter ',' csv header" %s >/dev/null 2>&1 &'''%( path, database,'other_stats_start.v', database))
            os.system('''psql -c "COPY(select 'current_setting'::text as name, current_setting('block_size')::numeric as info) TO '%s/%s.%s.csv' with delimiter ',' csv header" %s >/dev/null 2>&1 &'''%( path, database,'other_stats_start.cs', database))
            os.system('''psql -c "COPY(select 'current xid'::text as name, txid_current()::TEXT as info) TO '%s/%s.%s.csv' with delimiter ',' csv header" %s >/dev/null 2>&1 &'''%( path, database,'other_stats_start.cx', database))
            os.system('''psql -c "COPY(select 'database size'::text as name, pg_database_size(current_database()) as info) TO '%s/%s.%s.csv' with delimiter ',' csv header" %s >/dev/null 2>&1 &'''%( path, database,'other_stats_start.ds', database))
            os.system('''psql -c "COPY(select current_database() as dbname, * from information_schema.columns) TO '%s/%s.%s.csv' with delimiter ',' csv header" %s >/dev/null 2>&1 &'''%( path, database,'information_schema_columns_start', database))
            os.system('''psql -c "COPY(select current_database() as dbname, pg_relation_size(attrelid) as relation_size, * from pg_attribute) TO '%s/%s.%s.csv' with delimiter ',' csv header" %s >/dev/null 2>&1 &'''%(path, database,'pg_attribute_start', database))
            os.system('''psql -c "COPY(select current_database() as dbname, * from pg_statistic) TO '%s/%s.%s.csv' with delimiter ',' csv header" %s >/dev/null 2>&1 &'''%(path, database,'pg_statistic_start', database))

    except:
        return 1

def ending_database_collection(database, req_sleep):
    # this ends data collection
    if req_sleep:
        sleep(.3)
    try:
        path = os.getcwd()+ "/OUTPUT/project/monitor/" + database

        if password:
            os.system('''PGPASSWORD=%s psql -c "COPY(select 'end'::text, now() - '5 minute'::INTERVAL as now) TO '%s/%s.%s.csv' with delimiter ',' csv header" %s >/dev/null 2>&1 &'''%(password, path, database,'times.end', database))
            os.system('''PGPASSWORD=%s psql -c "COPY(select current_database() as dbname, * from pg_stat_user_tables) TO '%s/%s.%s.csv' with delimiter ',' csv header" %s >/dev/null 2>&1 &'''%(password, path, database,'pg_stat_user_tables_end', database))
            os.system('''PGPASSWORD=%s psql -c "COPY(select current_database() as dbname, * from pg_stat_sys_tables) TO '%s/%s.%s.csv' with delimiter ',' csv header" %s >/dev/null 2>&1 &'''%(password, path, database,'pg_stat_sys_tables_end', database))
            os.system('''PGPASSWORD=%s psql -c "COPY(select current_database() as dbname, * from pg_statio_user_tables) TO '%s/%s.%s.csv' with delimiter ',' csv header" %s >/dev/null 2>&1 &'''%(password, path, database,'pg_statio_user_tables_end', database))
            os.system('''PGPASSWORD=%s psql -c "COPY(select current_database() as dbname, * from pg_statio_sys_tables) TO '%s/%s.%s.csv' with delimiter ',' csv header" %s >/dev/null 2>&1 &'''%(password, path, database,'pg_statio_sys_tables_end', database))
            os.system('''PGPASSWORD=%s psql -c "COPY(select current_database() as dbname, * from pg_statio_user_indexes) TO '%s/%s.%s.csv' with delimiter ',' csv header" %s >/dev/null 2>&1 &'''%(password, path, database,'pg_statio_user_indexes_end', database))
            os.system('''PGPASSWORD=%s psql -c "COPY(select current_database() as dbname, * from pg_statio_sys_indexes) TO '%s/%s.%s.csv' with delimiter ',' csv header" %s >/dev/null 2>&1 &'''%(password, path, database,'pg_statio_sys_indexes_end', database))
            os.system('''PGPASSWORD=%s psql -c "COPY(select current_database() as dbname, * from pg_stat_user_indexes) TO '%s/%s.%s.csv' with delimiter ',' csv header" %s >/dev/null 2>&1 &'''%(password, path, database,'pg_stat_user_indexes_end', database))
            os.system('''PGPASSWORD=%s psql -c "COPY(select current_database() as dbname, * from pg_stat_sys_indexes) TO '%s/%s.%s.csv' with delimiter ',' csv header" %s >/dev/null 2>&1 &'''%(password, path, database,'pg_stat_sys_indexes_end', database))
            os.system('''PGPASSWORD=%s psql -c "COPY(select current_database() as dbname, * from pg_stat_user_functions) TO '%s/%s.%s.csv' with delimiter ',' csv header" %s >/dev/null 2>&1 &'''%(password, path, database,'pg_stat_user_functions_end', database))
            os.system('''PGPASSWORD=%s psql -c "COPY(select relid, schemaname, relname, pg_relation_size(relid), pg_total_relation_size(relid) from pg_stat_user_tables) TO '%s/%s.%s.csv' with delimiter ',' csv header" %s >/dev/null 2>&1 &'''%(password, path, database,'table_size_end', database))
            os.system('''PGPASSWORD=%s psql -c "COPY(select 'server start time'::text as name, to_char(pg_postmaster_start_time(), 'YYYY-MM-DD HH24:MI:SS') as info) TO '%s/%s.%s.csv' with delimiter ',' csv header" %s >/dev/null 2>&1 &'''%(password, path, database,'other_stats_end.ssd', database))
            os.system('''PGPASSWORD=%s psql -c "COPY(select 'version'::text as name, version() as info) TO '%s/%s.%s.csv' with delimiter ',' csv header" %s >/dev/null 2>&1 &'''%(password, path, database,'other_stats_end.v', database))
            os.system('''PGPASSWORD=%s psql -c "COPY(select 'current_setting'::text as name, current_setting('block_size')::numeric as info) TO '%s/%s.%s.csv' with delimiter ',' csv header" %s >/dev/null 2>&1 &'''%(password, path, database,'other_stats_end.cs', database))
            os.system('''PGPASSWORD=%s psql -c "COPY(select 'current xid'::text as name, txid_current()::TEXT as info) TO '%s/%s.%s.csv' with delimiter ',' csv header" %s >/dev/null 2>&1 &'''%(password, path, database,'other_stats_end.cx', database))
            os.system('''PGPASSWORD=%s psql -c "COPY(select 'database size'::text as name, pg_database_size(current_database()) as info) TO '%s/%s.%s.csv' with delimiter ',' csv header" %s >/dev/null 2>&1 &'''%(password, path, database,'other_stats_end.ds', database))

        else:
            os.system('''psql -c "COPY(select 'end'::text, now() - '5 minute'::INTERVAL as now) TO '%s/%s.%s.csv' with delimiter ',' csv header" %s >/dev/null 2>&1 &'''%( path, database,'times.end', database))
            os.system('''psql -c "COPY(select current_database() as dbname, * from pg_stat_user_tables) TO '%s/%s.%s.csv' with delimiter ',' csv header" %s >/dev/null 2>&1 &'''%( path, database,'pg_stat_user_tables_end', database))
            os.system('''psql -c "COPY(select current_database() as dbname, * from pg_stat_sys_tables) TO '%s/%s.%s.csv' with delimiter ',' csv header" %s >/dev/null 2>&1 &'''%( path, database,'pg_stat_sys_tables_end', database))
            os.system('''psql -c "COPY(select current_database() as dbname, * from pg_statio_user_tables) TO '%s/%s.%s.csv' with delimiter ',' csv header" %s >/dev/null 2>&1 &'''%( path, database,'pg_statio_user_tables_end', database))
            os.system('''psql -c "COPY(select current_database() as dbname, * from pg_statio_sys_tables) TO '%s/%s.%s.csv' with delimiter ',' csv header" %s >/dev/null 2>&1 &'''%( path, database,'pg_statio_sys_tables_end', database))
            os.system('''psql -c "COPY(select current_database() as dbname, * from pg_statio_user_indexes) TO '%s/%s.%s.csv' with delimiter ',' csv header" %s >/dev/null 2>&1 &'''%( path, database,'pg_statio_user_indexes_end', database))
            os.system('''psql -c "COPY(select current_database() as dbname, * from pg_statio_sys_indexes) TO '%s/%s.%s.csv' with delimiter ',' csv header" %s >/dev/null 2>&1 &'''%( path, database,'pg_statio_sys_indexes_end', database))
            os.system('''psql -c "COPY(select current_database() as dbname, * from pg_stat_user_indexes) TO '%s/%s.%s.csv' with delimiter ',' csv header" %s >/dev/null 2>&1 &'''%( path, database,'pg_stat_user_indexes_end', database))
            os.system('''psql -c "COPY(select current_database() as dbname, * from pg_stat_sys_indexes) TO '%s/%s.%s.csv' with delimiter ',' csv header" %s >/dev/null 2>&1 &'''%( path, database,'pg_stat_sys_indexes_end', database))
            os.system('''psql -c "COPY(select current_database() as dbname, * from pg_stat_user_functions) TO '%s/%s.%s.csv' with delimiter ',' csv header" %s >/dev/null 2>&1 &'''%( path, database,'pg_stat_user_functions_end', database))
            os.system('''psql -c "COPY(select relid, schemaname, relname, pg_relation_size(relid), pg_total_relation_size(relid) from pg_stat_user_tables) TO '%s/%s.%s.csv' with delimiter ',' csv header" %s >/dev/null 2>&1 &'''%( path, database,'table_size_end', database))
            os.system('''psql -c "COPY(select 'server start time'::text as name, to_char(pg_postmaster_start_time(), 'YYYY-MM-DD HH24:MI:SS') as info) TO '%s/%s.%s.csv' with delimiter ',' csv header" %s >/dev/null 2>&1 &'''%( path, database,'other_stats_end.ssd', database))
            os.system('''psql -c "COPY(select 'version'::text as name, version() as info) TO '%s/%s.%s.csv' with delimiter ',' csv header" %s >/dev/null 2>&1 &'''%( path, database,'other_stats_end.v', database))
            os.system('''psql -c "COPY(select 'current_setting'::text as name, current_setting('block_size')::numeric as info) TO '%s/%s.%s.csv' with delimiter ',' csv header" %s >/dev/null 2>&1 &'''%( path, database,'other_stats_end.cs', database))
            os.system('''psql -c "COPY(select 'current xid'::text as name, txid_current()::TEXT as info) TO '%s/%s.%s.csv' with delimiter ',' csv header" %s >/dev/null 2>&1 &'''%( path, database,'other_stats_end.cx', database))
            os.system('''psql -c "COPY(select 'database size'::text as name, pg_database_size(current_database()) as info) TO '%s/%s.%s.csv' with delimiter ',' csv header" %s >/dev/null 2>&1 &'''%( path, database,'other_stats_end.ds', database))

    except:
        return 1

def begining_server_collection(database):
# This starts server related data collection
    try:
        path = os.getcwd()+ "/OUTPUT/project/monitor/server"
        if not os.path.exists(path):
            os.makedirs(path)
        if password:
            os.system('''PGPASSWORD=%s psql -c "COPY(select current_timestamp, checkpoints_timed, checkpoints_req, buffers_checkpoint, buffers_clean, maxwritten_clean, buffers_backend, buffers_backend_fsync, buffers_alloc, stats_reset from pg_stat_bgwriter) TO '%s/server.%s.csv' with delimiter ',' csv header" %s >/dev/null 2>&1 &'''%(password, path, 'pg_stat_bgwriter_start', database))
            os.system('''PGPASSWORD=%s psql -c "COPY(select datid, datname, numbackends, xact_commit, xact_rollback, blks_read, blks_hit, tup_returned, tup_fetched, tup_inserted, tup_updated, tup_deleted, conflicts, stats_reset from pg_stat_database) TO '%s/server.%s.csv' with delimiter ',' csv header" %s >/dev/null 2>&1 &'''%(password, path, 'pg_stat_database_start', database))
            os.system('''PGPASSWORD=%s psql -c "COPY(select * from pg_stat_activity) TO '%s/server.%s.csv' with delimiter ',' csv header" %s >/dev/null 2>&1 &'''%(password, path, 'pg_stat_activity_start', database))
            os.system('''PGPASSWORD=%s psql -c "COPY(select * from pg_settings) TO '%s/server.%s.csv' with delimiter ',' csv header" %s >/dev/null 2>&1 &'''%(password, path, 'current_pg_settings', database))
            os.system('''PGPASSWORD=%s psql -c "COPY(select * from pg_stat_replication) TO '%s/server.%s.csv' with delimiter ',' csv header" %s >/dev/null 2>&1 &'''%(password, path, 'current_pg_stat_replication', database))
            os.system('''PGPASSWORD=%s psql -c "COPY(select * from pg_stat_database_conflicts) TO '%s/server.%s.csv' with delimiter ',' csv header" %s >/dev/null 2>&1 &'''%(password, path, 'pg_stat_database_conflicts_start', database))
            os.system('''PGPASSWORD=%s psql -c "COPY(SELECT pg_database.datname, pg_size_pretty(pg_database_size(pg_database.datname)) AS size FROM pg_database where "datname" <> 'template1' AND "datname" <> 'template0' AND "datname" <> 'postgres') TO '%s/server.%s.csv' with delimiter ',' csv header" %s >/dev/null 2>&1 &'''%(password, path, 'pg_database_size_start', database))
        else:
            os.system('''psql -c "COPY(select current_timestamp, checkpoints_timed, checkpoints_req, buffers_checkpoint, buffers_clean, maxwritten_clean, buffers_backend, buffers_backend_fsync, buffers_alloc, stats_reset from pg_stat_bgwriter) TO '%s/server.%s.csv' with delimiter ',' csv header" %s >/dev/null 2>&1 &'''%( path,'pg_stat_bgwriter_start', database))
            os.system('''psql -c "COPY(select datid, datname, numbackends, xact_commit, xact_rollback, blks_read, blks_hit, tup_returned, tup_fetched, tup_inserted, tup_updated, tup_deleted, conflicts, stats_reset from pg_stat_database) TO '%s/server.%s.csv' with delimiter ',' csv header" %s >/dev/null 2>&1 &'''%( path, 'pg_stat_database_start', database))
            os.system('''psql -c "COPY(select * from pg_stat_activity) TO '%s/server.%s.csv' with delimiter ',' csv header" %s >/dev/null 2>&1 &'''%( path, 'pg_stat_activity_start', database))
            os.system('''psql -c "COPY(select * from pg_settings) TO '%s/server.%s.csv' with delimiter ',' csv header" %s >/dev/null 2>&1 &'''%( path, 'current_pg_settings', database))
            os.system('''psql -c "COPY(select * from pg_stat_replication) TO '%s/server.%s.csv' with delimiter ',' csv header" %s >/dev/null 2>&1 &'''%( path, 'current_pg_stat_replication', database))
            os.system('''psql -c "COPY(select * from pg_stat_database_conflicts) TO '%s/server.%s.csv' with delimiter ',' csv header" %s >/dev/null 2>&1 &'''%( path, 'pg_stat_database_conflicts_start', database))
            os.system('''psql -c "COPY(SELECT pg_database.datname, pg_size_pretty(pg_database_size(pg_database.datname)) AS size FROM pg_database where "datname" <> 'template1' AND "datname" <> 'template0' AND "datname" <> 'postgres') TO '%s/server.%s.csv' with delimiter ',' csv header" %s >/dev/null 2>&1 &'''%( path, 'pg_database_size_start', database))
            os.system('''psql -c "COPY(SELECT oid, amname, amstrategies, amsupport, amcanorder, amcanbackward, amcanunique, amcanmulticol, amoptionalkey, amsearchnulls, amstorage, amclusterable, amkeytype, aminsert, ambeginscan, amgettuple, amgetbitmap, amrescan, amendscan, ammarkpos, amrestrpos, ambuild, ambulkdelete, amvacuumcleanup, amcostestimate, amoptions FROM pg_am) TO '%s/server.%s.csv' with delimiter ',' csv header" %s >/dev/null 2>&1 &'''%( path, 'current_pg_am', database))
    except:
        return 1

def ending_server_collection(database):
# This ends server related data collection

    try:
        path = os.getcwd()+ "/OUTPUT/project/monitor/server"

        if password:
            os.system('''PGPASSWORD=%s psql -c "COPY(select current_setting('block_size'), current_timestamp, checkpoints_timed, checkpoints_req, buffers_checkpoint, buffers_clean, maxwritten_clean, buffers_backend, buffers_backend_fsync, buffers_alloc, stats_reset from pg_stat_bgwriter) TO '%s/server.%s.csv' with delimiter ',' csv header" %s >/dev/null 2>&1 &'''%(password, path, 'pg_stat_bgwriter_end', database))
            os.system('''PGPASSWORD=%s psql -c "COPY(select datid, datname, numbackends, xact_commit, xact_rollback, blks_read, blks_hit, tup_returned, tup_fetched, tup_inserted, tup_updated, tup_deleted, conflicts, stats_reset from pg_stat_database) TO '%s/server.%s.csv' with delimiter ',' csv header" %s >/dev/null 2>&1 &'''%(password, path, 'pg_stat_database_end', database))
            os.system('''PGPASSWORD=%s psql -c "COPY(select * from pg_stat_activity) TO '%s/server.%s.csv' with delimiter ',' csv header" %s >/dev/null 2>&1 &'''%(password, path, 'pg_stat_activity_end', database))
            os.system('''PGPASSWORD=%s psql -c "COPY(select * from pg_stat_database_conflicts) TO '%s/server.%s.csv' with delimiter ',' csv header" %s >/dev/null 2>&1 &'''%(password, path, 'pg_stat_database_conflicts_end', database))
            os.system('''PGPASSWORD=%s psql -c "COPY(SELECT pg_database.datname, pg_size_pretty(pg_database_size(pg_database.datname)) AS size FROM pg_database where "datname" <> 'template1' AND "datname" <> 'template0' AND "datname" <> 'postgres') TO '%s/server.%s.csv' with delimiter ',' csv header" %s >/dev/null 2>&1 &'''%(password, path, 'pg_database_size_end', database))
        else:
            os.system('''psql -c "COPY(select current_setting('block_size'), current_timestamp, checkpoints_timed, checkpoints_req, buffers_checkpoint, buffers_clean, maxwritten_clean, buffers_backend, buffers_backend_fsync, buffers_alloc, stats_reset from pg_stat_bgwriter) TO '%s/server.%s.csv' with delimiter ',' csv header" %s >/dev/null 2>&1 &'''%( path, 'pg_stat_bgwriter_end', database))
            os.system('''psql -c "COPY(select datid, datname, numbackends, xact_commit, xact_rollback, blks_read, blks_hit, tup_returned, tup_fetched, tup_inserted, tup_updated, tup_deleted, conflicts, stats_reset from pg_stat_database) TO '%s/server.%s.csv' with delimiter ',' csv header" %s >/dev/null 2>&1 &'''%( path, 'pg_stat_database_end', database))
            os.system('''psql -c "COPY(select * from pg_stat_activity) TO '%s/server.%s.csv' with delimiter ',' csv header" %s >/dev/null 2>&1 &'''%( path, 'pg_stat_activity_end', database))
            os.system('''psql -c "COPY(select * from pg_stat_database_conflicts) TO '%s/server.%s.csv' with delimiter ',' csv header" %s >/dev/null 2>&1 &'''%( path, 'pg_stat_database_conflicts_end', database))
            os.system('''psql -c "COPY(SELECT pg_database.datname, pg_size_pretty(pg_database_size(pg_database.datname)) AS size FROM pg_database where "datname" <> 'template1' AND "datname" <> 'template0' AND "datname" <> 'postgres') TO '%s/server.%s.csv' with delimiter ',' csv header" %s >/dev/null 2>&1 &'''%( path, 'pg_database_size_end', database))
    except:
        return 1
### End Postgres functions ###


def check_sar_devices():
    dev = re.compile('disk [0-9]+, [0-9]+')
    disk_name = re.compile('output for the \S+')
    with open(SAR_DIRECTORY+'datacollection.sar.txt', 'r') as sar_file:
        with open(DIRECTORY+'data_for_settings.txt','r') as settings_file:
            for line in settings_file.readlines():
                y = disk_name.findall(line)
                x = dev.findall(line)
                if x:
                    disk = x[0].find(' ')
                    comma = x[0].find(',')
                    dev_machine = 'dev' + x[0][disk+1:comma]+'-'+x[0][comma+2:] + ' '
                    print "  " +dev_machine
                    device_was_found = False
                    for line in sar_file:
                        device_found = line.find(dev_machine)
                        if device_found != -1:
                            print "     device is in the sar file"
                            device_was_found = True
                            break
                    if not device_was_found:
                        top_device = 'dev' + x[0][disk+1:comma]+'-'+'0'

                        print "     device is NOT in the sar file"
                        sar_file.seek(0)
                        for line in sar_file:
                            device_found = line.find(top_device)
                            if device_found != -1:
                                print "     However "+top_device+" is in the sar file, this is likely the correct device to use"
                                break

                elif y:
                    the = y[0].find('the')
                    print 'Device info for '+y[0][the+4:]+':'

def wrap_up(database, database_list, needsreset, sar_created, db, monitor_db, req_sleep):

# wake up, take a finishing snapshot, and reload the configuration
    if database == None:
        for db in database_list:#go through each database
            print("\n%s"%db.upper())
            try:
                print "Collecting data for %s"%db
                if ending_database_collection(db, req_sleep) == 1:
                    abort_clean('initialization of database statistics failed', needsreset, sar_created)
            except Exception as problem:
                abort_clean('finishing database stats collection failed: %s'%problem, needsreset, sar_created)
            needsreset = False
    else:
            print("\n%s"%db.upper())
            print "Collecting data for %s"%database
            try:
                if ending_database_collection(db, req_sleep) == 1:
                    abort_clean('initialization of database statistics failed', needsreset, sar_created)
            except Exception as problem:
                abort_clean('finishing database stats collection failed: %s'%problem, needsreset, sar_created)
            needsreset = False
# end collection
    try:
        print("SERVERBASED DATA")
        print "Collecting data for server."
        if ending_server_collection(monitor_db) == 1:
            abort_clean('wrap-up of database statistics failed', needsreset, sar_created)
    except Exception as problem:
        abort_clean('finishing database stats collection failed: %s'%problem, needsreset, sar_created)
    needsreset = False


### End database collection ###

### Wrap up
    if password:
        os.system("PGPASSWORD=%s psql -c 'select pg_reload_conf()'"%password)
    else:
        os.system("psql -c 'select pg_reload_conf()'")
    find_device()
    postgresql_non_default()
    cpu_info()
    os.system("sar -A -d -f datacollection.sar > %sdatacollection.sar.txt"%SAR_DIRECTORY)
    move("datacollection.sar", "%sdatacollection%s.sar"%(DIRECTORY, strftime("%b%d%Y_%H:%M:%S", localtime())))
    null_file.close()
    check_sar_devices()
#finished
    print "\nHealth check data collection completed.\n"
    print "Do not forget to grab the log files from %s\n" % log_path

### Start ending scripts functions ###
def abort_clean(message, rollback, sar_created):
#exit, cleaning up the postgresql config if necessary
    if sar_created:
        print "SAR CREATED!"
        move("datacollection.sar", "%sdatacollection%s.sar"%(DIRECTORY, strftime("%b%d%Y_%H:%M:%S", localtime())))
        global sar_pid
        sar_data = "ps -fp %s"%sar_pid
        if "datacollection.sar" in commands.getstatusoutput(sar_data)[1]:
            kill_command = "kill %s"%sar_pid
            os.system(kill_command)
    if rollback:
        copyfile(backup_file, config_file)
        
        if password:
            os.system('PGPASSWORD=%s psql -c "SELECT pg_reload_conf()" %s'%(password, db ))
        else:
            os.system('psql -c "SELECT pg_reload_conf()" %s'%db )
    sys.exit(message)

### End ending script functions ###

### Start Wrap up functions ###

def find_device():
    timezone = commands.getoutput('date')
    if password:
        version = commands.getoutput('PGPASSWORD=%s psql --tuples-only --no-align -c "select version()"'%password)
    else:
        version = commands.getoutput('psql --tuples-only --no-align -c "select version()"')
    with open('%sdata_for_settings.txt'%DIRECTORY, 'a') as file:
        file.write("The date information for this server is: ")
        file.write(timezone)
        file.write("\n\n")
        file.write("The version information for postgres is: ")
        file.write(version)
        file.write("\n\n")
    file.close()
    if password:
        data_list =[commands.getstatusoutput("PGPASSWORD={psswrd} psql --tuples-only --no-align --command 'show data_directory;' -U postgres".format(psswrd=password))[1],]
    else:
        data_list =[commands.getstatusoutput("psql --tuples-only --no-align --command 'show data_directory;' -U postgres")[1],]
    data_list.extend(("/", "%s/pg_xlog"%data_list[0], "%s/pg_tblspc"%data_list[0]))

    print "\n\n"
    for data in data_list:
        device = commands.getstatusoutput('df %s'%data)[1].split()[6]
        x=7
        while not device[0] == '/':
            device = commands.getstatusoutput('df %s'%data)[1].split()[x]
            x += 1
            if x == 32:
                address = "Device data unavailable"
                exit
        address = commands.getstatusoutput('ls -l %s'%device)[1]

        if address.find("../") >-1:
            split_address = address.split()
            address = commands.getstatusoutput('ls -l /dev%s'%split_address[-1:][0][2:])[1]
        with open('%sdata_for_settings.txt'%DIRECTORY, 'a') as file:
            file.write("This should be the ls -l output for the %s device for the settings.py file.\n"%data)
            file.write(address)
            file.write("\n\n")
        file.close()



def postgresql_non_default():
    path = os.getcwd()+ "/OUTPUT/"
    if password:
        os.system('''PGPASSWORD=%s psql -c "COPY(select name, setting, unit from pg_settings where source <> 'default';) TO '%s/%s.csv' with delimiter ',' csv header"  >/dev/null 2>&1 &'''%(password, path, 'nondefault_postgresql_settings'))
    else:
        os.system('''psql -c "COPY(select name, setting, unit from pg_settings where source <> 'default';) TO '%s/%s.csv' with delimiter ',' csv header"  >/dev/null 2>&1 &'''%( path, 'nondefault_postgresql_settings'))


def cpu_info():
    operating_system = platform.system()
    if operating_system == "Darwin":
        os.system("echo '# This is the output of: sysctl -a | grep machdep.cpu \n' > %scpuinfo.txt"%DIRECTORY)
        os.system("sysctl -a | grep machdep.cpu >> %scpuinfo.txt"%DIRECTORY)
        os.system("echo '# This is the output of: diskutil list \n' > %sgen_device_info.txt"%DIRECTORY)
        os.system("diskutil list >> %sgen_device_info.txt"%DIRECTORY)
        os.system("echo '\n\n# This is the output of: diskutil info disk0s2 \n' >> %sgen_device_info.txt"%DIRECTORY)
        os.system("diskutil info disk0s2 >> %sgen_device_info.txt"%DIRECTORY)
        os.system("echo '\n\n# This is the output of: ls -lh /private/var/vm/swapfile* \n' >> %sgen_device_info.txt"%DIRECTORY)
        os.system("ls -lh /private/var/vm/swapfile* >> %sgen_device_info.txt"%DIRECTORY)
    elif operating_system in ("Linux", "Unix"):
        os.system("echo '# This is the output of: cat /proc/cpuinfo \n' > %scpuinfo.txt"%DIRECTORY)
        os.system("cat /proc/cpuinfo >> %scpuinfo.txt"%DIRECTORY)
        os.system("echo '# This is the output of: cat /etc/fstab \n' > %sgen_device_info.txt"%DIRECTORY)
        os.system("cat /etc/fstab  >> %sgen_device_info.txt"%DIRECTORY)
        os.system("echo '\n\n# This is the output of: cat /proc/swaps \n' >> %sgen_device_info.txt"%DIRECTORY)
        os.system("cat /proc/swaps >> %sgen_device_info.txt"%DIRECTORY)
        os.system("echo '\n\n# This is the output of: free -m \n' >> %sgen_device_info.txt"%DIRECTORY)
        os.system("free -m >> %sgen_device_info.txt"%DIRECTORY)
    else:
        print "You are not on Linux/Unix or Mac, you will need to get the cpu info manually."
    try:
        ec2_all = commands.getstatusoutput("ec2metadata")
        if ec2_all[0] == 0:
            with open("%sec2metadata.txt"%DIRECTORY, "w") as device_info:
                device_info.write("# This is the output of: ec2metadata \n")
                device_info.write(ec2_all[1])
            device_info.close()
    except:
        pass
    try:
        os.system("ps aux | sort -n -k 3 > %sps_after.txt"%DIRECTORY)
    except:
        pass

    with open("%suname.txt"%DIRECTORY, "w") as uname:
        uname.write("# This is the output of: uname \n\n")
        for item in platform.uname():
            uname.write(str(item)+ "\n")
    uname.close()
    if operating_system == "Linux":
        os.system("echo '\n\n# This is the output of: cat /proc/cmdline \n' >> %suname.txt"%DIRECTORY)
        os.system("cat /proc/cmdline >> %suname.txt"%DIRECTORY)
    os.system("echo '\n\n# This is the output of: find /dev -type b -exec ls -l {} \; \n' >> %sgen_device_info.txt"%DIRECTORY)
    os.system("find /dev -type b -exec ls -l {} \; >> %sgen_device_info.txt"%DIRECTORY)
    os.system("echo '\n\n# This is the output of: df -h \n' >> %sgen_device_info.txt"%DIRECTORY)
    os.system("df -h >> %sgen_device_info.txt"%DIRECTORY)
    os.system("echo '\n\n# This is the output of: mount \n' >> %sgen_device_info.txt"%DIRECTORY)
    os.system("mount >> %sgen_device_info.txt"%DIRECTORY)
    os.system("echo '\n\n# This is the output of: cat /etc/sysctl.conf \n' >> %sgen_device_info.txt"%DIRECTORY)
    os.system("cat /etc/sysctl.conf >> %sgen_device_info.txt"%DIRECTORY)

### End Wrap up functions ###


######################## Parameters ########################

hours = 2.0 #default hours
config_file = ""
database = ""
backup = ""
sar_created = False
monitor_db = ''
DIRECTORY = "OUTPUT/"
CONFIG_FILE_copy = DIRECTORY + "postgresql.conf"
PROJECT_DIRECTORY = DIRECTORY + "project/"
DB_DATA_DIRECTORY = PROJECT_DIRECTORY + "monitor/"
SAR_DIRECTORY = PROJECT_DIRECTORY + "sar/"
LOG_DIRECTORY = PROJECT_DIRECTORY + "logs/"
OLD_SAR_DIRECTORY = DIRECTORY + "old_sar/"



#parse the data entered by the user
parser = OptionParser()
parser.add_option('-t', '--hours', help='The time in hours to run the data collection', type = float, dest = "hours")
parser.add_option('-m', '--minutes', help='The time in minutes to run the data collection', type = int, dest = "minutes")
parser.add_option('-d', '--database', help='The name of the database', dest = "database")
parser.add_option('-c', '--config_file', help='The the full path to your postgresql.conf file', dest = "config_file")
parser.add_option('-r', action='store_true', dest = "needsreset")
parser.add_option('-s', action='store_true', dest="req_sleep")
parser.add_option('-p', '--password', help='Use this option if the database is password protected', dest = "password")
parser.add_option('--oldpg', action='store_true', dest='oldpg')
parser.add_option('--clear', help='Will clear out data from past run.', action='store_true', dest= "clear")
(options, args) = parser.parse_args()


needsreset = options.needsreset
req_sleep = options.req_sleep

if options.clear:
    clear()

if os.path.exists(os.path.dirname(PROJECT_DIRECTORY)):
    complain("Datacollection seems to have already run. Please delete duplicate files and run again or run again with the flag --clear.")

if not os.path.exists(os.path.dirname(DB_DATA_DIRECTORY)):
    os.makedirs(os.path.dirname(DB_DATA_DIRECTORY))
if not os.path.exists(os.path.dirname(SAR_DIRECTORY)):
    os.makedirs(os.path.dirname(SAR_DIRECTORY))
if not os.path.exists(os.path.dirname(LOG_DIRECTORY)):
    os.makedirs(os.path.dirname(LOG_DIRECTORY))

database_list = []

#set time and report
if not options.hours:
    if not options.minutes:
        print"This will run for", options.hours, "hours"
    else:
        print"This will run for", options.minutes, "minutes."
        hours = options.minutes/60.0
else:
    hours = options.hours

if options.password:
    password = options.password
else:
    password = None

#set database/s
if not options.database:
    database = raw_input("Please submit your database name:")
else:
    if options.database == "*+":
        while not os.path.isfile("%scheck_databases.txt"%DIRECTORY):
            print "Please create your file with each database to be checked on a new line. Ensure the file is named '%scheck_databases.txt'."%DIRECTORY
            print "Ensure the file is in this directory. The program will continue checking for the file until it is there."
            print "Press CTRL-C at any time to abort."
            print "\nWILL CHECK AGAIN IN 60 SECONDS"
            sleep(60)
        make_database_list()
        database = None
        database_data = database_list[0]
    elif options.database == "**":
        if password:
            os.system("PGPASSWORD=%s psql -c 'select datname from pg_database;' postgres > %scheck_databases.txt"%(password, DIRECTORY))
        else:
            os.system("psql -c 'select datname from pg_database;' postgres > %scheck_databases.txt"%DIRECTORY)
        make_database_list()
        database = None #easy trigger for multidb's
        database_data = database_list[0]
    else:
        database = options.database
        database_data = database

log_path = ""


if not options.config_file:
    config_file = raw_input("Please submit the full path to your postgresql.conf file: ")
else:
    config_file = options.config_file

if options.needsreset:
    choose_exit = raw_input("Your database will reset. Please press y to confirm, n to exit the program: ")
    if choose_exit == 'n':
        exit()



date = strftime("%a, %d %b %Y %H:%M:%S", localtime())


######################## Prerequisites #####################



if sys.version_info[:2] < (2,6):
    complain("This version requires Python 2.6. Found version " + '.'.join(map(str, sys.version_info)))
else:
    mention("Python version " + '.'.join(map(str, sys.version_info)) + ", great.")

check_installed("sar")
check_writable(options.config_file)
check_writable(os.getcwd())

#Ensures can connect to psql, logging is on.
try:
    if password:
        x = commands.getstatusoutput("PGPASSWORD=%s psql --tuples-only --no-align --command 'show logging_collector;' %s"%(password, database_data))
    else:
        x = commands.getstatusoutput("psql --tuples-only --no-align --command 'show logging_collector;' %s"%database_data)
    if not x[1].find('psql:') == -1:
        complain("Can not connect to psql with this error: %s"%x[1])
    elif not x[1].find('on') == -1:
        mention("Can connect to psql, great!\n Logging collector is on, great!")
    elif not x[1].find('off') == -1:
        complain("Can connect to psql, great!\n But logging is off, contact the client to get this turned on. It requires a restart.")
    else:
        mention("Please assess this message for an attempt at a PSQL connection returned %s"%x[1])
except:
    complain("Can't access postgresql.")

#Ensures there is enough room in the log directory, and that the log directory is writeable.
try:
    if password:
        y = commands.getstatusoutput("PGPASSWORD=%s psql --tuples-only --no-align --command 'show log_directory;' %s"%(password, database_data))
    else:
        y = commands.getstatusoutput("psql --tuples-only --no-align --command 'show log_directory;' %s"%database_data)
    if y[0] == 0:
        if y[1] == 'pg_log':
            if password:
                y = commands.getstatusoutput("PGPASSWORD=%s psql --tuples-only --no-align --command 'show data_directory;' %s"%(password,database_data))
            else:
                y = commands.getstatusoutput("psql --tuples-only --no-align --command 'show data_directory;' %s"%database_data)
        x = commands.getstatusoutput("df -Pm %s | tail -1 | awk '{print $4}'"%y[1])
        if hours <=1.5:
            if not int(x[1])/5120 > 0:
                complain("There is not enough space for the logs in " + y[1] + "There are only " + str(x[1]) + "MB left.")
            else:
                mention("There is plenty of room in the log file " + y[1] + " Great! There is " + str(int(x[1])/1024) + "G left.")
        else:
            if not int(x[1])/9216 > 0:
                complain("There is not enough space for the logs in " + y[1] + "There are only " + str(x[1]) + "MB left.")
            else:
                mention("There is plenty of room in the log file " + y[1] + " Great! There is " + str(int(x[1])/1024) + "G left.")

        check_writable(y[1])
    else:
        complain("Can't access the log_directory" + y)
except:
    complain("Can't access the log_directory")
log_path = y[1]

######################## Start of code ########################



try:
    # append log configuration to the postgresql.conf file
    backup_file = "%s.prod"%config_file
    ## pg.conf settings for query recording

    new_config = """
#added for performance data collection
#on {date}
log_destination = 'csvlog'
log_connections = on
log_disconnections = on
log_min_duration_statement = 1ms
log_autovacuum_min_duration = 0
log_temp_files = 1kB
log_lock_waits = on
log_checkpoints = on
log_filename = 'datacollection-%H'
log_rotation_age = 1h
log_rotation_size = 1GB
log_truncate_on_rotation = off
log_duration = off
log_statement = 'none'
log_error_verbosity = 'default'

    """.format(date = date)
    copyfile(config_file, CONFIG_FILE_copy)
    with open (CONFIG_FILE_copy, 'r+') as postgres_config:
        for line in postgres_config:
            if 'include' in line:
                if not '#' in line:
                    append_file = line[7:].strip().strip("'").strip('"')
                    new_name = DIRECTORY + "/" + append_file + "_conf_include"
                    copyfile(append_file, new_name)
    postgres_config.close()
    try:
    #snapshot of config file
        copyfile(config_file, backup_file)
    except Exception as problem:
        abort_clean('Configfile could not be copied: %s'%problem, needsreset, sar_created)
    try:
    #append logging data to config file
        with open(config_file, 'a+') as conf_file:
            conf_file.write(new_config)
        conf_file.close()
    except Exception as problem:
        abort_clean('Configfile could not be opened and appended: %s'%problem, needsreset, sar_created)

    try:
        os.system("ps aux | sort -n -k 3 > %sps_after.txt"%DIRECTORY)
    except:
        pass
    try:
        #Check if sar is already running and then start it.
        process_output = commands.getstatusoutput("pgrep sar")
        sar_data = "ps -fp %s"%process_output[1]
        if process_output[0] == 0:
            print
            if "datacollection.sar" in commands.getstatusoutput(sar_data)[1]:
                abort_clean("sar file already running. Please attend and rerun.", needsreset, sar_created)
        cycles = int(hours * 120 + 40) #cycles are the number of times we will look at sar for 30 secs. cycles * 30 secs is how long this will actually run for.
        null_file = open ('/dev/null', 'w')
        x = subprocess.Popen(["sar", "-d", "-A", "-o", "datacollection.sar", "30", "%d"%cycles], stdout=null_file)

        if x.poll() != None:
            abort_clean("unable to start sar.", needsreset, sar_created)
        global sar_pid
        sar_pid = x.pid
        sar_created = True
        sleep(.5)
    except Exception as problem:
        abort_clean('unable to start sar: %s'%problem, needsreset, sar_created)

### Start database collection ###

    # reload the configuration and take a snapshot of database stats
    needsreset = True
    if database == None:#For multiple databases
        for db in database_list:#go through each database
            print "For database: %s"%db.upper()
            try:
                print "Collecting data for %s"%db
                if begining_database_collection(db, req_sleep) == 1:
                    abort_clean('initialization of database statistics failed', needsreset, sar_created)
                print("\n")
            except Exception as problem:
                abort_clean('initialization of database statistics failed: %s'%problem, needsreset, sar_created)
    else:
        try:
            print "Collecting data for %s"%database
            if begining_database_collection(database, req_sleep) == 1:
                abort_clean('initialization of database statistics failed', needsreset, sar_created)
        except Exception as problem:
            abort_clean('initialization of database statistics failed: %s'%problem, needsreset, sar_created)
        db = database
    monitor_db = db
    try:
        print "Collecting data for the server"
        if begining_server_collection(monitor_db) == 1:
            abort_clean('initialization of database statistics failed', needsreset, sar_created)
    except Exception as problem:
        abort_clean('initialization of database statistics failed: %s'%problem, needsreset, sar_created)

### Start Reload config attempts, we do this three times to make sure the data actually reloads. Program quits if it does not.
    #tries a reload()
    try:
        if password:
            if not os.system('PGPASSWORD=%s psql -c "SELECT pg_reload_conf()" %s'%(password, db)) == 0:
                abort_clean('pg_reload_conf failed:', needsreset, sar_created)
        else:
            if not os.system('psql -c "SELECT pg_reload_conf()" %s'%db) == 0:
                abort_clean('pg_reload_conf failed:', needsreset, sar_created)
    except Exception as problem:
        abort_clean('pg_reload_conf failed: %s'%problem, needsreset, sar_created)
    sleep(6)

    #checks if can kill pid of of pg_stat_activity and kills if in newer then 9.1 this may be causing the issue.
    try:
        if options.oldpg == None:
            print "psql -c 'select pid from pg_stat_activity' %s"%db
            if password:
                x = commands.getstatusoutput("PGPASSWORD=%s psql --tuples-only --no-align -c 'select pid from pg_stat_activity' %s"%(password, db))
            else:
                x = commands.getstatusoutput("psql --tuples-only --no-align -c 'select pid from pg_stat_activity' %s"%db)
            if x[0] !=0:
                print "pid did not reset, this should be ok, but you may be in a version older than 9.1"
        elif options.oldpg == True:
            print "psql -c 'select procpid from pg_stat_activity' %s"%db
            if password:
                x = commands.getstatusoutput("PGPASSWORD=%s psql --tuples-only --no-align -c 'select procpid from pg_stat_activity' %s"%(password, db))
            else:
                x = commands.getstatusoutput("psql --tuples-only --no-align -c 'select procpid from pg_stat_activity' %s"%db)
            if x[0] !=0:
                print "pid did not reset, this should be ok, but you may be in a version newer than 9.1"
        else:
            abort_clean("oldpg not set correctly.", needsreset, sar_created)
        for r in x[1:]:
            try:
                pid = int(r)
                if not os.kill(pid, "-HUP") == None:
                    print "Kill -HUP failed - script is still running"
                print "Reload PID: %d"%pid
            except:
                pass
    except Exception as problem:
        print "Kill -HUP failed - script is still running %s"%problem

# hack for issues with the logging process not taking the reload, so we check if it is loaded and keep trying.
#If it fails after 10 tries the user is notified to quit the program.
    if password:
        y = commands.getstatusoutput("""PGPASSWORD=%s psql --tuples-only --no-align --command "select setting, reset_val from pg_settings where name = 'log_min_duration_statement'" %s"""%(password, db))[1]
    else:
        y = commands.getstatusoutput("""psql --tuples-only --no-align --command "select setting, reset_val from pg_settings where name = 'log_min_duration_statement'" %s"""%db)[1]
    x = 1
    while not y == '1|1':
        sleep(3);
        try:
            if password:
                if not os.system('PGPASSWORD=%s psql -c "SELECT pg_reload_conf()" %s'%(password, monitor_db)) == 0:
                    abort_clean('pg_reload_conf failed:', needsreset, sar_created)
            else:
                if not os.system('psql -c "SELECT pg_reload_conf()" %s'%monitor_db) == 0:
                    abort_clean('pg_reload_conf failed:', needsreset, sar_created)
        except Exception as problem:
            abort_clean('pg_reload_conf failed: %s'%problem, needsreset, sar_created)
        x+=1
        print "This is the %d pg_reload attempt" %x
        if x == 10:
            abort_clean("log_min_duration_statement does not equal 1ms, exiting...\n", needsreset, sar_created)
        if password:
            y = commands.getstatusoutput("""PGPASSWORD={psswrd} psql --tuples-only --no-align --command "select setting, reset_val from pg_settings where name = 'log_min_duration_statement'" data""".format(psswrd=password))[1]
        else:
            y = commands.getstatusoutput("""psql --tuples-only --no-align --command "select setting, reset_val from pg_settings where name = 'log_min_duration_statement'" data""")[1]

### End Reload config attempts

# copy the config file back the way it was in case the process is
# aborted and restarted
    try:
        copyfile(backup_file, config_file)
    except Exception as problem:
        abort_clean('could not restore postgresql.conf to original state: %s'%problem, needsreset, sar_created)

### Waiting period ###

    # wait for hours
    if options.hours:
        print "Data collection begun.  Data collection will continue for %d hours.\n"%hours
    else:
        print "Data collection begun.  Data collection will continue for %d minutes.\n"%options.minutes

    print "Press CTRL-C at any time to abort.\n"

    minutes = hours * 60 + 10
    this_minute = 0

    while(this_minute <= minutes):
        sleep(600)
        this_minute +=10
        print "%d minutes of queries collected. \n"%this_minute

    wrap_up(database, database_list, needsreset, sar_created, db, monitor_db, req_sleep)

except KeyboardInterrupt:
    user_input = (raw_input("Please enter f for fast exit or g for a graceful exit: ")).lower()
    if user_input == 'g':
        wrap_up(database, database_list, needsreset, sar_created, db, monitor_db, req_sleep)
    else:
        abort_clean('Execution cancelled by user.', needsreset, sar_created)
exit(0);
