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

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 說明啟動成功

總結

以上是生活随笔為你收集整理的prometheus监控之postgresql的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。