[prometheus]监控postgres

前言

相关Repo: https://github.com/prometheus-community/postgres_exporter

本文使用的postgres_exporter的版本为 0.16.0，postgres的版本为15.7

步骤

1. 在db中创建用户并授权。

create user exporter encrypted password '123456';
ALTER user exporter SET search_path TO pg_catalog, public;
GRANT CONNECT ON DATABASE postgres TO exporter;
GRANT pg_monitor to exporter;
-- 不确定是否需要做
-- GRANT USAGE ON SCHEMA pg_catalog TO exporter;
-- GRANT SELECT ON pg_stat_statements TO exporter;

2. （可选，建议）安装 pg_stat_statements。具体操作可查询其它文档。安装完后可以切exporter用户执行SELECT * FROM pg_stat_statements LIMIT 1; 测试 exporter 用户能否正常访问 pg_stat_statements 的视图。
3. 启动。编写了一个脚本来启动。更多 postgres_exporter 的启动参数可以参考./postgres_exporter --help

#!/bin/bash


export DATA_SOURCE_URI="localhost:5432/postgres?sslmode=disable"
export DATA_SOURCE_USER="exporter"
export DATA_SOURCE_PASS="123456"

script_dir=$(cd $(dirname $0) && pwd)
app_name="postgres_exporter"

is_running() {
    ps -ef | grep -v grep | grep "${script_dir}/${app_name}" > /dev/null
    if [ $? -eq 0 ]; then
        echo "${script_dir}/${app_name} is running"
        return 0
    else
        echo "${script_dir}/${app_name} is not running"
        return 1
    fi
}

start_app() {
    is_running
    if [ $? -eq 0 ]; then
        return 0
    fi
    echo "starting ${script_dir}/${app_name}"
    nohup ${script_dir}/${app_name} \
        --collector.postmaster \
        --collector.stat_statements >  ${script_dir}/app.log 2>&1 &
}

stop_app() {
    is_running
    if [ $? -eq 1 ]; then
        return 0
    fi
    echo "stopping ${script_dir}/${app_name}"
    kill $(ps -ef | grep -v grep | grep "${script_dir}/${app_name}" | awk '{print $2}')
}

restart_app() {
    stop_app
    sleep 1
    start_app
}


main() {
    if [ ! -f "${script_dir}/${app_name}" ]; then
        echo "${script_dir}/${app_name} not found"
    fi
    local action=$1
    if [ x"$action" == "x" ]; then
        local action="start"
    fi
    case "$action" in
        start)
            start_app
            ;;
        stop)
            stop_app
            ;;
        restart)
            restart_app
            ;;
        status)
            is_running
            ;;
        *)
            echo "Usage:  {start|stop|restart|status}"
            exit 1
            ;;
    esac
}

main $@

4. 编辑prometheus.yml，添加收集postgres_exporter。注意替换路径

# ...
scrape_configs:
  - job_name: "postgres"
    file_sd_configs:
    - files: ['/etc/prometheus/sd_configs/postgres/*.yaml']
      refresh_interval:  10s

5. 编辑/etc/prometheus/sd_configs/postgres/pg.yaml

- targets: ['192.168.0.201:9187']
  labels:
    instance: 192.168.0.201

6. 发送 hup 信号给 prometheus 或重启 prometheus
7. grafana 导入 dashboard。在grafana官网找的ID为 9628 的dashboard
8. (可选)没事干的话，可以跑个 pgbench，观察基准测试下的 pg metrics。不过再没事干也别拿生产在用的DB跑基准测试。