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'
