Guía de Administración de Base de Datos — CreaRack Pro

Guía de Administración de Base de Datos — CreaRack Pro

Audiencia: Administradores de BD y DevOps Última actualización: 07-04-2026 Versión BD: PostgreSQL 18 (Alpine)

1. Arquitectura General

┌──────────────┐     ┌───────────────┐     ┌─────────────────┐     ┌──────────────────┐
│  Agent (WS)  │────▶│  Daphne ASGI  │────▶│   pgbouncer     │────▶│  PostgreSQL 18   │
│  Browser     │     │  (web container)│    │  (:6432)        │     │  (db container)  │
└──────────────┘     └───────────────┘     └─────────────────┘     └──────────────────┘
                     ┌───────────────┐            │
                     │  Huey Worker  │────────────┘
                     │  (worker)     │
                     └───────────────┘

• pgbouncer ACTIVO — modo transaction, pool_size=30, max_client_conn=300. Django conecta a pgbouncer:6432 (no directamente a db:5432)
• CONN_MAX_AGE = 0 — OBLIGATORIO con pgbouncer en modo transaction (jamás aumentar este valor)
• Sesiones almacenadas en Valkey (cache), no en BD

2. Acceso a la Base de Datos

2.1 Usuarios PostgreSQL

2.2 Conexión desde fuera del servidor

PostgreSQL no expone puerto externamente (no hay port mapping en Docker). Para conectarse se requiere un SSH tunnel:

# Terminal 1: Abrir el tunnel
ssh -L 5432:localhost:5432 root@crearack.com

# Terminal 2: Conectar con cualquier cliente PG (psql, DBeaver, pgAdmin...)
psql -h localhost -p 5432 -U dani_readonly -d crearack_pro

Credenciales readonly:

2.3 Conexión directa en el servidor

# Desde SSH en el servidor Hetzner
ssh root@crearack.com

# psql como owner
docker exec -it crearack-pro-zcmvsl-db-1 psql -U crearack_user -d crearack_pro

# psql como readonly
docker exec -it crearack-pro-zcmvsl-db-1 psql -U dani_readonly -d crearack_pro

3. Configuración de Producción

3.1 Contenedor PostgreSQL

# compose.prod.yml
image: postgres:18-alpine
command: >
  postgres
    -c max_connections=200
    -c shared_buffers=256MB
    -c shared_preload_libraries=pg_stat_statements
    -c pg_stat_statements.track=all
    -c archive_mode=on
    -c archive_command='cp %p /wal_archive/%f'
    -c wal_level=replica

3.2 pgbouncer (Connection Pooler)

pgbouncer actúa como proxy entre Django y PostgreSQL, reduciendo el número de conexiones reales a la BD.

IMPORTANTE: En modo transaction, las conexiones PG se reutilizan entre requests de distintos usuarios. Esto requiere CONN_MAX_AGE=0 en Django — de lo contrario Django mantiene una conexión “suya” que bloquea el pool.

3.3 Configuración Django

# config/settings/production.py
DATABASES = {
    "default": {
        "ENGINE": "django.db.backends.postgresql",
        "NAME": os.getenv("POSTGRES_DB", "crearack_pro"),
        "USER": os.getenv("POSTGRES_USER", "crearack_user"),
        "PASSWORD": os.getenv("POSTGRES_PASSWORD"),
        "HOST": os.getenv("POSTGRES_HOST", "pgbouncer"),  # ← pgbouncer, NO db
        "PORT": os.getenv("POSTGRES_PORT", "6432"),       # ← puerto pgbouncer
        # ⚠ MUST be 0 with pgbouncer transaction mode — DO NOT increase
        "CONN_MAX_AGE": 0,
        "CONN_HEALTH_CHECKS": True,
        "OPTIONS": {
            "connect_timeout": 10,
        }
    }
}

3.4 Por qué CONN_MAX_AGE = 0 es obligatorio

Con pgbouncer en modo transaction, cada transacción se sirve desde un pool de 30 conexiones reales. Si Django mantuviese una conexión propia (CONN_MAX_AGE > 0), “robaría” una conexión del pool durante todo el ciclo de vida del thread — en Daphne ASGI, eso puede ser indefinido para conexiones WebSocket.

Adicionalmente, Daphne crea un thread por cada handler WS/HTTP. Cuando el Agent reconecta tras estar offline, dispara un burst de mensajes que generaría cientos de conexiones idle → saturación.

Este valor se ha revertido incorrectamente 4 veces — no cambiarlo bajo ningún concepto.

4. Row-Level Security (RLS)

4.1 Qué es

RLS es una capa de seguridad a nivel PostgreSQL que garantiza que cada organización (tenant) solo puede ver sus propios datos. Funciona como red de seguridad adicional al filtrado por ORM de Django.

4.2 Cómo funciona

1. El middleware TenantRLSMiddleware intercepta cada request
2. Determina el organization_id del usuario autenticado
3. Ejecuta SET LOCAL app.current_org_id = '<org_id>' dentro de una transacción
4. PostgreSQL aplica las políticas RLS que filtran filas automáticamente

4.3 Bypass

Nota para dani_readonly: Al conectarse directamente por psql, app.current_org_id queda vacío → se ve toda la data de todas las organizaciones. Esto es intencional para diagnóstico.

4.4 Tablas protegidas

29 tablas con aislamiento estricto (organization_id NOT NULL):

5 tablas con registros globales (organization_id nullable):

Excluida: core_user — Django AuthenticationMiddleware consulta usuarios antes de que el middleware RLS se ejecute.

5. Sistema de Backups

5.1 Backup a nivel aplicación (por organización)

Huey ejecuta run_org_backups diariamente a las 3:30 AM:

• Genera un ZIP por cada organización activa
• Ubicación: MEDIA_ROOT/backups/{org_id}/backup_YYYY-MM-DD.zip
• 14 tipos de entidades: organización, racks+dispositivos, rack groups, box categories, stencils, blueprints+placements+annotations, config backups (últimos 5/device), scripts, monitoring targets, monitoring alerts, device profiles, AI prompts
• Incluye archivos media de MEDIA_ROOT/uploads/
• Verifica integridad del ZIP tras generación
• Limpieza automática según retención del plan (Starter: 7 días, Pro: 30 días)

API: GET /api/backup/latest — descarga último backup (solo admin)

5.2 Backup a nivel PostgreSQL (pg_dump)

Cron configurado en el host Hetzner (fuera de Docker). Dump diario completo de la BD.

# Ver backups locales
ls -lh /opt/backups/dumps/

# Ver logs del cron de backup
grep pg_backup /var/log/syslog | tail -20

# Ejecutar backup manualmente
/opt/backups/pg_backup.sh

5.3 WAL Archiving y Point-in-Time Recovery (PITR)

Además del pg_dump diario, PostgreSQL archiva continuamente los WAL (Write-Ahead Logs), lo que permite recuperar datos con una pérdida mínima de minutos (vs. 24h con pg_dump solo).

Configuración:

Crons de backup en producción:

Ventaja PITR: Combinar un base backup físico + WAL archives permite restaurar a cualquier punto en el tiempo con pérdida de datos de minutos.

⚠️ Incidente resuelto (17-07-2026, s227): el archivado estuvo roto desde el 06-04-2026 — el directorio /wal_archive del contenedor era propiedad de root y el usuario postgres no podía escribir (pg_stat_archiver acumuló 334k fallos y 0 archivados; pg_wal creció hasta 7,6 GB sin poder reciclarse, y wal_sync.sh corría en vacío con “No WAL files to sync”). Fix: chown postgres:postgres /wal_archive → backlog de 495 segmentos drenado y subido a Object Storage, pg_wal de vuelta a ~81 MB, archivado en vivo verificado. Al replicar el stack, crear el volumen con owner postgres. Ojo relacionado: los datos reales de PG 18 viven en un volumen ANÓNIMO (el PGDATA de la imagen postgres:18-alpine es /var/lib/postgresql/18/docker, no /var/lib/postgresql/data), así que el volumen con nombre pg_data_prod está VACÍO — corrección fina planificada con el simulacro de restore de octubre 2026.

5.4 Restauración manual

# Desde el servidor Hetzner
ssh root@crearack.com

# Crear dump
docker exec crearack-pro-zcmvsl-db-1 pg_dump -U crearack_user crearack_pro > backup.sql

# Restaurar (¡detener la app primero!)
docker stop crearack-pro-zcmvsl-web-1 crearack-pro-zcmvsl-worker-1
docker exec -i crearack-pro-zcmvsl-db-1 psql -U crearack_user -d crearack_pro < backup.sql
docker start crearack-pro-zcmvsl-web-1 crearack-pro-zcmvsl-worker-1

6. Tareas Automáticas de Mantenimiento

7. Monitorización y Diagnóstico

7.1 Conexiones activas

-- Resumen por estado
SELECT count(*), state FROM pg_stat_activity GROUP BY state;

-- Detalle de conexiones activas
SELECT pid, state, application_name, client_addr,
       age(clock_timestamp(), query_start) AS query_age,
       left(query, 80) AS query
FROM pg_stat_activity
WHERE state != 'idle'
ORDER BY query_start;

-- Conexiones por aplicación
SELECT count(*), application_name
FROM pg_stat_activity
GROUP BY application_name
ORDER BY count DESC;

Valores saludables (con pgbouncer activo):

• idle: < 5 (pgbouncer recicla conexiones, no quedan idle)
• active: 1-30 en uso normal (limitado por pool_size=30)
• Total conexiones PG reales: ≤ 30 en operación normal
• Clientes en pgbouncer: hasta 300 simultáneos

Señales de alerta:

• idle > 20 en PG: CONN_MAX_AGE probablemente no es 0 o Django conecta directamente sin pgbouncer
• idle in transaction: conexiones atascadas, buscar queries bloqueadas
• Total PG > 150: riesgo de saturación (max=200) — revisar pgbouncer pool_size

Nota: Las conexiones que se ven en pg_stat_activity son las conexiones reales de pgbouncer a PostgreSQL, no los clientes Django. Con pool_size=30, nunca deberías ver más de ~35 conexiones totales en PG.

7.2 Queries lentas

-- Top 10 queries más lentas (requiere pg_stat_statements)
SELECT round(total_exec_time::numeric, 2) AS total_ms,
       calls,
       round(mean_exec_time::numeric, 2) AS avg_ms,
       left(query, 100) AS query
FROM pg_stat_statements
ORDER BY mean_exec_time DESC
LIMIT 10;

7.3 Tamaño de tablas

SELECT schemaname || '.' || tablename AS table,
       pg_size_pretty(pg_total_relation_size(schemaname || '.' || tablename)) AS total_size
FROM pg_tables
WHERE schemaname = 'public'
ORDER BY pg_total_relation_size(schemaname || '.' || tablename) DESC
LIMIT 20;

7.4 Índices no usados

SELECT schemaname || '.' || relname AS table,
       indexrelname AS index,
       pg_size_pretty(pg_relation_size(indexrelid)) AS size,
       idx_scan AS scans
FROM pg_stat_user_indexes
WHERE idx_scan = 0
ORDER BY pg_relation_size(indexrelid) DESC
LIMIT 20;

8. Operaciones Comunes

8.1 Matar conexiones idle (emergencia)

-- Matar todas las conexiones idle (excepto la tuya)
SELECT pg_terminate_backend(pid)
FROM pg_stat_activity
WHERE state = 'idle'
  AND pid != pg_backend_pid();

8.2 Verificar RLS está activo

-- Listar tablas con RLS habilitado
SELECT tablename, rowsecurity
FROM pg_tables
WHERE schemaname = 'public' AND rowsecurity = true;

8.3 Consultar datos de una organización específica

-- Setear contexto de organización (simula lo que hace Django)
SET app.current_org_id = '1';  -- ID de la org

-- Ahora las queries devuelven solo datos de esa org
SELECT * FROM racks_rack;

-- Volver a ver todo
SET app.current_org_id = '0';

8.4 Ver estado del sistema de backups

-- Últimos logs de backup
SELECT timestamp, level, message
FROM core_systemlog
WHERE message LIKE '%backup%'
ORDER BY timestamp DESC
LIMIT 20;

8.5 Verificar integridad cross-tenant

-- Devices en racks de otra organización
SELECT d.id, d.name, d.organization_id AS device_org,
       r.organization_id AS rack_org
FROM racks_device d
JOIN racks_rack r ON d.rack_id = r.id
WHERE d.organization_id != r.organization_id;

9. Contenedores Docker

Comandos de gestión

# Ver logs de PostgreSQL
docker logs crearack-pro-zcmvsl-db-1 --tail 50

# Reiniciar solo la BD (¡las conexiones se pierden!)
docker restart crearack-pro-zcmvsl-db-1

# Reiniciar app tras reiniciar BD
docker restart crearack-pro-zcmvsl-web-1 crearack-pro-zcmvsl-worker-1

# Ver uso de recursos
docker stats crearack-pro-zcmvsl-db-1 --no-stream

10. Diferencias Local vs Producción

11. Troubleshooting

“FATAL: sorry, too many clients already”

Causa: Conexiones idle acumuladas (>200 en PG, o pgbouncer saturado con >300 clientes).

Fix inmediato:

# Reiniciar pgbouncer primero (limpia el pool sin tocar PG)
docker restart crearack-pro-zcmvsl-pgbouncer-1
# Si persiste, reiniciar la BD también
docker restart crearack-pro-zcmvsl-db-1
docker restart crearack-pro-zcmvsl-web-1 crearack-pro-zcmvsl-worker-1

Fix permanente: Verificar que:

1. CONN_MAX_AGE=0 en config/settings/production.py
2. POSTGRES_HOST=pgbouncer y POSTGRES_PORT=6432 en las variables de entorno de producción
3. Django NO conecta directamente a db:5432

Queries extremadamente lentas

-- Buscar bloqueos
SELECT blocked_locks.pid AS blocked_pid,
       blocking_locks.pid AS blocking_pid,
       blocked_activity.query AS blocked_query
FROM pg_catalog.pg_locks blocked_locks
JOIN pg_catalog.pg_locks blocking_locks
    ON blocking_locks.locktype = blocked_locks.locktype
    AND blocking_locks.relation = blocked_locks.relation
    AND blocking_locks.pid != blocked_locks.pid
JOIN pg_catalog.pg_stat_activity blocked_activity
    ON blocked_activity.pid = blocked_locks.pid
WHERE NOT blocked_locks.granted;

RLS bloqueando datos inesperadamente

Verificar que app.current_org_id se está seteando correctamente:

-- Ver el valor actual
SELECT current_setting('app.current_org_id', true);

Si devuelve vacío o 0, se ven todos los datos. Si devuelve un ID de org, solo esa org.

Mantenido por: Equipo CreaRack

Véase también

• [[crearack-tech—admin—cache-and-database]] — cache Valkey y BD
• [[crearack-tech—backend—database-architecture]] — arquitectura de la BD
• [[decision—20260315—postgres-18-pgbouncer]] — ADR migración a PostgreSQL 18 + pgbouncer
• [[crearack-tech—architecture—pg18-migration-plan]] — plan de migración a PostgreSQL 18
• [[crearack-tech—guides—disaster-recovery]] — procedimiento de disaster recovery
• [[crearack-tech—admin—backup-restore]] — backup y restore
• [[crearack-tech—architecture—valkey-persistence]] — persistencia de Valkey