queries92.yaml

pg_table:
  query: "SELECT c.oid,nspname AS table_schema, relname AS TABLE_NAME, c.reltuples AS row_estimate , pg_total_relation_size(c.oid) AS total_bytes , pg_indexes_size(c.oid) AS index_bytes , pg_total_relation_size(reltoastrelid) AS toast_bytes FROM pg_class c LEFT JOIN pg_namespace n ON n.oid = c.relnamespace WHERE relkind = 'r'"
  metrics:
    - oid:
        usage: "LABEL"
        description: "pg_class oid"
    - table_schema:
        usage: "LABEL"
        description: "table schema"
    - table_name:
        usage: "LABEL"
        description: "table name"
    - row_estimate:
        usage: "GAUGE"
        description: "row count estimate"
    - total_bytes:
        usage: "GAUGE"
        description: "total table bytes"
    - index_bytes:
        usage: "GAUGE"
        description: "table index bytes"
    - toast_bytes:
        usage: "GAUGE"
        description: "table toast bytes"

pg_replication:
  query: "SELECT EXTRACT(EPOCH FROM (now() - pg_last_xact_replay_timestamp())) as lag"
  metrics:
    - lag:
        usage: "GAUGE"
        description: "Replication last transaction replay behind master in seconds"


pg_stat_replication:
  query: "SELECT pid, application_name, client_addr, state FROM pg_stat_replication"
  metrics:
    - application_name:
        usage: "LABEL"
        description: "Replica recovery.conf application_name"
    - client_addr:
        usage: "LABEL"
        description: "Replica client database IP address"
    - state:
        usage: "LABEL"
        description: "Replica client streaming state"
    - pid:
        usage: "GAUGE"
        description: "Replication pid"

#pg_stat_replication_location:
#  query: "SELECT *, (case pg_is_in_recovery() when 't' then null else pg_current_xlog_location() end) AS pg_current_xlog_location, (case pg_is_in_recovery() when 't' then null else pg_xlog_location_diff(pg_current_xlog_location(), replay_location)::float end) AS pg_xlog_location_diff FROM pg_stat_replication"

pg_replication_replay_location:
  query: "SELECT application_name, client_addr, pg_xlog_location_diff(pg_stat_replication.sent_location, pg_stat_replication.replay_location) AS lag FROM pg_stat_replication"
  metrics:
    - application_name:
        usage: "LABEL"
        description: "Replica recovery.conf application_name"
    - client_addr:
        usage: "LABEL"
        description: "Replica client database IP address"
    - lag:
        usage: "GAUGE"
        description: "Replication lag behind master in bytes"

pg_postmaster:
  query: "SELECT pg_postmaster_start_time as start_time_seconds from pg_postmaster_start_time()"
  metrics:
    - start_time_seconds:
        usage: "GAUGE"
        description: "Time at which postmaster started"

pg_stat_user_tables:
  query: "SELECT 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, last_vacuum, last_autovacuum, last_analyze, last_autoanalyze, vacuum_count, autovacuum_count, analyze_count, autoanalyze_count FROM pg_stat_user_tables"
  metrics:
    - schemaname:
        usage: "LABEL"
        description: "Name of the schema that this table is in"
    - relname:
        usage: "LABEL"
        description: "Name of this table"
    - seq_scan:
        usage: "COUNTER"
        description: "Number of sequential scans initiated on this table"
    - seq_tup_read:
        usage: "COUNTER"
        description: "Number of live rows fetched by sequential scans"
    - idx_scan:
        usage: "COUNTER"
        description: "Number of index scans initiated on this table"
    - idx_tup_fetch:
        usage: "COUNTER"
        description: "Number of live rows fetched by index scans"
    - n_tup_ins:
        usage: "COUNTER"
        description: "Number of rows inserted"
    - n_tup_upd:
        usage: "COUNTER"
        description: "Number of rows updated"
    - n_tup_del:
        usage: "COUNTER"
        description: "Number of rows deleted"
    - n_tup_hot_upd:
        usage: "COUNTER"
        description: "Number of rows HOT updated (i.e., with no separate index update required)"
    - n_live_tup:
        usage: "GAUGE"
        description: "Estimated number of live rows"
    - n_dead_tup:
        usage: "GAUGE"
        description: "Estimated number of dead rows"
    - last_vacuum:
        usage: "GAUGE"
        description: "Last time at which this table was manually vacuumed (not counting VACUUM FULL)"
    - last_autovacuum:
        usage: "GAUGE"
        description: "Last time at which this table was vacuumed by the autovacuum daemon"
    - last_analyze:
        usage: "GAUGE"
        description: "Last time at which this table was manually analyzed"
    - last_autoanalyze:
        usage: "GAUGE"
        description: "Last time at which this table was analyzed by the autovacuum daemon"
    - vacuum_count:
        usage: "COUNTER"
        description: "Number of times this table has been manually vacuumed (not counting VACUUM FULL)"
    - autovacuum_count:
        usage: "COUNTER"
        description: "Number of times this table has been vacuumed by the autovacuum daemon"
    - analyze_count:
        usage: "COUNTER"
        description: "Number of times this table has been manually analyzed"
    - autoanalyze_count:
        usage: "COUNTER"
        description: "Number of times this table has been analyzed by the autovacuum daemon"

pg_statio_user_tables:
  query: "SELECT 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"
  metrics:
    - schemaname:
        usage: "LABEL"
        description: "Name of the schema that this table is in"
    - relname:
        usage: "LABEL"
        description: "Name of this table"
    - heap_blks_read:
        usage: "COUNTER"
        description: "Number of disk blocks read from this table"
    - heap_blks_hit:
        usage: "COUNTER"
        description: "Number of buffer hits in this table"
    - idx_blks_read:
        usage: "COUNTER"
        description: "Number of disk blocks read from all indexes on this table"
    - idx_blks_hit:
        usage: "COUNTER"
        description: "Number of buffer hits in all indexes on this table"
    - toast_blks_read:
        usage: "COUNTER"
        description: "Number of disk blocks read from this table's TOAST table (if any)"
    - toast_blks_hit:
        usage: "COUNTER"
        description: "Number of buffer hits in this table's TOAST table (if any)"
    - tidx_blks_read:
        usage: "COUNTER"
        description: "Number of disk blocks read from this table's TOAST table indexes (if any)"
    - tidx_blks_hit:
        usage: "COUNTER"
        description: "Number of buffer hits in this table's TOAST table indexes (if any)"
        
pg_database:
  query: "SELECT pg_database.datname, pg_database_size(pg_database.datname) as size FROM pg_database" 
  metrics:
    - datname:
        usage: "LABEL"
        description: "Name of the database"
    - size:
        usage: "GAUGE"
        description: "Disk space used by the database"

ccp_transaction_wraparound:
  query: "WITH max_age AS ( SELECT 2000000000 as max_old_xid, setting AS autovacuum_freeze_max_age FROM pg_catalog.pg_settings WHERE name = 'autovacuum_freeze_max_age'), per_database_stats AS ( SELECT datname , m.max_old_xid::int , m.autovacuum_freeze_max_age::int , age(d.datfrozenxid) AS oldest_current_xid FROM pg_catalog.pg_database d JOIN max_age m ON (true) WHERE d.datallowconn) SELECT max(oldest_current_xid) AS oldest_current_xid , max(ROUND(100*(oldest_current_xid/max_old_xid::float))) AS percent_towards_wraparound , max(ROUND(100*(oldest_current_xid/autovacuum_freeze_max_age::float))) AS percent_towards_emergency_autovac FROM per_database_stats"
  metrics:
    - oldest_current_xid:
        usage: "GAUGE"
        description: "Oldest current transaction ID in cluster"
    - percent_towards_wraparound:
        usage: "GAUGE"
        description: "Percentage towards transaction ID wraparound"
    - percent_towards_emergency_autovac:
        usage: "GAUGE"
        description: "Percentage towards emergency autovacuum process starting"

pg_stat_database:
  query: "SELECT datid, datname, numbackends, xact_commit, xact_rollback, blks_read, blks_hit, tup_returned, tup_fetched, tup_inserted, tup_updated, tup_deleted, conflicts, temp_files, temp_bytes, deadlocks, blk_read_time, blk_write_time, stats_reset from pg_stat_database"
  metrics:
    - datid:
        usage: "LABEL"
        description: "OID of a database"
    - datname:
        usage: "LABEL"
        description: "Name of this database"
    - numbackends:
        usage: "GAUGE"
        description: "Number of backends currently connected to this database. This is the only column in this view that returns a value reflecting current state; all other columns return the accumulated values since the last reset"
    - xact_commit:
        usage: "COUNTER"
        description: "Number of transactions in this database that have been committed"
    - xact_rollback:
        usage: "COUNTER"
        description: "Number of transactions in this database that have been rolled back"
    - blks_read:
        usage: "COUNTER"
        description: "Number of disk blocks read in this database"
    - blks_hit:
        usage: "COUNTER"
        description: "Number of times disk blocks were found already in the buffer cache, so that a read was not necessary (this only includes hits in the PostgreSQL buffer cache, not the operating system's file system cache"
    - tup_returned:
        usage: "COUNTER"
        description: "Number of rows returned by queries in this database"
    - tup_fetched:
        usage: "COUNTER"
        description: "Number of rows fetched by queries in this database"
    - tup_inserted:
        usage: "COUNTER"
        description: "Number of rows inserted by queries in this database"
    - tup_updated:
        usage: "COUNTER"
        description: "Number of rows updated by queries in this database"
    - tup_deleted:
        usage: "COUNTER"
        description: "Number of rows deleted by queries in this database"
    - conflicts:
        usage: "COUNTER"
        description: "Number of queries canceled due to conflicts with recovery in this database. (Conflicts occur only on standby servers; see pg_stat_database_conflicts for details.)"
    - temp_files:
        usage: "COUNTER"
        description: "Number of temporary files created by queries in this database. All temporary files are counted, regardless of why the temporary file was created (e.g., sorting or hashing), and regardless of the log_temp_files setting."
    - temp_bytes:
        usage: "COUNTER"
        description: "iTotal amount of data written to temporary files by queries in this database. All temporary files are counted, regardless of why the temporary file was created, and regardless of the log_temp_files setting."
    - deadlocks:
        usage: "COUNTER"
        description: "Number of deadlocks detected in this database"
    - blk_read_time:
        usage: "COUNTER"
        description: "Time spent reading data file blocks by backends in this database, in milliseconds"
    - blk_write_time:
        usage: "COUNTER"
        description: "Time spent writing data file blocks by backends in this database, in milliseconds"
    - stats_reset:
        usage: "COUNTER"
        description: "Time at which these statistics were last reset"

pg_stat_activity:
  query: "SELECT pg_database.datname, tmp.state, COALESCE(count,0) as count, COALESCE(max_tx_duration,0) as max_tx_duration FROM ( VALUES ('active'), ('idle'), ('idle in transaction'), ('idle in transaction (aborted)'), ('fastpath function call'), ('disabled')) AS tmp(state) CROSS JOIN pg_database LEFT JOIN ( SELECT datname, state, count(*) AS count, MAX(EXTRACT(EPOCH FROM now() - xact_start))::float AS max_tx_duration FROM pg_stat_activity GROUP BY datname,state) AS tmp2 ON tmp.state = tmp2.state AND pg_database.datname = tmp2.datname"
  metrics:
    - datname:
        usage: "LABEL"
        description: "Name of this database"
    - state:
        usage: "LABEL"
        description: "Connection state"
    - count:
        usage: "GAUGE"
        description: "Number of connections in this state"
    - max_tx_duration:
        usage: "GAUGE"
        description: "Max duration in seconds any active transaction has been running"

pg_stat_database_conflicts:
  query: "select * from pg_stat_database_conflicts"
  metrics:
    - datid:
        usage: "LABEL"
        description: "OID of a database"
    - datname:
        usage: "LABEL"
        description: "Name of this database"
    - confl_tablespace:
        usage: "COUNTER"
        description: "Number of queries in this database that have been canceled due to dropped tablespaces"
    - confl_lock:
        usage: "COUNTER"
        description: "Number of queries in this database that have been canceled due to lock timeouts"
    - confl_snapshot:
        usage: "COUNTER"
        description: "Number of queries in this database that have been canceled due to old snapshots"
    - confl_bufferpin:
        usage: "COUNTER"
        description: "Number of queries in this database that have been canceled due to pinned buffers"
    - confl_deadlock:
        usage: "COUNTER"
        description: "Number of queries in this database that have been canceled due to deadlocks"

pg_stat_bgwriter:
  query: "SELECT checkpoints_timed, checkpoints_req, checkpoint_write_time, checkpoint_sync_time, buffers_checkpoint, buffers_clean, maxwritten_clean, buffers_backend, buffers_backend_fsync, buffers_alloc, stats_reset from pg_stat_bgwriter"
  metrics:
    - checkpoints_timed:
        usage: "COUNTER"
        description: "Number of scheduled checkpoints that have been performed"
    - checkpoints_req:
        usage: "COUNTER"
        description: "Number of requested checkpoints that have been performed"
    - checkpoint_write_time:
        usage: "COUNTER"
        description: "Total amount of time that has been spent in the portion of checkpoint processing where files are written to disk, in milliseconds"
    - checkpoint_sync_time:
        usage: "COUNTER"
        description: "Total amount of time that has been spent in the portion of checkpoint processing where files are synchronized to disk, in milliseconds"
    - buffers_checkpoint:
        usage: "COUNTER"
        description: "Number of buffers written during checkpoints"
    - buffers_clean:
        usage: "COUNTER"
        description: "Number of buffers written by the background writer"
    - maxwritten_clean:
        usage: "COUNTER"
        description: "Number of times the background writer stopped a cleaning scan because it had written too many buffers"
    - buffers_backend:
        usage: "COUNTER"
        description: "Number of buffers written directly by a backend"
    - buffers_backend_fsync:
        usage: "COUNTER"
        description: "Number of times a backend had to execute its own fsync call (normally the background writer handles those even when the backend does its own write)"
    - buffers_alloc:
        usage: "COUNTER"
        description: "Number of buffers allocated"
    - stats_reset:
        usage: "COUNTER"
        description: "Time at which these statistics were last reset"

pg_locks:
  query: "SELECT pg_database.datname,tmp.mode,COALESCE(count,0) as count FROM ( VALUES ('accesssharelock'), ('rowsharelock'), ('rowexclusivelock'), ('shareupdateexclusivelock'), ('sharelock'), ('sharerowexclusivelock'), ('exclusivelock'), ('accessexclusivelock')) AS tmp(mode) CROSS JOIN pg_database LEFT JOIN (SELECT database, lower(mode) AS mode,count(*) AS count FROM pg_locks WHERE database IS NOT NULL GROUP BY database, lower(mode)) AS tmp2 ON tmp.mode=tmp2.mode and pg_database.oid = tmp2.database ORDER BY 1"
  metrics:
    - datname:
        usage: "LABEL"
        description: "Name of this database"
    - mode:
        usage: "LABEL"
        description: "Type of Lock"
    - count:
        usage: "GAUGE"
        description: "Number of locks"