Example #1
0
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
Example #2
0
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
Example #3
0
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
Example #4
0
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
Example #5
0
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'
Example #6
0
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 }
Example #7
0
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.'
Example #8
0
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
Example #9
0
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
Example #10
0
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'
Example #11
0
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.'
Example #12
0
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'
Example #13
0
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')
Example #14
0
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
Example #15
0
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'