Surveillez une base de données Oracle Cloud Infrastructure Database avec PostgreSQL à l'aide de Prometheus et de Grafana

Introduction

PostgreSQL est l'une des bases de données les plus populaires parmi les développeurs d'applications. Au-delà de l'élimination des frais généraux de gestion qu'offre Oracle Cloud Infrastructure Database avec PostgreSQL (OCI Database with PostgreSQL), il va également plus loin en matière d'implémentation d'une facilité de surveillance. Prometheus et Grafana sont une paire très populaire lorsqu'il s'agit de surveiller les performances des applications pour le stockage et la visualisation des données de séries chronologiques respectivement.

Dans ce tutoriel, nous allons montrer comment surveiller une base de données OCI Database with PostgreSQL en extrayant des mesures de niveau requête à l'aide de l'exportateur PostgreSQL, en les grattant avec Prometheus, en les visualisant dans Grafana et en configurant des alertes pour les mesures bloat.

Présentation de l'architecture

Le schéma suivant illustre l'approche globale. Les composants suivants sont utilisés :

OCI Database with PostgreSQL.
Postgres Export pour afficher les mesures.
Prometheus pour gratter les mesures.
Grafana pour visualiser les mesures.

Vue d'architecture

Remarque : bien que l'architecture affiche trois sous-réseaux pour plus de clarté, ce tutoriel en utilise seulement deux, et l'hôte de saut est également la machine virtuelle utilisée pour déployer Prometheus et Grafana.

Objectifs

Extrayez les mesures PostgreSQL avec l'exportateur PostgreSQL pour Prometheus.
Ingérez ces mesures dans Prométhée.
Visualisez les mesures à l'aide des tableaux de bord Grafana.
Configurez des alertes pour détecter le blocage de la table à l'aide de mesures exposées via l'exportateur PostgreSQL.

Prérequis

Accès à une location OCI avec une base de données OCI Database with PostgreSQL. Pour plus d'informations, reportez-vous à Connexion à Oracle Cloud Infrastructure Database avec PostgreSQL à l'aide de DBeaver.
Instance OCI Compute (hôte de vidage) dans un sous-réseau public du même VCN que la base de données OCI Database with PostgreSQL.
Liste de sécurité OCI : autorisez les ports entrants 9090, 9187 et 3000.

Exécutez les commandes suivantes pour configurer le pare-feu sur l'instance.

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

Tâche 1 : activation des extensions de surveillance sur OCI Database with PostgreSQL

Activez l'extension pg_stat_statements.

Ajoutez pg_stat_statements sur OCI Database with PostgreSQL via la console OCI. Pour plus d'informations, reportez-vous à Activation des extensions pour une base de données.
Créer un utilisateur de surveillance.

Exécutez les requêtes suivantes pour créer et connecter un utilisateur à l'aide de DBeaver via l'hôte Jump.
```
CREATE USER postgres_exporter WITH PASSWORD '<password>';
GRANT CONNECT ON DATABASE postgres TO postgres_exporter;
GRANT pg_monitor TO postgres_exporter;
```
Remarque : l'extension pg_stat_io est disponible à partir d'OCI Database with PostgreSQL v16.

Tâche 2 : installer l'exportateur PostgreSQL pour Prometheus

Remarque : exécutez les commandes suivantes sur votre instance OCI Compute.

Installez PostgreSQL Export.

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/

Créez un fichier queries.yaml personnalisé.

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"

Définissez la variable d'environnement de source de données.
```
export DATA_SOURCE_NAME="postgresql://postgres_exporter:<password>@<POSTGRES_HOST>:5432/postgres?sslmode=require"
```
Remarque : <POSTGRES_HOST> est le nom de domaine qualifié complet de l'adresse principale. Vous pouvez le trouver sur la page de détails du cluster PostgreSQL dans la console OCI.
Démarrez l'export PostgreSQL.

Exécutez l'exportateur PostgreSQL avec le fichier queries.yaml personnalisé à l'aide de la commande suivante. L'exportateur démarre alors sur le port 9187 et consigne la sortie dans exporter.log.
```
nohup /usr/local/bin/postgres_exporter --extend.query-path=/home/opc/queries.yaml --web.listen-address=":9187" > exporter.log 2>&1 &
```
Remarque : pour continuer à fonctionner après la réinitialisation, envisagez de configurer le service à l'aide de systemd.
Vérifiez l'exportateur.
```
curl http://localhost:9187/metrics
```

Tâche 3 : installer et configurer Prometheus

Installez 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/

Créer une configuration de Prométhée.

Créez le fichier /etc/prometheus/prometheus.yml avec le contenu suivant.

global:
scrape_interval: 15s

scrape_configs:
- job_name: 'postgres_exporter'
	static_configs:
	- targets: ['127.0.0.1:9187']

Démarrer Prométhée.
```
nohup prometheus --config.file=/etc/prometheus/prometheus.yml > prometheus.log 2>&1 &
```
Remarque : pour continuer à fonctionner après la réinitialisation, envisagez de configurer le service à l'aide de systemd.
Vérifier les cibles.

Visitez ici : http://<instance_ip>:9090/targets.

Tâche 4 : installer et configurer Grafana

Installez 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

Connexion à Grafana.

Accédez à l'URL suivante : http://<instance_ip>:3000 et entrez Nom utilisateur dans admin et Mot de passe dans admin.
Ajoutez la source de données dans Grafana.
1. Accédez à Connexions, à Sources de données et cliquez sur Ajouter une source de données.
2. Sélectionnez Prometheus.
3. Entrez URL comme http://localhost:9090.
Importer le tableau de bord PostgreSQL.
1. Sélectionnez Importer un tableau de bord.
2. Importez l'ID de tableau de bord 9628 à partir des tableaux de bord Grafana.

Tâche 5 : découvrir OCI Database with PostgreSQL dans Grafana

Les images suivantes présentent les tableaux de bord finaux.

Exemple de tableau de bord

Visualisez les mesures affichées à partir de votre fichier queries.yaml personnalisé dans l'onglet Explorer de Grafana.
Ajoutez des visualisations pour :
- Instructions SQL les plus lentes.
- Utilisateurs principaux.
- Requêtes actives.
- Principales adresses IP client.
- Fréquence des requêtes (la plupart des requêtes appelées).

Tâche 6 : configuration d'une alerte pour OCI Database with PostgreSQL dans Grafana

Collecter la mesure Bloat.
- Une requête SQL personnalisée a été utilisée pour estimer le volume de la table, en fonction des vues du catalogue système PostgreSQL telles que pg_class, pg_namespace, pg_attribute et pg_stats.
- Cette requête a été exposée en tant que mesure personnalisée via un exportateur PostgreSQL en l'ajoutant au fichier queries.yaml, comme indiqué dans la tâche 2.2, et a ensuite été grattée par Prométhée.
Configurer une règle d'alerte.
1. Accédez à Alerte et cliquez sur Règles d'alerte dans Grafana.
2. Cliquez sur + Nouvelle règle d'alertes.
3. Entrez le nom de votre règle d'alerte.
4. Sélectionnez Prometheus en tant que source de données.
5. Dans la section Définir la condition de requête et d'alerte, entrez les informations suivantes :
  - Recherchez la mesure table_bloat_bloat_size_percent.
  - Dans le générateur de condition d'alerte, entrez les informations suivantes.
    - Définir l'entrée : sélectionnez A.
    - Condition : sélectionnez IS ABOVE 30.
    Remarque : vous pouvez cliquer sur Aperçu pour vérifier si l'alerte se déclencherait dans les conditions actuelles.
6. Dans la section Définir le comportement d'évaluation, entrez les informations suivantes :
  1. Cliquez sur + Nouveau groupe d'évaluation.
  2. Groupe d'évaluation : entrez postgres-bloat-alerts.
  3. Intervalle d'évaluation : sélectionnez 5m (cela définit la fréquence d'évaluation de la règle d'alerte par Grafana).
  4. Période en attente : définissez cette valeur sur 5m (l'alerte doit rester vraie pendant 5 minutes continues avant son déclenchement). Cette valeur doit être supérieure ou égale à l'intervalle d'évaluation).
7. Cliquez sur Enregistrer la règle et Quitter pour activer l'alerte.
Configuration des alertes par e-mail via SMTP (Gmail).

Pour activer les notifications par courriel pour les alertes dans Grafana, configurez la section smtp de votre fichier grafana.ini.
1. Modifiez le fichier de configuration Grafana dans l'instance OCI Compute où vous avez installé Grafana.
```
sudo nano /etc/grafana/grafana.ini
```
2. Annulez le commentaire et mettez à jour la section smtp avec le contenu suivant.
```
[smtp]
enabled = true
host = smtp.gmail.com:587
user = your_email@gmail.com
password = ***************
skip_verify = true
from_address = your_email@gmail.com
from_name = Grafana
ehlo_identity = localhost
startTLS_policy = OpportunisticStartTLS
enable_tracing = false
```
  Remarque : pour utiliser Gmail, vous devez activer la vérification en 2 étapes et générer un mot de passe d'application à partir des paramètres de votre compte Google. N'utilisez pas votre mot de passe Gmail standard.
3. Après avoir enregistré la configuration, redémarrez Grafana pour appliquer les modifications à l'aide de la commande suivante.
```
sudo systemctl restart grafana-server
```
Configurer des points de contact pour les notifications d'alerte.

Les points de contact définissent où et comment Grafana envoie des alertes lorsqu'une règle est déclenchée, par exemple un e-mail, Slack, un webhook, etc.
1. Accédez à Alerte et cliquez sur Points de contact dans Grafana.
2. Cliquez sur + Nouveau point de contact pour en créer un ou sélectionnez et modifiez un point de contact existant.
3. Pour configurer un point de contact par courriel, entrez les informations suivantes.
  - Nom : entrez le nom du point de contact (par exemple, grafana-default-email).
  - Intégration : sélectionnez Courriel.
  - Adresses : entrez une ou plusieurs adresses électroniques.
4. Cliquez sur Tester pour vérifier la remise des courriels.
5. Cliquez sur Enregistrer le point de contact.
Configurer la stratégie de notification.

Les stratégies de notification dans Grafana contrôlent comment et quand les alertes sont acheminées vers les points de contact. Après avoir configuré un point de contact, vous devez créer une stratégie de notification pour connecter les alertes à ce point de contact.
1. Accédez à Alerte et cliquez sur Stratégies de notification dans Grafana.
2. Cliquez sur Modifier dans la stratégie par défaut ou sur + Nouvelle stratégie.
3. Sous Point de contact par défaut, sélectionnez votre contact (grafana-default-email).
4. Sous Regrouper par, vous pouvez regrouper les alertes en fonction de valeurs de libellé telles que :
  - grafana_folder
  - alertname
5. Cliquez sur Options de programmation et entrez les informations suivantes.
  - Attente de groupe : sélectionnez 30s. Il s'agit du temps d'attente avant l'envoi de la première alerte d'un groupe.
  - Intervalle de groupe : sélectionnez 5m. Il s'agit de la durée entre les lots d'alertes groupées.
  - Intervalle de répétition : sélectionnez 30m. Il s'agit de l'heure au-delà de laquelle la même alerte est renvoyée si elle est encore déclenchée.
6. Cliquez sur Mettre à jour la stratégie par défaut pour l'enregistrer.
Déclencher et vérifier l'alerte.
1. Simuler une violation de seuil (facultatif).
  
  Pour tester le déclenchement d'une alerte, vous pouvez :
  - Abaissez temporairement le seuil d'alerte (par exemple, définissez bloat_size_percent > 0).
    
    Ou,
  - Introduisez le gonflement en effectuant des opérations UPDATE ou DELETE répétées sur une table de test volumineuse.
2. Attendre l'évaluation.
  
  Grafana vérifie les conditions d'alerte à l'intervalle d'évaluation défini (par exemple, toutes les 5 minutes). Si la condition est vraie pour la période en attente (par exemple, 5 minutes), l'alerte se déclenche.
3. Vérification de la remise des courriels.
  
  Lorsqu'elle est déclenchée, l'alerte est envoyée via le point de contact SMTP configuré (par exemple, Gmail). Vérifiez la boîte de réception de l'adresse du destinataire pour un courriel de notification de Grafana.
4. Commentaires sur l'interface utilisateur Grafana.
  
  Accédez à Alerte et cliquez sur Règles d'alerte pour afficher le statut de l'alerte. Des indicateurs d'état tels que Exécution sont affichés sur la page Règles d'alerte.

Accusés de réception

Auteur - Irine Benoy (ceinture noire de données open source)

Ressources de formation supplémentaires

Explorez d'autres ateliers sur le site docs.oracle.com/learn ou accédez à d'autres contenus d'apprentissage gratuits sur le canal Oracle Learning YouTube. En outre, visitez le site education.oracle.com/learning-explorer pour devenir un explorateur Oracle Learning.

Pour obtenir de la documentation sur le produit, consultez Oracle Help Center.

Informations relatives au titre et au copyright

Monitor an OCI Database with PostgreSQL using Prometheus and Grafana

G39867-01