prometheus + postgres_exporter监控postgresql

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

六、监控指标

1、连接数

连接数是指当前与PostgreSQL数据库建立的连接数，这是一个重要的指标，因为连接数过高可能导致性能下降和资源浪费。

以下是一些与连接数有关的指标：

pg_stat_activity_count：当前活跃的连接数。

pg_stat_activity_waiting_count：当前等待中的连接数。

pg_stat_activity_idle_count：当前空闲的连接数。

2、CPU使用率

CPU使用率指的是PostgreSQL实例使用CPU的百分比。如果CPU使用率过高，可能会导致性能问题。

以下是一些与CPU使用率有关的指标：

cpu_system_seconds_total：PostgreSQL进程在内核态运行的总时间。

cpu_user_seconds_total：PostgreSQL进程在用户态运行的总时间。

3、锁

由于PostgreSQL的锁机制非常复杂，因此锁是一个重要的性能指标。

以下是一些与锁有关的指标：

pg_locks_count：当前所有锁的数量。

pg_locks_blocks_total：所有等待锁的进程数。

4、表空间使用

表空间使用率指的是PostgreSQL表空间的使用情况，包括表和索引的大小。

以下是一些与表空间使用有关的指标：

pg_table_size_bytes：每个表的大小。

pg_indexes_size_bytes：每个索引的大小。

pg_total_relation_size_bytes：每个数据库中所有表和索引的总大小。

5、索引使用

索引使用指的是索引在PostgreSQL中的使用情况。

以下是一些与索引使用有关的指标：

pg_stat_user_indexes_scan_count：用户索引扫描的次数。

pg_stat_user_indexes_tup_read：用户索引扫描期间读取的元组数。

6、表大小和活跃进程数

表大小和活跃进程数也是重要的指标。表大小指的是某个表或索引的大小，活跃进程数指的是PostgreSQL当前的工作进程数量。

以下是一些与表大小和活跃进程数有关的指标：

pg_table_size_bytes：每个表的大小。

pg_indexes_size_bytes：每个索引的大小。

pg_stat_database_numbackends：当前活跃的进程数。