Métodos para Determinar el Número Máximo Histórico de Conexiones en PostgreSQL

Consulta del Pico Máximo de Conexiones en PostgreSQL

PostgreSQL ofrece múltiples técnicas para identificar el número máximo histórico de conexiones o procesos. A continuación, se detallan enfoques prácticos para obtener esta información.

1. Uso de Datos del Recolector Estadístico

1.1 Verificar Configuración Actual de Conexiones

SELECT param_valor AS limite_conexiones, 
       (SELECT param_valor FROM pg_config WHERE param_nombre = 'superuser_reserved_connections') AS conexiones_reservadas
FROM pg_config 
WHERE param_nombre = 'max_connections';

Resultado de ejemplo:

 limite_conexiones | conexiones_reservadas 
-------------------+-----------------------
 100               | 3
(1 fila)

1.2 Consultar Pico Histórico (Reqiuere Recolector Habilitado)

-- Máximo de conexiones desde el último reinicio de estadísticas
SELECT max(backends_activos) AS max_conexiones_historicas
FROM estadisticas_base_datos;

-- Desglose por base de datos
SELECT nombre_bd, 
       max(backends_activos) AS conexiones_max
FROM estadisticas_base_datos
GROUP BY nombre_bd
ORDER BY conexiones_max DESC;

Ejemplo de salida:

 max_conexiones_historicas 
--------------------------
                       2
(1 fila)

nombre_bd   | conexiones_max 
------------+----------------
 mi_base    |               2
 otra_base  |               0
 postgres   |               0
(3 filas)

2. Historial mediante pg_stat_activity

2.1 Configurar Tabla de Historial

CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

-- Crear tabla para registrar datos periódicos
CREATE TABLE historial_conexiones AS
SELECT now() AS instante_muestra, 
       count(*) AS cantidad_conexiones
FROM sesiones_activas;

-- Insertar datos de forma periódica (vía cron o similar)
INSERT INTO historial_conexiones
SELECT now(), count(*) FROM sesiones_activas;

2.2 Analizar Datos Históricos

SELECT max(cantidad_conexiones) AS max_historico
FROM historial_conexiones;

-- Agrupado por horas para los últimos 7 días
SELECT date_trunc('hour', instante_muestra) AS hora,
       max(cantidad_conexiones) AS max_conexiones_hora
FROM historial_conexiones
WHERE instante_muestra > now() - interval '7 days'
GROUP BY 1
ORDER BY 1;

3. Aálisis de Registros de Conexión

3.1 Habilitar Registro en postgresql.conf

log_connections = on
log_disconnections = on
log_line_prefix = '%t [%p] %a@%d '

3.2 Utilizar Herramientas de Análisis

# Generar reporte con pgBadger
pgbadger /var/log/postgresql/postgresql-*.log -o reporte_conexiones.html

# Alternativa manual con grep
grep "connection authorized" /var/log/postgresql/postgresql-main.log | \
  cut -d' ' -f1 | sort | uniq -c | sort -rn

4. Integración con Sistemas de Monitoreo

4.1 Ejemplo con Prometheus

-- Promedio máximo diario
max_over_time(pg_stat_activity_count[24h])

4.2 Usar Vistas Preconfiguradas

SELECT * FROM vista_monitor_conexiones
ORDER BY valor_maximo DESC
LIMIT 5;

5. Consultas Avanzadas con Vistas del Sistema

5.1 Informe Consolidado

WITH datos_actuales AS (
  SELECT 
    count(*) AS conexiones_actuales,
    (SELECT setting::int FROM pg_settings WHERE name = 'max_connections') AS limite_sistema
  FROM pg_stat_activity
)
SELECT 
  conexiones_actuales,
  limite_sistema,
  round(conexiones_actuales * 100.0 / limite_sistema, 2) AS porcentaje_uso,
  (SELECT max(numbackends) FROM pg_stat_database) AS max_historico_registrado
FROM datos_actuales;

5.2 Trazabilidad Continua

CREATE TABLE IF NOT EXISTS registro_conexiones (
  marca_temporal timestamp PRIMARY KEY,
  conteo integer,
  maximo_desde_inicio integer
);

-- Función para actualización automática
CREATE OR REPLACE FUNCTION actualizar_registro_conexiones() RETURNS void AS $$
DECLARE
  conteo_actual integer;
  maximo_global integer;
BEGIN
  SELECT count(*) INTO conteo_actual FROM sesiones_activas;
  SELECT max(numbackends) INTO maximo_global FROM estadisticas_base_datos;
  
  INSERT INTO registro_conexiones 
  VALUES (now(), conteo_actual, maximo_global)
  ON CONFLICT (marca_temporal) DO NOTHING;
END;
$$ LANGUAGE plpgsql;

6. Uso de Utilidades del Sistema

# Analizar archivo de control (información general)
pg_controldata /ruta/base_datos

# Buscar líneas relevantes en la salida
# "Último checkpoint: máximo de conexiones:"

Recomendaciones Operativas

Implementar muestreo periódico (cada minnuto) para capturar fluctuaciones
Configurar alertas cuando el uso supere el 80% del límite configurado
Considerar soluciones de pooling como PgBouncer para optimizar recursos
Revisar tendencias mensuales y ajustar parámetros según sea necesario

Etiquetas: PostgreSQL SQL pg_stat_activity pg_stat_database monitoreo_base_datos

Publicado el 6-17 22:04

Friki Work