def getReplicaLags(param=None):
item_name = 'POSTGRES_REPLICA_LAG'
status = []
perfdata = '-'
output = ''
if param != None:
warning = int(param['warning'])
critical = int(param['critical'])
query = "SELECT pg_current_xlog_location() "
dbname = param.get('dbname')[0]
hosts = param['host']
ports = param['port']
if len(hosts) > len(ports):
ports = ports * len(hosts)
master = sql.getSQLResult(
{
'host': hosts[0],
'port': ports[0],
'dbname': dbname,
'user': param['user'],
'password': param['password']
}, query)
if master[0] == None:
return '2' + ' ' + item_name + ' ' + '-' + ' ' + str(master[1])
counter = 1
for rep_host in hosts[1:]:
query = "SELECT pg_last_xlog_replay_location()"
replica = sql.getSQLResult ( {'host': rep_host , 'port' : ports[counter], 'dbname': dbname \
, 'user' : param['user'] ,'password' : param['password'] } ,query )
if replica[0] == 0:
wal_lag = hexa.computeMegaByteDiff(master[1], replica[1]) / 16
elif replica[0] == None:
wal_lag = -1
if perfdata == '-':
perfdata = perf.getPerfStm(rep_host, wal_lag, warning,
str(critical))
output = 'replica on {0:s} lags behind master {1:s} by a total of {2:s} WALs.'\
.format( rep_host , hosts[0] , str(wal_lag) )
elif perfdata != '-':
perfdata = perfdata + '|' + perf.getPerfStm(
rep_host, wal_lag, warning, str(critical))
output = output + ';\n replica on {0:s} lags behind master {1:s} by a total of {2:s} WALs'\
.format( rep_host , hosts[0] , str(wal_lag) )
if wal_lag != -1:
status.append(st.getStatus(wal_lag, warning, critical))
elif wal_lag == -1:
status.append(2)
counter += 1
status.sort(reverse=True)
output = output + '\n NOTE: a -1 lag value means the replica is not available !!!'
return str(
status[0]) + ' ' + item_name + ' ' + str(perfdata) + ' ' + output
def getBackends( param=None ) :
item_name = 'POSTGRES_BACKENDS'
status = []
perfdata = '-'
output = ''
if param != None :
exclude = param.get('exclude') if param.get('exclude') != None else ''
excdb = ''
if exclude != '' :
for exc in exclude :
excdb = ' AND dbs.datname <> ' + "'" + exc + "'"
query = "SELECT \
dbs.datname, \
( current_setting('max_connections')::int - current_setting('superuser_reserved_connections')::int ) as max_allowed_connect,\
coalesce( nullif((numbackends -1 ),-1), 0 ) as connect_count,\
( coalesce( nullif((numbackends -1 ),-1), 0 ) * 100 ) / current_setting('max_connections')::int as percent_connect \
FROM \
pg_stat_database dbs \
JOIN \
pg_database db on db.datname = dbs.datname \
WHERE \
db.datistemplate IS FALSE {0:s} ".format(excdb)
results = sql.getSQLResult ( {'host': param['host'][0] , 'port' : param['port'][0], 'dbname': param['dbname'], 'user' : param['user'] ,'password' : param['password'] } ,query )
connect_sum = 0
if results[0] == None :
return '2' + ' ' + item_name + ' ' + '-' + ' ' + str(results[1])
rows = results[1]
if len(rows) > 0 :
max_connect = rows[0][1]
warning = fac.getNumberPercentLimits( param['warning'] , max_connect )
critical = fac.getNumberPercentLimits( param['critical'] , max_connect )
for row in rows :
if int(row[2]) != 0 :
if perfdata == '-' :
perfdata = row[0] + '=' + str(row[2]) + ';' + str(warning) + ';' + str(critical) + ';' + '1' + ';' + str(row[1])
output = '{0:s} has {1:d}% of the total connections used'.format(row[0],row[3])
elif perfdata != '-' :
perfdata = perfdata + '|' + row[0] + '=' + str(row[2]) + ';' + str(warning) + ';' + str(critical) + ';' + '1' + ';' + str(row[1])
output = output + ';{0:s} has {1:d}% of the total connections used'.format(row[0],row[3])
connect_sum += int(row[2])
else :
if perfdata == '-' :
perfdata = row[0] + '=' + str(row[2]) + ';' + str(warning) + ';' + str(critical) + ';' + '1' + ';' + str(row[1])
output = 'No backend currenlty exists for {0:s} database - which might implies that the Application connected to the database might be down or the database currently refuses connections to it '.format(row[0])
elif perfdata != '-' :
perfdata = perfdata + '|' + row[0] + '=' + str(row[2]) + ';' + str(warning) + ';' + str(critical) + ';' + '1' + ';' + str(row[1])
output = output + ';No backend currenlty exists for {0:s} database - which might implies that the Application connected to the database might be down or the database currently refuses connections to it '.format(row[0])
status.append(2)
status.append( st.getStatus( int(connect_sum),int(warning) , int(critical), int('0') , int(max_connect) ) )
status.sort( reverse=True )
return str(status[0]) + ' ' + item_name + ' ' + str(perfdata) + ' ' + output
def getNonBlockingIterator(rows,item_name,warning,critical) :
perfdata = '-'
output = ''
for row in rows :
if perfdata == '-' :
perfdata = row[0] + '=' + str(row[5]) + ';' + str(warning[0]) + ';' + str(critical[0])
output = '{0:s} has been locked({1:s}) by {2:s}({3:s}) for {4:d} \n Locking Query : {5:s}'.format(row[0],row[1],row[4],row[2],int(row[6]), row[3] )
elif perfdata != '-' :
perfdata = perfdata + '|' + row[0] + '=' + str(row[5]) + ';' + str(warning[0]) + ';' + str(critical[0])
output = output + ';{0:s} has been locked({1:s}) by {2:s}({3:s}) for {4:d} \n Locking Query : {5:s}'.format(row[0],row[1],row[4],row[2],int(row[6]), row[3] )
status.append( st.getStatus( row[6],int(warning[0]) , int(critical[0]) ) )
status.sort( reverse=True )
return str(status[0]) + ' ' + item_name + ' ' + str(perfdata) + ' ' + output
def getReplicaLags( param=None ) :
item_name = 'POSTGRES_REPLICA_LAG'
status = []
perfdata = '-'
output = ''
if param != None :
warning = int( param['warning'] )
critical = int( param['critical'] )
query = "SELECT pg_current_xlog_location() "
dbname = param.get('dbname')[0]
hosts = param['host']
ports = param['port']
if len(hosts) > len(ports) :
ports = ports * len(hosts)
master = sql.getSQLResult ( {'host': hosts[0] , 'port' : ports[0], 'dbname': dbname, 'user' : param['user'] ,'password' : param['password'] } ,query )
if master[0] == None :
return '2' + ' ' + item_name + ' ' + '-' + ' ' + str(master[1])
counter = 1
for rep_host in hosts[1:] :
query = "SELECT pg_last_xlog_replay_location()"
replica = sql.getSQLResult ( {'host': rep_host , 'port' : ports[counter], 'dbname': dbname \
, 'user' : param['user'] ,'password' : param['password'] } ,query )
if replica[0] == 0 :
wal_lag = hexa.computeMegaByteDiff ( master[1] , replica[1] ) / 16
elif replica[0] == None :
wal_lag = -1
if perfdata == '-' :
perfdata = perf.getPerfStm (rep_host , wal_lag ,warning,str(critical))
output = 'replica on {0:s} lags behind master {1:s} by a total of {2:s} WALs.'\
.format( rep_host , hosts[0] , str(wal_lag) )
elif perfdata != '-' :
perfdata = perfdata + '|' + perf.getPerfStm (rep_host , wal_lag ,warning,str(critical))
output = output + ';\n replica on {0:s} lags behind master {1:s} by a total of {2:s} WALs'\
.format( rep_host , hosts[0] , str(wal_lag) )
if wal_lag != -1 :
status.append( st.getStatus( wal_lag , warning , critical ) )
elif wal_lag == -1 :
status.append(2)
counter += 1
status.sort(reverse=True)
output = output + '\n NOTE: a -1 lag value means the replica is not available !!!'
return str(status[0]) + ' ' + item_name + ' ' + str(perfdata) + ' ' + output
def getBloats( param=None ) :
item_name = 'POSTGRES_'
status = []
perfdata = '-'
output = ''
if param != None :
check = param['check']
item_name = item_name + check.upper()
dbnames = param.get('dbname')
retval = fac.warningAndOrCriticalProvided (param.get('warning'),param.get('critical'))
warning = []
critical = []
if retval != None :
warning = retval.get('warning')
critical = retval.get('critical')
else :
return '2' + ' ' + item_name + ' ' + '-' + ' ' + 'Invalid parameters supplied'
query = getQuery(check)
query = query.format(int(warning[1]),int(warning[0]))
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 + ' ' + results[1]
rows = results[1]
if len(rows) > 0 :
for row in rows :
out_unit = ''
status.append(st.getStatus(row[3] , warning[0] , critical[0]) )
if perfdata == '-' :
perfdata = perf.getPerfStm (row[0],row[3],warning[0],str(critical[0]))
output = '{0:s} has {1:s} {2:s} ({3:s})% worth of bloat'.format(row[0],str(row[3]), warning[2], str(row[2]) )
elif perfdata != '-' :
perfdata = perfdata + '|' + perf.getPerfStm (row[0],row[3],warning[0],str(critical[0]))
output = output + ';\n {0:s} has {1:s} {2:s} ({3:s})% worth of bloat'.format(row[0],str(row[3]), warning[2], str(row[2]) )
if perfdata != '-' :
status.sort( reverse=True )
return str(status[0]) + ' ' + item_name + ' ' + str(perfdata) + ' ' + output
else :
return '0' + ' ' + item_name + ' ' + '-' + ' ' + 'OK'
def getNonBlockingIterator(rows,item_name,warning,critical, status) :
perfdata = '-'
output = ''
for row in rows :
if perfdata == '-' :
perfdata = perf.getPerfStm (row[0],row[6],warning[0],str(critical[0]))
output = '{0:s} has been locked({1:s}) by {2:s}(pid->{3:s},lock_mode->{6:s}) for {4:s} mins \n Locking Query : {5:s}'.format( \
str(row[0]),str(row[1]),str(row[4]),str(row[2]),str(row[6]), str(row[3]), str(row[7]) )
elif perfdata != '-' :
perfdata = perfdata + '|' + perf.getPerfStm (row[0],row[6],warning[0],str(critical[0]))
output = output + ';\n {0:s} has been locked({1:s}) by {2:s}(pid->{3:s},lock_mode->{6:s}) for {4:s} mins \n Locking Query : {5:s}'.format( \
str(row[0]),str(row[1]),str(row[4]),str(row[2]),str(row[6]), str(row[3]),str(row[7]) )
status.append( st.getStatus( row[6],int(warning[0]) , int(critical[0]) ) )
status.sort( reverse=True )
return {'status' : status[0], 'item' : item_name , 'perfdata' : perfdata, 'output' : output }
def getWALs(param=None):
item_name = 'POSTGRES_WALS'
status = []
perfdata = '-'
output = ''
if param != None:
dbname = param.get('dbname')[0]
query = "SELECT count(bar.*) wal_count , \
ceil(( ( 2 + current_setting('checkpoint_completion_target')::double precision)* current_setting('checkpoint_segments')::double precision) + 1) max_wal \
FROM \
( \
SELECT (pg_stat_file('pg_xlog/' || foo.files )).* FROM \
( SELECT pg_ls_dir('pg_xlog') files ) foo \
WHERE (pg_stat_file('pg_xlog/' || foo.files )).isdir IS FALSE \
) bar \
WHERE bar.size >= 16777216"
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 + ' ' + '-' + ' ' + str(results[1])
rows = results[1]
if len(rows) > 0:
warning = fac.getNumberPercentLimits(param.get('warning'),
rows[0][1])
critical = fac.getNumberPercentLimits(param.get('critical'),
rows[0][1])
for row in rows:
if perfdata == '-':
perfdata = perf.getPerfStm('WALS', row[0], warning,
str(critical))
output = '{0:s} WAL file(s) found'.format(str(row[0]))
status.append(st.getStatus(row[0], int(warning),
int(critical)))
status.sort(reverse=True)
return str(status[0]) + ' ' + item_name + ' ' + str(
perfdata) + ' ' + output
else:
return '2' + ' ' + item_name + ' ' + '-' + ' ' + 'No WAL file found, at least One WAL file should be present.'
def getRelationSizes( param=None ) :
item_name = 'POSTGRES_'
status = []
perfdata = '-'
output = 'OK'
warning = []
critical = []
if param != None :
retval = fac.warningAndOrCriticalProvided (param.get('warning'),param.get('critical'))
if retval != None :
warning = retval.get('warning')
critical = retval.get('critical')
else :
return '2' + ' ' + item_name + ' ' + '-' + ' ' + 'Invalid parameters passed !'
item_name = item_name + str(param['check']).upper()
col_name = 'table_name'
query = getQuery ( param['check'],warning[1],warning[0] )
dbnames = param.get('dbname')
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(row[1] , warning[0] , critical[0] ) )
if perfdata == '-' :
perfdata = perf.getPerfStm (row[0],row[1],warning[0],critical[0])
output = '{0:s} is {1:d} {2:s} big'.format(row[0],row[1],warning[2])
elif perfdata != '-' :
perfdata = perfdata + '|' + perf.getPerfStm (row[0],row[1],warning[0],critical[0])
output = output + ';\n {0:s} is {1:d} {2:s} big'.format(row[0],row[1],warning[2])
if perfdata != '-' :
status.sort( reverse=True )
return str(status[0]) + ' ' + item_name + ' ' + str(perfdata) + ' ' + output
else :
return '0' + ' ' + item_name + ' ' + str(perfdata) + ' ' + output
def getReplicaLags( param=None ) :
item_name = 'POSTGRES_REPLICA_LAG'
status = []
perfdata = '-'
output = ''
if param != None :
warning = int( param['warning'] )
critical = int( param['critical'] )
query = "SELECT pg_current_xlog_location() "
master_host = param['host'][0]
master = sql.getSQLResult ( {'host': master_host , 'port' : param['port'][0], 'dbname': param['dbname'], 'user' : param['user'] ,'password' : param['password'] } ,query )
if master[0] == None :
return '2' + ' ' + item_name + ' ' + '-' + ' ' + str(master[1])
counter = 1
for rep_host in param['host'][1:] :
replica = sql.getSQLResult ( {'host': rep_host , 'port' : param['port'][counter], 'dbname': param['dbname'] \
, 'user' : param['user'] ,'password' : param['password'] } ,query )
if replica[0] == 0 :
wal_lag = hexa.computeMegaByteDiff ( master[1] , replica[1] ) / 16
elif replica[0] == None :
wal_lag = -1
if perfdata == '-' :
perfdata = rep_host + '=' + str(wal_lag) + ';' + str(warning) + ';' + str(critical)
output = 'replica on {0:s} lags behind master {1:s} by a total of {2:s} WALs. NOTE: a -1 lag value means the replica is not available !!! '\
.format( rep_host , master_host , str(wal_lag) )
elif perfdata != '-' :
perfdata = perfdata + '|' + rep_host + '=' + str(wal_lag) + ';' + str(warning) + ';' + str(critical)
output = output + ';replica on {0:s} lags behind master {1:s} by a total of {2:s} WALs. NOTE: a -1 lag value means the replica is not available !!!'\
.format( rep_host , master_host , str(wal_lag) )
if wal_lag != -1 :
status.append( st.getStatus( wal_lag , warning , critical ) )
elif wal_lag == -1 :
status.append(2)
counter += 1
status.sort(reverse=True)
return str(status[0]) + ' ' + item_name + ' ' + str(perfdata) + ' ' + output
def getBloats( param=None ) :
item_name = 'POSTGRES_'
status = []
perfdata = '-'
output = ''
if param != None :
check = param['check']
item_name = item_name + check.upper()
warning = fac.getSizeFactor(param['warning'])
critical = fac.getSizeFactor(param['critical'])
query = getQuery(check,warning[1],warning[0])
results = sql.getSQLResult ( {'host': param['host'][0] , 'port' : param['port'][0], 'dbname': param['dbname'], 'user' : param['user'] ,'password' : param['password'] } ,query )
if results[0] == None :
return '2' + ' ' + item_name + ' ' + '-' + ' ' + results[1]
rows = results[1]
if len(rows) > 0 :
for row in rows :
out_unit = ''
if int(row[3]) >= int(critical[0]) :
status.append(2)
out_unit = [row[3] , critical[2] ]
elif int(row[3]) >= int(warning[0]) :
status.append(1)
out_unit = [ row[3], warning[2] ]
if perfdata == '-' :
perfdata = row[0] + '=' + str(row[3]) + ';' + str(warning[0]) + ';' + str(critical[0])
output = '{0:s} has {1:s} {2:s} ({3:s})% worth of bloat'.format(row[0],str(row[3]), out_unit[1], str(row[2]) )
elif perfdata != '-' :
perfdata = perfdata + '|' + row[0] + '=' + str(row[3]) + ';' + str(warning[0]) + ';' + str(critical[0])
output = output + ';{0:s} has {1:s} {2:s} ({3:s})% worth of bloat'.format(row[0],str(row[3]), out_unit[1], str(row[2]) )
status.append( st.getStatus( int(row[3]),int(warning[0]) , int(critical[0]) ) )
status.sort( reverse=True )
return str(status[0]) + ' ' + item_name + ' ' + str(perfdata) + ' ' + output
else :
return '0' + ' ' + item_name + ' ' + '-' + ' ' + 'OK'
def getWALs( param=None ) :
item_name = 'POSTGRES_WALS'
status = []
perfdata = '-'
output = ''
if param != None :
dbname = param.get('dbname')[0]
query = "SELECT count(bar.*) wal_count , \
ceil(( ( 2 + current_setting('checkpoint_completion_target')::double precision)* current_setting('checkpoint_segments')::double precision) + 1) max_wal \
FROM \
( \
SELECT (pg_stat_file('pg_xlog/' || foo.files )).* FROM \
( SELECT pg_ls_dir('pg_xlog') files ) foo \
WHERE (pg_stat_file('pg_xlog/' || foo.files )).isdir IS FALSE \
) bar \
WHERE bar.size >= 16777216"
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 + ' ' + '-' + ' ' + str(results[1])
rows = results[1]
if len(rows) > 0 :
warning = fac.getNumberPercentLimits( param.get('warning'), rows[0][1])
critical = fac.getNumberPercentLimits( param.get('critical'), rows[0][1])
for row in rows :
if perfdata == '-' :
perfdata = perf.getPerfStm ('WALS',row[0],warning,str(critical))
output = '{0:s} WAL file(s) found'.format( str(row[0]) )
status.append( st.getStatus( row[0],int(warning) , int(critical) ) )
status.sort( reverse=True )
return str(status[0]) + ' ' + item_name + ' ' + str(perfdata) + ' ' + output
else :
return '2' + ' ' + item_name + ' ' + '-' + ' ' + 'No WAL file found, at least One WAL file should be present.'
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 getBackends( param=None ) :
item_name ='POSTGRES_BACKENDS'
status = []
perfdata = '-'
output = ''
if param != None : # extra safety check, but absolutely not necessary
output_format = param.get('output_format')
dbnames = param.get('dbname')
exclude = param.get('exclude_db') if param.get('exclude_db') != None else None
exclude_db = ''
if exclude != None :
for exc in exclude :
if exclude_db == '' :
exclude_db = "'" + exc + "'"
else :
exclude_db = exclude_db + ",'" + exc + "'"
query = "SELECT \
dbs.datname, \
( current_setting('max_connections')::int - current_setting('superuser_reserved_connections')::int ) as max_allowed_connect,\
coalesce( nullif((numbackends -1 ),-1), 0 ) as connect_count,\
( coalesce( nullif((numbackends -1 ),-1), 0 ) * 100 ) / current_setting('max_connections')::int as percent_connect \
FROM \
pg_stat_database dbs \
JOIN \
pg_database db on db.datname = dbs.datname \
WHERE \
db.datistemplate IS FALSE AND dbs.datname NOT IN ({0:s})".format(exclude_db)
results = sql.getSQLResult ( {'host': param['host'][0] , 'port' : param['port'][0], 'dbname': dbnames[0], 'user' : param['user'] ,'password' : param['password'] } ,query )
connect_sum = 0
if results[0] == None :
return '2' + ' ' + item_name + ' ' + '-' + ' ' + str(results[1])
rows = results[1]
if len(rows) > 0 :
max_connect = rows[0][1]
warning = fac.getNumberPercentLimits( param['warning'] , max_connect )
critical = fac.getNumberPercentLimits( param['critical'] , max_connect )
for row in rows :
if int(row[2]) != 0 :
if perfdata == '-' :
perfdata = row[0] + '=' + str(row[2]) + ';' + str(warning) + ';' + str(critical) + ';' + '1' + ';' + str(row[1])
output = '{0:s} has {1:d}% of the total connections used'.format(row[0],row[3])
elif perfdata != '-' :
perfdata = perfdata + '|' + row[0] + '=' + str(row[2]) + ';' + str(warning) + ';' + str(critical) + ';' + '1' + ';' + str(row[1])
output = output + '; \n {0:s} has {1:d}% of the total connections used'.format(row[0],row[3])
connect_sum += int(row[2])
else :
if perfdata == '-' :
perfdata = row[0] + '=' + str(row[2]) + ';' + str(warning) + ';' + str(critical) + ';' + '1' + ';' + str(row[1])
output = 'No backend currenlty exists for {0:s} database - which might implies that the Application connected to the database might be down or the database currently refuses connections to it '.format(row[0])
elif perfdata != '-' :
perfdata = perfdata + '|' + row[0] + '=' + str(row[2]) + ';' + str(warning) + ';' + str(critical) + ';' + '1' + ';' + str(row[1])
output = output + '; \n No backend currenlty exists for {0:s} database - which might implies that the Application connected to the database might be down or the database currently refuses connections to it '.format(row[0])
status.append(2)
status.append( st.getStatus( int(connect_sum),int(warning) , int(critical), int('0') , int(max_connect) ) )
status.sort( reverse=True )
if output_format == 'cmk' or output_format == 'check_mk' :
return str(status[0]) + ' ' + item_name + ' ' + str(perfdata) + ' ' + output
elif output_format == 'ngs' or output_format == 'nagios' :
return item_name + ' - ' + output + ' | ' + str(perfdata).replace('|',' \n')
def getRelationSizes(param=None):
item_name = 'POSTGRES_'
status = []
perfdata = '-'
output = 'OK'
warning = []
critical = []
if param != None:
retval = fac.warningAndOrCriticalProvided(param.get('warning'),
param.get('critical'))
if retval != None:
warning = retval.get('warning')
critical = retval.get('critical')
else:
return '2' + ' ' + item_name + ' ' + '-' + ' ' + 'Invalid parameters passed !'
item_name = item_name + str(param['check']).upper()
col_name = 'table_name'
query = getQuery(param['check'], warning[1], warning[0])
dbnames = param.get('dbname')
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(row[1], warning[0],
critical[0]))
if perfdata == '-':
perfdata = perf.getPerfStm(row[0], row[1], warning[0],
critical[0])
output = '{0:s} is {1:d} {2:s} big'.format(
row[0], row[1], warning[2])
elif perfdata != '-':
perfdata = perfdata + '|' + perf.getPerfStm(
row[0], row[1], warning[0], critical[0])
output = output + ';\n {0:s} is {1:d} {2:s} big'.format(
row[0], row[1], warning[2])
if perfdata != '-':
status.sort(reverse=True)
return str(status[0]) + ' ' + item_name + ' ' + str(
perfdata) + ' ' + output
else:
return '0' + ' ' + item_name + ' ' + str(perfdata) + ' ' + output
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'