prometheus监控之postgresql

文章目录

• • prometheus监控之postgresql
  • • • 下载postgres_exporter
      • 安装
      • 配置unit服务
      • 配置授权
      • • PostgreSQL server versions >= 10(pgsql版本大于10)
        • PostgreSQL versions older than 10 (pgsql版本小于10)
      • 启动postgres_exporter

下载postgres_exporter

项目地址：https://github.com/prometheus-community/postgres_exporter

wget https://github.com/prometheus-community/postgres_exporter/releases/download/v0.11.1/postgres_exporter-0.11.1.linux-amd64.tar.gz

安装

tar xf postgres_exporter-0.11.1.linux-amd64.tar.gz
mv postgres_exporter-0.11.1.linux-amd64/postgres_exporter  /usr/bin/

配置unit服务

# cat  /usr/lib/systemd/system/postgres_exporter.service
[Unit]
Description=postgres Exporter
Wants=network-online.target
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/bin/postgres_exporter
ExecReload=/bin/kill -HUP
KillMode=process
TimeoutStopSec=20s
Restart=always[Install]
WantedBy=default.target

配置授权

PostgreSQL server versions >= 10(pgsql版本大于10)

创建postgres_exporter用户和密码(postgres_exporter)

CREATE OR REPLACE FUNCTION __tmp_create_user() returns void as $$
BEGINIF NOT EXISTS (SELECT                       -- SELECT list can stay empty for thisFROM   pg_catalog.pg_userWHERE  usename = 'postgres_exporter') THENCREATE 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  with your root user.
-- GRANT postgres_exporter TO ;GRANT CONNECT ON DATABASE postgres TO postgres_exporter;
GRANT pg_monitor to postgres_exporter;

PostgreSQL versions older than 10 (pgsql版本小于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
ASSELECT * 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
ASSELECT * 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
ASSELECT * FROM get_pg_stat_statements();GRANT SELECT ON postgres_exporter.pg_stat_statements TO postgres_exporter;

启动postgres_exporter

systemctl daemon-reload
systemctl  restart postgres_exporter.service
systemctl  enable postgres_exporter.service
curl -s x.x.x.x:9187/metrics|grep pg_up
# HELP pg_up Whether the last scrape of metrics from PostgreSQL was able to connect to the server (1 for yes, 0 for no).
# TYPE pg_up gauge
pg_up 1

pg_up 1 说明启动成功