Control de una instancia de Oracle Cloud Infrastructure Database con PostgreSQL mediante Prometheus y Grafana

Introducción

PostgreSQL es una de las bases de datos más populares entre los desarrolladores de aplicaciones. Más allá de la eliminación de los gastos generales de gestión que ofrece Oracle Cloud Infrastructure Database con PostgreSQL (OCI Database with PostgreSQL), también va un paso más allá cuando se trata de implantar una facilidad de control. Prometheus y Grafana son un par muy popular cuando se trata de supervisar el rendimiento de las aplicaciones para el almacenamiento y la visualización de datos de series temporales, respectivamente.

En este tutorial, mostraremos cómo supervisar una base de datos de OCI Database with PostgreSQL mediante la extracción de métricas de nivel de consulta mediante el exportador PostgreSQL, su desguace con Prometheus, su visualización en Grafana y la configuración de alertas para métricas bloat.

Visión general de la arquitectura

El siguiente esquema muestra el enfoque general. Se utilizan los siguientes componentes:

OCI Database with PostgreSQL.
Exportador de Postgres para exponer métricas.
Prometheus para desechar métricas.
Grafana para visualizar métricas.

Vista Arquitectura

Nota: Aunque la arquitectura muestra tres subredes para mayor claridad, este tutorial utiliza solo dos, y el host de salto también es la VM utilizada para desplegar Prometheus y Grafana.

Objetivos

Extraiga las métricas PostgreSQL con el exportador PostgreSQL de Prometheus.
Ingerir esas métricas en Prometeo.
Visualice las métricas mediante los paneles de control de Grafana.
Configure alertas para detectar el bloat de tablas mediante las métricas expuestas a través del exportador PostgreSQL.

Requisitos

Acceso a un arrendamiento de OCI con un cluster de OCI Database with PostgreSQL aprovisionado. Para obtener más información, consulte Conexión a Oracle Cloud Infrastructure Database con PostgreSQL mediante DBeaver.
Instancia informática de OCI (host de salto) en una subred pública en la misma VCN que OCI Database with PostgreSQL.
Lista de seguridad de OCI: permite los puertos de entrada 9090, 9187, 3000.

Ejecute los siguientes comandos para configurar el firewall en la instancia.

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

Tarea 1: Activación de las extensiones de supervisión en OCI Database with PostgreSQL

Active la extensión pg_stat_statements.

Agregue pg_stat_statements en las configuraciones de OCI Database with PostgreSQL a través de la consola de OCI. Para obtener más información, consulte Activación de extensiones para una base de datos.
Crear Usuario de Supervisión.

Ejecute las siguientes consultas para crear y conectar el usuario mediante DBeaver mediante el host de salto.
```
CREATE USER postgres_exporter WITH PASSWORD '<password>';
GRANT CONNECT ON DATABASE postgres TO postgres_exporter;
GRANT pg_monitor TO postgres_exporter;
```
Nota: La extensión pg_stat_io está disponible desde OCI Database with PostgreSQL v16 en adelante.

Tarea 2: Instalación del exportador PostgreSQL para Prometheus

Nota: Ejecute los siguientes comandos en la instancia de OCI Compute.

Instale PostgreSQL Exporter.

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/

Cree queries.yaml personalizado.

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"

Defina la variable de entorno de origen de datos.
```
export DATA_SOURCE_NAME="postgresql://postgres_exporter:<password>@<POSTGRES_HOST>:5432/postgres?sslmode=require"
```
Nota: <POSTGRES_HOST> es el FQDN de punto final principal. Puede encontrarlo en la página de detalles del cluster PostgreSQL en la consola de OCI.
Inicie el exportador PostgreSQL.

Ejecute el exportador PostgreSQL con el archivo queries.yaml personalizado mediante el siguiente comando. Esto inicia el exportador en el puerto 9187 y registra la salida en exporter.log.
```
nohup /usr/local/bin/postgres_exporter --extend.query-path=/home/opc/queries.yaml --web.listen-address=":9187" > exporter.log 2>&1 &
```
Nota: Para mantener las cosas en ejecución después del reinicio, considere la posibilidad de configurar el servicio mediante systemd
Verifique el exportador.
```
curl http://localhost:9187/metrics
```

Tarea 3: Instalación y configuración de Prometheus

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

Crear configuración de Prometheus.

Cree el archivo /etc/prometheus/prometheus.yml con el siguiente contenido.

global:
scrape_interval: 15s

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

Iniciar Prometheus.
```
nohup prometheus --config.file=/etc/prometheus/prometheus.yml > prometheus.log 2>&1 &
```
Nota: Para mantener las cosas en ejecución después del reinicio, considere la posibilidad de configurar el servicio mediante systemd
Verificar Destinos.

Visite aquí: http://<instance_ip>:9090/targets.

Tarea 4: Instalación y configuración de Grafana

Instale 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

Inicie sesión en Grafana.

Vaya a la siguiente URL: http://<instance_ip>:3000 e introduzca Nombre de usuario como admin y Contraseña como admin.
Agregue el origen de datos en Grafana.
1. Vaya a Conexiones, Orígenes de datos y haga clic en Agregar origen de datos.
2. Seleccione Prometheus.
3. Introduzca URL como http://localhost:9090.
Importar panel de control PostgreSQL.
1. Seleccione Importar un panel de control.
2. Importe el ID del panel de control 9628 desde los paneles de control de Grafana.

Tarea 5: Exploración de OCI Database with PostgreSQL en Grafana

En las siguientes imágenes se muestran los cuadros de mandos finales.

Ejemplo de panel de control

Vea las métricas expuestas desde el archivo queries.yaml personalizado desde el separador Explorar de Grafana.
Agregar visualizaciones para:
- Sentencias SQL más lentas.
- Usuarios principales.
- Consultas activas.
- IP de cliente principales.
- Frecuencia de consulta (la mayoría llamadas consultas).

Tarea 6: Configuración de alerta para OCI Database with PostgreSQL en Grafana

Recopilar Métrica de Bloat.
- Se ha utilizado una consulta SQL personalizada para estimar el bloat de tablas, según las vistas del catálogo del sistema PostgreSQL, como pg_class, pg_namespace, pg_attribute y pg_stats.
- Esta consulta se expuso como métrica personalizada a través de un exportador PostgreSQL agregándola al archivo queries.yaml, como se muestra en la tarea 2.2, y posteriormente fue eliminada por Prometheus.
Configure Alert Rule (Configurar regla de alerta).
1. Vaya a Alerting (Alertas) y haga clic en Alert rules (Reglas de alerta) en Grafana.
2. Haga clic en + Nueva regla de alerta.
3. Introduzca un nombre para la regla.
4. Seleccione Prometheus como Origen de datos.
5. En la sección Definir condición de consulta y alerta, introduzca la siguiente información.
  - Busque la métrica table_bloat_bloat_size_percent.
  - En el generador de Condición de alerta, introduzca la siguiente información.
    - Definir entrada: seleccione A.
    - Condición: seleccione ABAJO DE 30.
    Nota: Puede hacer clic en Vista previa para validar si la alerta se activará en las condiciones actuales.
6. En la sección Definir comportamiento de evaluación, introduzca la siguiente información.
  1. Haga clic en + Nuevo grupo de evaluación.
  2. Grupo de valoración: introduzca postgres-bloat-alerts.
  3. Intervalo de evaluación: seleccione 5m (esto define la frecuencia con la que Grafana evalúa la regla de alerta).
  4. Período pendiente: defínalo en 5m (la alerta debe permanecer verdadera durante 5 minutos continuos antes de que se dispare. Debe ser igual o mayor que el intervalo de evaluación).
7. Haga clic en Guardar regla y en Salir para activar la alerta.
Configurar alertas de correo electrónico mediante SMTP (Gmail).

Para activar las notificaciones de correo electrónico para alertas en Grafana, configure la sección smtp del archivo grafana.ini.
1. Edite el archivo de configuración de Grafana en la instancia de OCI Compute donde haya instalado Grafana.
```
sudo nano /etc/grafana/grafana.ini
```
2. Elimine los comentarios y actualice la sección smtp con el siguiente contenido.
```
[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
```
  Nota: Para utilizar Gmail, debe activar la verificación en 2 pasos y generar una contraseña de aplicación desde la configuración de su cuenta de Google. No utilice su contraseña habitual de Gmail.
3. Después de guardar la configuración, reinicie Grafana para aplicar los cambios mediante el siguiente comando.
```
sudo systemctl restart grafana-server
```
Configuración de puntos de contacto para notificaciones de alerta.

Los puntos de contacto definen dónde y cómo Grafana envía alertas cuando se dispara una regla, como correo electrónico, Slack, webhook, etc.
1. Vaya a Alerting (Alertas) y haga clic en Contact points (Puntos de contacto) en Grafana.
2. Haga clic en + Nuevo punto de contacto para crear uno o seleccione y edite uno existente.
3. Para configurar un punto de contacto de correo electrónico, introduzca la siguiente información.
  - Nombre: introduzca el nombre del punto de contacto (por ejemplo, grafana-default-email).
  - Integración: seleccione Correo electrónico.
  - Direcciones: introduzca una o más direcciones de correo electrónico.
4. Haga clic en Probar para verificar la entrega del correo electrónico.
5. Haga clic en Guardar punto de contacto.
Configurar Política de Notificación.

Las políticas de notificación de Grafana controlan cómo y cuándo se envían las alertas a los puntos de contacto. Después de configurar un punto de contacto, debe crear una política de notificación para conectar alertas a ese punto de contacto.
1. Vaya a Alerting (Alertas) y haga clic en Notification Policies (Políticas de notificación) en Grafana.
2. Haga clic en Editar en la política por defecto o en + Nueva política.
3. En Punto de contacto predeterminado, seleccione su contacto (grafana-default-email).
4. En Agrupar por, puede agrupar alertas en función de valores de etiqueta como:
  - grafana_folder
  - alertname
5. Haga clic en Opciones de tiempo e introduzca la siguiente información.
  - Espera de grupo: seleccione 30s, este es el tiempo de espera antes de enviar la primera alerta de un grupo.
  - Intervalo de grupo: seleccione 5m, que es el tiempo entre lotes de alertas agrupadas.
  - Intervalo de repetición: seleccione 30m, este es el tiempo después del cual se vuelve a enviar la misma alerta si aún se activa.
6. Haga clic en Actualizar política por defecto para guardar.
Trigger and Verify Alert (Disparar y verificar alerta).
1. Simular una infracción de umbral (opcional).
  
  Para probar los disparadores de alertas, puede:
  - Reduzca temporalmente el umbral de alerta (por ejemplo, defina bloat_size_percent > 0).
    
    O bien,
  - Introducir la hinchazón mediante la realización de operaciones UPDATE o DELETE repetidas en una tabla de prueba grande.
2. Espere la evaluación.
  
  Grafana comprueba las condiciones de alerta en el intervalo de evaluación definido (por ejemplo, cada 5 minutos). Si la condición es verdadera para el período pendiente (por ejemplo, 5 minutos), se activará la alerta.
3. Comprobación de Email Delivery.
  
  Cuando se dispara, la alerta se envía a través del punto de contacto SMTP configurado (por ejemplo, Gmail). Compruebe la bandeja de entrada de la dirección del destinatario para ver un correo electrónico de notificación de Grafana.
4. Comentarios de IU de Grafana.
  
  Vaya a Alerting (Alertas) y haga clic en Alert rules (Reglas de alerta) para ver el estado de la alerta. Verá indicadores de estado como Firing en la página Alert rules.

Acuses de recibo

Autor: Irine Benoy (cinturón negro de datos de código abierto)

Más recursos de aprendizaje

Explore otros laboratorios en docs.oracle.com/learn o acceda a más contenido de aprendizaje gratuito en el canal YouTube de Oracle Learning. Además, visite education.oracle.com/learning-explorer para convertirse en un explorador de Oracle Learning.

Para obtener documentación sobre el producto, visite Oracle Help Center.

Título e Información de Copyright

Monitor an OCI Database with PostgreSQL using Prometheus and Grafana

G39865-01