Guía de Migración de MySQL a PostgreSQL

Migrar de MySQL a PostgreSQL es un proceso que requiere convertir el esquema, transformar los datos y adaptar las consultas SQL de la aplicación para aprovechar las características superiores de PostgreSQL. Con pgLoader, la mayor parte del proceso se puede automatizar, aunque siempre hay diferencias de sintaxis y comportamiento que requieren revisión manual antes de poner la migración en producción.

Requisitos Previos

  • Acceso de lectura a la base de datos MySQL de origen
  • Instancia PostgreSQL de destino configurada y accesible
  • pgLoader instalado en el servidor de migración
  • Suficiente espacio en disco para el proceso de migración
  • Copia de seguridad completa de la base de datos MySQL antes de empezar
# Hacer un backup completo de MySQL antes de cualquier cambio
mysqldump \
  --host=localhost \
  --user=root \
  --password \
  --all-databases \
  --single-transaction \
  --routines \
  --triggers \
  --events \
  > backup_mysql_completo_$(date +%Y%m%d).sql

# Verificar el backup
wc -l backup_mysql_completo_*.sql

Análisis del Esquema MySQL

Antes de migrar, es fundamental entender el esquema actual y los posibles problemas de compatibilidad.

# Exportar el esquema completo de MySQL para revisión
mysqldump \
  --no-data \
  --routines \
  --triggers \
  --host=localhost \
  --user=root \
  --password \
  mibase > esquema_mysql.sql

# Ver las tablas y sus tipos de columnas
mysql -u root -p mibase -e "
SELECT 
  TABLE_NAME,
  COLUMN_NAME,
  DATA_TYPE,
  COLUMN_TYPE,
  IS_NULLABLE,
  COLUMN_DEFAULT,
  EXTRA
FROM information_schema.COLUMNS
WHERE TABLE_SCHEMA = 'mibase'
ORDER BY TABLE_NAME, ORDINAL_POSITION;"

# Buscar tipos de datos problemáticos
mysql -u root -p mibase -e "
SELECT TABLE_NAME, COLUMN_NAME, DATA_TYPE, COLUMN_TYPE
FROM information_schema.COLUMNS
WHERE TABLE_SCHEMA = 'mibase'
  AND DATA_TYPE IN ('tinyint', 'mediumint', 'year', 'set', 'enum', 'longblob')
ORDER BY TABLE_NAME;"

# Listar stored procedures y funciones (requieren reescritura manual)
mysql -u root -p mibase -e "
SELECT ROUTINE_TYPE, ROUTINE_NAME, ROUTINE_DEFINITION
FROM information_schema.ROUTINES
WHERE ROUTINE_SCHEMA = 'mibase';"

# Ver todos los triggers (requieren adaptación)
mysql -u root -p mibase -e "SHOW TRIGGERS FROM mibase\G"

# Tamaño de cada tabla para estimar tiempo de migración
mysql -u root -p -e "
SELECT 
  TABLE_NAME,
  ROUND(((DATA_LENGTH + INDEX_LENGTH) / 1024 / 1024), 2) as 'Tamaño (MB)',
  TABLE_ROWS as 'Filas (aprox)'
FROM information_schema.TABLES
WHERE TABLE_SCHEMA = 'mibase'
ORDER BY (DATA_LENGTH + INDEX_LENGTH) DESC;"

Instalación de pgLoader

# Instalar pgLoader (Ubuntu/Debian)
apt update && apt install -y pgloader

# Para versiones más recientes, compilar desde código fuente o usar Docker
docker pull dimitri/pgloader

# Verificar la instalación
pgloader --version

# Para Rocky Linux/CentOS - usar Docker
docker run --rm dimitri/pgloader pgloader --version

Migración de Datos con pgLoader

# Migración básica en una sola línea (sin archivo de configuración)
pgloader mysql://usuario:contraseña@localhost/mibase \
         postgresql://postgres:contraseña@postgres-server/mibase_pg

# Migración con archivo de configuración (recomendado para producción)
cat > migracion-mysql-pg.load << 'EOF'
LOAD DATABASE
  FROM      mysql://root:password@localhost/mibase
  INTO      postgresql://postgres:password@localhost/mibase_pg

-- Transformaciones y opciones durante la migración
WITH
  workers = 4,                    -- Número de workers paralelos
  concurrency = 1,                -- Concurrencia para cada worker
  multiple readers per thread,    -- Múltiples lectores por thread
  rows per range = 50000          -- Filas por chunk

SET
  PostgreSQL PARAMETERS
    maintenance_work_mem TO '512MB',
    work_mem TO '128MB'

CAST
  -- Convertir tinyint(1) a boolean (MySQL lo usa para booleans)
  type tinyint when (= precision 1) to boolean drop typemod using tinyint-to-boolean,
  
  -- Convertir tipos que no existen en PostgreSQL
  type int when unsigned to bigint drop typemod,
  type bigint when unsigned to numeric drop typemod,
  
  -- Convertir YEAR a integer
  type year to integer drop typemod,
  
  -- Convertir ENUM a tipo text
  type enum to text drop typemod,
  
  -- Convertir SET a array de texto
  type set to text drop typemod

-- Excluir tablas específicas de la migración
EXCLUDING TABLE NAMES MATCHING 'tmp_', 'cache_', 'sessions'

-- Incluir solo ciertas tablas (alternativa a EXCLUDING)
-- INCLUDING TABLE NAMES MATCHING 'usuarios', 'pedidos', 'productos'

-- Renombrar tablas durante la migración
RENAMING TABLE 'old_name' TO 'new_name'

BEFORE LOAD DO
  -- Ejecutar SQL en PostgreSQL antes de la migración
  $$ CREATE SCHEMA IF NOT EXISTS mibase; $$,
  $$ SET search_path TO mibase, public; $$

AFTER LOAD DO
  -- Ejecutar SQL en PostgreSQL después de la migración
  $$ ANALYZE; $$,
  $$ SELECT pg_stat_reset(); $$;
EOF

# Ejecutar la migración
pgloader migracion-mysql-pg.load

# Ejecutar con logs detallados
pgloader --verbose migracion-mysql-pg.load 2>&1 | tee migracion.log

Conversión del Esquema

Diferencias clave entre MySQL y PostgreSQL que requieren atención manual:

-- =============================================
-- TIPOS DE DATOS: MySQL → PostgreSQL
-- =============================================

-- MySQL: AUTO_INCREMENT → PostgreSQL: SERIAL o GENERATED ALWAYS AS IDENTITY
-- MySQL:
CREATE TABLE usuarios (
  id INT AUTO_INCREMENT PRIMARY KEY
);
-- PostgreSQL:
CREATE TABLE usuarios (
  id SERIAL PRIMARY KEY
  -- O mejor (PostgreSQL 10+):
  -- id INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY
);

-- MySQL: TINYINT(1) → PostgreSQL: BOOLEAN
-- MySQL:
activo TINYINT(1) NOT NULL DEFAULT 1
-- PostgreSQL:
activo BOOLEAN NOT NULL DEFAULT TRUE

-- MySQL: DATETIME → PostgreSQL: TIMESTAMP
-- MySQL:
fecha_creacion DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
-- PostgreSQL:
fecha_creacion TIMESTAMP DEFAULT CURRENT_TIMESTAMP
-- (ON UPDATE no existe; usar un trigger en PostgreSQL)

-- MySQL: TEXT con FULLTEXT INDEX → PostgreSQL: tsvector
-- MySQL:
ALTER TABLE articulos ADD FULLTEXT INDEX idx_contenido (titulo, cuerpo);
-- PostgreSQL:
ALTER TABLE articulos ADD COLUMN busqueda tsvector 
  GENERATED ALWAYS AS (to_tsvector('spanish', titulo || ' ' || cuerpo)) STORED;
CREATE INDEX idx_busqueda ON articulos USING GIN (busqueda);

-- =============================================
-- FUNCIONES Y SINTAXIS DIFERENTES
-- =============================================

-- MySQL: IFNULL → PostgreSQL: COALESCE
SELECT IFNULL(campo, 'valor_defecto') FROM tabla;  -- MySQL
SELECT COALESCE(campo, 'valor_defecto') FROM tabla; -- PostgreSQL

-- MySQL: GROUP_CONCAT → PostgreSQL: STRING_AGG
SELECT GROUP_CONCAT(nombre ORDER BY id SEPARATOR ', ') FROM tabla;  -- MySQL
SELECT STRING_AGG(nombre, ', ' ORDER BY id) FROM tabla;              -- PostgreSQL

-- MySQL: LIMIT offset, count → PostgreSQL: LIMIT count OFFSET offset
SELECT * FROM tabla LIMIT 10, 20;        -- MySQL (offset=10, count=20)
SELECT * FROM tabla LIMIT 20 OFFSET 10;  -- PostgreSQL

-- MySQL: backticks para nombres → PostgreSQL: comillas dobles
SELECT `nombre-columna` FROM `mi-tabla`;   -- MySQL
SELECT "nombre-columna" FROM "mi-tabla";   -- PostgreSQL (o sin comillas si no hay caracteres especiales)

-- MySQL: ON DUPLICATE KEY UPDATE → PostgreSQL: ON CONFLICT
INSERT INTO tabla (id, valor) VALUES (1, 'nuevo')
  ON DUPLICATE KEY UPDATE valor = 'nuevo';  -- MySQL

INSERT INTO tabla (id, valor) VALUES (1, 'nuevo')
  ON CONFLICT (id) DO UPDATE SET valor = EXCLUDED.valor;  -- PostgreSQL

Adaptación de Consultas SQL

# Script para encontrar patrones de MySQL en el código de la aplicación
grep -rn "IFNULL\|GROUP_CONCAT\|ON DUPLICATE KEY\|LIMIT [0-9]*, [0-9]*\|AUTO_INCREMENT\|TINYINT(1)" \
  /var/www/mi-aplicacion/ --include="*.php" --include="*.py" --include="*.js"

# Reemplazos comunes con sed (revisar antes de aplicar)
# IFNULL → COALESCE
sed -i 's/IFNULL(/COALESCE(/gi' queries.sql

# GROUP_CONCAT → STRING_AGG (requiere ajuste manual del separador)
grep -n "GROUP_CONCAT" queries.sql

Estrategia de Pruebas

# Paso 1: Verificar el conteo de filas por tabla
# En MySQL:
mysql -u root -p mibase -e "
SELECT TABLE_NAME, TABLE_ROWS 
FROM information_schema.TABLES 
WHERE TABLE_SCHEMA = 'mibase'
ORDER BY TABLE_NAME;" > conteo_mysql.txt

# En PostgreSQL:
psql -h postgres-server -U postgres mibase_pg -c "
SELECT schemaname, relname as tabla, n_live_tup as filas
FROM pg_stat_user_tables
ORDER BY relname;" > conteo_pg.txt

# Comparar los conteos
diff conteo_mysql.txt conteo_pg.txt

# Paso 2: Verificar integridad de datos en tablas clave
# Comparar sumas de control para detectar diferencias en los datos
mysql -u root -p mibase -e "
SELECT MD5(GROUP_CONCAT(id, nombre, email ORDER BY id)) as checksum 
FROM usuarios;" 

psql -h postgres-server -U postgres mibase_pg -c "
SELECT MD5(STRING_AGG(id::text || nombre || email, '' ORDER BY id)) as checksum 
FROM usuarios;"

# Paso 3: Ejecutar las pruebas de la aplicación contra PostgreSQL
DATABASE_URL="postgresql://user:pass@postgres-server/mibase_pg" \
  python -m pytest tests/ -v

# Paso 4: Prueba de carga con la nueva base de datos
# Usar pgbench u otras herramientas de benchmarking
pgbench -h postgres-server -U postgres -d mibase_pg -c 20 -T 60

Plan de Rollback

# Antes de la migración definitiva, asegurar que la vuelta atrás es posible

# Configurar replicación continua de MySQL para mantener el origen actualizado
# Configurar la aplicación para escribir en ambas bases de datos en paralelo

# En el momento del cambio definitivo:
# 1. Poner la aplicación en modo mantenimiento
# 2. Hacer un dump final de MySQL con solo los cambios recientes
mysqldump \
  --single-transaction \
  --where="fecha_modificacion >= '2024-01-15 00:00:00'" \
  mibase tabla_importante > cambios_finales.sql

# 3. Migrar los cambios finales a PostgreSQL
pgloader cambios-finales.load

# 4. Verificar que los datos están sincronizados
# 5. Cambiar la cadena de conexión de la aplicación a PostgreSQL
# 6. Verificar que la aplicación funciona correctamente

# Si algo falla, el rollback es revertir la cadena de conexión a MySQL
# La base de datos MySQL no se modifica durante la migración
echo "Rollback: cambiar DATABASE_URL de vuelta a MySQL"
echo "mysql://usuario:contraseña@localhost/mibase"

Solución de Problemas

Error: columna de tipo ENUM no se migra correctamente:

# pgLoader convierte ENUM a TEXT, verificar que los valores son correctos
psql -d mibase_pg -c "SELECT DISTINCT estado FROM pedidos ORDER BY 1;"

# Si se necesita el tipo ENUM en PostgreSQL (más restrictivo):
psql -d mibase_pg -c "
CREATE TYPE estado_pedido AS ENUM ('pendiente', 'procesando', 'enviado', 'entregado');
ALTER TABLE pedidos ALTER COLUMN estado TYPE estado_pedido USING estado::estado_pedido;"

Migración muy lenta:

# Deshabilitar temporalmente los índices durante la migración
psql -d mibase_pg -c "
SELECT 'ALTER TABLE ' || tablename || ' DISABLE TRIGGER ALL;'
FROM pg_tables WHERE schemaname = 'public';" | psql -d mibase_pg

# Volver a habilitar después de la migración
# y reconstruir los índices en paralelo
psql -d mibase_pg -c "REINDEX DATABASE CONCURRENTLY mibase_pg;"

Errores de codificación de caracteres:

# Verificar la codificación de MySQL
mysql -u root -p mibase -e "SHOW VARIABLES LIKE 'character%';"

# Crear la base PostgreSQL con la misma codificación
createdb --encoding=UTF8 --locale=es_ES.UTF-8 mibase_pg

# Si hay problemas con caracteres especiales, usar iconv
mysqldump mibase | iconv -f latin1 -t utf8 | psql mibase_pg

Conclusión

La migración de MySQL a PostgreSQL es un proyecto que requiere planificación cuidadosa pero es completamente alcanzable con las herramientas correctas. pgLoader automatiza la mayor parte del trabajo pesado de transferencia de datos, mientras que la revisión manual del esquema y las consultas SQL garantiza que la aplicación funcione correctamente en el nuevo motor. La clave del éxito es hacer pruebas exhaustivas en un entorno de staging que sea lo más parecido posible a producción antes de ejecutar el cambio definitivo.