python.d/postgres.chart.py

   1 # -*- coding: utf-8 -*-
   2
   3 import psycopg2
   4 from base import SimpleService
   5 from psycopg2.extras import DictCursor
   6
   7 # default module values
   8 update_every = 1
   9 priority = 90000
  10 retries = 60
  11
  12 # Default Config options.
  13 # {
  14 #    'database': None,
  15 #    'user': 'postgres',
  16 #    'password': None,
  17 #    'host': 'localhost',
  18 #    'port': 5432
  19 # }
  20
  21 CHARTS = {
  22     "Tuples": {
  23         'options': ["tuples", "PostgreSQL tuple access", "Tuples / sec", "tuples", "postgres.tuples", "line"],
  24         'lines': [
  25             ["tup_inserted", "tup_inserted", "incremental", 1, 1],
  26             ["tup_inserted", "tup_inserted", "incremental", 1, 1],
  27             ["tup_fetched", "tup_fetched", "incremental", 1, 1],
  28             ["tup_updated", "tup_updated", "incremental", 1, 1],
  29             ["tup_deleted", "tup_deleted", "incremental", 1, 1],
  30         ]},
  31     "Transactions": {
  32         'options': ["transactions", "Transactions", "transactions / sec", "transactions", "postgres.transactions", "line"],
  33         'lines': [
  34             ["xact_commit", "xact_commit", "incremental", 1, 1],
  35             ["xact_rollback", "xact_rollback", "incremental", 1, 1],
  36         ]},
  37     "BlockAccess": {
  38         'options': ["block_access", "block_access", "Block / sec ", "block_access", "postgres.block_access", "line"],
  39         'lines': [
  40             ["blks_read", "blks_read", "incremental", 1, 1],
  41             ["blks_hit", "blks_hit", "incremental", 1, 1],
  42         ]},
  43     "Checkpoints": {
  44         'options': ["checkpoints", "Checkpoints", "Checkpoints", "checkpoints", "postgres.checkpoints", "line"],
  45         'lines': [
  46             ["bg_checkpoint_time", "bg_checkpoint_time", "absolute", 1, 1],
  47             ["bg_checkpoint_requested", "bg_checkpoint_requested", "absolute", 1, 1],
  48         ]},
  49     "Buffers": {
  50         'options': ["buffers", "buffers", "Buffer/ sec", "buffers", "postgres.buffers", "line"],
  51         'lines': [
  52             ["buffers_written", "buffers_written", "incremental", 1, 1],
  53             ["buffers_allocated", "buffers_allocated", "incremental", 1, 1],
  54         ]},
  55 }
  56 ORDER = ["Tuples", "Transactions", "BlockAccess", "Checkpoints", "Buffers"]
  57
  58
  59 class Service(SimpleService):
  60     def __init__(self, configuration=None, name=None):
  61         super(self.__class__, self).__init__(configuration=configuration, name=name)
  62         self.order = ORDER
  63         self.definitions = CHARTS
  64         self.configuration = configuration
  65         self.connection = None
  66
  67     def connect(self):
  68         params = dict(user='postgres',
  69                       database='postgres',
  70                       password=None,
  71                       host=None,
  72                       port=5432)
  73         params.update(self.configuration)
  74         if self.connection is None:
  75             self.connection = psycopg2.connect(**params)
  76             self.connection.set_session(readonly=True)
  77
  78     def check(self):
  79         try:
  80             self.connect()
  81             return True
  82         except Exception as e:
  83             self.error(e)
  84             return False
  85
  86     def _get_data(self):
  87         cursor = self.connection.cursor(cursor_factory=DictCursor)
  88         cursor.execute("""
  89             SELECT
  90               pg_stat_database.*,
  91               pg_stat_get_bgwriter_timed_checkpoints()     AS bg_checkpoint_time,
  92               pg_stat_get_bgwriter_requested_checkpoints() AS bg_checkpoint_requested,
  93               pg_stat_get_buf_written_backend()            AS buffers_written,
  94               pg_stat_get_buf_alloc()                      AS buffers_allocated
  95             FROM pg_stat_database
  96             WHERE datname = %(database)s
  97         """, self.configuration)
  98         graph_data = dict(cursor.fetchone())
  99         self.connection.commit()
 100         cursor.close()
 101         return graph_data