Runbook: Database Migrations

Overview

Propósito: Ejecutar migraciones de esquema en la base de datos PostgreSQL multi-tenant.

Frecuencia: Por release (cada 1-2 semanas)

Tiempo estimado: 10-30 minutos (dependiendo de tamaño de datos)

Criticidad: 🔴 ALTA - Puede causar downtime si falla

---

Tipos de Migraciones

Tipo	Riesgo	Ejemplo
DDL Safe	Bajo	ADD COLUMN nullable
DDL Risky	Alto	DROP COLUMN, ALTER TYPE
DML	Medio	UPDATE masivo, data backfill
Schema Multi-Tenant	Alto	Aplicar a todos los tenant schemas

---

Pre-Migration Checklist

• Backup completo realizado (ver Runbook: Backup)
• Migration testeada en staging/desarrollo
• Downtime window aprobado (si aplica)
• Rollback script preparado
• Team notificado

Caution

NUNCA ejecutar migraciones no testeadas directamente en producción.

---

Procedure - Standard Migration

1. Conectar a servidor de producción

   ssh [email protected]
   cd /opt/nostromo/orchestrator

2. Verificar estado actual de migraciones

   npm run migrate:status

   Expected output:

   Applied migrations:
     ✓ 001_initial_schema.sql
     ✓ 002_add_indexes.sql
     ✓ 003_add_audit_tables.sql
   Pending migrations:
     ○ 004_add_employee_fields.sql

3. Crear backup pre-migration

   BACKUP_FILE="/opt/backups/pre-migration-$(date +%Y%m%d-%H%M).sql.gz"
   sudo -u postgres pg_dumpall | gzip > "$BACKUP_FILE"

4. Ejecutar migration en modo dry-run (si disponible)

   npm run migrate:dry-run

5. Ejecutar migration

   npm run migrate:up

   Expected output:

   Running migration: 004_add_employee_fields.sql
   ✓ Migration applied successfully

6. Verificar que migration se aplicó

   npm run migrate:status

   También verificar en DB:

   SELECT * FROM central.schema_migrations ORDER BY applied_at DESC LIMIT 5;

7. Smoke test de aplicación

   curl http://localhost:8000/health

---

Procedure - Multi-Tenant Migration

Para migraciones que afectan todos los tenant schemas:

1. Listar todos los tenant schemas

   SELECT schema_name FROM central.tenants WHERE active = true;

2. Ejecutar migration para cada tenant

   # Script que itera sobre tenants
   npm run migrate:tenants

   O manualmente:

   for SCHEMA in tenant_empresa_1 tenant_empresa_2; do
     echo "Migrating $SCHEMA..."
     psql -U postgres -d nostromo -c "SET search_path TO $SCHEMA; \i migration.sql"
   done

3. Verificar cada tenant

   SELECT schema_name,
          (SELECT COUNT(*) FROM information_schema.columns
           WHERE table_schema = t.schema_name AND column_name = 'new_column') as has_column
   FROM central.tenants t;

Tip

Considerar ejecutar en batches de 10 tenants para detectar problemas temprano.

---

Procedure - Rollback

Si la migration falla o causa problemas:

1. Evaluar severidad

   • ¿Aplicación funciona? → Puede esperar
   • ¿Errores 500? → Rollback inmediato

2. Ejecutar rollback script

   npm run migrate:down

   O manualmente:

   -- Rollback específico (ejemplo)
   ALTER TABLE employees DROP COLUMN IF EXISTS new_field;
   DELETE FROM central.schema_migrations WHERE name = '004_add_employee_fields.sql';

3. Restaurar desde backup (si necesario)

   gunzip -c /opt/backups/pre-migration-*.sql.gz | sudo -u postgres psql

4. Verificar estado post-rollback

   npm run migrate:status
   curl http://localhost:8000/health

5. Post-mortem

   Documentar qué falló y cómo prevenirlo.

---

Migraciones Seguras vs Riesgosas

✅ Operaciones Seguras (sin downtime)

-- Agregar columna nullable
ALTER TABLE employees ADD COLUMN middle_name VARCHAR(100);

-- Agregar índice concurrente
CREATE INDEX CONCURRENTLY idx_employees_email ON employees(email);

-- Agregar constraint con validación diferida
ALTER TABLE employees ADD CONSTRAINT check_rut
  CHECK (rut ~ '^[0-9]+-[0-9Kk]$') NOT VALID;

⚠️ Operaciones Riesgosas (requieren downtime)

-- Cambiar tipo de columna (bloquea tabla)
ALTER TABLE employees ALTER COLUMN salary TYPE DECIMAL(12,2);

-- Agregar columna NOT NULL sin default
ALTER TABLE employees ADD COLUMN required_field VARCHAR(50) NOT NULL;

-- Borrar columna
ALTER TABLE employees DROP COLUMN old_field;

🔄 Patrón Seguro para Cambios Riesgosos

-- Paso 1: Agregar columna nueva nullable
ALTER TABLE employees ADD COLUMN salary_new DECIMAL(12,2);

-- Paso 2: Backfill en batches (fuera de transaction)
UPDATE employees SET salary_new = salary::decimal(12,2) WHERE id BETWEEN 1 AND 10000;
UPDATE employees SET salary_new = salary::decimal(12,2) WHERE id BETWEEN 10001 AND 20000;
-- ...

-- Paso 3: Agregar NOT NULL constraint
ALTER TABLE employees ALTER COLUMN salary_new SET NOT NULL;

-- Paso 4: Renombrar columnas (en siguiente release)
ALTER TABLE employees RENAME COLUMN salary TO salary_old;
ALTER TABLE employees RENAME COLUMN salary_new TO salary;

-- Paso 5: Drop columna vieja (en release posterior)
ALTER TABLE employees DROP COLUMN salary_old;

---

Tracking de Migraciones

Tabla de Control

CREATE TABLE central.schema_migrations (
    id SERIAL PRIMARY KEY,
    name VARCHAR(255) NOT NULL UNIQUE,
    applied_at TIMESTAMP DEFAULT NOW(),
    checksum VARCHAR(64),
    execution_time_ms INTEGER
);

Registrar Migration Manualmente

INSERT INTO central.schema_migrations (name, checksum, execution_time_ms)
VALUES ('004_add_employee_fields.sql', 'sha256hash...', 1500);

---

Migraciones Multi-Tenant - Script Template

migrate_tenants.py

#!/usr/bin/env python3
import psycopg2
import sys

MIGRATION_FILE = sys.argv[1]

conn = psycopg2.connect(dbname='nostromo', user='postgres')
conn.autocommit = True
cur = conn.cursor()

# Get active tenants
cur.execute("SELECT schema_name FROM central.tenants WHERE active = true")
tenants = [row[0] for row in cur.fetchall()]

# Read migration
with open(MIGRATION_FILE) as f:
    migration_sql = f.read()

# Apply to each tenant
for schema in tenants:
    print(f"Migrating {schema}...")
    try:
        cur.execute(f"SET search_path TO {schema}")
        cur.execute(migration_sql)
        print(f"  ✓ {schema} OK")
    except Exception as e:
        print(f"  ✗ {schema} FAILED: {e}")
        # Continue or abort based on your policy

cur.close()
conn.close()

---

Troubleshooting

Error: “relation already exists”

-- Verificar si ya existe
SELECT EXISTS (
  SELECT 1 FROM information_schema.columns
  WHERE table_name = 'employees' AND column_name = 'new_column'
);

-- Usar IF NOT EXISTS
ALTER TABLE employees ADD COLUMN IF NOT EXISTS new_column VARCHAR(50);

Error: “lock wait timeout”

-- Ver locks activos
SELECT * FROM pg_locks WHERE NOT granted;

-- Cancelar query bloqueadora
SELECT pg_cancel_backend(pid);

Migration parcialmente aplicada

-- Verificar estado
SELECT * FROM central.schema_migrations WHERE name LIKE '%004%';

-- Limpiar y reintentar
BEGIN;
  DELETE FROM central.schema_migrations WHERE name = '004_...';
  -- Rollback manual de cambios parciales
COMMIT;

---

Related Documentation

• Runbook: Backup - Backup antes de migrations
• Runbook: Recovery - Disaster recovery
• PostgreSQL Config - Configuración DB
• ADR-001: Multi-Tenant - Arquitectura de schemas

---

Changelog

Fecha	Version	Cambios
2026-01-18	1.0	Runbook inicial creado