如何通过postgres_exporter+Prometheus+Grafana监控PostgreSQL列值临近上限

需要监控PostgreSQL中的列，确保其不超出对应数据类型的最大允许范围（例如跟踪SMALLINT类型的PRIMARY KEY，避免达到32767的上限）。希望通过postgres_exporter + Prometheus + Grafana搭建监控体系，实现类似MySQL Exporter的效果：在MySQL中使用-collect.auto_increment.columns参数，结合Grafana公式auto_increment.columns / auto_increment.columns_max * 100，以百分比形式展示AUTO_INCREMENT值距上限的接近程度。

尝试过以下方法但未成功：

• 查找Postgres Exporter文档中类似MySQL Exporter的-collect.auto_increment.columns的参数
• 搜索用于跟踪PostgreSQL列值的自定义Prometheus查询

Postgres Exporter没有现成的对应参数，需通过自定义查询实现指标提取，再结合Prometheus和Grafana完成监控可视化。

一、通过Postgres Exporter自定义查询提取指标 #

1. 创建自定义查询配置文件（例如custom_queries.yaml），定义两个核心指标：列数据类型的最大值、列当前的最大取值

custom_queries:
- name: column_data_type_max
  query: |
    SELECT
      table_schema AS schema,
      table_name AS table,
      column_name AS column,
      data_type,
      CASE data_type
        WHEN 'smallint' THEN 32767
        WHEN 'integer' THEN 2147483647
        WHEN 'bigint' THEN 9223372036854775807
        -- 按需补充其他需监控的数值类型
      END AS max_value
    FROM information_schema.columns
    WHERE data_type IN ('smallint', 'integer', 'bigint')
      AND column_name IN ('id') -- 替换为你要监控的主键/自增列名，多列用逗号分隔
  metrics:
  - schema:
      usage: "LABEL"
      description: "表所在的Schema名称"
  - table:
      usage: "LABEL"
      description: "表名称"
  - column:
      usage: "LABEL"
      description: "列名称"
  - data_type:
      usage: "LABEL"
      description: "列的数据类型"
  - max_value:
      usage: "GAUGE"
      description: "该数据类型允许的最大值"

- name: column_current_max
  query: |
    SELECT
      c.table_schema AS schema,
      c.table_name AS table,
      c.column_name AS column,
      MAX((c.column_name)::bigint) AS current_max
    FROM information_schema.columns c
    JOIN pg_class pc ON c.table_name = pc.relname
    JOIN pg_namespace pn ON c.table_schema = pn.nspname
    WHERE c.data_type IN ('smallint', 'integer', 'bigint')
      AND c.column_name IN ('id') -- 替换为你要监控的列名
      AND pc.relkind = 'r'
    GROUP BY c.table_schema, c.table_name, c.column_name
  metrics:
  - schema:
      usage: "LABEL"
      description: "表所在的Schema名称"
  - table:
      usage: "LABEL"
      description: "表名称"
  - column:
      usage: "LABEL"
      description: "列名称"
  - current_max:
      usage: "GAUGE"
      description: "该列当前的最大取值"

2. 启动postgres_exporter时加载该配置文件

postgres_exporter --extend.query-path=/path/to/custom_queries.yaml

二、Prometheus配置指标采集 #

确保Prometheus配置文件中已添加postgres_exporter的采集任务：

scrape_configs:
  - job_name: 'postgres_exporter'
    static_configs:
      - targets: ['<postgres_exporter_IP>:9187'] # 替换为你的exporter地址和端口

三、Grafana可视化与告警 #

1. 创建面板，使用以下PromQL计算列取值占上限的百分比：

(
  max by (schema, table, column) (column_current_max_current_max)
  /
  max by (schema, table, column) (column_data_type_max_max_value)
) * 100

2. 配置告警规则（添加到Prometheus配置中），当百分比超过阈值时触发告警：

groups:
- name: postgres_column_capacity_alerts
  rules:
  - alert: PostgresColumnNearMaxLimit
    expr: |
      (
        max by (schema, table, column) (column_current_max_current_max)
        /
        max by (schema, table, column) (column_data_type_max_max_value)
      ) * 100 > 90 # 自定义阈值，例如90%
    for: 5m
    labels:
      severity: warning
    annotations:
      summary: "PostgreSQL列 {{ $labels.table }}.{{ $labels.column }} 即将达到上限"
      description: "{{ $labels.schema }}库中{{ $labels.table }}表的{{ $labels.column }}列已使用{{ $value | round }}%的容量"

内容的提问来源于stack exchange，提问作者Zhaba_BUN