def getDefaults (check , warning , critical ) : if check == 'backends' : # only whole digits and percentages allowed, an or separator is also allowed return fac.getNumberPercentMix (warning, critical, '80%', '90%' ) elif check == 'wals' : # -- warning 70%Max count # -- critical 80%Max count return fac.getNumberPercentMix (warning, critical, '70%', '80%' ) elif check == 'autovacuum' or check == 'vacuum' or check == 'autoanalyze' or check == 'analyze': # -- warning = 1 week # -- critical = 2 weeks return fac.getTimeDefaults (warning, critical , '1wk', '2wks',0.8) elif check == 'table_bloat' or check == 'index_bloat' : # Warning and/or Critical must be provided return fac.warningAndOrCriticalProvided (warning,critical,0.8) elif check == 'table_size' or check == 'index_size' or check == 'database_size' : # warning and/or critical value must be supplied return fac.warningAndOrCriticalProvided (warning,critical,0.5) elif check == 'nonblocking' or check == 'blocking' : # --warning = 2min # critical = 3min return fac.getTimeDefaults (warning, critical , '1min', '2mins', 0.8) elif check == 'checkpoints' : # warning and/or critical value must be supplied return None elif check == 'replica_lag' : # -- warning = 5 wal files # -- critical = 10 wal files return fac.getNumberPercentMix (warning, critical, '5', '10' ) elif check == 'connections' : return {'warning':'dummy', 'critical' :'dummy'}
def getLocks( param=None ) : item_name = 'POSTGRES_' status = [] perfdata = '-' output = '' if param != None : check = (param['check']).lower() findText = param.get('find') item_name = item_name + check.upper() + '_LOCKS' dbnames = param.get('dbname') user = param['user'] password = param.get('password') host = param['host'][0] port = param['port'][0] warning = [] critical = [] query = "SELECT substring(version() FROM '(\d.\d)')::double precision" results = sql.getSQLResult ( {'host': host , 'port' : port, 'dbname': dbnames[0], 'user' : param['user'] ,'password' : param['password'] } ,query ) if results[0] == None : return str('2') + ' ' + str(item_name) + ' ' + '-' + ' ' + str(results[1]) version = (results[1])[0][0] retvals = fac.getTimeDefaults ( param.get('warning'), param.get('critical') ) if retvals !=None : warning = retvals.get('warning') critical = retvals.get('critical') else : return '2' + ' ' + item_name + ' ' + '-' + ' ' + 'Invalid parameter passed !' results = [] exclude_db = param.get('exclude_db') for db in exclude_db : if db in dbnames : dbnames.remove(db) for dbname in dbnames : if check == 'nonblocking' : results = sql.getSQLResult ( {'host': host , 'port' : port , 'dbname': dbname,\ 'user' : user ,'password' : password } ,getNonBlockingVersionQuery(version,warning[0], warning[1]) ) elif check == 'blocking' : results = sql.getSQLResult ( {'host': host , 'port' : port , 'dbname': dbname,\ 'user' : user ,'password' : password } ,getBlockingVersionQuery(version) ) if results[0] == None : return str('2') + ' ' + str(item_name) + ' ' + str(perfdata) + ' ' + str(results[1]) retval = {} if len(results[1]) > 0 and check == 'nonblocking' : retval = getNonBlockingIterator(results[1],'POSTGRES_NONBLOCKING_LOCKS',warning,critical, status) elif len(results[1]) > 0 and check == 'blocking' : retval = getBlockingIterator(results[1],'POSTGRES_BLOCKING_LOCKS',findText, status,warning,critical) status.append(retval.get('status')) if perfdata != '-' and len(results[1]) > 0 : perfdata = perfdata + retval.get('perfdata') output = retval.get('output') elif perfdata == '-' and len(results[1]) > 0 : perfdata = retval.get('perfdata') output = output + ';\n' + retval.get('output') if perfdata != '-' : status.sort(reverse=True) return str(status[0]) + ' ' + str(item_name) + ' ' + str(perfdata) + ' ' + str(output) else : return '0' + ' ' + item_name + ' ' + '-' + ' ' + 'OK'
def getVacuums( param=None ) : item_name = 'POSTGRES_' status = [] perfdata = '-' output = '' warning = [] critical = [] if param != None : dbnames = param.get('dbname') check = param['check'] item_name = item_name + check.upper() index = getReturnIndex(check) retval = fac.getTimeDefaults ( param.get('warning'), param.get('critical') ) if retval != None : warning = retval.get('warning') critical = retval.get('critical') else : return '2' + ' ' + item_name + ' ' + '-' + ' ' + 'Invalid Parameters supplied !' query = "SELECT table_name,vacuum_time FROM ( \ SELECT (schemaname || '.' || relname) table_name,\ CASE WHEN {0:s} IS NULL AND {1:s} IS NULL THEN \ date_part('epoch',clock_timestamp() - '1970-01-01 00:00:00'::timestamp ) /60 \ WHEN {0:s} IS NOT NULL AND {1:s} IS NULL THEN \ date_part('epoch',clock_timestamp() - {0:s} ) /60 \ WHEN {0:s} IS NULL AND {1:s} IS NOT NULL THEN \ date_part('epoch',clock_timestamp() - {1:s} ) /60 \ WHEN {0:s} IS NOT NULL AND {1:s} IS NOT NULL THEN \ GREATEST(date_part('epoch',clock_timestamp() - {0:s} ) /60, \ date_part('epoch',clock_timestamp() - {1:s} ) /60 ) \ END AS vacuum_time \ FROM \ pg_stat_user_tables \ ) foo \ WHERE \ ( vacuum_time ) >= ( {2:d} * {3:d} ) \ ORDER BY vacuum_time DESC LIMIT 5".format( index[1],index[2] , int(warning[0]) , int(warning[1]) ) exclude_db = param.get('exclude_db') for db in exclude_db : if db in dbnames : dbnames.remove(db) for dbname in dbnames : results = sql.getSQLResult ( {'host': param['host'][0] , 'port' : param['port'][0], 'dbname': dbname, 'user' : param['user'] ,'password' : param['password'] } ,query ) if results[0] == None : return '2' + ' ' + item_name + ' ' + perfdata + ' ' + str(results[1]) rows = results[1] if len(rows) > 0 : for row in rows : status.append( st.getStatus( int(row[index[0]]) , int(warning[0]) * int(warning[1]) , int(critical[0]) * int(critical[1]) ) ) out_unit = str ( int(row[1]/(60*24)) ) + ' days ago ' if perfdata == '-' : perfdata = perf.getPerfStm (row[0],str(row[index[0]]),str(warning[0]),str(critical[0])) output = '{0:s} last {1:s} time was {2:s} '.format(row[0],check,out_unit) elif perfdata != '-' : perfdata = perfdata + '|' + perf.getPerfStm (row[0],str(row[index[0]]),str(warning[0]),str(critical[0])) output = output + ';\n {0:s} last {1:s} time was {2:s}'.format(row[0],check,out_unit) status.sort( reverse=True ) if perfdata != '-' : return str(status[0]) + ' ' + item_name + ' ' + str(perfdata) + ' ' + output else : return str('0') + ' ' + item_name + ' ' + '-' + ' ' + 'OK'
def getVacuums(param=None): item_name = 'POSTGRES_' status = [] perfdata = '-' output = '' warning = [] critical = [] if param != None: dbnames = param.get('dbname') check = param['check'] item_name = item_name + check.upper() index = getReturnIndex(check) retval = fac.getTimeDefaults(param.get('warning'), param.get('critical')) if retval != None: warning = retval.get('warning') critical = retval.get('critical') else: return '2' + ' ' + item_name + ' ' + '-' + ' ' + 'Invalid Parameters supplied !' query = "SELECT table_name,vacuum_time FROM ( \ SELECT (schemaname || '.' || relname) table_name,\ CASE WHEN {0:s} IS NULL AND {1:s} IS NULL THEN \ date_part('epoch',clock_timestamp() - '1970-01-01 00:00:00'::timestamp ) /60 \ WHEN {0:s} IS NOT NULL AND {1:s} IS NULL THEN \ date_part('epoch',clock_timestamp() - {0:s} ) /60 \ WHEN {0:s} IS NULL AND {1:s} IS NOT NULL THEN \ date_part('epoch',clock_timestamp() - {1:s} ) /60 \ WHEN {0:s} IS NOT NULL AND {1:s} IS NOT NULL THEN \ GREATEST(date_part('epoch',clock_timestamp() - {0:s} ) /60, \ date_part('epoch',clock_timestamp() - {1:s} ) /60 ) \ END AS vacuum_time \ FROM \ pg_stat_user_tables \ ) foo \ WHERE \ ( vacuum_time ) >= ( {2:d} * {3:d} ) \ ORDER BY vacuum_time DESC LIMIT 5".format(index[1], index[2], int(warning[0]), int(warning[1])) exclude_db = param.get('exclude_db') for db in exclude_db: if db in dbnames: dbnames.remove(db) for dbname in dbnames: results = sql.getSQLResult( { 'host': param['host'][0], 'port': param['port'][0], 'dbname': dbname, 'user': param['user'], 'password': param['password'] }, query) if results[0] == None: return '2' + ' ' + item_name + ' ' + perfdata + ' ' + str( results[1]) rows = results[1] if len(rows) > 0: for row in rows: status.append( st.getStatus(int(row[index[0]]), int(warning[0]) * int(warning[1]), int(critical[0]) * int(critical[1]))) out_unit = str(int(row[1] / (60 * 24))) + ' days ago ' if perfdata == '-': perfdata = perf.getPerfStm(row[0], str(row[index[0]]), str(warning[0]), str(critical[0])) output = '{0:s} last {1:s} time was {2:s} '.format( row[0], check, out_unit) elif perfdata != '-': perfdata = perfdata + '|' + perf.getPerfStm( row[0], str(row[index[0]]), str(warning[0]), str(critical[0])) output = output + ';\n {0:s} last {1:s} time was {2:s}'.format( row[0], check, out_unit) status.sort(reverse=True) if perfdata != '-': return str(status[0]) + ' ' + item_name + ' ' + str( perfdata) + ' ' + output else: return str('0') + ' ' + item_name + ' ' + '-' + ' ' + 'OK'