def query_eachdb(self, sql, exclude=[]): """ execure query on each database of the cluster Params: sql (string): query text exclude (list of strings): database names to exclude Out: { dbname: query_result, ... } """ connect_dict = { 'host': self.config.get('host', None), # none = connect via socket 'user': self.config.get('user', 'postgres'), 'password': self.config.get('password', None), 'database': self.config.get('database', 'postgres') } ret = {} if not self.dbs: self.get_dblist() for db in self.dbs: if db in exclude: continue connect_dict['database'] = db pdb = PgConn(connect_dict, self.logger) ret[db] = pdb.query(sql) return ret
def query_eachdb(self, sql, exclude=None): """ execure query on each database of the cluster Params: sql (string): query text exclude (list of strings): database names to exclude Out: { dbname: query_result, ... } """ connect_dict = { 'host': self.config.get('host', None), # none = connect via socket 'user': self.config.get('user', 'postgres'), 'password': self.config.get('password', None), 'database': self.config.get('database', 'postgres') } ret = {} if not self.dbs: self.get_dblist() for db in self.dbs: # pylint: disable-msg=C0103 if db in exclude: continue connect_dict['database'] = db pdb = PgConn(connect_dict, self.logger) ret[db] = pdb.query(sql) return ret
def _myinit(self): connect_dict = { 'host': self.config.get('host', None), # none = connect via socket 'user': self.config.get('user', 'postgres'), 'password': self.config.get('password', None), 'database': self.config.get('database', 'postgres') } self.dbconn = PgConn(connect_dict, self.logger)
def get_dblist(self): connect_dict = { 'host': self.config.get('host', None), # none = connect via socket 'user': self.config.get('user', 'postgres'), 'password': self.config.get('password', None), 'database': self.config.get('database', 'postgres') } d = PgConn(connect_dict, self.logger) dbs = d.query("SELECT datname FROM pg_database") self.dbs = [x[0] for x in dbs]
def get_dblist(self): """ Get list of all databases Returns: list of strings - names of all databases of postgresql cluster """ connect_dict = { 'host': self.config.get('host', None), # none = connect via socket 'user': self.config.get('user', 'postgres'), 'password': self.config.get('password', None), 'database': self.config.get('database', 'postgres')} db = PgConn(connect_dict, self.logger) # pylint: disable-msg=C0103 dbs = db.query("SELECT datname FROM pg_database") self.dbs = [x[0] for x in dbs]
def get_dblist(self): """ Get list of all databases Returns: list of strings - names of all databases of postgresql cluster """ connect_dict = { 'host': self.config.get('host', None), # none = connect via socket 'user': self.config.get('user', 'postgres'), 'password': self.config.get('password', None), 'database': self.config.get('database', 'postgres') } db = PgConn(connect_dict, self.logger) # pylint: disable-msg=C0103 dbs = db.query("SELECT datname FROM pg_database") self.dbs = [x[0] for x in dbs]
def _myinit(self): connect_dict = { 'host': self.config.get('host', None), # none = connect via socket 'user': self.config.get('user', 'postgres'), 'password': self.config.get('password', None), 'database': self.config.get('database', 'postgres')} self.dbconn = PgConn(connect_dict, self.logger)
class PgDB(ZTCCheck): """ Connection to single database """ name = 'pgsql' dbconn = None OPTPARSE_MIN_NUMBER_OF_ARGS = 1 OPTPARSE_MAX_NUMBER_OF_ARGS = 3 def _myinit(self): connect_dict = { 'host': self.config.get('host', None), # none = connect via socket 'user': self.config.get('user', 'postgres'), 'password': self.config.get('password', None), 'database': self.config.get('database', 'postgres') } self.dbconn = PgConn(connect_dict, self.logger) def _get(self, metric, *args, **kwargs): if metric == 'query': q = args[0] return self.dbconn.query(q) elif metric == 'autovac_freeze': return self.get_autovac_freeze() elif metric == 'ping': return self.get_ping() elif metric == 'tnxage': state = args[0] return self.get_tnx_age(state) elif metric == 'buffers': buf_metric = args[0] return self.get_buffers(buf_metric) elif metric == 'conn': state = args[0] return self.get_conn_nr(state) elif metric == 'dbstat': m = args[0] return self.get_dbstat(m) elif metric == 'fsm': m = args[0] return self.get_fsm(m) elif metric == 'locks': m = args[0] mm = args[1] return self.get_locks(m, mm) elif metric == 'bgwriter': bgw_metric = args[0] return self.get_bgwriter(bgw_metric) elif metric == 'settings': param = args[0] return self.get_setting(param) elif metric == 'wal': m = args[0] if m == 'num': return self.get_wal_num() else: CheckFail('uncknown wal metric: %s' % m) else: raise CheckFail('uncknown metric %s' % metric) def get_fsm(self, metric): """ PostgreSQL freespacemap metrics. Requirements: pg_freespacement, PostgreSQL <= 8.3""" q = pgq.FSM[metric] ret = self.dbconn.query(q)[0][0] return ret def get_buffers(self, metric): """ PostgreSQL buffer metrics: number of clear/dirty/used/total buffers. Requirements: pg_buffercache contrib """ q = pgq.BUFFER[metric] ret = self.dbconn.query(q)[0][0] return ret def get_dbstat(self, m): """ get sum of passed metric from dbstat """ q = "SELECT SUM(%s) FROM pg_stat_database" % m ret = self.dbconn.query(q)[0][0] return ret def get_conn_nr(self, state): """ Get number of connections in given state """ v = self._get_version() if v >= (9, 2, 0): vtag = 'post92' else: vtag = 'pre92' # we don't need to be able to read queries to get total and max if state != 'total': q = pgq.CHECK_INSUFF_PRIV[vtag] insuff_priv = self.dbconn.query(q) if insuff_priv: raise CheckFail( "Insufficient privileges to read queries from pg_stat_activity" ) try: q = pgq.CONN_NUMBER[vtag][state] except KeyError: raise CheckFail("Unknown connection state requested") ret = self.dbconn.query(q)[0][0] return ret def get_tnx_age(self, state): """ Get max age of transactions in given state. Supported states are: 'running', 'idle_tnx', 'prepared' """ v = self._get_version() if v >= (9, 2, 0): vtag = 'post92' else: vtag = 'pre92' if state == 'prepared': q = pgq.TNX_AGE_PREPARED else: q = pgq.CHECK_INSUFF_PRIV[vtag] insuff_priv = self.dbconn.query(q) if insuff_priv: raise CheckFail( "Insufficient privileges to read queries from pg_stat_activity" ) try: q = pgq.TNX_AGE[vtag][state] except KeyError: raise CheckFail("Unknown transaction state requested") # the query always returns something becuase of COALESCE(..., 0), # even if there's no transactions in given state ret = self.dbconn.query(q)[0][0] return abs(ret) def get_ping(self): """get amount of time required to execute trivial query""" st = time.time() try: if self.dbconn.query('SELECT 1'): return time.time() - st else: return 0 except: return 0 def get_autovac_freeze(self): """ Checks how close each database is to the Postgres autovacuum_freeze_max_age setting. This action will only work for databases version 8.2 or higher. The 'age' of the transactions in each database is compared to the autovacuum_freeze_max_age setting (200 million by default) to generate a rounded percentage. Returns: (float) maximum age of transaction from all databases, in % (compared to autovacuum_freeze_max_age) """ max_percent = 0 q = pgq.AUTOVAC_FREEZE ret = self.dbconn.query(q) for (freeze, age, percent, dbname) in ret: # @UnusedVariable if self.debug: self.logger.info("Freeze %% for %s: %s" % (dbname, percent)) max_percent = max(max_percent, percent) return max_percent def get_locks(self, m, mm=None): """ get number of database locks """ if m == 'mode': q = pgq.LOCKS_BY_MODE[mm.lower()] else: q = pgq.LOCKS[m] ret = self.dbconn.query(q)[0][0] return ret def get_wal_num(self): """ get number of wal files in pg_xlog directory """ q = pgq.WAL_NUMBER ret = self.dbconn.query(q)[0][0] return ret def get_bgwriter(self, m): """ Get stats for bgwriter. Currently used metrics are: 'checkpoints_timed', 'checkpoints_req', 'buffers_checkpoint', 'buffers_clean', 'maxwritten_clean', 'buffers_backend', 'buffers_alloc' """ q = "select %s from pg_stat_bgwriter" % m ret = self.dbconn.query(q)[0][0] return ret def _get_version(self): """ Get postgresql version. Result returned as (x,y,z) tuple, each member of which is an integer. Functions returns (0,0,0) if fails to obtain or parse version string. """ dummy_version = (0, 0, 0) q = 'SELECT version()' v_str = self.dbconn.query(q)[0][0] if not v_str: self.logger.warning("Failed to fetch version with query %s" % q) return dummy_version m = re.search(r'PostgreSQL (\d+)\.(\d+)\.(\d+).+', v_str) if not m: self.logger.warning("Failed to parse version string: %s" % v_str) return dummy_version # convert every element of resulting tuple to integer # there's no way it can cause ValueError, becuase search regexp matches # only digits. version = tuple(int(k) for k in m.groups()) return version def get_setting(self, parameter): """ Get arbitrary postgresql user configurable parameter """ q = pgq.GET_SETTING % (parameter) res = self.dbconn.query(q) if res: return res[0][0] raise CheckFail( "The requested parameter %s does not exist in pg_settings" % parameter)
class PgDB(ZTCCheck): """ Connection to single database """ name = 'pgsql' dbconn = None OPTPARSE_MIN_NUMBER_OF_ARGS = 1 OPTPARSE_MAX_NUMBER_OF_ARGS = 3 def _myinit(self): connect_dict = { 'host': self.config.get('host', None), # none = connect via socket 'user': self.config.get('user', 'postgres'), 'password': self.config.get('password', None), 'database': self.config.get('database', 'postgres')} self.dbconn = PgConn(connect_dict, self.logger) def _get(self, metric, *args, **kwargs): if metric == 'query': q = args[0] return self.dbconn.query(q) elif metric == 'autovac_freeze': return self.get_autovac_freeze() elif metric == 'ping': return self.get_ping() elif metric == 'tnxage': state = args[0] return self.get_tnx_age(state) elif metric == 'buffers': buf_metric = args[0] return self.get_buffers(buf_metric) elif metric == 'conn': state = args[0] return self.get_conn_nr(state) elif metric == 'dbstat': m = args[0] return self.get_dbstat(m) elif metric == 'fsm': m = args[0] return self.get_fsm(m) elif metric == 'locks': m = args[0] mm = args[1] return self.get_locks(m, mm) elif metric == 'bgwriter': bgw_metric = args[0] return self.get_bgwriter(bgw_metric) elif metric == 'settings': param = args[0] return self.get_setting(param) elif metric == 'wal': m = args[0] if m == 'num': return self.get_wal_num() else: CheckFail('uncknown wal metric: %s' % m) else: raise CheckFail('uncknown metric %s' % metric) def get_fsm(self, metric): """ PostgreSQL freespacemap metrics. Requirements: pg_freespacement, PostgreSQL <= 8.3""" q = pgq.FSM[metric] ret = self.dbconn.query(q)[0][0] return ret def get_buffers(self, metric): """ PostgreSQL buffer metrics: number of clear/dirty/used/total buffers. Requirements: pg_buffercache contrib """ q = pgq.BUFFER[metric] ret = self.dbconn.query(q)[0][0] return ret def get_dbstat(self, m): """ get sum of passed metric from dbstat """ q = "SELECT SUM(%s) FROM pg_stat_database" % m ret = self.dbconn.query(q)[0][0] return ret def get_conn_nr(self, state): """ Get number of connections in given state """ v = self._get_version() if v >= (9, 2, 0): vtag = 'post92' else: vtag = 'pre92' # we don't need to be able to read queries to get total and max if state!='total': q = pgq.CHECK_INSUFF_PRIV[vtag] insuff_priv = self.dbconn.query(q) if insuff_priv: raise CheckFail( "Insufficient privileges to read queries from pg_stat_activity") try: q = pgq.CONN_NUMBER[vtag][state] except KeyError: raise CheckFail("Unknown connection state requested") ret = self.dbconn.query(q)[0][0] return ret def get_tnx_age(self, state): """ Get max age of transactions in given state. Supported states are: 'running', 'idle_tnx', 'prepared' """ v = self._get_version() if v >= (9, 2, 0): vtag = 'post92' else: vtag = 'pre92' if state == 'prepared': q = pgq.TNX_AGE_PREPARED else: q = pgq.CHECK_INSUFF_PRIV[vtag] insuff_priv = self.dbconn.query(q) if insuff_priv: raise CheckFail( "Insufficient privileges to read queries from pg_stat_activity") try: q = pgq.TNX_AGE[vtag][state] except KeyError: raise CheckFail("Unknown transaction state requested") # the query always returns something becuase of COALESCE(..., 0), # even if there's no transactions in given state ret = self.dbconn.query(q)[0][0] return abs(ret) def get_ping(self): """get amount of time required to execute trivial query""" st = time.time() try: if self.dbconn.query('SELECT 1'): return time.time() - st else: return 0 except: return 0 def get_autovac_freeze(self): """ Checks how close each database is to the Postgres autovacuum_freeze_max_age setting. This action will only work for databases version 8.2 or higher. The 'age' of the transactions in each database is compared to the autovacuum_freeze_max_age setting (200 million by default) to generate a rounded percentage. Returns: (float) maximum age of transaction from all databases, in % (compared to autovacuum_freeze_max_age) """ max_percent = 0 q = pgq.AUTOVAC_FREEZE ret = self.dbconn.query(q) for (freeze, age, percent, dbname) in ret: # @UnusedVariable if self.debug: self.logger.info("Freeze %% for %s: %s" % (dbname, percent)) max_percent = max(max_percent, percent) return max_percent def get_locks(self, m, mm=None): """ get number of database locks """ if m == 'mode': q = pgq.LOCKS_BY_MODE[mm.lower()] else: q = pgq.LOCKS[m] ret = self.dbconn.query(q)[0][0] return ret def get_wal_num(self): """ get number of wal files in pg_xlog directory """ q = pgq.WAL_NUMBER ret = self.dbconn.query(q)[0][0] return ret def get_bgwriter(self, m): """ Get stats for bgwriter. Currently used metrics are: 'checkpoints_timed', 'checkpoints_req', 'buffers_checkpoint', 'buffers_clean', 'maxwritten_clean', 'buffers_backend', 'buffers_alloc' """ q = "select %s from pg_stat_bgwriter" % m ret = self.dbconn.query(q)[0][0] return ret def _get_version(self): """ Get postgresql version. Result returned as (x,y,z) tuple, each member of which is an integer. Functions returns (0,0,0) if fails to obtain or parse version string. """ dummy_version = (0, 0, 0) q = 'SELECT version()' v_str = self.dbconn.query(q)[0][0] if not v_str: self.logger.warning("Failed to fetch version with query %s" % q) return dummy_version m = re.search(r'PostgreSQL (\d+)\.(\d+)\.(\d+).+', v_str) if not m: self.logger.warning("Failed to parse version string: %s" % v_str) return dummy_version # convert every element of resulting tuple to integer # there's no way it can cause ValueError, becuase search regexp matches # only digits. version = tuple(int(k) for k in m.groups()) return version def get_setting(self, parameter): """ Get arbitrary postgresql user configurable parameter """ q = pgq.GET_SETTING % (parameter) res = self.dbconn.query(q) if res: return res[0][0] raise CheckFail("The requested parameter %s does not exist in pg_settings" % parameter)
class PgDB(ZTCCheck): """ Connection to single database """ name= 'pgsql' dbconn = None OPTPARSE_MIN_NUMBER_OF_ARGS = 1 OPTPARSE_MAX_NUMBER_OF_ARGS = 3 def _myinit(self): connect_dict = { 'host': self.config.get('host', None), # none = connect via socket 'user': self.config.get('user', 'postgres'), 'password': self.config.get('password', None), 'database': self.config.get('database', 'postgres') } self.dbconn = PgConn(connect_dict, self.logger) def _get(self, metric, *args, **kwargs): if metric == 'query': q = args[0] return self.dbconn.query(q) elif metric == 'autovac_freeze': return self.get_autovac_freeze() elif metric == 'ping': return self.get_ping() elif metric == 'tnxage': state = args[0] return self.get_tnx_age(state) elif metric == 'buffers': buf_metric = args[0] return self.get_buffers(buf_metric) elif metric == 'conn': state = args[0] return self.get_conn_nr(state) elif metric == 'dbstat': m = args[0] return self.get_dbstat(m) elif metric == 'fsm': m = args[0] return self.get_fsm(m) elif metric == 'locks': m = args[0] mm = args[1] return self.get_locks(m, mm) elif metric == 'wal': m = args[0] if m == 'num': return self.get_wal_num() else: CheckFail('uncknown wal metric: %s' % m) else: raise CheckFail('uncknown metric %s' % metric) def get_fsm(self, metric): """ PostgreSQL freespacemap metrics. Requirements: pg_freespacement, PostgreSQL <= 8.3""" q = pgq.FSM[metric] ret = self.dbconn.query(q)[0][0] return ret def get_buffers(self, metric): """ PostgreSQL buffer metrics: number of clear/dirty/used/total buffers. Requirements: pg_buffercache contrib """ q = pgq.BUFFER[metric] ret = self.dbconn.query(q)[0][0] return ret def get_dbstat(self, m): """ get sum of passed metric from dbstat """ q = "SELECT SUM(%s) FROM pg_stat_database" % m ret = self.dbconn.query(q)[0][0] return ret def get_conn_nr(self, state): """ Get number of connections in given state """ q = pgq.CONN_NUMBER[state] ret = self.dbconn.query(q)[0][0] return ret def get_tnx_age(self, state): """ Get max age of transactions in given state. Supported states are: 'running', 'idle_tnx' """ if state == 'idle_tnx': q = pgq.TNX_AGE_IDLE_TNX elif state == 'running': q = pgq.TNX_AGE_RUNNING else: raise CheckFail("uncknown transaction state requested") ret = self.dbconn.query(q) if ret: ret = ret[0][0] return abs(ret) else: # no rows returned => no transactions in given state return 0 def get_ping(self): st = time.time() try: if self.dbconn.query('SELECT 1'): return time.time() - st else: return 0 except: return 0 def get_autovac_freeze(self): """ Checks how close each database is to the Postgres autovacuum_freeze_max_age setting. This action will only work for databases version 8.2 or higher. The 'age' of the transactions in each database is compared to the autovacuum_freeze_max_age setting (200 million by default) to generate a rounded percentage. Returns: (float) maximum age of transaction from all databases, in % (compared to autovacuum_freeze_max_age) """ max_percent = 0 q = pgq.AUTOVAC_FREEZE ret = self.dbconn.query(q) for (freeze, age, percent, dbname) in ret: if self.debug: self.logger.info("Freeze %% for %s: %s" % (dbname, percent)) max_percent = max(max_percent, percent) return max_percent def get_locks(self, m, mm=None): """ get number of database locks """ if m == 'mode': q = pgq.LOCKS_BY_MODE[mm.lower()] else: q = pgq.LOCKS[m] ret = self.dbconn.query(q)[0][0] return ret def get_wal_num(self): """ get number of wal files in pg_xlog directory """ q = pgq.WAL_NUMBER ret = self.dbconn.query(q)[0][0] return ret