Grafana+Prometheus监控PostgreSQL

各组件使用端口

• grafana:3000

• prometheus:9090

• node_exporter:9100

• postgres_exporter:9187

• pgscv:9890

安装node_exporter

[root@yejf]/opt/monitor# tar -zxvf node_exporter-1.2.1.linux-amd64.tar.gz
node_exporter-1.2.1.linux-amd64/
node_exporter-1.2.1.linux-amd64/LICENSE
node_exporter-1.2.1.linux-amd64/NOTICE
node_exporter-1.2.1.linux-amd64/node_exporter

启动

[root@yejf]/opt/monitor/node_exporter-1.2.1.linux-amd64# ./node_exporter 

查看采集的数据

http://192.168.43.100:9100/metrics

* 安装数据采集器postgres_exporter

下载地址

https://github.com/prometheus-community/postgres_exporter/tags

解压

[root@yejf]/opt/monitor# tar -zxvf postgres_exporter-0.10.0.linux-amd64.tar.gz
postgres_exporter-0.10.0.linux-amd64/
postgres_exporter-0.10.0.linux-amd64/LICENSE
postgres_exporter-0.10.0.linux-amd64/NOTICE
postgres_exporter-0.10.0.linux-amd64/postgres_exporter

queries.xml文件

文件内容见:
https://github.com/prometheus-community/postgres_exporter/blob/master/queries.yaml

使用--extend.query-path=
参数启动

./postgres_exporter --extend.query-path=/opt/monitor/postgres_exporter-0.10.0.linux-amd64/queries.xml

查看采集的数据

http://192.168.43.100:9187/metrics

* 安装数据采集器pgscv

下载地址

https://github.com/weaponry/pgscv/releases

解压

[root@yejf]/opt/monitor# mkdir /opt/monitor/pgscv
[root@yejf]/opt/monitor# tar -zxvf pgscv_0.7.1_linux_amd64.tar.gz -C /opt/monitor/pgscv
LICENSE
README.md
pgscv

启动

export PGSCV_LISTEN_ADDRESS="0.0.0.0:9890"
export POSTGRES_DSN="postgresql://postgres:postgres@192.168.43.100:5432/postgres?sslmode=disable"
/opt/monitor/pgscv/pgscv 

查看采集数据

http://192.168.43.100:9890/metrics

prometheus添加agent节点

/usr/local/prometheus/prometheus.yml
添加

  - job_name: 'greenplum'
    static_configs:
    - targets: ['192.168.43.100:9297']

  - job_name: 'system'
    static_configs:
    - targets: ['192.168.43.100:9100']

  - job_name: 'postgresql'
    static_configs:
    - targets: ['192.168.43.100:9187']
      labels:
        instance: 'pg01'
        platform: 'test'
    - targets: ['192.168.43.100:9890']
      labels:
        instance: 'pg02'
        platform: 'test'

安装Prometheus

略..

启动命令

/usr/local/prometheus/prometheus --config.file=/usr/local/prometheus/prometheus.yml

查看采集指标情况

http://192.168.43.100:9090/targets

安装邮件告警插件alertmanager

下载地址

https://github.com/prometheus/alertmanager/releases

解压

[root@yejf]/opt/monitor# tar -zxvf alertmanager-0.22.2.linux-amd64.tar.gz 
alertmanager-0.22.2.linux-amd64/
alertmanager-0.22.2.linux-amd64/alertmanager.yml
alertmanager-0.22.2.linux-amd64/LICENSE
alertmanager-0.22.2.linux-amd64/NOTICE
alertmanager-0.22.2.linux-amd64/alertmanager
alertmanager-0.22.2.linux-amd64/amtool

编辑配置文件

[root@yejf]/opt/monitor/alertmanager-0.22.2.linux-amd64# cat alertmanager.yml 
global:
  resolve_timeout: 5m      #处理超时时间，默认为5min
  smtp_smarthost: 'xxx'
  smtp_from: 'xxx'  #邮件发送地址
  smtp_auth_username: 'xxx'  #邮件发送地址用户名
  smtp_auth_password: 'xxx' #邮件发送地址授权码
  smtp_require_tls: false
route:
  group_by: ['alertname']
  group_wait: 30s
  group_interval: 5m
  repeat_interval: 1h
  receiver: 'default'
receivers:
- name: 'default'
  email_configs:
  - to: 'xxx'
    send_resolved: true

修改prometheus的配置文件

[root@yejf]~# vi /usr/local/prometheus/prometheus.yml

# Alertmanager configuration
alerting:
  alertmanagers:
  - static_configs:
    - targets:
      - 192.168.43.100:9093

# Load rules once and periodically evaluate them according to the global 'evaluation_interval'.
rule_files:
 - "rules/*.yml"  #配置报警规则
  # - "first_rules.yml"
  # - "second_rules.yml"

配置告警规则文件

[root@yejf]~# cat /usr/local/prometheus/rules/memory_over.yml 
groups:
- name: example
  rules:
  - alert: 主机内存超限
    expr: (1 - (node_memory_MemAvailable_bytes / (node_memory_MemTotal_bytes))) * 100 > 80
    for: 1m
    labels:
      severity: warning
    annotations:
      summary: "{{$labels.instance}}: 主机内存使用超过告警限制"
      description: "{{$labels.instance}}: 内存使用率超过80% (当前值是：{{ $value }})"

启动

[root@yejf]/opt/monitor/alertmanager-0.22.2.linux-amd64# ./alertmanager --config.file="/opt/monitor/alertmanager-0.22.2.linux-amd64/alertmanager.yml"

查看web

http://192.168.43.100:9093/#/alerts

临时把内存的阀值调低，触发邮件告警

安装Grafana

略

启动命令

/usr/local/grafana/bin/grafana-server -homepath /usr/local/grafana -config /usr/local/grafana/conf/defaults.ini

查看

http://192.168.43.100:3000/

导入dashboards

dashboards资源

https://grafana.com/grafana/dashboards

在线导入主机监控

https://grafana.com/grafana/dashboards/8919

导入成功后显示

在线导入数据库监控

导入pgscv的dashboards

https://grafana.com/grafana/dashboards/14540

导入postgresql_export的dashboards

https://grafana.com/grafana/dashboards/9628

https://mp.weixin.qq.com/s/s93NWYbjhC4iyWYg9nj28Q
https://www.cnblogs.com/ilifeilong/p/10543876.html