Monitore um Oracle Cloud Infrastructure Database com PostgreSQL usando Prometheus e Grafana

Introdução

PostgreSQL é um dos bancos de dados mais populares entre os desenvolvedores de aplicativos. Além da eliminação da sobrecarga de gerenciamento que o Oracle Cloud Infrastructure Database com PostgreSQL (OCI Database with PostgreSQL) oferece, ele também vai um passo além quando se trata de implementar uma facilidade de monitoramento. Prometheus e Grafana são um par muito popular quando se trata de monitorar o desempenho do aplicativo para armazenamento e visualização de dados de séries temporais, respectivamente.

Neste tutorial, demonstraremos como monitorar um OCI Database with PostgreSQL extraindo métricas no nível da consulta usando o Exportador PostgreSQL, raspando-as com o Prometheus, visualizando-as no Grafana e configurando alertas para métricas inchadas.

Visão Geral da Arquitetura

O esquema a seguir descreve a abordagem geral. Os seguintes componentes são usados:

OCI Database with PostgreSQL.
Exportador de Postgres para expor métricas.
Prometheus para raspar métricas.
Grafana para visualizar métricas.

View de arquitetura

Observação: Embora a arquitetura exiba três sub-redes para maior clareza, este tutorial usa apenas duas, e o jump host também é a VM usada para implantar o Prometheus e o Grafana.

Objetivos

Extraia métricas PostgreSQL com o Exportador PostgreSQL para Prometheus.
Insira essas métricas em Prometheus.
Visualize as métricas usando painéis do Grafana.
Configure alertas para detectar o inchaço da tabela usando métricas expostas por meio do Exportador PostgreSQL.

Pré-requisitos

Acesso a uma tenancy do OCI com um cluster do OCI Database with PostgreSQL. Para obter mais informações, consulte Estabelecer Conexão com o Oracle Cloud Infrastructure Database com PostgreSQL usando DBeaver.
Instância do OCI Compute (host de salto) em uma sub-rede pública na mesma VCN que o OCI Database with PostgreSQL.
Lista de Segurança do OCI: permite as portas de entrada 9090, 9187, 3000.

Execute os comandos a seguir para configurar o firewall na instância.

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

Tarefa 1: Ativar Extensões de Monitoramento no OCI Database with PostgreSQL

Ative a Extensão pg_stat_statements.

Adicione pg_stat_statements nas configurações do OCI Database with PostgreSQL por meio da Console do OCI. Para obter mais informações, consulte Ativando Extensões para um Banco de Dados.
Criar Usuário de Monitoramento.

Execute as consultas a seguir para criar e conectar o usuário usando DBeaver por meio do jump host.
```
CREATE USER postgres_exporter WITH PASSWORD '<password>';
GRANT CONNECT ON DATABASE postgres TO postgres_exporter;
GRANT pg_monitor TO postgres_exporter;
```
Observação: a extensão pg_stat_io está disponível no OCI Database with PostgreSQL v16 em diante.

Tarefa 2: Instalar o Exportador PostgreSQL do Prometheus

Observação: Execute os seguintes comandos na sua instância do OCI Compute.

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

Criar 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"

Definir a Variável de Ambiente de Origem de Dados.
```
export DATA_SOURCE_NAME="postgresql://postgres_exporter:<password>@<POSTGRES_HOST>:5432/postgres?sslmode=require"
```
Observação: <POSTGRES_HOST> é o FQDN do ponto final principal. Você pode encontrá-lo na página de detalhes de cluster PostgreSQL na Console do OCI.
Inicie o Exportador PostgreSQL.

Execute o Exportador PostgreSQL com o arquivo queries.yaml personalizado usando o comando a seguir. Isso inicia o exportador na porta 9187 e registra a saída em exporter.log.
```
nohup /usr/local/bin/postgres_exporter --extend.query-path=/home/opc/queries.yaml --web.listen-address=":9187" > exporter.log 2>&1 &
```
Observação: para manter as coisas em execução após a reinicialização, considere configurar o serviço usando systemd
Verificar o Exportador.
```
curl http://localhost:9187/metrics
```

Tarefa 3: Instalar e Configurar o Prometheus

Instale o 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/

Criar Configuração do Prometheus.

Crie o arquivo /etc/prometheus/prometheus.yml com o conteúdo a seguir.

global:
scrape_interval: 15s

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

Inicie o Prometheus.
```
nohup prometheus --config.file=/etc/prometheus/prometheus.yml > prometheus.log 2>&1 &
```
Observação: para manter as coisas em execução após a reinicialização, considere configurar o serviço usando systemd
Verificar Destinos.

Visite aqui: http://<instance_ip>:9090/targets.

Tarefa 4: Instalar e Configurar o Grafana

Instalar 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

Faça login no Grafana.

Vá para o seguinte URL: http://<instance_ip>:3000 e digite Username como admin e Password como admin.
Adicione a Origem de Dados no Grafana.
1. Vá para Conexões, Origens de Dados e clique em Adicionar origem de dados.
2. Selecione Prometheus.
3. Informe o URL como http://localhost:9090.
Importar Painel de Controle PostgreSQL.
1. Selecione Importar um painel.
2. Importe o ID do painel de controle 9628 dos painéis do Grafana.

Tarefa 5: Explorar o OCI Database with PostgreSQL no Grafana

As imagens a seguir mostram os painéis finais.

Exemplo de Painel

Exiba métricas expostas do seu arquivo queries.yaml personalizado na guia Explorar no Grafana.
Adicione visualizações para:
- Principais instruções SQL mais lentas.
- Principais usuários.
- Consultas ativas.
- Principais IPs do cliente.
- Frequência de consulta (a maioria das consultas chamadas).

Tarefa 6: Configurar Alerta para o OCI Database with PostgreSQL no Grafana

Coletar Métrica de Inchaço.
- Uma consulta SQL personalizada foi usada para estimar o inchaço da tabela, com base nas views do catálogo do sistema PostgreSQL, como pg_class, pg_namespace, pg_attribute e pg_stats.
- Essa consulta foi exposta como uma métrica personalizada por meio de um Exportador PostgreSQL, adicionando-a ao arquivo queries.yaml, conforme mostrado na Tarefa 2.2, e foi subsequentemente raspada pelo Prometheus.
Configurar Regra de Alerta.
1. Navegue até Alerta e clique em Regras de alerta no Grafana.
2. Clique em + Nova regra de alerta.
3. Informe um Nome para a regra do alerta.
4. Selecione Prometheus como a Origem de dados.
5. Na seção Definir condição de consulta e alerta, especifique as informações a seguir.
  - Procure a métrica table_bloat_bloat_size_percent.
  - No construtor de condição de alerta, especifique as informações a seguir.
    - Definir Entrada: Selecione A.
    - Condição: Selecione IS ABOVE 30.
    Observação: você pode clicar em Visualizar para validar se o alerta seria acionado nas condições atuais.
6. Na seção Definir comportamento de avaliação, especifique as informações a seguir.
  1. Clique em + Novo grupo de avaliação.
  2. Grupo de Avaliações: Informe postgres-bloat-alerts.
  3. Intervalo de avaliação: Selecione 5m (isso define a frequência com que o Grafana avalia a regra de alerta).
  4. Período pendente: Defina como 5m (o alerta deve permanecer verdadeiro por 5 minutos contínuos antes de ser acionado. Deve ser igual ou maior que o intervalo de avaliação).
7. Clique em Salvar regra e sair para ativar seu alerta.
Configurar Alerta de E-mail por meio de SMTP (Gmail).

Para ativar notificações por e-mail para alertas no Grafana, configure a seção smtp do seu arquivo grafana.ini.
1. Edite o arquivo de configuração do Grafana na instância do OCI Compute na qual você instalou o Grafana.
```
sudo nano /etc/grafana/grafana.ini
```
2. Remova o comentário e atualize a seção smtp com o conteúdo a seguir.
```
[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
```
  Observação: para usar o Gmail, você deve ativar a Verificação em 2 Etapas e gerar uma Senha do Aplicativo com base nas definições da sua conta do Google. Não use sua senha regular do Gmail.
3. Depois de salvar a configuração, reinicie o Grafana para aplicar as alterações usando o comando a seguir.
```
sudo systemctl restart grafana-server
```
Configurar Pontos de Contato para Notificações de Alerta.

Os pontos de contato definem onde e como o Grafana envia alertas quando uma regra é acionada, como e-mail, Slack, webhook etc.
1. Navegue até Alerta e clique em Pontos de contato no Grafana.
2. Clique em + Novo ponto de contato para criar um ou selecione e edite um existente.
3. Para configurar um Ponto de Contato de E-mail, especifique as informações a seguir.
  - Nome: Informe o nome do ponto de contato (por exemplo, grafana-default-email).
  - Integração: Selecione E-mail.
  - Endereços: Informe um ou mais endereços de e-mail.
4. Clique em Testar para verificar a entrega de e-mail.
5. Clique em Salvar ponto de contato.
Configurar Política de Notificação.

As políticas de notificação no Grafana controlam como e quando os alertas são roteados para pontos de contato. Depois de configurar um ponto de contato, você deve criar uma política de notificação para conectar alertas a esse ponto de contato.
1. Navegue até Alerta e clique em Políticas de notificação no Grafana.
2. Clique em Editar na política padrão ou em + Nova política.
3. Em Ponto de contato padrão, selecione seu contato (grafana-default-email).
4. Em Agrupar por, você pode agrupar alertas com base em valores de label, como:
  - grafana_folder
  - alertname
5. Clique em Opções de tempo e especifique as seguintes informações.
  - Aguardar grupo: Selecione 30s, este é o tempo de espera antes de enviar o primeiro alerta em um grupo.
  - Intervalo do grupo: Selecione 5m. Esse é o tempo entre lotes de alertas agrupados.
  - Intervalo de repetição: Selecione 30m, este é o horário após o qual o mesmo alerta será reenviado se ainda estiver sendo acionado.
6. Clique em Atualizar política padrão para salvar.
Acionar e Verificar Alerta.
1. Simule uma violação de limite (opcional).
  
  Para testar o acionamento de alerta, você pode:
  - Reduza temporariamente o limite de alerta (por exemplo, defina bloat_size_percent > 0).
    
    Ou
  - Introduza o inchaço executando operações UPDATE ou DELETE repetidas em uma tabela de teste grande.
2. Aguarde a avaliação.
  
  O Grafana verifica as condições de alerta no intervalo de avaliação definido (por exemplo, a cada 5 minutos). Se a condição for verdadeira para o período pendente (por exemplo, 5 minutos), o alerta será acionado.
3. Verificação do Email Delivery.
  
  Quando acionado, o alerta será enviado através do ponto de contato SMTP configurado (por exemplo, Gmail). Marque a caixa de entrada do endereço do destinatário para um e-mail de notificação do Grafana.
4. Feedback da Interface do Usuário do Grafana.
  
  Navegue até Alerta e clique em Regras de alerta para exibir o status do alerta. Você verá indicadores de status como Acionamento na página Regras de alerta.

Confirmações

Autor - Irine Benoy (Cinturão Preta de Dados de Código Aberto)

Mais Recursos de Aprendizado

Explore outros laboratórios em docs.oracle.com/learn ou acesse mais conteúdo de aprendizado gratuito no canal do Oracle Learning YouTube. Além disso, acesse education.oracle.com/learning-explorer para se tornar um Oracle Learning Explorer.

Para obter a documentação do produto, visite o Oracle Help Center.

Título e Informações de Copyright

Monitor an OCI Database with PostgreSQL using Prometheus and Grafana

G39871-01