prometheus + postgres_exporter监控postgresql

pg_total_relation_size_bytes：每个数据库中所有表和索引的总大小。pg_stat_user_indexes_tup_read：用户索引扫描期间读取的元组数。pg_stat_user_indexes_scan_count：用户索引扫描的次数。pg_stat_activity_waiting_count：当前等待中的连接数。pg_stat_activity_idle_coun

k.o.b.e-24

1503人浏览 · 2024-08-07 09:59:14

k.o.b.e-24 · 2024-08-07 09:59:14 发布

一、postgres_exporter是什么

Postgres_exporter是Prometheus官方支持的PostgreSQL性能监控工具。使用Postgres_exporter可以将PostgreSQL的监控指标导出到Prometheus中，从而进行可视化展示和告警。Postgres_exporter监控的指标包括：连接数、CPU使用率、锁、表空间使用、索引使用、表大小、活跃进程数等，其中很多指标都是对于数据库性能调优非常有帮助的。

二、下载postgres_exporter安装包

1、本地下载后上传到linux服务器

下载地址：https://github.com/prometheus-community/postgres_exporter/releases

2、直接在linux服务器上wget方式下载

# 新建目录
mkdir -p /data/prometheus/postgres_exporter
# 进入目标目录
cd /data/prometheus/postgres_exporter
# 下载
wget https://github.com/prometheus-community/postgres_exporter/releases/download/v0.13.2/postgres_exporter-0.13.2.linux-amd64.tar.gz
# 解压
tar -vxzf postgres_exporter-0.13.2.linux-amd64.tar.gz
# 移动到安装目录
mv /data/prometheus/postgres_exporter/postgres_exporter-0.13.2.linux-amd64 /usr/local/postgres_exporter
# 进入目录
cd /usr/local/postgres_exporter

三、将postgres_exporter配置为系统服务

1、进入systemd目录

cd /usr/lib/systemd/system

2、创建文件

vim postgres_exporter.service

# 添加如下内容
[Unit]
Description=https://github.com/prometheus-community/postgres_exporter
After=network-online.target

[Service]
Type=simple
User=root
Group=root
Environment=DATA_SOURCE_NAME=postgresql://postgres_exporter:postgres_exporter@x.x.x.x:5432/postgres?sslmode=disable
ExecStart=/usr/local/postgres_exporter/postgres_exporter
ExecReload=/bin/kill -HUP
KillMode=process
TimeoutStopSec=20s
Restart=always

[Install]
WantedBy=default.target

3、服务命令

# 生效系统systemd文件
systemctl daemon-reload
# 设置开机自启
systemctl enable postgres_exporter
# 查看状态
systemctl status postgres_exporter
# 启动
systemctl start postgres_exporter.service
# 停止
systemctl stop postgres_exporter.service

四、启动postgres_exporter

# 后台启动
nohup ./postgres_exporter >> /usr/local/postgres_exporter/postgres_exporter.out 2>&1 &

postgres_exporter服务器端通过调用默认端口9187来获取服务器信息。访问blackbox_exporter

http://172.30.18.244:9187/metrics，默认端口为9187

五、配置授权

1、pgsql版本大于10

PostgreSQL server versions >= 10，创建postgres_exporter用户和密码（postgres_exporter）。

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>;

GRANT CONNECT ON DATABASE postgres TO postgres_exporter;
GRANT pg_monitor to postgres_exporter;

2、pgsql版本小于10

PostgreSQL versions < 10。

CREATE SCHEMA IF NOT EXISTS postgres_exporter;
GRANT USAGE ON SCHEMA postgres_exporter 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 EXTENSION IF NOT EXISTS pg_stat_statements;
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;