PrometheusおよびGrafanaを使用したPostgreSQLによるOracle Cloud Infrastructure Databaseの監視

はじめに

PostgreSQLは、アプリケーション開発者の間で最も人気のあるデータベースの1つです。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エクスポータ: メトリックを公開します。
• Prometheusはメトリックをスクレイプします。
• Grafana: メトリックをビジュアル化します。

ノート:アーキテクチャにはわかりやすくするために3つのサブネットが表示されますが、このチュートリアルでは2つのみを使用し、ジャンプ・ホストはPrometheusおよびGrafanaのデプロイに使用されるVMでもあります。

目的

• PrometheusのPostgreSQLエクスポータを使用して、PostgreSQLメトリックを抽出します。

• これらのメトリックをPrometheusに取り込みます。

• Grafanaダッシュボードを使用してメトリックをビジュアル化します。

• PostgreSQLエクスポータを介して公開されたメトリックを使用して、表の浮動を検出するアラートを設定します。

前提条件

• Access to an OCI tenancy with a provisioned OCI Database with PostgreSQL cluster.詳細は、DBeaverを使用したPostgreSQLを使用したOracle Cloud Infrastructure Databaseへの接続を参照してください。

• OCI Compute instance (jump host) in a public subnet in the same VCN as the OCI Database with 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: PostgreSQLOCI Database with PostgreSQLを使用したOCI Databaseでの拡張の監視の有効化

1. pg_stat_statements拡張機能を有効にします。

   OCIコンソールを使用して、OCI Database with PostgreSQL構成に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;
   

   Note: pg_stat_io extension is available from OCI Database with PostgreSQL v16 onwards.

タスク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. 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をインポートします。

   

Task 5: Explore OCI Database with PostgreSQL Metrics in Grafana

次の図は、最終的なダッシュボードを示しています。

• Grafanaの「探索」タブから、カスタムqueries.yamlファイルから公開されたメトリックを表示します。

• 次のビジュアライゼーションを追加します。

  • 最も遅いSQL文。
  • 最上位ユーザー。
  • アクティブなクエリー。
  • 上位クライアントIP。
  • 問合せ頻度(最もコールされた問合せ)。

Task 6: Set Up Alert for OCI Database with PostgreSQL Bloat Metrics in Grafana

1. Bloatメトリックの収集。

   • カスタムSQL問合せを使用して、pg_class、pg_namespace、pg_attribute、pg_statsなどのPostgreSQLシステム・カタログ・ビューに基づいて表の浮動小数点数を推定しました。

   • この問合せは、タスク2.2に示すように、queries.yamlファイルに追加することでPostgreSQLエクスポータを介してカスタム・メトリックとして公開され、その後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分間連続してtrueのままにする必要があります)。これは、評価間隔以上である必要があります)。

   7. 「ルールの保存」および「終了」をクリックして、アラートをアクティブ化します。

      

      

3. 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を使用するには、2ステップ検証を有効にして、Googleアカウント設定からアプリケーション・パスワードを生成する必要があります。通常のGmailパスワードは使用しないでください。

   3. 構成を保存した後、次のコマンドを使用してGrafanaを再起動し、変更を適用します。

      sudo systemctl restart grafana-server
      

4. アラート通知の連絡先の設定。

   連絡先は、電子メール、Slack、Webフックなどのルールがトリガーされたときに、Grafanaがアラートを送信する場所と方法を定義します。

   1. 「アラート」にナビゲートし、Grafanaで「連絡先」をクリックします。

   2. 「+新規連絡先」をクリックして連絡先を作成するか、既存の連絡先を選択して編集します。

   3. 「Eメール連絡先」を構成するには、次の情報を入力します。

      • 名前:連絡先名(grafana-default-emailなど)を入力します。

      • 統合: 「電子メール」を選択します。

      • アドレス: 1つ以上の電子メール・アドレスを入力します。

   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分など)の条件がtrueの場合、アラートが起動します。

   3. 電子メール配信チェック。

      トリガーされると、構成されたSMTP連絡先(Gmailなど)を介してアラートが送信されます。Grafanaからの通知電子メールの受信者アドレスの受信ボックスをチェックします。

      

   4. Grafana UIフィードバック。

      「アラート」にナビゲートし、「アラート・ルール」をクリックしてアラート・ステータスを表示します。「アラート・ルール」ページに、「起動中」などのステータス・インジケータが表示されます。

      

関連リンク

• OCI Database with PostgreSQL

• データベースの拡張の有効化

• Prometheus

• Grafana

• Grafana PostgreSQLデータベース・コミュニティ・ダッシュボード

• DBパフォーマンスのサイレント・キラー: 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

G39869-01

Copyright ©2025, Oracle and/or its affiliates.