def getLoggingSettings(self, comp):
util.read_env_file(comp)
conn = psycopg2.connect(
"dbname='postgres' user='******' host='localhost'")
query = "select category, name, setting, short_desc from pg_settings where name in " \
+ repr(tuple(map(str,log_param_dict.keys())))
cursor = conn.cursor()
cursor.execute(query)
columns = [desc[0] for desc in cursor.description]
result = []
for res in cursor:
result.append(dict(zip(columns, res)))
cursor.close()
conn.close()
final_result = []
for k in result:
f_row = {}
f_row['category'] = k['category']
f_row['name'] = k['name']
f_row['setting'] = k['setting']
if k['setting'] == 'on':
f_row['enabled'] = True
elif k['setting'] == 'off':
f_row['enabled'] = False
f_row['short_desc'] = k['short_desc']
f_row['recommended'] = log_param_dict[k['name']][1]
f_row['order'] = log_param_dict[k['name']][0]
final_result.append(f_row)
sorted_list = sorted(final_result, key=itemgetter('order'))
return sorted_list
def pg_settings(self, comp):
"""
Method to get the settings of cluster.
:return: It yields json string.
"""
result = dict()
result['component'] = comp
port = util.get_column('port', comp)
try:
if comp in self.pgpassfiles:
os.environ['PGPASSFILE'] = self.pgpassfiles[comp]
else:
util.read_env_file(comp)
self.pgpassfiles[comp] = os.environ['PGPASSFILE']
pg = PgInstance("localhost", "postgres", "postgres", int(port))
pg.connect()
settings_list = pg.get_pg_settings()
pg.close()
import itertools
final_list = []
for key, group in itertools.groupby(settings_list,
key=lambda x: x['category']):
final_list.append({'name': str(key), 'settings': list(group)})
result['list'] = final_list
except Exception as e:
pass
yield self.session.publish('com.bigsql.onPGsettings', result)
def get(self, comp):
import util
try:
result = dict()
util.read_env_file(comp)
result['PGUSER'] = os.environ['PGUSER']
result['PGDATABASE'] = os.environ['PGDATABASE']
result['PGPORT'] = os.environ['PGPORT']
except Exception as e:
result = {}
result['error'] = 1
result['msg'] = str(e)
return result
def switchLogfile(self, comp, logFile):
util.read_env_file(comp)
conn = psycopg2.connect(
"dbname='postgres' user='******' host='localhost'")
old_isolation_level = conn.isolation_level
conn.set_isolation_level(0)
cursor = conn.cursor()
try:
if logFile:
alterQuery = "alter system set log_filename='%s';" % (logFile)
notice_msg = "pgDevOps by BigSQL: Switched logfile to {0}"
else:
alterQuery = "alter system reset log_filename;"
notice_msg = "pgDevOps by BigSQL: Reset logfile to {0}"
cursor.execute(alterQuery)
pgReloadQuery = "select pg_reload_conf();"
cursor.execute(pgReloadQuery)
rotateQuery = "select pg_rotate_logfile();"
cursor.execute(rotateQuery)
time.sleep(2)
log_file_name_query = "show log_filename;"
cursor.execute(log_file_name_query)
curResult = cursor.fetchone()
log_file_name = curResult[0]
log_file_name = log_file_name.replace("%", "%%")
notice_msg = notice_msg.format(log_file_name)
query = """BEGIN;
SET log_min_messages = notice;
DO language plpgsql $$
BEGIN
RAISE NOTICE '{0}';
END
$$;
RESET log_min_messages;
END;"""
raise_query = query.format(notice_msg)
cursor.execute(raise_query)
conn.set_isolation_level(old_isolation_level)
conn.close()
return True
except Exception as e:
conn.set_isolation_level(old_isolation_level)
conn.close()
return str(e)
def changeLoggingParams(self, comp, logDict={}):
util.read_env_file(comp)
conn = psycopg2.connect(
"dbname='postgres' user='******' host='localhost'")
old_isolation_level = conn.isolation_level
conn.set_isolation_level(0)
cursor = conn.cursor()
try:
for k in logDict.keys():
alterQuery = "alter system set %s='%s';" % (k, logDict[k])
cursor.execute(alterQuery)
pgReloadQuery = "select pg_reload_conf();"
cursor.execute(pgReloadQuery)
conn.set_isolation_level(old_isolation_level)
conn.close()
return True
except Exception as e:
conn.set_isolation_level(old_isolation_level)
conn.close()
return str(e)
def do_pgc_tune(p_comp, email, print_json=False):
tune_metadata = "tuning_service_descriptor"
tuning_service = get_json_file(tune_metadata, print_json)
tune_request = {}
tune_request['tune_request'] = {}
util.read_env_file(p_comp)
port = util.get_comp_port(p_comp)
pg = PgInstance("localhost", "postgres", "postgres", int(port))
pg.connect()
tune_request['tune_request']['postgres_config'] = pg.get_raw_pg_settings()
pg.close()
tune_request['tune_request']['email'] = email
tune_request['tune_request']['user_agent'] = 'pgc {}'.format(
util.get_pgc_version())
tune_request['tune_request']['postgres_config'][
'postgres_version'] = util.get_column('version', p_comp)
tune_request['tune_request']['system_config'] = api.info(
True, 'na', 'na', False)
# Web request
tune_url = tuning_service['bigtuna']['url']
tune_url = tune_url + tuning_service['bigtuna']['path']
req = urllib2.Request(tune_url)
req.add_header('Content-Type', 'application/json')
tune_result = urllib2.urlopen(req, json.dumps(tune_request))
result = json.load(tune_result)
if print_json:
print(
json.dumps(result,
sort_keys=True,
indent=2,
separators=(',', ': ')))
else:
for key in result['tune_result']:
print("{0} = {1}".format(key, result['tune_result'][key]))
def generate_profiler_reports(self,
hostname="localhost",
username="******",
port=5432,
database="",
password="",
queries="",
report_title="",
report_desc="",
comp=None):
if comp:
util.read_env_file(comp)
username = os.environ.get("PGUSER")
port = os.environ.get("PGPORT")
result = {}
try:
from ProfilerReport import ProfilerReport
args = {}
args['pgPass'] = password
args['hostName'] = hostname
args['pgDB'] = database
args['pgUser'] = username
args['pgPort'] = port
plReport = ProfilerReport(args)
report_file = plReport.generateSQLReports(queries, report_title,
report_desc)
result['error'] = 0
result['report_file'] = report_file
except Exception as e:
import traceback
print traceback.format_exc()
print e
result = {}
result['error'] = 1
result['msg'] = str(e)
yield self.session.publish('com.bigsql.profilerReports', result)
def db_list(self, comp):
"""
Method to get the list of database available in cluster.
:return: It yields json string.
"""
result = dict()
result['component'] = comp
port = util.get_column('port', comp)
try:
if comp in self.pgpassfiles:
os.environ['PGPASSFILE'] = self.pgpassfiles[comp]
else:
util.read_env_file(comp)
self.pgpassfiles[comp] = os.environ['PGPASSFILE']
pg = PgInstance("localhost", "postgres", "postgres", int(port))
pg.connect()
database_list = pg.get_database_list()
pg.close()
result['list'] = database_list
except Exception as e:
pass
yield self.session.publish('com.bigsql.ondblist', result)
def checkExtension(self, database, comp, extension):
"""
Method to get the list of database available in cluster.
:return: It yields json string.
"""
result = dict()
result['component'] = comp
port = util.get_column('port', comp)
try:
if comp in self.pgpassfiles:
os.environ['PGPASSFILE'] = self.pgpassfiles[comp]
else:
util.read_env_file(comp)
self.pgpassfiles[comp] = os.environ['PGPASSFILE']
pg = PgInstance("localhost", "postgres", database, int(port))
pg.connect()
is_extension_installed = pg.is_extension_installed(extension)
pg.close()
result['status'] = is_extension_installed
except Exception as e:
pass
yield self.session.publish('com.bigsql.onCheckExtension', result)
import subprocess
import os
import sys
MY_HOME = os.getenv("MY_HOME", "")
sys.path.append(os.path.join(MY_HOME, 'hub', 'scripts'))
sys.path.append(os.path.join(MY_HOME, 'hub', 'scripts', 'lib'))
import util
util.set_lang_path()
pgver = "pg9X"
dotver = pgver[2] + "." + pgver[3]
datadir = util.get_column('datadir', pgver)
logdir = util.get_column('logdir', pgver)
autostart = util.get_column('autostart', pgver)
pg_ctl = os.path.join(MY_HOME, pgver, "bin", "pg_ctl")
logfile = util.get_column('logdir', pgver) + os.sep + "postgres.log"
util.read_env_file(pgver)
cmd = pg_ctl + ' start -s -w -D "' + datadir + '" ' + '-l "' + logfile + '"'
util.system(cmd)
def save_dbstats_data(self):
try:
connL = sqlite3.connect(db_local)
c = connL.cursor()
sql = "SELECT component, port" + \
" FROM components " + \
" WHERE project='pg' AND datadir != ''"
c.execute(sql)
t_comp = c.fetchall()
for comp in t_comp:
pg_comp = str(comp[0])
try:
if pg_comp in self.pgpassfiles:
os.environ['PGPASSFILE'] = self.pgpassfiles[pg_comp]
else:
util.read_env_file(pg_comp)
self.pgpassfiles[pg_comp] = os.environ['PGPASSFILE']
port = comp[1]
pg = PgInstance("localhost", "postgres", "postgres",
int(port))
pg.connect()
cluster_stats_data = pg.get_cluster_stats()
activity_details = pg.get_current_activity()
connections = pg.get_active_connections()
self.db_activity[pg_comp] = activity_details
pg.close()
stats_timestamp = datetime.utcnow()
stats_time = stats_timestamp.strftime('%Y/%m/%d %H:%M:%S')
if cluster_stats_data and cluster_stats_data[0]:
comp_db_stats = cluster_stats_data[0]
else:
continue
comp_db_stats['time'] = stats_time
if pg_comp in self.db_stats:
if len(self.db_stats[pg_comp]) == 60:
self.db_stats[pg_comp].pop(0)
previous = self.previous_db_stats[pg_comp]
self.previous_db_stats[pg_comp] = {}
time_diff = stats_timestamp - previous['timestamp']
diff_seconds = time_diff.seconds
current_xact_commit = (
comp_db_stats['xact_commit'] -
previous['xact_commit']) / diff_seconds
current_xact_rollback = (
comp_db_stats['xact_rollback'] -
previous['xact_rollback']) / diff_seconds
self.previous_db_stats[pg_comp][
'xact_commit'] = comp_db_stats['xact_commit']
self.previous_db_stats[pg_comp][
'xact_rollback'] = comp_db_stats['xact_rollback']
self.previous_db_stats[pg_comp][
'timestamp'] = stats_timestamp
comp_db_stats['xact_commit'] = int(
round(current_xact_commit))
comp_db_stats['xact_rollback'] = int(
round(current_xact_rollback))
tup_fetched = (comp_db_stats['tup_fetched'] -
previous['tup_fetched']) / diff_seconds
tup_inserted = (
comp_db_stats['tup_inserted'] -
previous['tup_inserted']) / diff_seconds
tup_updated = (comp_db_stats['tup_updated'] -
previous['tup_updated']) / diff_seconds
tup_deleted = (comp_db_stats['tup_deleted'] -
previous['tup_deleted']) / diff_seconds
self.previous_db_stats[pg_comp][
'tup_fetched'] = comp_db_stats['tup_fetched']
self.previous_db_stats[pg_comp][
'tup_inserted'] = comp_db_stats['tup_inserted']
self.previous_db_stats[pg_comp][
'tup_updated'] = comp_db_stats['tup_updated']
self.previous_db_stats[pg_comp][
'tup_deleted'] = comp_db_stats['tup_deleted']
self.previous_db_stats[pg_comp][
'timestamp'] = stats_timestamp
comp_db_stats['tup_fetched'] = int(round(tup_fetched))
comp_db_stats['tup_inserted'] = int(
round(tup_inserted))
comp_db_stats['tup_updated'] = int(round(tup_updated))
comp_db_stats['tup_deleted'] = int(round(tup_deleted))
comp_db_stats['connections'] = {}
for conn in connections:
comp_db_stats['connections'][str(
conn['state'])] = conn['count']
self.db_stats[pg_comp].append(comp_db_stats)
self.latest_db_stats[pg_comp] = comp_db_stats
pass
else:
self.db_stats[pg_comp] = []
self.previous_db_stats[pg_comp] = {}
self.previous_db_stats[pg_comp][
'xact_commit'] = comp_db_stats['xact_commit']
self.previous_db_stats[pg_comp][
'xact_rollback'] = comp_db_stats['xact_rollback']
self.previous_db_stats[pg_comp][
'tup_fetched'] = comp_db_stats['tup_fetched']
self.previous_db_stats[pg_comp][
'tup_inserted'] = comp_db_stats['tup_inserted']
self.previous_db_stats[pg_comp][
'tup_updated'] = comp_db_stats['tup_updated']
self.previous_db_stats[pg_comp][
'tup_deleted'] = comp_db_stats['tup_deleted']
self.previous_db_stats[pg_comp][
'timestamp'] = stats_timestamp
except Exception as e:
pass
connL.close()
except sqlite3.Error as e:
print str(e)
def generate_profiler_reports(self,
hostname="localhost",
username="******",
port=5432,
database="",
password="",
queries="",
report_title="",
report_desc="",
action=None,
comp=None):
if comp:
util.read_env_file(comp)
username = os.environ.get("PGUSER")
port = os.environ.get("PGPORT")
result = {}
result['action'] = action
try:
from ProfilerReport import ProfilerReport
args = {}
args['pgPass'] = password
args['hostName'] = hostname
args['pgDB'] = database
args['pgUser'] = username
args['pgPort'] = port
plReport = ProfilerReport(args)
report_file = ""
result['error'] = 0
if action == "enable":
plReport.enableProfiler()
result[
'msg'] = "Global profiling statistics has been enabled. Execute a PL/pgSQL workload before viewing the report."
elif action == "disable":
plReport.disableProfiler()
result[
'msg'] = "Global profiling statistics has been disabled."
elif action == "check":
is_enabled = plReport.is_enabled()
result['enabled'] = is_enabled
if is_enabled:
result['status'] = 'enabled'
else:
result['status'] = 'disabled'
elif action == "reset":
plReport.resetSharedData()
result['msg'] = "Global profiling statistics reset."
elif action == "profile_query":
report_file = plReport.generateQueryReports(
queries, report_title, report_desc)
elif action == "generate":
if plReport.has_data():
report_file = plReport.generateGlobalReports(
report_title, report_desc)
else:
result['error'] = 1
result['msg'] = "No profiling statistics available."
if not plReport.is_enabled():
result['msg'] = "Profiler is not enabled."
result['report_file'] = report_file
except Exception as e:
import traceback
print traceback.format_exc()
print e
result = {}
result['error'] = 1
result['msg'] = str(e)
yield self.session.publish('com.bigsql.profilerReports', result)
