PostgreSQL Prometheus Grafana

1、前言

Prometheus：是从云原生计算基金会（CNCF）毕业的项目。Prometheus是Google监控系统BorgMon类似实现的开源版，整套系统由监控服务、告警服务、时序数据库等几个部分，及周边生态的各种指标收集器（Exporter）组成，是在当下主流的监控告警系统。
exporter：广义上向Prometheus提供监控数据的程序都可以成为一个exporter的，一个exporter的实例称为target， exporter来源主要有2个方面：一方面是社区提供的，另一方面是用户自定义的。
Grafana：是一款采用go语言编写的开源应用，主要用于大规模指标数据的可视化展现，是网络架构和应用分析中最流行的时序数据展示工具。目前已经支持绝大部分常用的时序数据库。
Prometheus+Grafana是目前较为流行的数据库监控实施方案，下面就介绍一下相关的基本部署。部署架构如下：
Prometheus Grafana PG监控部署以及自定义监控指标 - 图1
其中exporter端建议与PG部署在一起，但也可以单独部署到Prometheus机器中。

2、部署Prometheus

2.1 下载 https://prometheus.io/download/

image.png

2.2 添加用户Prometheus

  1. useradd prometheus;

2.3 解压到

Prometheus Grafana PG监控部署以及自定义监控指标 - 图3

2.4 vim/usr/lib/systemd/system/prometheus.service

  1. [Unit]
  2. Description= Prometheus
  3. After=network.target
  5. [Service]
  6. Type=simple
  7. User=prometheus
  8. ExecStart=/home/prometheus/prometheus-2.28.0.linux-amd64/prometheus --config.file=/home/prometheus/prometheus-2.28.0.linux-amd64/prometheus.yml --storage.tsdb.path=/home/prometheus/prometheus-2.28.0.linux-amd64/data 
  9. ExecReload=/bin/kill -HUP $MAINPID
  10. Restart=on-failure
  12. [Install]
  13. WantedBy=multi-user.target

Prometheus Grafana PG监控部署以及自定义监控指标 - 图4

2.5 将Prometheus添加自启动；启动服务；查看状态

  1. systemctl enable prometheus 
  2. systemctl start prometheus启动服务
  3. systemctl status prometheus服务查看服务器状态

2.6 开启防火墙端口9090

  1. firewall-cmd --zone=public --add-port=9090/tcp --permanent
  2. firewall-cmd --reload

3、配置PostgreSQL

参考：https://github.com/prometheus-community/postgres_exporter
如果是新环境需要用超级用户先执行
（有可能已经在postgres数据安装了，用命令 \dx 可以查看 ）：
Prometheus Grafana PG监控部署以及自定义监控指标 - 图5
如果没有：

  1. create extension if not exists pg_stat_statements;

并且在配置文件postgresql.conf中添加：

  1. shared_preload_libraries = 'pg_stat_statements'
  2. pg_stat_statements.max = 10000
  3. pg_stat_statements.track = all

否则执行下面的SQL会报错:

  1. -- To use IF statements, hence to be able to check if the user exists before
  2. -- attempting creation, we need to switch to procedural SQL (PL/pgSQL)
  3. -- instead of standard SQL.
  4. -- More: https://www.postgresql.org/docs/9.3/plpgsql-overview.html
  5. -- To preserve compatibility with <9.0, DO blocks are not used; instead,
  6. -- a function is created and dropped.
  7. CREATE OR REPLACE FUNCTION __tmp_create_user() returns void as $$
  8. BEGIN
  9.   IF NOT EXISTS (
  10.           SELECT                       -- SELECT list can stay empty for this
  11.           FROM   pg_catalog.pg_user
  12.           WHERE  usename = 'postgres_exporter') THEN
  13.     CREATE USER postgres_exporter;
  14.   END IF;
  15. END;
  16. $$ language plpgsql;
  18. SELECT __tmp_create_user();
  19. DROP FUNCTION __tmp_create_user();
  21. ALTER USER postgres_exporter WITH PASSWORD 'password';
  22. ALTER USER postgres_exporter SET SEARCH_PATH TO postgres_exporter,pg_catalog;
  24. -- If deploying as non-superuser (for example in AWS RDS), uncomment the GRANT
  25. -- line below and replace <MASTER_USER> with your root user.
  26. -- GRANT postgres_exporter TO <MASTER_USER>;
  27. CREATE SCHEMA IF NOT EXISTS postgres_exporter;
  28. GRANT USAGE ON SCHEMA postgres_exporter TO postgres_exporter;
  29. GRANT CONNECT ON DATABASE postgres TO postgres_exporter;
  31. CREATE OR REPLACE FUNCTION get_pg_stat_activity() RETURNS SETOF pg_stat_activity AS
  32. $$ SELECT * FROM pg_catalog.pg_stat_activity; $$
  33. LANGUAGE sql
  34. VOLATILE
  35. SECURITY DEFINER;
  37. CREATE OR REPLACE VIEW postgres_exporter.pg_stat_activity
  38. AS
  39.   SELECT * from get_pg_stat_activity();
  41. GRANT SELECT ON postgres_exporter.pg_stat_activity TO postgres_exporter;
  43. CREATE OR REPLACE FUNCTION get_pg_stat_replication() RETURNS SETOF pg_stat_replication AS
  44. $$ SELECT * FROM pg_catalog.pg_stat_replication; $$
  45. LANGUAGE sql
  46. VOLATILE
  47. SECURITY DEFINER;
  49. CREATE OR REPLACE VIEW postgres_exporter.pg_stat_replication
  50. AS
  51.   SELECT * FROM get_pg_stat_replication();
  53. GRANT SELECT ON postgres_exporter.pg_stat_replication TO postgres_exporter;
  55. CREATE OR REPLACE FUNCTION get_pg_stat_statements() RETURNS SETOF pg_stat_statements AS
  56. $$ SELECT * FROM public.pg_stat_statements; $$
  57. LANGUAGE sql
  58. VOLATILE
  59. SECURITY DEFINER;
  61. CREATE OR REPLACE VIEW postgres_exporter.pg_stat_statements
  62. AS
  63.   SELECT * FROM get_pg_stat_statements();
  65. GRANT SELECT ON postgres_exporter.pg_stat_statements TO postgres_exporter;

4、部署postgres_exporter

https://github.com/wrouesnel/postgres_exporter/releases
image.png
下载最新版本的linux amd64位压缩包：pg_queries.yaml
下载地址：https://github.com/prometheus-community/postgres_exporter
或者使用以下内容（此内容下载的是git的pg_queries.yaml并添加了一点自己的监控指标）。

  1. pg_replication:
  2.   query: "SELECT CASE WHEN NOT pg_is_in_recovery() THEN 0 ELSE GREATEST (0, EXTRACT(EPOCH FROM (now() - pg_last_xact_replay_timestamp()))) END AS lag"
  3.   master: true
  4.   metrics:
  5.     - lag:
  6.         usage: "GAUGE"
  7.         description: "Replication lag behind master in seconds"
  9. pg_postmaster:
  10.   query: "SELECT pg_postmaster_start_time as start_time_seconds from pg_postmaster_start_time()"
  11.   master: true
  12.   metrics:
  13.     - start_time_seconds:
  14.         usage: "GAUGE"
  15.         description: "Time at which postmaster started"
  17. pg_stat_user_tables:
  18.   query: |
  19.    SELECT
  20.      current_database() datname,
  21.      schemaname,
  22.      relname,
  23.      seq_scan,
  24.      seq_tup_read,
  25.      idx_scan,
  26.      idx_tup_fetch,
  27.      n_tup_ins,
  28.      n_tup_upd,
  29.      n_tup_del,
  30.      n_tup_hot_upd,
  31.      n_live_tup,
  32.      n_dead_tup,
  33.      n_mod_since_analyze,
  34.      COALESCE(last_vacuum, '1970-01-01Z') as last_vacuum,
  35.      COALESCE(last_autovacuum, '1970-01-01Z') as last_autovacuum,
  36.      COALESCE(last_analyze, '1970-01-01Z') as last_analyze,
  37.      COALESCE(last_autoanalyze, '1970-01-01Z') as last_autoanalyze,
  38.      vacuum_count,
  39.      autovacuum_count,
  40.      analyze_count,
  41.      autoanalyze_count
  42.    FROM
  43.      pg_stat_user_tables
  44.   metrics:
  45.     - datname:
  46.         usage: "LABEL"
  47.         description: "Name of current database"
  48.     - schemaname:
  49.         usage: "LABEL"
  50.         description: "Name of the schema that this table is in"
  51.     - relname:
  52.         usage: "LABEL"
  53.         description: "Name of this table"
  54.     - seq_scan:
  55.         usage: "COUNTER"
  56.         description: "Number of sequential scans initiated on this table"
  57.     - seq_tup_read:
  58.         usage: "COUNTER"
  59.         description: "Number of live rows fetched by sequential scans"
  60.     - idx_scan:
  61.         usage: "COUNTER"
  62.         description: "Number of index scans initiated on this table"
  63.     - idx_tup_fetch:
  64.         usage: "COUNTER"
  65.         description: "Number of live rows fetched by index scans"
  66.     - n_tup_ins:
  67.         usage: "COUNTER"
  68.         description: "Number of rows inserted"
  69.     - n_tup_upd:
  70.         usage: "COUNTER"
  71.         description: "Number of rows updated"
  72.     - n_tup_del:
  73.         usage: "COUNTER"
  74.         description: "Number of rows deleted"
  75.     - n_tup_hot_upd:
  76.         usage: "COUNTER"
  77.         description: "Number of rows HOT updated (i.e., with no separate index update required)"
  78.     - n_live_tup:
  79.         usage: "GAUGE"
  80.         description: "Estimated number of live rows"
  81.     - n_dead_tup:
  82.         usage: "GAUGE"
  83.         description: "Estimated number of dead rows"
  84.     - n_mod_since_analyze:
  85.         usage: "GAUGE"
  86.         description: "Estimated number of rows changed since last analyze"
  87.     - last_vacuum:
  88.         usage: "GAUGE"
  89.         description: "Last time at which this table was manually vacuumed (not counting VACUUM FULL)"
  90.     - last_autovacuum:
  91.         usage: "GAUGE"
  92.         description: "Last time at which this table was vacuumed by the autovacuum daemon"
  93.     - last_analyze:
  94.         usage: "GAUGE"
  95.         description: "Last time at which this table was manually analyzed"
  96.     - last_autoanalyze:
  97.         usage: "GAUGE"
  98.         description: "Last time at which this table was analyzed by the autovacuum daemon"
  99.     - vacuum_count:
  100.         usage: "COUNTER"
  101.         description: "Number of times this table has been manually vacuumed (not counting VACUUM FULL)"
  102.     - autovacuum_count:
  103.         usage: "COUNTER"
  104.         description: "Number of times this table has been vacuumed by the autovacuum daemon"
  105.     - analyze_count:
  106.         usage: "COUNTER"
  107.         description: "Number of times this table has been manually analyzed"
  108.     - autoanalyze_count:
  109.         usage: "COUNTER"
  110.         description: "Number of times this table has been analyzed by the autovacuum daemon"
  112. pg_statio_user_tables:
  113.   query: "SELECT current_database() datname, schemaname, relname, heap_blks_read, heap_blks_hit, idx_blks_read, idx_blks_hit, toast_blks_read, toast_blks_hit, tidx_blks_read, tidx_blks_hit FROM pg_statio_user_tables"
  114.   metrics:
  115.     - datname:
  116.         usage: "LABEL"
  117.         description: "Name of current database"
  118.     - schemaname:
  119.         usage: "LABEL"
  120.         description: "Name of the schema that this table is in"
  121.     - relname:
  122.         usage: "LABEL"
  123.         description: "Name of this table"
  124.     - heap_blks_read:
  125.         usage: "COUNTER"
  126.         description: "Number of disk blocks read from this table"
  127.     - heap_blks_hit:
  128.         usage: "COUNTER"
  129.         description: "Number of buffer hits in this table"
  130.     - idx_blks_read:
  131.         usage: "COUNTER"
  132.         description: "Number of disk blocks read from all indexes on this table"
  133.     - idx_blks_hit:
  134.         usage: "COUNTER"
  135.         description: "Number of buffer hits in all indexes on this table"
  136.     - toast_blks_read:
  137.         usage: "COUNTER"
  138.         description: "Number of disk blocks read from this table's TOAST table (if any)"
  139.     - toast_blks_hit:
  140.         usage: "COUNTER"
  141.         description: "Number of buffer hits in this table's TOAST table (if any)"
  142.     - tidx_blks_read:
  143.         usage: "COUNTER"
  144.         description: "Number of disk blocks read from this table's TOAST table indexes (if any)"
  145.     - tidx_blks_hit:
  146.         usage: "COUNTER"
  147.         description: "Number of buffer hits in this table's TOAST table indexes (if any)"
  149. pg_database:
  150.   query: "SELECT pg_database.datname, pg_database_size(pg_database.datname) as size_bytes FROM pg_database"
  151.   master: true
  152.   cache_seconds: 30
  153.   metrics:
  154.     - datname:
  155.         usage: "LABEL"
  156.         description: "Name of the database"
  157.     - size_bytes:
  158.         usage: "GAUGE"
  159.         description: "Disk space used by the database"
  161. pg_stat_statements:
  162.   query: "SELECT t2.rolname, t3.datname, queryid, calls, total_time / 1000 as total_time_seconds, min_time / 1000 as min_time_seconds, max_time / 1000 as max_time_seconds, mean_time / 1000 as mean_time_seconds, stddev_time / 1000 as stddev_time_seconds, rows, shared_blks_hit, shared_blks_read, shared_blks_dirtied, shared_blks_written, local_blks_hit, local_blks_read, local_blks_dirtied, local_blks_written, temp_blks_read, temp_blks_written, blk_read_time / 1000 as blk_read_time_seconds, blk_write_time / 1000 as blk_write_time_seconds FROM pg_stat_statements t1 JOIN pg_roles t2 ON (t1.userid=t2.oid) JOIN pg_database t3 ON (t1.dbid=t3.oid) WHERE t2.rolname != 'rdsadmin'"
  163.   master: true
  164.   metrics:
  165.     - rolname:
  166.         usage: "LABEL"
  167.         description: "Name of user"
  168.     - datname:
  169.         usage: "LABEL"
  170.         description: "Name of database"
  171.     - queryid:
  172.         usage: "LABEL"
  173.         description: "Query ID"
  174.     - calls:
  175.         usage: "COUNTER"
  176.         description: "Number of times executed"
  177.     - total_time_seconds:
  178.         usage: "COUNTER"
  179.         description: "Total time spent in the statement, in milliseconds"
  180.     - min_time_seconds:
  181.         usage: "GAUGE"
  182.         description: "Minimum time spent in the statement, in milliseconds"
  183.     - max_time_seconds:
  184.         usage: "GAUGE"
  185.         description: "Maximum time spent in the statement, in milliseconds"
  186.     - mean_time_seconds:
  187.         usage: "GAUGE"
  188.         description: "Mean time spent in the statement, in milliseconds"
  189.     - stddev_time_seconds:
  190.         usage: "GAUGE"
  191.         description: "Population standard deviation of time spent in the statement, in milliseconds"
  192.     - rows:
  193.         usage: "COUNTER"
  194.         description: "Total number of rows retrieved or affected by the statement"
  195.     - shared_blks_hit:
  196.         usage: "COUNTER"
  197.         description: "Total number of shared block cache hits by the statement"
  198.     - shared_blks_read:
  199.         usage: "COUNTER"
  200.         description: "Total number of shared blocks read by the statement"
  201.     - shared_blks_dirtied:
  202.         usage: "COUNTER"
  203.         description: "Total number of shared blocks dirtied by the statement"
  204.     - shared_blks_written:
  205.         usage: "COUNTER"
  206.         description: "Total number of shared blocks written by the statement"
  207.     - local_blks_hit:
  208.         usage: "COUNTER"
  209.         description: "Total number of local block cache hits by the statement"
  210.     - local_blks_read:
  211.         usage: "COUNTER"
  212.         description: "Total number of local blocks read by the statement"
  213.     - local_blks_dirtied:
  214.         usage: "COUNTER"
  215.         description: "Total number of local blocks dirtied by the statement"
  216.     - local_blks_written:
  217.         usage: "COUNTER"
  218.         description: "Total number of local blocks written by the statement"
  219.     - temp_blks_read:
  220.         usage: "COUNTER"
  221.         description: "Total number of temp blocks read by the statement"
  222.     - temp_blks_written:
  223.         usage: "COUNTER"
  224.         description: "Total number of temp blocks written by the statement"
  225.     - blk_read_time_seconds:
  226.         usage: "COUNTER"
  227.         description: "Total time the statement spent reading blocks, in milliseconds (if track_io_timing is enabled, otherwise zero)"
  228.     - blk_write_time_seconds:
  229.         usage: "COUNTER"
  230.         description: "Total time the statement spent writing blocks, in milliseconds (if track_io_timing is enabled, otherwise zero)"
  232. pg_process_idle:
  233.   query: |
  234.     WITH
  235.       metrics AS (
  236.         SELECT
  237.           application_name,
  238.           SUM(EXTRACT(EPOCH FROM (CURRENT_TIMESTAMP - state_change))::bigint)::float AS process_idle_seconds_sum,
  239.           COUNT(*) AS process_idle_seconds_count
  240.         FROM pg_stat_activity
  241.         WHERE state = 'idle'
  242.         GROUP BY application_name
  243.       ),
  244.       buckets AS (
  245.         SELECT
  246.           application_name,
  247.           le,
  248.           SUM(
  249.             CASE WHEN EXTRACT(EPOCH FROM (CURRENT_TIMESTAMP - state_change)) <= le
  250.               THEN 1
  251.               ELSE 0
  252.             END
  253.           )::bigint AS bucket
  254.         FROM
  255.           pg_stat_activity,
  256.           UNNEST(ARRAY[1, 2, 5, 15, 30, 60, 90, 120, 300]) AS le
  257.         GROUP BY application_name, le
  258.         ORDER BY application_name, le
  259.       )
  260.     SELECT
  261.       application_name,
  262.       process_idle_seconds_sum as seconds_sum,
  263.       process_idle_seconds_count as seconds_count,
  264.       ARRAY_AGG(le) AS seconds,
  265.       ARRAY_AGG(bucket) AS seconds_bucket
  266.     FROM metrics JOIN buckets USING (application_name)
  267.     GROUP BY 1, 2, 3
  268.   metrics:
  269.     - application_name:
  270.         usage: "LABEL"
  271.         description: "Application Name"
  272.     - seconds:
  273.         usage: "HISTOGRAM"
  274.         description: "Idle time of server processes"
  277. pg_active_lockedsql:
  278.   query: |
  279.     select case  when replace(replace(pg_blocking_pids(pid)::text,'{',''),'}','')='' then 'numsofnopidblock'  else 'numsofsomepidblock' end  pidblock,
  280.     count(1) pidnums from pg_stat_activity 
  281.     where state not in('idle') and query !=''  group by pidblock order by pidblock;
  282.   metrics:
  283.     - pidblock:
  284.         usage: "LABEL"
  285.         description: "Possible values:numsofnopidblock--The processes that are not locked; numsofsomepidblock--The processes locked by some "
  286.     - pidnums:
  287.         usage: "COUNTER"
  288.         description: "The number of processes"
  290. pg_active_slowsql:
  291.   query: |
  292.     select datname,usename,count(1) slowsql_count          
  293.     from pg_stat_activity where state not in('idle') and query !='' 
  294.     and extract(epoch from (now() - query_start)) > 60*5 group by datname,usename order by count(1) desc;
  295.   metrics:
  296.     - datname:
  297.         usage: "LABEL"
  298.         description: "Name of database"
  299.     - usename:
  300.         usage: "LABEL"
  301.         description: "Name of user"
  302.     - slowsql_count:
  303.         usage: "COUNTER"
  304.         description: "the numbers of slow sqls"
  306. pg_never_used_indexes:
  307.   query: |
  308.     select pi.schemaname, pi.relname, pi.indexrelname, 
  309.     pg_table_size(pi.indexrelid) as index_size from pg_indexes pis join 
  310.     pg_stat_user_indexes pi on pis.schemaname = pi.schemaname 
  311.     and pis.tablename = pi.relname and pis.indexname = pi.indexrelname 
  312.     left join pg_constraint pco on pco.conname = pi.indexrelname 
  313.     and pco.conrelid = pi.relid where pco.contype is distinct from 'p' 
  314.     and pco.contype is distinct from 'u' and (idx_scan,idx_tup_read,idx_tup_fetch) = (0,0,0) 
  315.     and pis.indexdef !~ ' UNIQUE INDEX ' and pi.relname !~ 'backup$' 
  316.     order by pg_table_size(indexrelid) desc;
  317.   metrics:
  318.     - schemaname:
  319.         usage: "LABEL"
  320.         description: "Schema of table"
  321.     - relname:
  322.         usage: "LABEL"
  323.         description: "Name of table"
  324.     - indexrelname:
  325.         usage: "LABEL"
  326.         description: "Name of index"
  327.     - index_size:
  328.         usage: "GAUGE"
  329.         description: "Size of index"
  331. pg_tablelocktops:
  332.   query: |
  333.     select  db.datname,relname tbname,mode locktype,count(1) locknums
  334.     from pg_database db join pg_locks lk on db.oid=lk.database 
  335.     join pg_class cl on lk.relation=cl.oid 
  336.     join pg_stat_activity act on lk.pid=act.pid 
  337.     where db.datname not in ('template0','template1') and fastpath='t' 
  338.     and cl.oid not in (select oid from pg_class where relname in ('pg_class','pg_locks'))
  339.     and act.pid  <>pg_backend_pid() and cl.reltablespace in (select oid from pg_tablespace) 
  340.     group by db.datname,relname,mode order by count(1) desc limit 10; 
  341.   metrics:
  342.     - datname:
  343.         usage: "LABEL"
  344.         description: "database of table"
  345.     - tbname:
  346.         usage: "LABEL"
  347.         description: "Name of table"
  348.     - locktype:
  349.         usage: "LABEL"
  350.         description: "type of lock"
  351.     - locknums:
  352.         usage: "COUNTER"
  353.         description: "the numbers of this lock"

注1：这个pg_exporter最好与PostgreSQL放在同一个服务器方便后续配置。
注2：尽量不要使用root用户运行，而是采用postgres用户或者别的适当用户。
注3：在需要新增监控指标的参数添加时，一定要参照git上的pg_queries.yaml格式进行修改（包括缩进等，最好就是复制原有的再进行修改，postgres_exporter在这里执行的非常严格，最开始配置的时候在这里调了不少时间）。

  1. [prometheus@localhost ~]$ 
  2. /home/prometheus/postgres_exporter-0.9.0.linux-amd64/postgres_exporter  --web.listen-address :9187 --extend.query-path="/home/prometheus/postgres_exporter-0.9.0.linux-amd64/pg_queries.yaml"  &

Prometheus Grafana PG监控部署以及自定义监控指标 - 图7
登录：192.168.254.128:9187/metrics查看有关postgres_exporter发送的相关数据，（如果有新增的参数的话，可以搜索一下，看是否有添加成功）。
部署完成postgres_exporter之后，登录192.168.254.128:9187/metrics刷新，启动进程的session没有报错，就是成功了的。

5、配置Prometheus

这一步骤，是要让Prometheus去接收postgres_exporter的数据。
修改prometheus.yaml配置文件如下:

  1. global:
  3. alerting:
  4.   alertmanagers:
  5.   - static_configs:
  6.     - targets:
  9. rule_files:
  11. scrape_configs:
  12.   - job_name: 'prometheus'
  14.     static_configs:
  15.     - targets: ['0.0.0.0:9090']
  16.   - job_name: 'postgresql-instancetest'
  17.     static_configs:
  18.     - targets: ['192.168.254.128:9187','192.168.254.129:9187']

主要配置的是scrape_configs下属配置。
每个需要监控的postgres_exporter实例，均是一个单独的job_name，并配置job名称，以及job的连接参数（机器:端口）
然后重启服务。

  1. systemctl restart prometheus

6、部署Grafana

Grafana是nodejs的产物，因此没办法做到一个bin文件形态的部署，建议根据操作系统，选择对应的安装办法：
https://grafana.com/grafana/download
Red Hat, CentOS, RHEL, and Fedora(64 Bit)

  1. wget https://dl.grafana.com/oss/release/grafana-8.0.3-1.x86_64.rpm
  2. sudo yum install grafana-8.0.3-1.x86_64.rpm

安装完成后，设置开机自启动，并启动服务：

  1. systemctl enable grafana-server
  2. systemctl start  grafana-server
  3. systemctl status grafana-server

默认监听端口为3000端口，默认用户名密码为admin/admin，第一次登录需要修改密码。
Prometheus Grafana PG监控部署以及自定义监控指标 - 图8
开放3000端口的防火墙

  1. firewall-cmd --zone=public --add-port=3000/tcp --permanent
  2. firewall-cmd --reload

7、配置Grafana

这一部分是最终配置Grafana页面。
首先是配置数据源（假设部署在192.168.254.128机器）：
从http://192.168.254.128:3000/datasources页面添加Prometheus数据源：在http url处写入http://:9090，（如果有需要）输入其他安全认证类参数。
导入监控模板
image.png
image.png
image.png
9628号模板即PostgreSQL Database的dashboard：https://grafana.com/grafana/dashboards/9628（复制链接至浏览器中打开）

8、新增监控指标：

8.1 在postgres_exporter端的pg_queries.yaml中加入该监控指标的查询SQL

Prometheus Grafana PG监控部署以及自定义监控指标 - 图12
例如：
Prometheus Grafana PG监控部署以及自定义监控指标 - 图13

8.2 在192.168.254.128:9187/metrics中查看是否有相关的参数输出

Prometheus Grafana PG监控部署以及自定义监控指标 - 图14

8.3 在Grafana中添加panel

8.3.1 点击添加

Prometheus Grafana PG监控部署以及自定义监控指标 - 图15

8.3.2 选择图标展示类型，以及标题等

Prometheus Grafana PG监控部署以及自定义监控指标 - 图16

8.3.3 依次选择和键入相关内容

instance=”instance” 这个是建立的实例，意思是遍历设置的实例dataname=~"datname"，这个参数是设置所传入的database的名字(如果需要的话)；不同的设置之间用“,”间隔。
Prometheus Grafana PG监控部署以及自定义监控指标 - 图17
Prometheus Grafana PG监控部署以及自定义监控指标 - 图18
上图中1的位置是设置需要显示在图例中的相关参数值，这个值是取自8.2中讲到的相关数字。
设置好之后数据就会又展示出来，保存即可。
最后效果图：
Prometheus Grafana PG监控部署以及自定义监控指标 - 图19
Prometheus Grafana PG监控部署以及自定义监控指标 - 图20
Prometheus Grafana PG监控部署以及自定义监控指标 - 图21
Prometheus Grafana PG监控部署以及自定义监控指标 - 图22