ganglia customized module : postgresql module

# Create your queries here. Keys whose names match those defined in the default
# set are overridden. Any additional key-value pairs (i.e. query) will not be
# added to the Ganglia metric definition but can be useful for data purposes.
metric_defs = {
    "pg_backends_waiting": {
        "description": "Number of postgres backends that are waiting",
        "units": "backends",
        "query": "SELECT count(*) AS backends_waiting FROM " + \
            "pg_stat_activity WHERE waiting = 't';"
    },
    "pg_database_size": {
        "description": "Total size of all databases in bytes",
        "value_type": "double",
        "format": "%.0f",
        "units": "bytes",
        "query": "SELECT sum(pg_database_size(d.oid)) AS " + \
            "size_database FROM pg_database d ORDER BY 1 DESC;"
    },
    "pg_idx_blks_read": {
        "description": "Total index blocks read",
        "slope": "positive",
        "units": "blocks",
        "query": "SELECT sum(idx_blks_read) AS idx_blks_read " + \
            "FROM pg_statio_all_indexes;"
    },
    "pg_idx_blks_hit": {
        "description": "Total index blocks hit",
        "slope": "positive",
        "units": "blocks",
        "query": "SELECT sum(idx_blks_hit) AS idx_blks_hit " + \
            "FROM pg_statio_all_indexes;"
    },
    "pg_locks": {
        "description": "Number of locks held",
        "units": "locks",
        "query": "SELECT count(*) FROM pg_locks;"
    },
    "pg_query_time_idle_in_txn": {
        "description": 'Age of longest _idle in transaction_ transaction',
        "units": "seconds",
        "query": "SELECT COALESCE(max(COALESCE(ROUND(EXTRACT(epoch " + \
            "FROM now()-query_start)),0)),0) AS " + \
            "query_time_idle_in_txn FROM pg_stat_activity " + \
            "WHERE current_query = '% in transaction';"
    },
    "pg_max_idle_txn_time": {
        "description": "Age of longest idle transaction",
        "units": "seconds",
        "query": "SELECT COALESCE(max(COALESCE(ROUND(EXTRACT(epoch " + \
            "FROM now()-query_start)),0)),0) as query_time_max FROM " + \
             "pg_stat_activity WHERE current_query <> '<IDLE>';"
    },
    "pg_txn_time_max": {
        "description": "Age of longest transaction",
        "units": "seconds",
        "query": "SELECT max(COALESCE(ROUND(EXTRACT(epoch " + \
            "FROM now()-xact_start)),0)) as txn_time_max " + \
            "FROM pg_stat_activity WHERE xact_start IS NOT NULL;"
    },
    "pg_connections": {
        "description": "Number of connections",
        "units": "connctions",
        "query": "SELECT sum(numbackends) FROM pg_stat_database;"
    },
    "pg_wal_files": {
        "description": "number of wal files in pg_xlog directory",
        "units": "# wal files",
        "query": "SELECT count(*) AS wal_files FROM " + \
            "pg_ls_dir('pg_xlog') WHERE pg_ls_dir ~ E'^[0-9A-F]{24}$';"
    },
    "pg_xact_commit": {
        "description": "Transactions committed",
        "slope": "positive",
        "units": "transactions",
        "query": "SELECT sum(xact_commit) as xact_commit FROM " + \
            "pg_stat_database;",
    },
    "pg_xact_rollback": {
        "description": "Transactions rolled back",
        "slope": "positive",
        "units": "transactions",
        "query": "SELECT sum(xact_rollback) as xact_rollback FROM " + \
            "pg_stat_database;",
    },
    "pg_blks_read": {
        "description": "Blocks read",
        "slope": "positive",
        "units": "blocks",
        "query": "SELECT sum(blks_read) as blks_read FROM " + \
            "pg_stat_database;",
    },
    "pg_blks_hit": {
        "description": "Blocks hit",
        "slope": "positive",
        "units": "blocks",
        "query": "SELECT sum(blks_hit) as blks_hit FROM " + \
            "pg_stat_database;",
    },
    "pg_tup_returned": {
        "description": "Tuples returned",
        "slope": "positive",
        "units": "tuples",
        "query": "SELECT sum(tup_returned) as tup_returned FROM " + \
            "pg_stat_database;",
    },
    "pg_tup_fetched": {
        "description": "Tuples fetched",
        "slope"       : "positive",
        "units": "tuples",
        "query": "SELECT sum(tup_fetched) as tup_fetched FROM " + \
            "pg_stat_database;",
    },
    "pg_tup_inserted": {
        "description": "Tuples inserted",
        "slope": "positive",
        "units": "tuples",
        "query": "SELECT sum(tup_inserted) as tup_inserted FROM " + \
            "pg_stat_database;",
    },
    "pg_tup_updated": {
        "description": "Tuples updated",
        "slope": "positive",
        "units": "tuples",
        "query": "SELECT sum(tup_updated) as tup_updated FROM " + \
            "pg_stat_database;",
    },
    "pg_tup_deleted": {
        "description": "Tuples deleted",
        "slope": "positive",
        "units": "tuples",
        "query": "SELECT sum(tup_deleted) as tup_deleted FROM " + \
            "pg_stat_database;",
    },
    "pg_heap_blks_read": {
        "description": "Heap blocks read",
        "slope": "positive",
        "units": "blocks",
        "query": "SELECT sum(heap_blks_read) as heap_blks_read FROM " + \
            "pg_statio_all_tables;",
    },
    "pg_heap_blks_hit": {
        "description": "Heap blocks hit",
        "slope": "positive",
        "units": "blocks",
        "query": "SELECT sum(heap_blks_hit) as heap_blks_hit FROM " + \
            "pg_statio_all_tables;",
    },
    "pg_idx_blks_read_tbl": {
        "description": "Index blocks read",
        "slope": "positive",
        "units": "blocks",
        "query": "SELECT sum(idx_blks_read) as idx_blks_read_tbl FROM " + \
            "pg_statio_all_tables;",
    },
    "pg_idx_blks_hit_tbl": {
        "description": "Index blocks hit",
        "slope": "positive",
        "units": "blocks",
        "query": "SELECT sum(idx_blks_hit) as idx_blks_hit_tbl FROM " + \
            "pg_statio_all_tables;",
    },
    "pg_toast_blks_read": {
        "description": "Toast blocks read",
        "slope": "positive",
        "units": "blocks",
        "query": "SELECT sum(toast_blks_read) as toast_blks_read FROM " + \
            "pg_statio_all_tables;",
    },
    "pg_toast_blks_hit": {
        "description": "Toast blocks hit",
        "slope": "positive",
        "units": "blocks",
        "query": "SELECT sum(toast_blks_hit) as toast_blks_hit FROM " + \
            "pg_statio_all_tables;",
    },
    "pg_tidx_blks_read": {
        "description": "Toast index blocks read",
        "slope": "positive",
        "units": "blocks",
        "query": "SELECT sum(tidx_blks_read) as tidx_blks_read FROM " + \
            "pg_statio_all_tables;",
    },
    "pg_tidx_blks_hit": {
        "description": "Toast index blocks hit",
        "slope": "positive",
        "units": "blocks",
        "query": "SELECT sum(tidx_blks_hit) as tidx_blks_hit FROM " + \
            "pg_statio_all_tables;",
    },
}