使用 Prometheus 和 Grafana 使用 PostgreSQL 監控 Oracle Cloud Infrastructure Database

簡介

PostgreSQL 是應用程式開發人員之間最受歡迎的資料庫之一。除了 Oracle Cloud Infrastructure Database with PostgreSQL (OCI Database with PostgreSQL) 提供的管理開銷之外，導入易於監控也更進一步。Prometheus 和 Grafana 是非常受歡迎的配對，分別用於監控時間序列資料儲存和視覺化的應用程式效能。

在本教學課程中，我們將示範如何使用 PostgreSQL 匯出程式擷取查詢層級度量、使用 Prometheus 刮除、在 Grafana 視覺化呈現及設定浮點數度量的警示，以使用 PostgreSQL 監控 OCI 資料庫。

架構概要

下列綱要描述整體方法。使用的元件如下：

• OCI Database with PostgreSQL。
• Postgres Exporter 可顯示測量結果。
• Prometheus 刮除度量。
• Grafana 可將指標視覺化。

注意：雖然架構顯示三個子網路供明確使用，但本教學課程只使用兩個子網路，而跳躍主機也是用來部署 Prometheus 和 Grafana 的 VM。

目標

• 使用 Prometheus 的 PostgreSQL 匯出器擷取 PostgreSQL 度量。

• 在 Prometheus 中擷取這些度量。

• 使用 Grafana 儀表板將指標視覺化。

• 設定警示，使用透過 PostgreSQL 匯出器顯示的測量結果偵測表格浮點。

必備條件

• 使用已佈建的 OCI 資料庫搭配 PostgreSQL 叢集存取 OCI 租用戶。如需詳細資訊，請參閱使用 DBeaver 連線至 Oracle Cloud Infrastructure Database 與 PostgreSQL 。

• VCN 中公用子網路的 OCI Compute 執行處理 (跳躍主機) 與 PostgreSQL 中的 OCI 資料庫相同。

• 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 主控台，使用 PostgreSQL 組態在 OCI 資料庫上新增 pg_stat_statements。如需詳細資訊，請參閱啟用資料庫的擴充功能。

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 Compute 執行處理上執行下列命令。

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. 安裝 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，輸入密碼作為 admin。

3. 將資料來源新增至 Grafana 。

   1. 前往連線、資料來源，然後按一下新增資料來源。

   2. 選取 Prometheus 。

   3. 輸入 URL 作為 http://localhost:9090。

   

4. 匯入 PostgreSQL 儀表板。

   1. 選取匯入儀表板。

   2. 從 Grafana 儀表板匯入 9628 儀表板 ID。

   

任務 5：使用 Grafana 中的 PostgreSQL 指標探索 OCI 資料庫

下列影像顯示最終儀表板。

• 從 Grafana 的瀏覽頁籤檢視自訂 queries.yaml 檔案公開的指標。

• 新增視覺化項目：

  • 最慢的 SQL 敘述句排行榜。
  • 常用使用者。
  • 作用中查詢。
  • 常用從屬端 IP。
  • 查詢頻率 (大多數稱為查詢)。

工作 6：在 Grafana 中使用 PostgreSQL Bloat 指標設定 OCI 資料庫的警示

1. 收集 Bloat 測量結果。

   • 自訂 SQL 查詢是用來根據 PostgreSQL 系統目錄檢視 (例如 pg_class、pg_namespace、pg_attribute 和 pg_stats) 來預估表格浮點數。

   • 此查詢會透過 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. 透過 SMTP (Gmail) 設定電子郵件警示。

   若要在 Grafana 中啟用警示的電子郵件通知，請設定 grafana.ini 檔案的 smtp 區段。

   1. 在您已安裝 Grafana 的 OCI Compute 執行處理中編輯 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 帳戶設定值產生 App 密碼。請勿使用一般 Gmail 密碼。

   3. 儲存組態之後，請使用下列命令重新啟動 Grafana 以套用變更。

      sudo systemctl restart grafana-server
      

4. 設定警示通知的聯絡點。

   聯絡點定義當觸發規則時，Grafana 傳送警示的位置和方式，例如電子郵件、Slack、Webhook 等等。

   1. 導覽至警示，然後按一下 Grafana 中的聯絡點。

   2. 按一下 + 新增聯絡點以建立聯絡點，或選取並編輯現有聯絡點。

   3. 若要設定電子郵件聯絡點，請輸入下列資訊。

      • 名稱：輸入聯絡點名稱 (例如，grafana-default-email)。

      • 整合：選取電子郵件。

      • 地址：輸入一或多個電子郵件地址。

   4. 按一下測試以驗證電子郵件傳遞。

   5. 按一下儲存聯絡點。

   

5. 設定通知原則。

   Grafana 中的通知原則可控制警示遞送至聯絡點的方式與時間。設定聯絡點後，您必須建立通知政策，以將警示連接至該聯絡點。

   1. 瀏覽至警示，然後按一下 Grafana 中的通知原則。

   2. 按一下預設原則上的編輯或 + 新原則。

   3. 在預設聯絡點下，選取您的聯絡資訊 (grafana-default-email)。

   4. 在分組依據下，您可以根據標籤值將警示分組，例如：

      • grafana_folder

      • alertname

   5. 按一下時機選項，然後輸入下列資訊。

      • 群組等待：選取 30s ，這是傳送群組中第一個警示之前等待的時間。

      • 群組間隔：選取 5m ，這是分組警示批次之間的時間。

      • 重複間隔：選取 30m ，這是在觸發時重新傳送相同警示的時間。

   6. 按一下更新預設原則即可儲存。

   

6. 觸發並驗證警示。

   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 Help Center 。

---

標題與著作權資訊

Monitor an OCI Database with PostgreSQL using Prometheus and Grafana

G39873-01

Copyright ©2025, Oracle and/or its affiliates.