加强普法网站建设的通知,专门做同人h的网站,中国航发网上商城,ddos的wordpress插件Docker 环境下 PostgreSQL 监控实战#xff1a;从 Exporter 到 Prometheus 的部署详解 文章目录 Docker 环境下 PostgreSQL 监控实战#xff1a;从 Exporter 到 Prometheus 的部署详解一 节点简述二 节点监控部署1#xff09;创建 PostgreSQL 的 exporter 账号2#xff09;…Docker 环境下 PostgreSQL 监控实战从 Exporter 到 Prometheus 的部署详解 文章目录 Docker 环境下 PostgreSQL 监控实战从 Exporter 到 Prometheus 的部署详解一 节点简述二 节点监控部署1创建 PostgreSQL 的 exporter 账号2Docker run 启动 postgres-exporter3docker-compose.pg05.yml 启动 postgres-exporter 三 示例 pg_query.yaml 配置四 验证监控是否部署成功五 配置prometheus 本文详细介绍了如何使用 Docker 部署并监控 PostgreSQL 数据库。通过创建专用的 PostgreSQL Exporter 账号、配置 Docker 容器以及使用 Prometheus 获取监控数据用户可以轻松实现对 PostgreSQL 服务的性能监控。文章提供了 Docker Compose 的完整配置并讲解了如何通过
pg_query.yaml 文件定制 SQL 查询监控。此外文章还包括 Prometheus 的配置示例确保能够成功抓取 PostgreSQL Exporter 数据。最终用户可以通过访问指定 URL 验证监控系统的运行状态。本指南适合希望快速上手 PostgreSQL 监控的开发者与运维人员。 预备课
Docker 安装与配置从入门到部署
Docker 部署 PrometheusGrafana 监控系统快速指南
一 节点简述
IP描述192.168.0.5PostgreSQL 服务节点
二 节点监控部署
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 2Docker run 启动 postgres-exporter
docker run \--nethost \-e DATA_SOURCE_NAMEpostgresql://postgres:passwordlocalhost:5432/postgres?sslmodedisable \quay.io/prometheuscommunity/postgres-exporter:v0.14.03docker-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_NAMEpostgresql://postgres_exporter:postgres_exporter192.168.0.5:5432/postgres?sslmodedisable- PG_EXPORTER_EXTEND_QUERY_PATH/file/queries.yamlnetworks:- appnetvolumes:- ./file/queries.yaml:/file/queries.yamlports:- 9187:9187networks: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)四 验证监控是否部署成功
访问 URLhttp://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忽略端口
