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     "BlockTime": {
  44         'options': ["block_time", "block_time", "milliseconds ", "block_time", "postgres.block_time", "line"],
  45         'lines': [
  46             ["blk_read_time", "blk_read_time", "incremental", 1, 1],
  47             ["blk_write_time", "blk_write_time", "incremental", 1, 1],
  48         ]},
  49     "Checkpoints": {
  50         'options': ["checkpoints", "Checkpoints", "Checkpoints", "checkpoints", "postgres.checkpoints", "line"],
  51         'lines': [
  52             ["bg_checkpoint_time", "bg_checkpoint_time", "absolute", 1, 1],
  53             ["bg_checkpoint_requested", "bg_checkpoint_requested", "absolute", 1, 1],
  54         ]},
  55     "Buffers": {
  56         'options': ["buffers", "buffers", "Buffer/ sec", "buffers", "postgres.buffers", "line"],
  57         'lines': [
  58             ["buffers_written", "buffers_written", "incremental", 1, 1],
  59             ["buffers_allocated", "buffers_allocated", "incremental", 1, 1],
  60         ]},
  61 }
  62 ORDER = ["Tuples", "Transactions", "BlockAccess", "BlockTime", "Checkpoints", "Buffers"]
  63
  64
  65 class Service(SimpleService):
  66     def __init__(self, configuration=None, name=None):
  67         super(self.__class__, self).__init__(configuration=configuration, name=name)
  68         self.order = ORDER
  69         self.definitions = CHARTS
  70         self.configuration = configuration
  71         self.connection = None
  72
  73     def connect(self):
  74         params = dict(user='postgres',
  75                       database='postgres',
  76                       password=None,
  77                       host='localhost',
  78                       port=5432)
  79         params.update(self.configuration)
  80         if self.connection is None:
  81             self.connection = psycopg2.connect(**params)
  82             self.connection.set_session(readonly=True)
  83
  84     def check(self):
  85         try:
  86             self.connect()
  87             return True
  88         except Exception as e:
  89             self.error(e)
  90             return False
  91
  92     def _get_data(self):
  93         cursor = self.connection.cursor(cursor_factory=DictCursor)
  94         cursor.execute("""
  95             SELECT
  96               pg_stat_database.*,
  97               pg_stat_get_bgwriter_timed_checkpoints()     AS bg_checkpoint_time,
  98               pg_stat_get_bgwriter_requested_checkpoints() AS bg_checkpoint_requested,
  99               pg_stat_get_buf_written_backend()            AS buffers_written,
 100               pg_stat_get_buf_alloc()                      AS buffers_allocated
 101             FROM pg_stat_database
 102             WHERE datname = %(database)s
 103         """, self.configuration)
 104         graph_data = dict(cursor.fetchone())
 105         self.connection.commit()
 106         cursor.close()
 107         return graph_data