Prometheus+Grafana PG监控部署以及自定义监控指标

点击上方"蓝字"

关注我们，享更多干货！

1.前言

Prometheus：是从云原生计算基金会（CNCF）毕业的项目。Prometheus是Google监控系统BorgMon类似实现的开源版，整套系统由监控服务、告警服务、时序数据库等几个部分，及周边生态的各种指标收集器（Exporter）组成，是在当下主流的监控告警系统。

exporter：广义上向Prometheus提供监控数据的程序都可以成为一个exporter的，一个exporter的实例称为target, exporter来源主要有2个方面：一方面是社区提供的，另一方面是用户自定义的。

Grafana：是一款采用go语言编写的开源应用，主要用于大规模指标数据的可视化展现，是网络架构和应用分析中最流行的时序数据展示工具。目前已经支持绝大部分常用的时序数据库。

Prometheus+Grafana是目前较为流行的数据库监控实施方案，下面就介绍一下相关的基本部署。部署架构如下：

其中exporter端建议与PG部署在一起，但也可以单独部署到Prometheus机器中。

2.部署Prometheus

2.1 下载 https://prometheus.io/download/

2.2 添加用户Prometheus

useradd prometheus;

2.3 解压到

2.4 vim/usr/lib/systemd/system/prometheus.service

[Unit]
Description= Prometheus
After=network.target

[Service]
Type=simple
User=prometheus
ExecStart=/home/prometheus/prometheus-2.28.0.linux-amd64/prometheus --config.file=/home/prometheus/prometheus-2.28.0.linux-amd64/prometheus.yml --storage.tsdb.path=/home/prometheus/prometheus-2.28.0.linux-amd64/data 
ExecReload=/bin/kill -HUP $MAINPID
Restart=on-failure

[Install]
WantedBy=multi-user.target

2.5 将Prometheus添加自启动；启动服务；查看状态

systemctl enable prometheus 
systemctl start prometheus启动服务
systemctl status prometheus服务查看服务器状态

2.6 开启防火墙端口9090

firewall-cmd --zone=public --add-port=9090/tcp --permanent
firewall-cmd --reload

3.配置PostgreSQL

参考：https://github.com/prometheus-community/postgres_exporter
如果是新环境需要用超级用户先执行
（有可能已经在postgres数据安装了,用命令 \dx 可以查看 ）：

如果没有：

create extension if not exists pg_stat_statements;

并且在配置文件postgresql.conf中添加：

shared_preload_libraries = 'pg_stat_statements'
pg_stat_statements.max = 10000
pg_stat_statements.track = all

否则执行下面的SQL会报错:

-- To use IF statements, hence to be able to check if the user exists before
-- attempting creation, we need to switch to procedural SQL (PL/pgSQL)
-- instead of standard SQL.
-- More: https://www.postgresql.org/docs/9.3/plpgsql-overview.html
-- To preserve compatibility with <9.0, DO blocks are not used; instead,
-- a function is created and dropped.
CREATE OR REPLACE FUNCTION __tmp_create_user() returns void as $$
BEGIN
  IF NOT EXISTS (
          SELECT                       -- SELECT list can stay empty for this
          FROM   pg_catalog.pg_user
          WHERE  usename = 'postgres_exporter') THEN
    CREATE USER postgres_exporter;
  END IF;
END;
$$ language plpgsql;

SELECT __tmp_create_user();
DROP FUNCTION __tmp_create_user();

ALTER USER postgres_exporter WITH PASSWORD 'password';
ALTER USER postgres_exporter SET SEARCH_PATH TO postgres_exporter,pg_catalog;

-- If deploying as non-superuser (for example in AWS RDS), uncomment the GRANT
-- line below and replace <MASTER_USER> with your root user.
-- GRANT postgres_exporter TO <MASTER_USER>;
CREATE SCHEMA IF NOT EXISTS postgres_exporter;
GRANT USAGE ON SCHEMA postgres_exporter TO postgres_exporter;
GRANT CONNECT ON DATABASE postgres TO postgres_exporter;

CREATE OR REPLACE FUNCTION get_pg_stat_activity() RETURNS SETOF pg_stat_activity AS
$$ SELECT * FROM pg_catalog.pg_stat_activity; $$
LANGUAGE sql
VOLATILE
SECURITY DEFINER;

CREATE OR REPLACE VIEW postgres_exporter.pg_stat_activity
AS
  SELECT * from get_pg_stat_activity();

GRANT SELECT ON postgres_exporter.pg_stat_activity TO postgres_exporter;

CREATE OR REPLACE FUNCTION get_pg_stat_replication() RETURNS SETOF pg_stat_replication AS
$$ SELECT * FROM pg_catalog.pg_stat_replication; $$
LANGUAGE sql
VOLATILE
SECURITY DEFINER;

CREATE OR REPLACE VIEW postgres_exporter.pg_stat_replication
AS
  SELECT * FROM get_pg_stat_replication();

GRANT SELECT ON postgres_exporter.pg_stat_replication TO postgres_exporter;

CREATE OR REPLACE FUNCTION get_pg_stat_statements() RETURNS SETOF pg_stat_statements AS
$$ SELECT * FROM public.pg_stat_statements; $$
LANGUAGE sql
VOLATILE
SECURITY DEFINER;

CREATE OR REPLACE VIEW postgres_exporter.pg_stat_statements
AS
  SELECT * FROM get_pg_stat_statements();

GRANT SELECT ON postgres_exporter.pg_stat_statements TO postgres_exporter;

4.部署postgres_exporter

https://github.com/wrouesnel/postgres_exporter/releases