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 监控系统快速指南

一 节点简述

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 postgresCREATE 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.0container_name: postgres_exporterhostname: postgres_exporter_90restart: alwaysenvironment:- DATA_SOURCE_NAME=postgresql://postgres_exporter:postgres_exporter@192.168.0.5:5432/postgres?sslmode=disable- PG_EXPORTER_EXTEND_QUERY_PATH=/file/queries.yamlnetworks:- appnetvolumes:- ./file/queries.yaml:/file/queries.yamlports:- "9187:9187"networks:appnet:external: true

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

三 示例 pg_query.yaml 配置

pg_query:name: pg_querydesc: PostgreSQL statement metrics, require pg_stat_statements installed in schema monitor, 9.4 ~ 12query: |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_timeFROM 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 > 4GROUP 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: 10timeout: 1min_version: 090400max_version: 130000tags:- cluster- extension:pg_stat_statements- schema:monitormetrics:- datname:usage: LABELdescription: database name- query:usage: LABELdescription: query identifier, bigint- calls:usage: COUNTERdescription: times been executed- total_time:usage: COUNTERdescription: Total time spent in the statement, in µs- min_time:usage: GAUGEdescription: Minimum time spent in the statement, in µs- max_time:usage: GAUGEdescription: Maximum time spent in the statement, in µs- mean_time:usage: GAUGEdescription: Mean time spent in the statement, in µs- stddev_time:usage: GAUGEdescription: Population standard deviation of time spent in the statement, in µs- rows:usage: COUNTERdescription: rows retrieved or affected by the statement- blk_io_time:usage: COUNTERdescription: 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，忽略端口