Automatización de Depuración de Tablas de Auditoría con `pg_cron` en Google Cloud SQL

1. Resumen y Propósito

Este documento describe el procedimiento para habilitar la extensión pg_cron en PostgreSQL administrado sobre Google Cloud SQL y detalla la implementación de una estrategia de mantenimiento automatizada para las tablas de auditoria.

El proceso consiste en desplegar un procedimiento almacenado diseñado para la depuración por lotes (batch-delete) en las tablas de logs (audit.error_log y audit.audit_log), el cual elimina de forma definitiva cualquier registro con una antigüedad mayor a un mes con respecto a la fecha de ejecución. Asimismo, se documenta la configuración del Job recurrente en pg_cron, el cual gestiona de manera óptima el desfase de la zona horaria UTC de GCP y normaliza los diversos formatos de fecha almacenados dentro de los campos de tipo JSONB.

2. Habilitación de `pg_cron` en Google Cloud SQL

Dado que Google Cloud SQL es un entorno gestionado, la extensión requiere inicializarse desde los parámetros del servidor.

Paso 2.1: Configurar Banderas (Flags) en GCP

Acceder a la consola de Google Cloud Platform > SQL > Instancias.
Seleccionar la instancia de PostgreSQL y hacer clic en Editar.
En la sección Marcas y parámetros configurar:

Bandera	Valor	Descripción
`cloudsql.enable_pg_cron`	`on`	Activa el soporte de `pg_cron` en Cloud SQL.
`cron.database_name`	`omnicanal`	(Crítico) Define la base de datos de destino donde `pg_cron` creará su esquema de control y ejecutará los metadatos de los Jobs.

⚠️ Nota: Guardar estos cambios reiniciará la instancia de base de datos.

Paso 2.2: Crear la Extensión en PostgreSQL

Conectarse obligatoriamente a la base de datos del sistema postgres con el usuario administrador postgres y ejecutar los siguientes scripts:

-- Conectado a la BD 'postgres'
CREATE EXTENSION IF NOT EXISTS pg_cron;

3. Procedimiento Almacenado de Depuración (`audit.sp_depurar_logs_audit`)

Para evitar bloqueos de tabla (table locks) y saturación del log de transacciones (WAL), la depuración se realiza en lotes configurables (LIMIT p_batch_size) con confirmación (COMMIT) tras cada ciclo.

Consideraciones Técnicas Clave:

SECURITY DEFINER: Permite que el proceso se ejecute con privilegios de administrador, evitando fallas de permission denied cuando pg_cron invoca el procedimiento.
Normalización Unicode de Java (\u202F): Los logs generados por aplicaciones Java pueden incluir caracteres de espacio estrecho no separable (Narrow No-Break Space) antes de AM/PM. Se normalizan antes de parsear a timestamp.

Código DDL del Procedimiento:

-- DROP PROCEDURE audit.sp_depurar_logs_audit(int4, interval);

CREATE OR REPLACE PROCEDURE audit.sp_depurar_logs_audit(IN p_batch_size integer DEFAULT 10000, IN p_retention_interval interval DEFAULT '1 mon'::interval)
 LANGUAGE plpgsql
AS $procedure$
DECLARE
    v_rows_deleted int := 0;
    v_total_deleted_error int := 0;
    v_total_deleted_audit int := 0;
    v_target_date timestamp;
BEGIN
    -- Definimos la fecha de corte basada en el intervalo de retención
       v_target_date := NOW() - p_retention_interval;
    
    -- NOTA: Para pruebas, descomenta la siguiente línea:
    -- v_target_date := '2026-06-20'::timestamp - INTERVAL '5 days';

    RAISE NOTICE 'Iniciando depuración de logs. Fecha de corte: %', v_target_date;

    ---------------------------------------------------------------------------
    -- 1. DEPURACIÓN DE LA TABLA: error_log
    ---------------------------------------------------------------------------
    LOOP
        WITH rows_to_delete AS (
            SELECT id
            FROM audit.error_log
            WHERE 
                dx_error_log_json->>'logDate' IS NOT NULL 
                AND CASE 
                    WHEN dx_error_log_json->>'logDate' ~ '^\d{4}-\d{2}-\d{2}' 
                        THEN (dx_error_log_json->>'logDate')::timestamp
                    WHEN dx_error_log_json->>'logDate' ~ '^[A-Za-z]{3}\s+\d{1,2},\s+\d{4}' 
                        THEN to_timestamp(
                            regexp_replace(
                                replace(dx_error_log_json->>'logDate', E'\u202F', ' '),
                                '\s+', ' ', 'g'
                            ),
                            'Mon DD, YYYY, HH12:MI:SS AM'
                        )::timestamp
                    ELSE NULL                 
                END < v_target_date
            LIMIT p_batch_size
        )
        DELETE FROM audit.error_log
        WHERE id IN (SELECT id FROM rows_to_delete);

        GET DIAGNOSTICS v_rows_deleted = ROW_COUNT;
        v_total_deleted_error := v_total_deleted_error + v_rows_deleted;

        -- Confirma la transacción para liberar bloqueos de error_log y vaciar buffers
        COMMIT;

        EXIT WHEN v_rows_deleted < p_batch_size;
    END LOOP;

    RAISE NOTICE 'Tabla error_log depurada. Registros eliminados: %', v_total_deleted_error;

    ---------------------------------------------------------------------------
    -- 2. DEPURACIÓN DE LA TABLA: audit_log
    ---------------------------------------------------------------------------
    LOOP
        WITH rows_to_delete AS (
            SELECT id
            FROM audit.audit_log
            WHERE 
                dx_audit_log_json->>'logDate' IS NOT NULL 
                AND CASE 
                    WHEN dx_audit_log_json->>'logDate' ~ '^\d{4}-\d{2}-\d{2}' 
                        THEN (dx_audit_log_json->>'logDate')::timestamp
                    WHEN dx_audit_log_json->>'logDate' ~ '^[A-Za-z]{3}\s+\d{1,2},\s+\d{4}' 
                        THEN to_timestamp(
                            regexp_replace(
                                replace(dx_audit_log_json->>'logDate', E'\u202F', ' '),
                                '\s+', ' ', 'g'
                            ),
                            'Mon DD, YYYY, HH12:MI:SS AM'
                        )::timestamp
                    ELSE NULL 
                END < v_target_date
            LIMIT p_batch_size
        )
        DELETE FROM audit.audit_log
        WHERE id IN (SELECT id FROM rows_to_delete);

        GET DIAGNOSTICS v_rows_deleted = ROW_COUNT;
        v_total_deleted_audit := v_total_deleted_audit + v_rows_deleted;

        -- Confirma la transacción para liberar bloqueos de audit_log
        COMMIT;

        EXIT WHEN v_rows_deleted < p_batch_size;
    END LOOP;

    RAISE NOTICE 'Tabla audit_log depurada. Registros eliminados: %', v_total_deleted_audit;
    RAISE NOTICE 'Depuración total completada. Total general eliminado: %', (v_total_deleted_error + v_total_deleted_audit);
END;
$procedure$;

4. Programación del Job en `pg_cron`

Debido a que pg_cron reside en la base de datos postgres (o según la base de datos de control definida en cron.database_name), se usa cron.schedule_in_database para enviar la ejecución hacia la base de datos donde reside la tabla (omnicanal).

Regla de uso Horario (UTC vs CDMX):

Google Cloud SQL opera bajo UTC (GMT-0) de forma nativa.
México (Centro) opera en UTC-6.
Para ejecutar el proceso a las 08:00 PM hora de México, la hora programada en UTC debe ser las 02:00 AM (0 2 * * *).

Alta del Job (Ejecutar conectado a la BD definida en `cron.database_name`):

SELECT cron.schedule_in_database(
    'job_depurar_logs_audit',
    '0 2 * * *', 
    $$CALL audit.sp_depurar_logs_audit(10000, INTERVAL '1 month')$$,
    'omnicanal' 
);

Desglose de Componentes de la Consulta

A continuación se detalla la función exacta de cada uno de los argumentos pasados a la función cron.schedule_in_database:

Componente	Tipo de Dato	Valor	Descripción y Función Exacta
`cron.schedule_in_database(...)`	Función	N/A	Función de la extensión `pg_cron` utilizada para programar tareas que deben ejecutarse en una base de datos específica del servidor, en lugar de la base de datos por defecto (`postgres`).
Primer argumento	`text`	`'job_depurar_logs_audit'`	Nombre único del Job. Funciona como identificador para poder buscarlo, modificarlo o eliminarlo posteriormente en la tabla `cron.job`.
Segundo argumento	`text`	`'0 2 * * *'`	Expresión Cron que dicta la frecuencia de ejecución (Todos los días a las 02:00 AM del servidor). (Ver sección de horario abajo).
Tercer argumento	`text`	`$$CALL audit.sp_...$$`	Bloque de código SQL a ejecutar. Delimitado por `$$` para evitar problemas con comillas simples internas. Contiene el procedimiento de depuración y las tareas de mantenimiento físico.
Cuarto argumento	`text`	`'omnicanal'`	Base de datos objetivo. Especifica que todo el código del tercer argumento debe ejecutarse bajo el contexto de la base de datos llamada `omnicanal`.

Desglose del Bloque SQL Ejecutado (`$$CALL audit.sp_depurar_logs_audit(10000, INTERVAL '1 month')$$`)

¿Qué hace?: Invoca al procedimiento almacenado de depuración.
Parámetro 10000: Define el tamaño de cada lote de eliminación (p_batch_size). Borrará los registros en bloques de 10,000 para evitar bloqueos de tabla prolongados (table locks).
Parámetro INTERVAL '1 month': Define el tiempo de retención. Cualquier log con fecha anterior a 1 mes atrás desde el momento de ejecución será eliminado de forma definitiva.

⏱️ Análisis del Horario (Sintaxis Cron)

El valor '0 2 * * *' se descompone de la siguiente manera:

 ┌────────────── minuto (0 - 59)
 │ ┌──────────── hora (0 - 23)
 │ │ ┌────────── día del mes (1 - 31)
 │ │ │ ┌──────── mes (1 - 12)
 │ │ │ │ ┌────── día de la semana (0 - 6) (0 es domingo)
 │ │ │ │ │
 0 2 * * *

Minuto 0: Se ejecuta exactamente al iniciar la hora.
Hora 2: Se ejecuta a las 02:00 AM en la zona horaria configurada en tu servidor de GCP Cloud SQL (por lo general, UTC).
Asteriscos * * *: Indican que se ejecuta todos los días del mes, todos los meses del año y todos los días de la semana.

5. Programar VACUUM FULL en `pg_cron`

¿Qué es VACUUM FULL?
Es un comando de PostgreSQL que reconstruye por completo la tabla en un archivo nuevo de disco, compactando únicamente los datos vivos y eliminando el espacio muerto (bloat). A diferencia del VACUUM ordinario, devuelve el espacio físico directamente al sistema operativo, requiriendo un bloqueo total exclusivo (ACCESS EXCLUSIVE) durante su ejecución.

A continuación se presenta la instrucción ejecutada para registrar los trabajos en el catálogo de pg_cron:

-- Programación para liberar espacio en audit.audit_log (03:00 AM diario)
SELECT cron.schedule(
    'libera_espacio_audit_log',
    '0 3 * * *',
    'VACUUM FULL audit.audit_log;'
);

-- Programación para liberar espacio en audit.error_log (03:30 AM diario)
SELECT cron.schedule(
    'libera_espacio_error_log',
    '30 3 * * *',
    'VACUUM FULL audit.error_log;'
);

5.1 Desglose de Tareas Programadas

Nombre del Trabajo (`jobname`)	Expresión Cron	Frecuencia y Horario	Comando SQL Ejecutado	Objetivo Principal
`libera_espacio_audit_log`	`0 3 * * *`	Diario a las 03:00 hrs	`VACUUM FULL audit.audit_log;`	Recompactar tabla y devolver espacio en disco al sistema operativo.
`libera_espacio_error_log`	`30 3 * * *`	Diario a las 03:30 hrs	`VACUUM FULL audit.error_log;`	Recompactar tabla de errores y regenerar índices.

5.2 Consideraciones Técnicas Críticas

5.2.1 Bloqueo Exclusivo (`ACCESS EXCLUSIVE LOCK`)

A diferencia del VACUUM estándar, VACUUM FULL reescribe por completo el contenido de la tabla en un nuevo archivo de disco.
Adquiere un bloqueo exclusivo (ACCESS EXCLUSIVE), lo que impide tanto lecturas (SELECT) como escrituras (INSERT, UPDATE, DELETE) en la tabla durante el tiempo que dure la operación.
Impacto Operativo: Si alguna aplicación intenta escribir un registro de auditoría o error mientras se ejecuta el job, la consulta quedará en espera hasta que termine el VACUUM FULL o alcance el lock_timeout. Se recomienda ejecutar exclusivamente en ventanas de baja o nula concurrencia.

5.2.1 Requisitos de Espacio en Disco

Para reescribir la tabla, PostgreSQL necesita espacio libre en disco equivalente aproximadamente al tamaño actual de la tabla más sus índices.
Si el disco se encuentra por encima del 80-90% de ocupación, la operación puede fallar por falta de espacio en disco temporal.

6. Operación, Monitoreo y Mantenimiento

Consultas de Monitoreo

-- 1. Ver configuración de Jobs registrados
SELECT jobid, schedule, command, database, username, active 
FROM cron.job;

-- 2. Consultar historial de ejecuciones y validar errores
SELECT runid, jobid, database, status, return_message, start_time, end_time 
FROM cron.job_run_details 
ORDER BY start_time DESC;

-- 3. Modificar horario del Job de forma segura
SELECT cron.alter_job(job_id := 1, schedule := '0 2 * * *');

-- 4. Desactivar / Cancelar un Job
SELECT cron.unschedule('job_depurar_logs_audit');

7. Matriz de Errores Comunes y Solución

Error Identificado	Causa Raíz	Solución
`ERROR: the pg_cron extension is not enabled`	No se agregaron las banderas en Cloud SQL o falta reiniciar la instancia.	Activar `cloudsql.enable_pg_cron` y `cron.database_name` en GCP.
`permission denied for table error_log`	El usuario ejecutor del cron no tiene permisos directos sobre la tabla de auditoría.	Agregar la propiedad `SECURITY DEFINER` al procedimiento almacenado.
`invalid value " AM" for "AM"`	Presencia del carácter Unicode `\u202F` (espacio estrecho) en la fecha del JSON.	Reemplazar `E'\u202F'` por espacio simple antes de aplicar la función `to_timestamp`.
`relation "error_log" does not exist`	El esquema de base de datos no fue especificado en la consulta del procedimiento.	Especificar siempre el esquema calificado: `audit.error_log`.

1. Resumen y Propósito​

2. Habilitación de pg_cron en Google Cloud SQL​

Paso 2.1: Configurar Banderas (Flags) en GCP​

Paso 2.2: Crear la Extensión en PostgreSQL​

3. Procedimiento Almacenado de Depuración (audit.sp_depurar_logs_audit)​

Consideraciones Técnicas Clave:​

Código DDL del Procedimiento:​

4. Programación del Job en pg_cron​

Regla de uso Horario (UTC vs CDMX):​

Alta del Job (Ejecutar conectado a la BD definida en cron.database_name):​

Desglose de Componentes de la Consulta​

Desglose del Bloque SQL Ejecutado ($$CALL audit.sp_depurar_logs_audit(10000, INTERVAL '1 month')$$)​

⏱️ Análisis del Horario (Sintaxis Cron)​

5. Programar VACUUM FULL en pg_cron​

5.1 Desglose de Tareas Programadas​

5.2 Consideraciones Técnicas Críticas​

5.2.1 Bloqueo Exclusivo (ACCESS EXCLUSIVE LOCK)​

5.2.1 Requisitos de Espacio en Disco​

6. Operación, Monitoreo y Mantenimiento​

Consultas de Monitoreo​

7. Matriz de Errores Comunes y Solución​