使用 Prometheus 和 Grafana 使用 PostgreSQL 监视 Oracle Cloud Infrastructure Database

简介

PostgreSQL 是应用程序开发人员中最常用的数据库之一。除了 Oracle Cloud Infrastructure Database with PostgreSQL(OCI Database with PostgreSQL) 提供的管理开销之外，在实施便捷监视方面，它还需要更进一步。Prometheus 和 Grafana 是非常受欢迎的组合，分别用于监视时间序列数据存储和可视化应用的性能。

In this tutorial, we will demonstrate how to monitor an OCI Database with PostgreSQL by extracting query-level metrics using the PostgreSQL Exporter, scraping them with Prometheus, visualizing them in Grafana, and setting up alerts for bloat metrics.

体系结构概览

以下方案描述了整体方法。使用了以下组件：

• OCI Database with PostgreSQL。
• Postgres Exporter ：显示度量。
• Prometheus 抓取度量。
• Grafana 可视化指标。

注：虽然体系结构显示三个子网以求清晰，但本教程仅使用两个子网，跳转主机也是用于部署 Prometheus 和 Grafana 的 VM。

目标

• 使用 Prometheus 的 PostgreSQL 导出程序提取 PostgreSQL 度量。

• 在 Prometheus 中摄取这些度量。

• 使用 Grafana 仪表盘可视化指标。

• 使用通过 PostgreSQL 导出程序公开的度量设置警报以检测表膨胀。

Prerequisites

• 使用带有 PostgreSQL 集群的预配 OCI 数据库访问 OCI 租户。有关详细信息，请参阅使用 DBeaver 通过 PostgreSQL 连接到 Oracle Cloud Infrastructure Database 。

• OCI 计算实例（跳转主机）位于与 OCI 数据库相同的 VCN 中的公共子网中，使用 PostgreSQL。

• OCI 安全列表：允许入站端口 9090、9187 和 3000。

• 运行以下命令以在实例上设置防火墙。

  sudo firewall-cmd --permanent --add-port=9090/tcp
  sudo firewall-cmd --permanent --add-port=9187/tcp
  sudo firewall-cmd --permanent --add-port=3000/tcp
  sudo firewall-cmd --reload
  

任务 1：使用 PostgreSQL 在 OCI 数据库上启用监视扩展

1. 启用 pg_stat_statements 扩展。

   通过 OCI 控制台在 OCI Database 上添加 pg_stat_statements 和 PostgreSQL 配置。有关详细信息，请参阅为数据库启用扩展。

2. 创建监视用户。

   运行以下查询，通过跳转主机使用 DBeaver 创建和连接用户。

   CREATE USER postgres_exporter WITH PASSWORD '<password>';
   GRANT CONNECT ON DATABASE postgres TO postgres_exporter;
   GRANT pg_monitor TO postgres_exporter;
   

   注： OCI Database 提供了 pg_stat_io 扩展，包括 PostgreSQL v16。

任务 2：安装 Prometheus 的 PostgreSQL 导出程序

注：在 OCI 计算实例上运行以下命令。

1. 安装 PostgreSQL 出口商。

   wget https://github.com/prometheus-community/postgres_exporter/releases/download/v0.15.0/postgres_exporter-0.15.0.linux-amd64.tar.gz
   
   tar -xvf postgres_exporter-0.15.0.linux-amd64.tar.gz
   
   sudo mv postgres_exporter-0.15.0.linux-amd64/postgres_exporter /usr/local/bin/
   

2. 创建定制 queries.yaml 。

   nano queries.yaml
   

   # Custom PostgreSQL Query Metrics for Postgres Exporter
   
   # Top 5 slowest SQL statements
   top_sql_statements:
   query: >
   	SELECT query, calls, total_exec_time + total_plan_time AS total_time
   	FROM pg_stat_statements
   	ORDER BY total_time DESC
   	LIMIT 5;
   metrics:
   	- query:
   		usage: "LABEL"
   		description: "SQL query text"
   	- calls:
   		usage: "COUNTER"
   		description: "Number of executions"
   	- total_time:
   		usage: "GAUGE"
   		description: "Total execution + plan time"
   
   # 2. Top users by total execution time
   top_sql_users:
   query: >
   	SELECT usename, SUM(total_exec_time + total_plan_time) AS total_exec_time
   	FROM pg_stat_statements
   	JOIN pg_user ON pg_stat_statements.userid = pg_user.usesysid
   	GROUP BY usename
   	ORDER BY total_exec_time DESC
   	LIMIT 5;
   metrics:
   	- usename:
   		usage: "LABEL"
   		description: "PostgreSQL user"
   	- total_exec_time:
   		usage: "GAUGE"
   		description: "Total execution time by user"
   
   # 3. Top client IPs by connection count
   top_clients:
   query: >
   	SELECT client_addr, COUNT(*) AS connections
   	FROM pg_stat_activity
   	WHERE client_addr IS NOT NULL
   	GROUP BY client_addr
   	ORDER BY connections DESC
   	LIMIT 5;
   metrics:
   	- client_addr:
   		usage: "LABEL"
   		description: "Client IP address"
   	- connections:
   		usage: "GAUGE"
   		description: "Number of active connections"
   
   # 4. Currently running queries by user and IP
   active_queries_by_user_ip:
   query: >
   	SELECT usename, client_addr, COUNT(*) as active_queries
   	FROM pg_stat_activity
   	WHERE state = 'active'
   	GROUP BY usename, client_addr;
   metrics:
   	- usename:
   		usage: "LABEL"
   		description: "User running queries"
   	- client_addr:
   		usage: "LABEL"
   		description: "Client IP address"
   	- active_queries:
   		usage: "GAUGE"
   		description: "Number of currently active queries"
   
   # 5. Query frequency (most called)
   frequent_sql:
   query: >
   	SELECT query, calls
   	FROM pg_stat_statements
   	ORDER BY calls DESC
   	LIMIT 5;
   metrics:
   	- query:
   		usage: "LABEL"
   		description: "SQL query text"
   	- calls:
   		usage: "COUNTER"
   		description: "Number of executions"
   
   # 6. Table bloat statistics
   table_bloat:
   query: >
   	SELECT
   	current_database() AS current_db,
   	schemaname AS schema_name,
   	tblname AS table_name,
   	(bs * tblpages) / 1024 AS actual_table_size_kb,
   	((tblpages - est_tblpages) * bs) / 1024 AS wasted_space_kb,
   	CASE
   		WHEN tblpages > 0 AND tblpages - est_tblpages > 0 THEN 100 * (tblpages - est_tblpages) / tblpages::float
   		ELSE 0
   	END AS wasted_space_percent,
   	fillfactor AS table_fill_factor,
   	CASE
   		WHEN tblpages - est_tblpages_ff > 0 THEN (tblpages - est_tblpages_ff) * bs / 1024
   		ELSE 0
   	END AS bloat_size_kb,
   	CASE
   		WHEN tblpages > 0 AND tblpages - est_tblpages_ff > 0 THEN 100 * (tblpages - est_tblpages_ff) / tblpages::float
   		ELSE 0
   	END AS bloat_size_percent
   	FROM (
   	SELECT
   		ceil(reltuples / ((bs - page_hdr) / tpl_size)) + ceil(toasttuples / 4) AS est_tblpages,
   		ceil(reltuples / ((bs - page_hdr) * fillfactor / (tpl_size * 100))) + ceil(toasttuples / 4) AS est_tblpages_ff,
   		tblpages,
   		fillfactor,
   		bs,
   		schemaname,
   		tblname
   	FROM (
   		SELECT
   		(4 + tpl_hdr_size + tpl_data_size + (2 * ma)
   		- CASE WHEN tpl_hdr_size % ma = 0 THEN ma ELSE tpl_hdr_size % ma END
   		- CASE WHEN ceil(tpl_data_size)::int % ma = 0 THEN ma ELSE ceil(tpl_data_size)::int % ma END
   		) AS tpl_size,
   		(heappages + toastpages) AS tblpages,
   		reltuples,
   		toasttuples,
   		bs,
   		page_hdr,
   		schemaname,
   		tblname,
   		fillfactor,
   		ma,
   		tpl_hdr_size,
   		tpl_data_size
   		FROM (
   		SELECT
   			ns.nspname AS schemaname,
   			tbl.relname AS tblname,
   			tbl.reltuples,
   			tbl.relpages AS heappages,
   			COALESCE(toast.relpages, 0) AS toastpages,
   			COALESCE(toast.reltuples, 0) AS toasttuples,
   			COALESCE(substring(array_to_string(tbl.reloptions, ' ') FROM 'fillfactor=([0-9]+)')::smallint, 100) AS fillfactor,
   			current_setting('block_size')::numeric AS bs,
   			CASE
   			WHEN version() ~ 'mingw32|64-bit|x86_64|ppc64|ia64|amd64' THEN 8
   			ELSE 4
   			END AS ma,
   			24 AS page_hdr,
   			23 +
   			CASE WHEN MAX(COALESCE(s.null_frac, 0)) > 0 THEN (7 + count(s.attname)) / 8 ELSE 0 END +
   			CASE WHEN bool_or(att.attname = 'oid' AND att.attnum < 0) THEN 4 ELSE 0 END AS tpl_hdr_size,
   			SUM((1 - COALESCE(s.null_frac, 0)) * COALESCE(s.avg_width, 0)) AS tpl_data_size
   		FROM pg_attribute AS att
   		JOIN pg_class AS tbl ON att.attrelid = tbl.oid
   		JOIN pg_namespace AS ns ON ns.oid = tbl.relnamespace
   		LEFT JOIN pg_stats AS s ON s.schemaname = ns.nspname AND s.tablename = tbl.relname AND s.attname = att.attname
   		LEFT JOIN pg_class AS toast ON tbl.reltoastrelid = toast.oid
   		WHERE NOT att.attisdropped
   			AND tbl.relkind IN ('r', 'm')
   		GROUP BY 1, 2, 3, 4, 5, 6, 7, 8, 9
   		) AS inner_subquery
   	) AS mid_subquery
   	) AS bloat_subquery
   	WHERE schemaname = 'public'
   	ORDER BY schema_name, table_name;
   metrics:
   	- schema_name:
   		usage: "LABEL"
   		description: "Schema name"
   	- table_name:
   		usage: "LABEL"
   		description: "Table name"
   	- bloat_size_kb:
   		usage: "GAUGE"
   		description: "Bloat size in KB"
   	- bloat_size_percent:
   		usage: "GAUGE"
   		description: "Bloat size as a percentage"
   

3. 设置数据源环境变量。

   export DATA_SOURCE_NAME="postgresql://postgres_exporter:<password>@<POSTGRES_HOST>:5432/postgres?sslmode=require"
   

   注：<POSTGRES_HOST> 是主要端点 FQDN。您可以在 OCI 控制台的 PostgreSQL 集群详细信息页面中找到它。

4. 启动 PostgreSQL 导出器。

   使用以下命令使用定制 queries.yaml 文件运行 PostgreSQL 导出程序。这将在端口 9187 上启动出口商，并将输出记录到 exporter.log。

   nohup /usr/local/bin/postgres_exporter --extend.query-path=/home/opc/queries.yaml --web.listen-address=":9187" > exporter.log 2>&1 &
   

   注：要使事情在重新引导后继续运行，请考虑使用 systemd 设置服务

5. 验证出口方。

   curl http://localhost:9187/metrics
   

任务 3：安装和配置 Prometheus

1. Install Prometheus 。

   wget https://github.com/prometheus/prometheus/releases/download/v2.52.0/prometheus-2.52.0.linux-amd64.tar.gz
   
   tar -xvf prometheus-2.52.0.linux-amd64.tar.gz
   
   sudo mv prometheus-2.52.0.linux-amd64/prometheus /usr/local/bin/
   sudo mv prometheus-2.52.0.linux-amd64/promtool /usr/local/bin/
   

2. 创建 Prometheus 配置。

   创建包含以下内容的 /etc/prometheus/prometheus.yml 文件。

   global:
   scrape_interval: 15s
   
   scrape_configs:
   - job_name: 'postgres_exporter'
   	static_configs:
   	- targets: ['127.0.0.1:9187']
   

3. 启动 Prometheus 。

   nohup prometheus --config.file=/etc/prometheus/prometheus.yml > prometheus.log 2>&1 &
   

   注：要使事情在重新引导后继续运行，请考虑使用 systemd 设置服务

4. 验证目标。

   访问这里：http://<instance_ip>:9090/targets。

   

任务 4：安装和配置 Grafana

1. 安装 Grafana 。

   sudo yum install -y https://dl.grafana.com/oss/release/grafana-10.4.2-1.x86_64.rpm
   sudo systemctl enable grafana-server
   sudo systemctl start grafana-server
   

2. 登录 Grafana 。

   转到以下 URL：http://<instance_ip>:3000，然后以 admin 格式输入 Username ，以 admin 格式输入 Password 。

3. 在 Grafana 中添加数据源。

   1. 转到连接、数据源，然后单击添加数据源。

   2. 选择 Prometheus 。

   3. 输入 URL 作为 http://localhost:9090。

   

4. 导入 PostgreSQL 仪表盘。

   1. 选择导入仪表盘。

   2. 从 Grafana 仪表盘导入 9628 仪表盘 ID。

   

任务 5：使用 Grafana 中的 PostgreSQL 指标了解 OCI 数据库

以下图像显示了最终仪表盘。

• 查看从 Grafana 中的 Explore（浏览）选项卡中的定制 queries.yaml 文件公开的度量。

• 为以下项添加可视化：

  • 最慢的 SQL 语句。
  • 排名前的用户。
  • 有效查询。
  • 顶级客户机 IP。
  • 查询频率（最称为查询）。

任务 6：使用 Grafana 中的 PostgreSQL Bloat 度量为 OCI 数据库设置预警

1. 收集浮点度量。

   • 基于 PostgreSQL 系统目录视图（例如 pg_class、pg_namespace、pg_attribute 和 pg_stats），使用定制 SQL 查询来估计表膨胀。

   • 此查询通过 PostgreSQL 导出器作为定制度量公开，方法是将其添加到 queries.yaml 文件（如任务 2.2 所示），随后被 Prometheus 报废。

2. 配置预警规则。

   1. 导航到预警，然后单击 Grafana 中的预警规则。

   2. 单击 + 新建预警规则。

      

   3. 为预警规则输入名称。

   4. 选择 Prometheus 作为数据源。

   5. 在定义查询和预警条件部分中，输入以下信息。

      • 搜索 table_bloat_bloat_size_percent 度量。

      • 在预警条件构建器中，输入以下信息。

        • 设置输入：选择 A 。

        • 条件：选择 IS ABOVE 30 。

        注：您可以单击预览以验证预警是否会在当前条件下触发。

        

   6. 在设置评估行为部分中，输入以下信息。

      1. 单击 + 新建评估组。

      2. 评估组：输入 postgres-bloat-alerts。

      3. 评估间隔：选择 5m（这定义了 Grafana 评估预警规则的频率）。

      4. 待定期间：设置为 5m（预警在触发前必须连续 5 分钟保持不变）。这应等于或大于评估间隔 )。

   7. 单击保存规则和退出以激活预警。

      

      

3. Configure Email Alerting through SMTP (Gmail) 。

   要在 Grafana 中为警报启用电子邮件通知，请配置 grafana.ini 文件的 smtp 部分。

   1. 编辑已安装 Grafana 的 OCI 计算实例中的 Grafana 配置文件。

      sudo nano /etc/grafana/grafana.ini
      

   2. 取消注释并更新包含以下内容的 smtp 部分。

      [smtp]
      enabled = true
      host = smtp.gmail.com:587
      user = [e-mail]
      password = ***************
      skip_verify = true
      from_address = [e-mail]
      from_name = Grafana
      ehlo_identity = localhost
      startTLS_policy = OpportunisticStartTLS
      enable_tracing = false
      

      注：要使用 Gmail，必须启用两步验证并从 Google 帐户设置生成应用程序密码。请勿使用您的常规 Gmail 密码。

   3. 保存配置后，使用以下命令重新启动 Grafana 以应用更改。

      sudo systemctl restart grafana-server
      

4. 设置警报通知的联系方式。

   联系方式定义在触发规则（如电子邮件、Slack、Webhook 等）时，Grafana 在何处以及如何发送警报。

   1. 导航到预警，然后单击 Grafana 中的联系点。

   2. 单击 + 新建联系方式以创建联系方式，或者选择并编辑现有联系方式。

   3. 要配置电子邮件联系方式，请输入以下信息。

      • 名称：输入联系方式名称（例如 grafana-default-email）。

      • 集成：选择电子邮件。

      • 地址：输入一个或多个电子邮件地址。

   4. 单击测试以验证电子邮件传送。

   5. 单击保存联系方式。

   

5. Configure Notification Policy 。

   Grafana 中的通知策略控制警报如何以及何时路由到联系点。设置联系方式后，必须创建通知策略以将警报连接到该联系方式。

   1. 导航到预警，然后单击 Grafana 中的通知策略。

   2. 在默认策略或 + 新策略上单击编辑。

   3. 在默认联系方式下，选择您的联系人 (grafana-default-email)。

   4. 在分组依据下，您可以根据标签值对警报进行分组，例如：

      • grafana_folder

      • alertname

   5. 单击计时选项并输入以下信息。

      • 组等待：选择 30s ，这是在组中发送第一个预警之前等待的时间。

      • 组间隔：选择 5m ，这是分组警报的批处理之间的时间。

      • 重复间隔：选择 30m ，在此时间之后，如果仍在触发，将重新发送同一预警。

   6. 单击更新默认策略以保存。

   

6. Trigger and Verify Alert 。

   1. 模拟阈值违规（可选）。

      要测试警报触发，您可以：

      • 临时降低警报阈值（例如，设置 bloat_size_percent > 0）。

        或者，

      • 通过对大型测试表执行重复的 UPDATE 或 DELETE 操作来引入 bloat。

   2. 等待评估。

      Grafana 按您定义的评估间隔（例如，每 5 分钟）检查警报条件。如果条件在暂挂期间为真（例如 5 分钟），则将触发预警。

   3. 电子邮件传送检查。

      触发后，将通过配置的 SMTP 联系点（例如 Gmail）发送警报。选中来自 Grafana 的通知电子邮件的收件人地址收件箱。

      

   4. Grafana UI 反馈。

      导航到预警，然后单击预警规则以查看预警状态。您将在预警规则页中看到触发等状态指示符。

      

相关链接

• OCI Database with PostgreSQL

• 启用数据库的扩展

• Prometheus

• Grafana

• Grafana PostgreSQL 数据库社区仪表盘

• 数据库性能的无声杀手：在 PostgreSQL 中解密表浮动

确认

• 作者 - Irine Benoy（开源数据黑带）

更多学习资源

通过 docs.oracle.com/learn 浏览其他实验室，或者通过 Oracle Learning YouTube 频道访问更多免费学习内容。此外，请访问 education.oracle.com/learning-explorer 以成为 Oracle Learning Explorer。

有关产品文档，请访问 Oracle 帮助中心。

---

标题和版权信息

Monitor an OCI Database with PostgreSQL using Prometheus and Grafana

G39872-01

Copyright ©2025, Oracle and/or its affiliates.