Monitoramento do PostgreSQL

# postgresql.conf:
shared_preload_libraries = 'pg_stat_statements'
pg_stat_statements.max = 10000
pg_stat_statements.track = all
pg_stat_statements.track_utility = on
track_activity_query_size = 4096     # aumentar para queries longas

# Reiniciar PostgreSQL
sudo systemctl restart postgresql

# Criar extensão no banco de monitoramento:
sudo -u postgres psql minha_db
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

-- Top 10 queries por tempo total (em minutos de CPU):
SELECT round(total_exec_time::numeric / 60000, 2) AS total_min,
       calls,
       round(mean_exec_time::numeric, 2) AS media_ms,
       round((100 * total_exec_time / sum(total_exec_time) OVER ())::numeric, 2) AS pct_total,
       left(query, 80) AS query_trunc
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 10;

-- Conexões por estado:
SELECT state,
       count(*) AS total,
       max(now() - state_change) AS max_tempo
FROM pg_stat_activity
WHERE datname = 'minha_db'
GROUP BY state
ORDER BY total DESC;

-- Queries rodando há mais de 30 segundos:
SELECT pid,
       now() - query_start AS duracao,
       substring(query, 1, 100) AS query_trunc,
       state,
       wait_event_type,
       wait_event
FROM pg_stat_activity
WHERE datname = 'minha_db'
  AND state != 'idle'
  AND now() - query_start > interval '30 seconds'
ORDER BY duracao DESC;

-- Bloqueios ativos (quem está bloqueando quem):
SELECT blocked.pid AS pid_bloqueado,
       blocked.query AS query_bloqueada,
       blocking.pid AS pid_bloqueador,
       blocking.query AS query_bloqueadora,
       now() - blocked.query_start AS tempo_bloqueado
FROM pg_stat_activity blocked
JOIN pg_stat_activity blocking
  ON blocking.pid = ANY(pg_blocking_pids(blocked.pid))
WHERE blocked.cardinality(pg_blocking_pids(blocked.pid)) > 0;

-- Encerrar query travada:
SELECT pg_terminate_backend(PID);

# Docker Compose — postgres_exporter:
services:
  postgres-exporter:
    image: prometheuscommunity/postgres-exporter:latest
    restart: always
    environment:
      DATA_SOURCE_NAME: "postgresql://monitor:senha_monitor@localhost:5432/postgres?sslmode=disable"
      PG_EXPORTER_DISABLE_DEFAULT_METRICS: "false"
    ports:
      - "127.0.0.1:9187:9187"
    extra_hosts:
      - "localhost:host-gateway"

# No PostgreSQL — criar usuário de monitoramento (somente leitura):
CREATE ROLE monitor WITH LOGIN PASSWORD 'senha_monitor';
GRANT pg_monitor TO monitor;   # papel de monitoramento built-in (PG 10+)

# prometheus.yml — adicionar scrape config:
scrape_configs:
  - job_name: 'postgresql'
    static_configs:
      - targets: ['localhost:9187']
    relabel_configs:
      - source_labels: [__address__]
        target_label: instance
        replacement: 'postgres-prod'

# Importar dashboard pronto para postgres_exporter:
# Dashboard ID: 9628 (PostgreSQL Database) — importar no Grafana
# Settings → Import → Dashboard ID: 9628

# Métricas principais do postgres_exporter:

# Taxa de conexões (alerta se > 80% do max):
pg_stat_activity_count{state="active"} / pg_settings_max_connections * 100

# Cache hit ratio (alerta se < 99%):
rate(pg_stat_database_blks_hit[5m]) /
(rate(pg_stat_database_blks_hit[5m]) + rate(pg_stat_database_blks_read[5m])) * 100

# Tamanho do banco (crescimento anormal):
pg_database_size_bytes

# Transações por segundo:
rate(pg_stat_database_xact_commit[1m]) + rate(pg_stat_database_xact_rollback[1m])

# Queries lentas (requer pg_stat_statements exporter):
topk(10, pg_stat_statements_mean_exec_time_seconds)

# Lag de replicação (se tiver standby):
pg_replication_lag

# prometheus-rules-postgres.yml
groups:
  - name: postgresql
    rules:
      # Muitas conexões abertas
      - alert: PostgresConnectionsHigh
        expr: |
          sum(pg_stat_activity_count) by (instance)
          / pg_settings_max_connections * 100 > 80
        for: 5m
        labels:
          severity: warning
        annotations:
          summary: "PostgreSQL: conexões > 80% do máximo em {{ $labels.instance }}"

      # Cache hit ratio baixo
      - alert: PostgresCacheHitRatioLow
        expr: |
          rate(pg_stat_database_blks_hit[5m])
          / (rate(pg_stat_database_blks_hit[5m]) + rate(pg_stat_database_blks_read[5m]) + 0.001)
          * 100 < 95
        for: 10m
        labels:
          severity: warning
        annotations:
          summary: "PostgreSQL: cache hit ratio < 95%: considere aumentar shared_buffers"

      # Replicação com lag alto
      - alert: PostgresReplicationLagHigh
        expr: pg_replication_lag > 60
        for: 5m
        labels:
          severity: critical
        annotations:
          summary: "PostgreSQL: lag de replicação > 60s"

      # Bloqueios de longa duração
      - alert: PostgresLongRunningLocks
        expr: pg_locks_count > 20
        for: 5m
        labels:
          severity: warning