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 ORDER = ["Tuples", "Scans", "BGWriter"]
  22 CHARTS = {
  23     "Tuples": {
  24         'options': ["tuples", "PostgreSQL tuple access", "Tuples / sec", "tuples", "postgres.tuples", "line"],
  25         'lines': [
  26             ["inserted", "inserted", "incremental", 1, 1],
  27             ["seqread", "seqread", "incremental", 1, 1],
  28             ["hotupdated", "hotupdated", "incremental", 1, 1],
  29             ["deleted", "deleted", "incremental", 1, 1],
  30             ["updated", "updated", "incremental", 1, 1],
  31             ["idxfetch", "idxfetch", "incremental", 1, 1],
  32         ]},
  33     "Scans": {
  34         'options': ["scans", "PostgreSQL scan types", "Scans / sec", "scans", "postgres.scans", "line"],
  35         'lines': [
  36             ["sequential", "sequential", "incremental", 1, 1],
  37             ["index", "index", "incremental", 1, 1],
  38         ]},
  39     "BGWriter": {
  40         'options': ["bgwriter", "BG Writer Activity", "Buffers / sec", "bgwriter", "postgres.bgwriter", "line"],
  41         'lines': [
  42             ["buffers_alloc", "buffers_alloc", "incremental", 1, 1],
  43             ["buffers_clean", "buffers_clean", "incremental", 1, 1],
  44             ["buffers_checkpoint", "buffers_checkpoint", "incremental", 1, 1],
  45             ["buffers_backend", "buffers_backend", "incremental", 1, 1],
  46         ]}
  47 }
  48
  49
  50 class Service(SimpleService):
  51     def __init__(self, configuration=None, name=None):
  52         super(self.__class__, self).__init__(configuration=configuration, name=name)
  53         self.order = ORDER
  54         self.definitions = CHARTS
  55         self.error(str(configuration))
  56         self.configuration = configuration
  57         self.connection = None
  58
  59     def connect(self):
  60         params = dict(user='postgres',
  61                       database=None,
  62                       password=None,
  63                       host='localhost',
  64                       port=5432)
  65         params.update(self.configuration)
  66         if self.connection is None:
  67             self.connection = psycopg2.connect(**params)
  68
  69     def check(self):
  70         try:
  71             self.connect()
  72             return True
  73         except Exception as e:
  74             self.error(e)
  75             return False
  76
  77     def _get_data(self):
  78         cursor = self.connection.cursor(cursor_factory=DictCursor)
  79         cursor.execute("""
  80             SELECT
  81                     -- Tuples
  82                     COALESCE(sum(seq_tup_read),0) AS seqread,
  83                     COALESCE(sum(idx_tup_fetch),0) AS idxfetch,
  84                     COALESCE(sum(n_tup_ins),0) AS inserted,
  85                     COALESCE(sum(n_tup_upd),0) AS updated,
  86                     COALESCE(sum(n_tup_del),0) AS deleted,
  87                     COALESCE(sum(n_tup_hot_upd),0) AS hotupdated,
  88
  89                     -- Scans
  90                     COALESCE(sum(seq_scan),0) AS sequential,
  91                     COALESCE(sum(idx_scan),0) AS index
  92             FROM pg_stat_user_tables
  93         """)
  94         graph_data = {k: float(v) for k, v in cursor.fetchone().items()}
  95
  96         cursor.execute("""
  97             SELECT
  98               buffers_checkpoint,
  99               buffers_clean,
 100               buffers_backend,
 101               buffers_alloc
 102             FROM
 103               pg_stat_bgwriter
 104         """)
 105         graph_data.update(dict(cursor.fetchone()))
 106
 107         self.connection.commit()
 108         cursor.close()
 109         return graph_data