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 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 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 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 getBlockingIterator(rows,item_name,findText, status,warning,critical) :
perfdata = '-'
output = ''
for row in rows :
if perfdata == '-' :
perfdata = perf.getPerfStm ('Blocking_query',row[6],warning[0],str(critical[0]))
output = '{0:s}(pid->{1:s}) has been blocked({2:s}) by {3:s}(pid->{4:s}) for {5:s} mins \n Blocked Query : {6:s} \
\n Blocking Query : {7:s} \n Waiting time : {8:s}'.format( str(row[8]) , str(row[7]) , str(row[2]) , str(row[4]), \
str(row[5]), str(row[6]), str(row[0]), str(row[1]), str(row[9]) )
elif perfdata != '-' :
perfdata = perfdata + '|' + perf.getPerfStm ('Blocking_query',row[6],1,'0')
output = output + '; \n {0:s}(pid->{1:s}) has been blocked({2:s}) by {3:s}(pid->{4:s}) for {5:s} mins \n Blocked Query : {6:s} \
\n Blocking Query : {7:s} \n Waiting time : {8:s}'.format( str(row[8]) , str(row[7]) , str(row[2]) , str(row[4]), \
str(row[5]), str(row[6]), str(row[0]), str(row[1]), str(row[9]) )
return {'status' : '2', '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 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 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'