采集器配置

pg_connections:
  desc: "实例连接数"
  query: |
    SELECT
      count(*) as total,
      count(*) FILTER (WHERE state = 'active') as active,
      count(*) FILTER (WHERE state = 'idle') as idle,
      count(*) FILTER (WHERE state = 'idle in transaction') as idle_in_transaction
    FROM pg_stat_activity
    WHERE pid != pg_backend_pid()
  tags: [cluster]
  ttl: 1
  metrics:
    - total: {usage: GAUGE, description: "总连接数"}
    - active: {usage: GAUGE, description: "活跃连接数"}
    - idle: {usage: GAUGE, description: "空闲连接数"}
    - idle_in_transaction: {usage: GAUGE, description: "事务中空闲连接数"}

pg_exporter --dry-run --config=./connections.yml
pg_exporter --config=./connections.yml

curl -fsS http://127.0.0.1:9630/metrics | grep '^pg_connections_'

collector_branch_name:           # 此采集器的唯一标识符
  name: metric_namespace         # 指标前缀（默认为分支名称）
  desc: "采集器描述"             # 人类可读的描述
  query: |                       # 要执行的 SQL 查询
    SELECT column1, column2
    FROM table

  # 执行控制
  priority: 100                  # 致命/非致命分组内，数值越小越先执行
  ttl: 10                        # 缓存生存时间（秒）
  timeout: 0.1                   # 查询超时（秒）
  fatal: false                   # 为 true 时，错误会停止该数据库的后续采集
  skip: false                    # 如果为 true，禁用此采集器

  # 版本兼容性
  min_version: 100000            # 最小 PostgreSQL 版本（包含）
  max_version: 999999            # 最大 PostgreSQL 版本（不包含）

  # 执行标签
  tags: [cluster, primary]       # 执行条件

  # 谓词查询（可选）
  predicate_queries:
    - name: "check_function"
      ttl: 60                    # 谓词结果的独立缓存时间，单位秒
      predicate_query: |
        SELECT EXISTS (...)

  # 指标定义
  metrics:
    - column_name:
        usage: GAUGE             # GAUGE、COUNTER、HISTOGRAM、LABEL 或 DISCARD
        rename: metric_name      # 可选：重命名指标
        description: "帮助文本"   # 指标描述
        default: 0               # NULL 时的默认值
        scale: 1000              # 值的缩放因子
        # usage 为 HISTOGRAM 时，还需配置 bucket: [1, 10, 100]

pg_stat_database:  # 必须唯一
  name: pg_db      # 实际的指标命名空间

query: |
  SELECT
    datname,
    numbackends,
    xact_commit,
    xact_rollback,
    blks_read,
    blks_hit
  FROM pg_stat_database
  WHERE datname NOT IN ('template0', 'template1')

pg_xact_age:
  name: pg_xact_age
  desc: "开放事务年龄分布直方图"
  query: |
    SELECT datname,
           greatest(0, extract(epoch FROM now() - xact_start)) AS seconds
    FROM pg_stat_activity
    WHERE pid <> pg_backend_pid() AND backend_type = 'client backend'
      AND datname IS NOT NULL AND xact_start IS NOT NULL;
  ttl: 10
  min_version: 100000
  tags: [cluster]
  metrics:
    - datname: {usage: LABEL, description: "数据库名称"}
    - seconds:
        usage: HISTOGRAM
        bucket: [1, 3, 10, 30, 100, 300, 1000, 3000, 10000, 30000, 100000]
        description: "开放事务年龄快照（秒）"

histogram_quantile(0.95, pg_xact_age_seconds_bucket)

# 快速查询 - 最小缓存
pg_stat_activity:
  ttl: 1  # 缓存 1 秒

# 昂贵查询 - 较长缓存
pg_table_bloat:
  ttl: 3600  # 缓存 1 小时

timeout: 0.1   # 100 ms；未配置或设置为 0 时也使用此值
timeout: 1.0   # 1 秒
timeout: -1    # 关闭该采集器的查询超时

min_version: 100000  # PostgreSQL 10.0+
max_version: 140000  # 低于 PostgreSQL 14.0

pg_exporter --tag="production,critical"

expensive_metrics:
  tags: [critical]  # 仅在有 'critical' 标签时运行

predicate_queries:
  - name: "检查 pg_stat_statements"
    predicate_query: |
      SELECT EXISTS (
        SELECT 1 FROM pg_extension
        WHERE extname = 'pg_stat_statements'
      )

metrics:
  - numbackends:
      usage: GAUGE
      description: "已连接的后端进程数"

metrics:
  - checkpoint_write_time:
      usage: COUNTER
      rename: write_time        # 重命名指标
      scale: 0.001              # 将毫秒转换为秒
      default: 0                # NULL 时使用 0
      description: "检查点写入时间（秒）"

pg_table_stats:
  desc: "表统计信息"
  query: |
    SELECT
      current_database() AS datname,
      schemaname,
      relname AS tablename,
      n_tup_ins,
      n_tup_upd,
      n_tup_del,
      n_live_tup,
      n_dead_tup
    FROM pg_stat_user_tables
  ttl: 10
  metrics:
    - datname: {usage: LABEL}
    - schemaname: {usage: LABEL}
    - tablename: {usage: LABEL}
    - n_tup_ins: {usage: COUNTER, description: "插入的元组数"}
    - n_tup_upd: {usage: COUNTER, description: "更新的元组数"}
    - n_tup_del: {usage: COUNTER, description: "删除的元组数"}
    - n_live_tup: {usage: GAUGE, description: "活跃元组数"}
    - n_dead_tup: {usage: GAUGE, description: "死亡元组数"}

pg_wal_stats:
  desc: "WAL 统计信息（PG 14–17）"
  min_version: 140000
  max_version: 180000
  query: |
    SELECT
      wal_records,
      wal_bytes,
      wal_buffers_full,
      wal_write_time,
      wal_sync_time
    FROM pg_stat_wal
  ttl: 10
  tags: [cluster]
  metrics:
    - wal_records: {usage: COUNTER}
    - wal_bytes: {usage: COUNTER}
    - wal_buffers_full: {usage: COUNTER}
    - wal_write_time: {usage: COUNTER, scale: 0.001}
    - wal_sync_time: {usage: COUNTER, scale: 0.001}

pg_stat_statements_metrics:
  desc: "查询性能统计"
  min_version: 130000
  tags: [cluster, "extension:pg_stat_statements"]
  query: |
    SELECT
      sum(calls) as total_calls,
      sum(total_exec_time) as total_time,
      sum(total_exec_time) / NULLIF(sum(calls), 0) as mean_time
    FROM pg_stat_statements
  ttl: 60
  metrics:
    - total_calls: {usage: COUNTER}
    - total_time: {usage: COUNTER, scale: 0.001}
    - mean_time: {usage: GAUGE, scale: 0.001}

sudo install -d -m 0755 /etc/pg_exporter
sudo cp -p /etc/pg_exporter.yml /etc/pg_exporter/0000-base.yml

# /etc/pg_exporter/9990-app.yml
app_metrics:
  desc: "应用特定指标"
  tags: ["dbname:app"]
  query: |
    SELECT
      (SELECT count(*) FROM users WHERE active = true) as active_users,
      (SELECT count(*) FROM orders WHERE created_at > NOW() - '1 hour'::interval) as recent_orders,
      (SELECT avg(processing_time) FROM jobs WHERE completed_at > NOW() - '5 minutes'::interval) as avg_job_time
  ttl: 30
  metrics:
    - active_users: {usage: GAUGE, description: "当前活跃用户数"}
    - recent_orders: {usage: GAUGE, description: "最近一小时的订单数"}
    - avg_job_time: {usage: GAUGE, description: "平均作业处理时间"}

pg_exporter --dry-run --config=/etc/pg_exporter/
pg_exporter --explain --fail-fast --config=/etc/pg_exporter/

partition_metrics:
  desc: "分区表指标"
  predicate_queries:
    - name: "检查是否使用了分区"
      predicate_query: |
        SELECT EXISTS (
          SELECT 1 FROM pg_class
          WHERE relkind = 'p' LIMIT 1
        )
  query: |
    SELECT
      current_database() AS datname,
      ns.nspname AS schemaname,
      parent.relname AS parent_table,
      count(*) as partition_count,
      sum(pg_relation_size(child.oid)) as total_size
    FROM pg_inherits
    JOIN pg_class parent ON parent.oid = pg_inherits.inhparent
    JOIN pg_class child ON child.oid = pg_inherits.inhrelid
    JOIN pg_namespace ns ON ns.oid = parent.relnamespace
    WHERE parent.relkind = 'p'
    GROUP BY ns.nspname, parent.relname
  ttl: 300
  min_version: 100000
  metrics:
    - datname: {usage: LABEL}
    - schemaname: {usage: LABEL}
    - parent_table: {usage: LABEL}
    - partition_count: {usage: GAUGE}
    - total_size: {usage: GAUGE}

# 查看采集器统计（命中/错误/跳过计数与耗时）
curl http://localhost:9630/stat

# 按 datname/query 维度查看每个采集器的耗时与错误
curl -s http://localhost:9630/metrics | grep -E 'pg_exporter_query_scrape_(duration|error_count)'

# 干运行 - 显示解析后的配置
pg_exporter --dry-run

# 解释 - 显示计划的查询
pg_exporter --explain

pg_exporter --log.level=debug