apiVersion: v1
kind: ConfigMap
metadata:
  name: cnp-basic-monitoring
data:
  custom-queries: |
    backend_summary:
      query: |
       SELECT sa.datname
           , states.state
           , sa.usename
           , sa.application_name
           , COALESCE(sa.count, 0) AS backends
           , COALESCE(sa.max_tx_secs, 0) AS max_tx_duration_seconds
           FROM ( VALUES ('active')
               , ('idle')
               , ('idle in transaction')
               , ('idle in transaction (aborted)')
               , ('fastpath function call')
               , ('disabled')
               ) AS states(state)
           LEFT JOIN (
               SELECT datname
                   , state
                   , usename
                   , COALESCE(application_name, '') AS application_name
                   , count(*)
                   , COALESCE(EXTRACT (EPOCH FROM (max(now() - xact_start))), 0) AS max_tx_secs
               FROM pg_stat_activity
               GROUP BY datname, state, usename, application_name
           ) sa ON states.state = sa.state
           WHERE sa.usename IS NOT NULL
      metrics:
        - datname:
            usage: "LABEL"
            description: "Name of the database"
        - state:
            usage: "LABEL"
            description: "State of the backend"
        - usename:
            usage: "LABEL"
            description: "Name of the user"
        - application_name:
            usage: "LABEL"
            description: "Name of the application"
        - backends:
            usage: "GAUGE"
            description: "Number of backends"
        - max_tx_duration_seconds:
            usage: "GAUGE"
            description: "Maximum duration of a transaction in seconds"

    pg_database:
      query: "SELECT d.datname, pg_database_size(d.datname) as size_bytes FROM pg_database d"
      metrics:
        - datname:
            usage: "LABEL"
            description: "Name of the database"
        - size_bytes:
            usage: "GAUGE"
            description: "Disk space used by the database"

    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 postgres started"

    pg_replication:
      query: "SELECT CASE WHEN NOT pg_is_in_recovery() THEN 0 ELSE GREATEST (0, EXTRACT(EPOCH FROM (now() - pg_last_xact_replay_timestamp()))) END AS lag"
      metrics:
        - lag:
            usage: "GAUGE"
            description: "Replication lag behind primary in seconds"

    pg_replication_slots:
      query: "SELECT slot_name, database, active, pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn) FROM pg_replication_slots"
      metrics:
        - slot_name:
            usage: "LABEL"
            description: "Name of the replication slot"
        - database:
            usage: "LABEL"
            description: "Name of the database"
        - active:
            usage: "GAUGE"
            description: "Flag indicating if the slot is active"
        - pg_wal_lsn_diff:
            usage: "GAUGE"
            description: "Replication lag in bytes"

    pg_stat_archiver:
      query: "SELECT archived_count, failed_count, extract(epoch from now() - last_archived_time) AS last_archived_age FROM pg_stat_archiver"
      metrics:
        - archived_count:
            usage: "COUNTER"
            description: "Number of WAL files that have been successfully archived"
        - failed_count:
            usage: "COUNTER"
            description: "Number of failed attempts for archiving WAL files"
        - last_archived_age:
            usage: "GAUGE"
            description: "Time in seconds since last WAL segment was successfully archived"

    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_stat_database_conflicts:
      query: "SELECT datname, confl_tablespace, confl_lock, confl_snapshot, confl_bufferpin, confl_deadlock FROM pg_stat_database_conflicts"
      metrics:
        - datname:
            usage: "LABEL"
            description: "Name of the 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"

    # NOTE: currently this query is useless as it is done on the postgres database.
    # TODO: Enhance to set the target database
    pg_stat_user_tables:
      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') 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
      metrics:
        - datname:
            usage: "LABEL"
            description: "Name of current database"
        - 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"
        - n_mod_since_analyze:
            usage: "GAUGE"
            description: "Estimated number of rows changed since last analyze"
        - 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_stat_replication:
      query: |
       SELECT usename
           , COALESCE(application_name, '') AS application_name
           , COALESCE(client_addr::text, '') AS client_addr
           , pg_wal_lsn_diff(pg_current_wal_lsn(), sent_lsn) AS sent_diff_bytes
           , pg_wal_lsn_diff(pg_current_wal_lsn(), write_lsn) AS write_diff_bytes
           , pg_wal_lsn_diff(pg_current_wal_lsn(), flush_lsn) AS flush_diff_bytes
           , COALESCE(pg_wal_lsn_diff(pg_current_wal_lsn(), replay_lsn),0) AS replay_diff_bytes
           , COALESCE((EXTRACT(EPOCH FROM write_lag)),0)::float AS write_lag_seconds
           , COALESCE((EXTRACT(EPOCH FROM flush_lag)),0)::float AS flush_lag_seconds
           , COALESCE((EXTRACT(EPOCH FROM replay_lag)),0)::float AS replay_lag_seconds
       FROM pg_stat_replication
      metrics:
        - usename:
            usage: "LABEL"
            description: "Name of the replication user"
        - application_name:
            usage: "LABEL"
            description: "Name of the application"
        - client_addr:
            usage: "LABEL"
            description: "Client IP address"
        - sent_diff_bytes:
            usage: "GAUGE"
            description: "Difference in bytes from the last write-ahead log location sent on this connection"
        - write_diff_bytes:
            usage: "GAUGE"
            description: "Difference in bytes from the last write-ahead log location written to disk by this standby server"
        - flush_diff_bytes:
            usage: "GAUGE"
            description: "Difference in bytes from the last write-ahead log location flushed to disk by this standby server"
        - replay_diff_bytes:
            usage: "GAUGE"
            description: "Difference in bytes from the last write-ahead log location replayed into the database on this standby server"
        - write_lag_seconds:
            usage: "GAUGE"
            description: "Time elapsed between flushing recent WAL locally and receiving notification that this standby server has written it"
        - flush_lag_seconds:
            usage: "GAUGE"
            description: "Time elapsed between flushing recent WAL locally and receiving notification that this standby server has written and flushed it"
        - replay_lag_seconds:
            usage: "GAUGE"
            description: "Time elapsed between flushing recent WAL locally and receiving notification that this standby server has written, flushed and applied it"

    # NOTE: currently this query is useless as it is done on the postgres database.
    # TODO: Enhance to set the target database
    pg_statio_user_tables:
      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"
      metrics:
        - datname:
            usage: "LABEL"
            description: "Name of current database"
        - 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)"

    waiting_backends:
      query: |
       SELECT count(*) AS total
       FROM pg_catalog.pg_locks         blocked_locks
       JOIN pg_catalog.pg_locks         blocking_locks
           ON blocking_locks.locktype = blocked_locks.locktype
           AND blocking_locks.database IS NOT DISTINCT FROM blocked_locks.database
           AND blocking_locks.relation IS NOT DISTINCT FROM blocked_locks.relation
           AND blocking_locks.page IS NOT DISTINCT FROM blocked_locks.page
           AND blocking_locks.tuple IS NOT DISTINCT FROM blocked_locks.tuple
           AND blocking_locks.virtualxid IS NOT DISTINCT FROM blocked_locks.virtualxid
           AND blocking_locks.transactionid IS NOT DISTINCT FROM blocked_locks.transactionid
           AND blocking_locks.classid IS NOT DISTINCT FROM blocked_locks.classid
           AND blocking_locks.objid IS NOT DISTINCT FROM blocked_locks.objid
           AND blocking_locks.objsubid IS NOT DISTINCT FROM blocked_locks.objsubid
           AND blocking_locks.pid != blocked_locks.pid
       JOIN pg_catalog.pg_stat_activity blocking_activity ON blocking_activity.pid = blocking_locks.pid
       WHERE NOT blocked_locks.granted
      metrics:
        - total:
            usage: "GAUGE"
            description: "Total number of backends that are currently waiting on other queries"
