---
apiVersion: v1
kind: ConfigMap
metadata:
  name: sql-exporter-config
data:
  config.yml: |
    ---
    jobs:
    - name: "master-nodes"
      interval: '1m'
      connections:
      - 'postgres://postgres_exporter@postgres/postgres?sslmode=disable'
      queries:
      - name: "pg_settings"
        help: "Values of PostgreSQL runtime settings"
        labels:
          - "name"
        values:
          - "setting"
        query:  |
                SELECT
                  name::text,
                  setting::float
                FROM pg_settings
                WHERE
                  vartype IN ('integer','real')
                UNION
                SELECT
                  name::text,
                  CASE setting WHEN 't' THEN 1::float ELSE 0::float END
                FROM pg_settings
                WHERE
                  vartype = 'bool';
      - name: "pg_stat_database"
        help: "PostgreSQL Database stats"
        labels:
          - "datname"
        values:
          - "numbackends"
          - "xact_commit"
          - "xact_rollback"
          - "blks_read"
          - "blks_hit"
          - "tup_returned"
          - "tup_fetched"
          - "tup_inserted"
          - "tup_updated"
          - "tup_deleted"
          - "conflicts"
          - "temp_files"
          - "deadlocks"
          - "blk_read_time"
          - "blk_write_time"
        query:  |
                SELECT
                  datname::text,
                  numbackends::float,
                  xact_commit::float,
                  xact_rollback::float,
                  blks_read::float,
                  blks_hit::float,
                  tup_returned::float,
                  tup_fetched::float,
                  tup_inserted::float,
                  tup_updated::float,
                  tup_deleted::float,
                  conflicts::float,
                  temp_files::float,
                  deadlocks::float,
                  blk_read_time::float,
                  blk_write_time::float
                FROM pg_stat_database;
      - name: "connections"
        help: "Number of connections in different states"
        labels:
          - "datname"
          - "usename"
          - "state"
        values:
          - "count"
        query:  |
                SELECT
                  datname::text,
                  usename::text,
                  state::text,
                  COUNT(state)::float AS count
                FROM pg_stat_activity
                GROUP BY datname, usename, state;
      - name: "db_sizes"
        help: "Database Sizes"
        labels:
          - "dbname"
        values:
          - "dbsize"
        query:  |
                SELECT
                  datname::text AS dbname,
                  pg_database_size(datname)::float AS dbsize
                FROM pg_database;
      - name: "replication_senders_count"
        help: "Replication Senders connected"
        values:
          - "count"
        query:  |
                SELECT
                  COUNT(*)::float AS count
                FROM
                  pg_stat_replication;
      - name: "pg_is_in_recovery"
        help: "Bool flag if the node is in recovery"
        values:
          - "case"
        query:  |
                SELECT
                  CASE pg_is_in_recovery() WHEN 't' THEN 1::float ELSE 0::float END;
    - name: "slave-nodes"
      interval: '1m'
      connections:
      - 'postgres://postgres_exporter@postgres-slave1/postgres?sslmode=disable'
      - 'postgres://postgres_exporter@postgres-slave2/postgres?sslmode=disable'
      queries:
      - name: "replication_lag_seconds"
        help: "Replication Lag in Seconds"
        labels:
          - "hostname"
        values:
          - "replication_lag"
        query:  |
                WITH lag AS ( 
                SELECT
                  CASE
                    WHEN pg_last_xlog_receive_location() = pg_last_xlog_replay_location() THEN 0
                    ELSE EXTRACT (EPOCH FROM now() - pg_last_xact_replay_timestamp())
                  END
                AS lag
                )
                SELECT
                  split_part(inet_server_addr()::text, '/', 1) AS hostname,
                  lag::float AS replication_lag
                FROM lag
      - name: "pg_settings"
        help: "Values of PostgreSQL runtime settings"
        labels:
          - "name"
        values:
          - "setting"
        query:  |
                SELECT
                  name::text,
                  setting::float
                FROM pg_settings
                WHERE
                  vartype IN ('integer','real')
                UNION
                SELECT
                  name::text,
                  CASE setting WHEN 't' THEN 1::float ELSE 0::float END
                FROM pg_settings
                WHERE
                  vartype = 'bool';
      - name: "pg_stat_database"
        help: "PostgreSQL Database stats"
        labels:
          - "datname"
        values:
          - "numbackends"
          - "xact_commit"
          - "xact_rollback"
          - "blks_read"
          - "blks_hit"
          - "tup_returned"
          - "tup_fetched"
          - "tup_inserted"
          - "tup_updated"
          - "tup_deleted"
          - "conflicts"
          - "temp_files"
          - "deadlocks"
          - "blk_read_time"
          - "blk_write_time"
        query:  |
                SELECT
                  datname::text,
                  numbackends::float,
                  xact_commit::float,
                  xact_rollback::float,
                  blks_read::float,
                  blks_hit::float,
                  tup_returned::float,
                  tup_fetched::float,
                  tup_inserted::float,
                  tup_updated::float,
                  tup_deleted::float,
                  conflicts::float,
                  temp_files::float,
                  deadlocks::float,
                  blk_read_time::float,
                  blk_write_time::float
                FROM pg_stat_database;
      - name: "connections"
        help: "Number of connections in different states"
        labels:
          - "datname"
          - "usename"
          - "state"
        values:
          - "count"
        query:  |
                SELECT
                  datname::text,
                  usename::text,
                  state::text,
                  COUNT(state)::float AS count
                FROM pg_stat_activity
                GROUP BY datname, usename, state;
      - name: "pg_is_in_recovery"
        help: "Bool flag if the node is in recovery"
        values:
          - "case"
        query:  |
                SELECT CASE pg_is_in_recovery() WHEN 't' THEN 1::float ELSE 0::float END;
    - name: "per-db-stats"
      interval: '15m'
      keepalive: false
      connections:
      - 'postgres://postgres_exporter@postgres/db1?sslmode=disable'
      - 'postgres://postgres_exporter@postgres/db2?sslmode=disable'
      - 'postgres://postgres_exporter@postgres/db3?sslmode=disable'
      queries:
      - name: "pg_stat_user_tables"
        help: "Table stats"
        labels:
          - "schemaname"
          - "relname"
        values:
          - "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"
          - "vacuum_count"
          - "autovacuum_count"
          - "analyze_count"
          - "autoanalyze_count"
        query:  |
                SELECT
                  schemaname::text
                , relname::text
                , seq_scan::float
                , seq_tup_read::float
                , idx_scan::float
                , idx_tup_fetch::float
                , n_tup_ins::float
                , n_tup_upd::float
                , n_tup_del::float
                , n_tup_hot_upd::float
                , n_live_tup::float
                , n_dead_tup::float
                , vacuum_count::float
                , autovacuum_count::float
                , analyze_count::float
                , autoanalyze_count::float
                FROM pg_stat_user_tables;
      - name: "pg_statio_user_tables"
        help: "IO Stats"
        labels:
          - "schemaname"
          - "relname"
        values:
          - "heap_blks_read"
          - "heap_blks_hit"
          - "idx_blks_read"
          - "idx_blks_hit"
        query:  |
                SELECT
                  schemaname::text
                , relname::text
                , heap_blks_read::float
                , heap_blks_hit::float
                , idx_blks_read::float
                , idx_blks_hit::float
                FROM pg_statio_user_tables;
