Docker 环境下 PostgreSQL 监控实战：从 Exporter 到 Prometheus 的部署详解

Docker 环境下 PostgreSQL 监控实战：从 Exporter 到 Prometheus 的部署详解

本文详细介绍了如何使用 Docker 部署并监控 PostgreSQL 数据库。通过创建专用的 PostgreSQL Exporter 账号、配置 Docker 容器，以及使用 Prometheus 获取监控数据，用户可以轻松实现对 PostgreSQL 服务的性能监控。文章提供了 Docker Compose 的完整配置，并讲解了如何通过 pg_query.yaml 文件定制 SQL 查询监控。此外，文章还包括 Prometheus 的配置示例，确保能够成功抓取 PostgreSQL Exporter 数据。最终，用户可以通过访问指定 URL 验证监控系统的运行状态。本指南适合希望快速上手 PostgreSQL 监控的开发者与运维人员。

预备课：

Docker 安装与配置：从入门到部署

Docker 部署 Prometheus+Grafana 监控系统快速指南

Docker Compose 启动 PostgreSQL 数据库

一 节点简述

IP	描述
192.168.0.5	PostgreSQL 服务节点

二 节点监控部署

1）创建 PostgreSQL 的 exporter 账号

# PostgreSQL server versions >= 10(pgsql版本大于10)
# 增加 postgres_exporter 监控 postgres
psql -U postgres -h 192.168.0.5 -p 5432 -d postgres

CREATE USER postgres_exporter;
ALTER USER postgres_exporter WITH PASSWORD 'postgres_exporter';
ALTER USER postgres_exporter SET SEARCH_PATH TO postgres_exporter,pg_catalog;
GRANT CONNECT ON DATABASE postgres TO postgres_exporter;
GRANT pg_monitor to postgres_exporter;
#退出当前连接
\q 

2）Docker run 启动 postgres-exporter

docker run \
  --net=host \
  -e DATA_SOURCE_NAME="postgresql://postgres:password@localhost:5432/postgres?sslmode=disable" \
  quay.io/prometheuscommunity/postgres-exporter:v0.14.0

3）docker-compose.pg05.yml 启动 postgres-exporter

version: '3'
services:
  postgres_exporter:
    image: quay.io/prometheuscommunity/postgres-exporter:v0.14.0
    container_name: postgres_exporter
    hostname: postgres_exporter_90
    restart: always
    environment:
      - DATA_SOURCE_NAME=postgresql://postgres_exporter:postgres_exporter@192.168.0.5:5432/postgres?sslmode=disable
      - PG_EXPORTER_EXTEND_QUERY_PATH=/file/queries.yaml
    networks:
      - appnet
    volumes:
      - ./file/queries.yaml:/file/queries.yaml
    ports:
      - "9187:9187"

networks:
  appnet:
    external: true

注：docker run 和 docker compose 任意选一种（file 宿主机目录记得改权限）。

三 示例 pg_query.yaml 配置

pg_query:
  name: pg_query
  desc: PostgreSQL statement metrics, require pg_stat_statements installed in schema monitor, 9.4 ~ 12
  query: |
    SELECT datname, query, calls, total_time, min_time, max_time, mean_time, stddev_time, rows, blk_io_time FROM
      (SELECT dbid,
          queryid               AS query,
          sum(calls)            AS calls,
          sum(total_time)       AS total_time,
          min(min_time)         AS min_time,
          max(max_time)         AS max_time,
          max(mean_time)        AS mean_time,
          max(stddev_time)      AS stddev_time,
          sum(rows)             AS rows,
          sum(blk_read_time) + sum(blk_write_time) AS blk_io_time
      FROM pg_stat_statements(false) pg_stat_statements(userid, dbid, queryid, query, calls,
          total_time, min_time, max_time, mean_time, stddev_time, rows,
          shared_blks_hit, shared_blks_read, shared_blks_dirtied, shared_blks_written,
          local_blks_hit, local_blks_read, local_blks_dirtied, local_blks_written, temp_blks_read, temp_blks_written,
          blk_read_time, blk_write_time)
      WHERE dbid != 1 AND userid != 10 AND calls > 4
      GROUP BY dbid, queryid ORDER BY total_time DESC LIMIT 64
    ) q NATURAL JOIN (SELECT oid AS dbid, datname FROM pg_database WHERE datname NOT IN ('postgres','template0','template1')) d;

  ttl: 10
  timeout: 1
  min_version: 090400
  max_version: 130000
  tags:
    - cluster
    - extension:pg_stat_statements
    - schema:monitor


  metrics:
    - datname:
        usage: LABEL
        description: database name
    - query:
        usage: LABEL
        description: query identifier, bigint
    - calls:
        usage: COUNTER
        description: times been executed
    - total_time:
        usage: COUNTER
        description: Total time spent in the statement, in µs
    - min_time:
        usage: GAUGE
        description: Minimum time spent in the statement, in µs
    - max_time:
        usage: GAUGE
        description: Maximum time spent in the statement, in µs
    - mean_time:
        usage: GAUGE
        description: Mean time spent in the statement, in µs
    - stddev_time:
        usage: GAUGE
        description: Population standard deviation of time spent in the statement, in µs
    - rows:
        usage: COUNTER
        description: rows retrieved or affected by the statement
    - blk_io_time:
        usage: COUNTER
        description: time spent reading/writing blocks in µs (if track_io_timing is enabled)

四 验证监控是否部署成功

访问 URL：http://192.168.0.5:9187/metrics 。

五 配置prometheus

prometheus.yml 的配置，用于获取监控数据。

global:
  scrape_interval:     15s # 全局默认抓取间隔时间，所有抓取任务的默认频率为15秒
  external_labels:
    monitor: your-postgres_exporter # 外部标签，用于标识该 Prometheus 实例或监控来源
scrape_configs:
  - job_name: 'postgres' # 定义抓取 PostgreSQL 指标数据的任务名称
    static_configs:
      - targets: # 监控目标列表
        - 192.168.0.5:9187 # PostgreSQL Exporter 服务所在的 IP 和端口
    relabel_configs: # 重写标签配置，用于调整或添加标签
      - source_labels: [ "__address__" ] # 使用地址标签（IP 和端口）作为源标签
        regex: "(.*):(.*)" # 使用正则表达式提取 IP 和端口
        target_label: "instance" # 将提取的 IP 设置为新的 instance 标签值
        replacement: $1 # 替换 instance 标签的值为提取到的 IP，忽略端口