cookbooks/postgresql/templates/default/postgres_queries.yml.erb

   1 pg_replication:
   2   query: "SELECT EXTRACT(EPOCH FROM (now() - pg_last_xact_replay_timestamp())) AS lag_seconds"
   3   master: true
   4   metrics:
   5     - lag_seconds:
   6         usage: "GAUGE"
   7         description: "Replication lag behind master in seconds"
   8
   9 pg_postmaster:
  10   query: "SELECT pg_postmaster_start_time as start_time_seconds from pg_postmaster_start_time()"
  11   master: true
  12   metrics:
  13     - start_time_seconds:
  14         usage: "GAUGE"
  15         description: "Time at which postmaster started"
  16
  17 pg_stat_user_tables:
  18   query: "SELECT current_database() datname, schemaname, relname, seq_scan, seq_tup_read, idx_scan, idx_tup_fetch, n_tup_ins, n_tup_upd, n_tup_del, n_tup_hot_upd, n_live_tup, n_dead_tup, n_mod_since_analyze, COALESCE(last_vacuum, '1970-01-01Z'), COALESCE(last_vacuum, '1970-01-01Z') as last_vacuum, COALESCE(last_autovacuum, '1970-01-01Z') as last_autovacuum, COALESCE(last_analyze, '1970-01-01Z') as last_analyze, COALESCE(last_autoanalyze, '1970-01-01Z') as last_autoanalyze, vacuum_count, autovacuum_count, analyze_count, autoanalyze_count FROM pg_stat_user_tables"
  19   metrics:
  20     - datname:
  21         usage: "LABEL"
  22         description: "Name of current database"
  23     - schemaname:
  24         usage: "LABEL"
  25         description: "Name of the schema that this table is in"
  26     - relname:
  27         usage: "LABEL"
  28         description: "Name of this table"
  29     - seq_scan:
  30         usage: "COUNTER"
  31         description: "Number of sequential scans initiated on this table"
  32     - seq_tup_read:
  33         usage: "COUNTER"
  34         description: "Number of live rows fetched by sequential scans"
  35     - idx_scan:
  36         usage: "COUNTER"
  37         description: "Number of index scans initiated on this table"
  38     - idx_tup_fetch:
  39         usage: "COUNTER"
  40         description: "Number of live rows fetched by index scans"
  41     - n_tup_ins:
  42         usage: "COUNTER"
  43         description: "Number of rows inserted"
  44     - n_tup_upd:
  45         usage: "COUNTER"
  46         description: "Number of rows updated"
  47     - n_tup_del:
  48         usage: "COUNTER"
  49         description: "Number of rows deleted"
  50     - n_tup_hot_upd:
  51         usage: "COUNTER"
  52         description: "Number of rows HOT updated (i.e., with no separate index update required)"
  53     - n_live_tup:
  54         usage: "GAUGE"
  55         description: "Estimated number of live rows"
  56     - n_dead_tup:
  57         usage: "GAUGE"
  58         description: "Estimated number of dead rows"
  59     - n_mod_since_analyze:
  60         usage: "GAUGE"
  61         description: "Estimated number of rows changed since last analyze"
  62     - last_vacuum:
  63         usage: "GAUGE"
  64         description: "Last time at which this table was manually vacuumed (not counting VACUUM FULL)"
  65     - last_autovacuum:
  66         usage: "GAUGE"
  67         description: "Last time at which this table was vacuumed by the autovacuum daemon"
  68     - last_analyze:
  69         usage: "GAUGE"
  70         description: "Last time at which this table was manually analyzed"
  71     - last_autoanalyze:
  72         usage: "GAUGE"
  73         description: "Last time at which this table was analyzed by the autovacuum daemon"
  74     - vacuum_count:
  75         usage: "COUNTER"
  76         description: "Number of times this table has been manually vacuumed (not counting VACUUM FULL)"
  77     - autovacuum_count:
  78         usage: "COUNTER"
  79         description: "Number of times this table has been vacuumed by the autovacuum daemon"
  80     - analyze_count:
  81         usage: "COUNTER"
  82         description: "Number of times this table has been manually analyzed"
  83     - autoanalyze_count:
  84         usage: "COUNTER"
  85         description: "Number of times this table has been analyzed by the autovacuum daemon"
  86
  87 pg_statio_user_tables:
  88   query: "SELECT current_database() datname, schemaname, relname, heap_blks_read, heap_blks_hit, idx_blks_read, idx_blks_hit, toast_blks_read, toast_blks_hit, tidx_blks_read, tidx_blks_hit FROM pg_statio_user_tables"
  89   metrics:
  90     - datname:
  91         usage: "LABEL"
  92         description: "Name of current database"
  93     - schemaname:
  94         usage: "LABEL"
  95         description: "Name of the schema that this table is in"
  96     - relname:
  97         usage: "LABEL"
  98         description: "Name of this table"
  99     - heap_blks_read:
 100         usage: "COUNTER"
 101         description: "Number of disk blocks read from this table"
 102     - heap_blks_hit:
 103         usage: "COUNTER"
 104         description: "Number of buffer hits in this table"
 105     - idx_blks_read:
 106         usage: "COUNTER"
 107         description: "Number of disk blocks read from all indexes on this table"
 108     - idx_blks_hit:
 109         usage: "COUNTER"
 110         description: "Number of buffer hits in all indexes on this table"
 111     - toast_blks_read:
 112         usage: "COUNTER"
 113         description: "Number of disk blocks read from this table's TOAST table (if any)"
 114     - toast_blks_hit:
 115         usage: "COUNTER"
 116         description: "Number of buffer hits in this table's TOAST table (if any)"
 117     - tidx_blks_read:
 118         usage: "COUNTER"
 119         description: "Number of disk blocks read from this table's TOAST table indexes (if any)"
 120     - tidx_blks_hit:
 121         usage: "COUNTER"
 122         description: "Number of buffer hits in this table's TOAST table indexes (if any)"
 123
 124 pg_database:
 125   query: "SELECT pg_database.oid AS datid, pg_database.datname, pg_database_size(pg_database.datname) AS size_bytes FROM pg_database"
 126   master: true
 127   cache_seconds: 30
 128   metrics:
 129     - datid:
 130         usage: "LABEL"
 131         description: "ID of the database"
 132     - datname:
 133         usage: "LABEL"
 134         description: "Name of the database"
 135     - size_bytes:
 136         usage: "GAUGE"
 137         description: "Disk space used by the database"