Replicación en PostgreSQL y MySQL

Por qué replicar

La replicación crea una o varias copias de la base de datos principal (primary/master) en otros servidores (replicas/standby). Los usos principales:

1. Alta disponibilidad (HA): si el primary cae, una réplica asume el rol (failover).
2. Distribución de carga de lectura: redirige SELECTs a réplicas, dejando las escrituras al primary (pattern read-replicas).
3. Backup en caliente: haces pg_basebackup o mysqldump desde la réplica sin impactar al primary.
4. Geo-distribución: réplicas en regiones cercanas al usuario para latencia baja.
5. Migraciones: replicación lógica para pasar datos a un nuevo servidor con versión distinta o esquema evolucionado.

PostgreSQL: streaming replication (física)

La replicación física envía el WAL (Write-Ahead Log) byte a byte al standby. El WAL es un log donde PostgreSQL registra cada cambio antes de aplicarlo. La réplica reproduce el WAL y mantiene una copia idéntica a nivel de archivo.

Configuración en el primary

En postgresql.conf:

wal_level = replica                # o 'logical' si tambien quieres logical
max_wal_senders = 10
wal_keep_size = 1GB                # WAL a conservar para replicas lentas (PG 13+)
archive_mode = on                  # opcional, para PITR
archive_command = 'cp %p /backup/wal/%f'

En pg_hba.conf permitir conexión de replicación:

host    replication     replicator      10.0.0.0/24     scram-sha-256

Crear usuario:

CREATE ROLE replicator WITH REPLICATION LOGIN PASSWORD 'secreto';

Bootstrap del standby

En el servidor replica:

# 1. Parar postgres si esta corriendo
systemctl stop postgresql

# 2. Limpiar data dir
rm -rf /var/lib/postgresql/data/*

# 3. Clonar desde el primary
pg_basebackup -h primary.host \
              -U replicator \
              -D /var/lib/postgresql/data \
              -X stream \
              -P \
              -R  # crea postgresql.auto.conf con los parametros de replica

# 4. Arrancar
systemctl start postgresql

El flag -R (PG 12+) genera automáticamente el archivo que activa el modo standby con la conexión al primary.

Verificar estado

-- En el primary
SELECT pid, usename, application_name, state, sync_state,
       replay_lag, flush_lag, write_lag
FROM pg_stat_replication;

-- En la replica
SELECT pg_is_in_recovery();  -- t
SELECT pg_last_wal_receive_lsn(), pg_last_wal_replay_lsn();

Lag y sincronía

Por defecto la replicación es asíncrona: el primary no espera confirmación de la réplica para hacer COMMIT. Puedes configurar modos síncronos:

# primary: postgresql.conf
synchronous_commit = on              # espera al menos al local WAL flush
synchronous_standby_names = 'replica1'  # espera ACK de replica1 tambien

Con on, el primary espera a que al menos una réplica confirme antes de devolver COMMIT al cliente. Más seguro, pero más lento ante problemas de red.

Replication slots: evitar pérdida de WAL

Sin slots, si el standby está caído durante mucho tiempo, el primary puede recortar (WAL que el standby aún no replicó), y la replicación se rompe. Los replication slots indican al primary que debe retener ese WAL hasta que el standby lo consuma.

-- Crear slot en el primary
SELECT pg_create_physical_replication_slot('replica1_slot');

-- En la replica, configurar primary_slot_name
-- postgresql.auto.conf (generado por pg_basebackup -R + parametro)
primary_conninfo = 'host=primary.host user=replicator ...'
primary_slot_name = 'replica1_slot'

Cuidado: un slot olvidado sin consumidor bloquea el vacuum del WAL y puede llenar el disco. Monitoriza:

SELECT slot_name, active, restart_lsn,
       pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn)) AS retained
FROM pg_replication_slots;

Logical replication (lógica)

La replicación lógica transmite cambios por tabla como eventos INSERT/UPDATE/DELETE. La réplica aplica esos cambios con SQL lógico, no copia archivos. Ventajas:

• Permite replicar subconjuntos de tablas.
• Funciona entre versiones distintas de PostgreSQL.
• Permite esquemas diferentes en origen y destino (p. ej. renames).
• No necesita que la réplica sea solo-lectura: puedes escribir en ella.

Usos: migración de versión mayor, ETL a data warehouse, CDC (Change Data Capture).

Configuración

En el primary:

wal_level = logical

-- Crear publication con las tablas a replicar
CREATE PUBLICATION mi_publicacion FOR TABLE pedidos, clientes;

-- O todas las tablas (PG 10+)
CREATE PUBLICATION todas_tablas FOR ALL TABLES;

En el destino:

-- Conectar al primary y suscribirse
CREATE SUBSCRIPTION mi_subscripcion
    CONNECTION 'host=primary.host dbname=origen user=replicator password=...'
    PUBLICATION mi_publicacion;

Automáticamente:

1. Se hace un copy inicial de las tablas.
2. Se sincronizan los cambios en streaming.

Monitorizar

-- En el primary
SELECT * FROM pg_stat_replication;
SELECT * FROM pg_publication_tables;

-- En el destino
SELECT * FROM pg_stat_subscription;

Failover: promocionar una réplica

Cuando el primary falla, promueves una réplica a primary:

-- En la replica (PG 12+)
SELECT pg_promote();

O con pg_ctl:

pg_ctl promote -D /var/lib/postgresql/data

La réplica deja el modo read-only y acepta escrituras. Las otras réplicas (si hay) apuntaban al antiguo primary y necesitan reconfigurarse para apuntar al nuevo.

En producción, nunca hagas failover manual. Usa herramientas:

• Patroni: orquestador popular con etcd/ZooKeeper/Consul.
• repmgr: de 2ndQuadrant.
• Stolon: similar a Patroni.
• PgBouncer + custom scripts: solución DIY ligera.

MySQL: replicación con binlog y GTID

MySQL usa el binary log (binlog) y desde 5.6+ los GTIDs (Global Transaction IDs) para identificar cada transacción de forma única.

Configuración del primary

my.cnf:

[mysqld]
server-id = 1
log-bin = mysql-bin
binlog_format = ROW        # obligatorio para GTID
gtid_mode = ON
enforce_gtid_consistency = ON

Crear usuario:

CREATE USER 'repl'@'%' IDENTIFIED BY 'secreto';
GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%';

Configuración del replica

my.cnf:

server-id = 2
log-bin = mysql-bin
gtid_mode = ON
enforce_gtid_consistency = ON
read_only = ON

Configurar y arrancar:

CHANGE REPLICATION SOURCE TO
    SOURCE_HOST = 'primary.host',
    SOURCE_USER = 'repl',
    SOURCE_PASSWORD = 'secreto',
    SOURCE_AUTO_POSITION = 1;  -- usa GTID

START REPLICA;
SHOW REPLICA STATUS\G;

Los GTIDs permiten un failover más limpio: la nueva réplica sabe exactamente qué transacciones ha visto, sin depender de posiciones del binlog.

Semi-síncrono

Plugin que espera ACK de al menos una réplica antes de COMMIT en el primary. Bueno para evitar pérdida de datos ante crash del primary:

-- Primary
INSTALL PLUGIN rpl_semi_sync_master SONAME 'semisync_master.so';
SET GLOBAL rpl_semi_sync_master_enabled = 1;

-- Replica
INSTALL PLUGIN rpl_semi_sync_slave SONAME 'semisync_slave.so';
SET GLOBAL rpl_semi_sync_slave_enabled = 1;

Patrones de uso

Read replicas para escalado

Aplicación conecta a dos pools:

• Pool "escritor": apunta al primary. Para INSERT/UPDATE/DELETE.
• Pool "lector": round-robin sobre réplicas. Para SELECT.

Cuidado con el lag de replicación: si la aplicación inserta y lee inmediatamente, puede no ver su propia escritura desde la réplica. Patrón para resolverlo:

• Tras una escritura, leer siempre del primary durante N segundos.
• O usar synchronous_commit = on para que la réplica esté siempre al día (coste en latencia).

Migration dolgona con logical replication

Para migrar de PG 14 a PG 17 con downtime mínimo:

1. Levanta el nuevo servidor PG 17.
2. Crea publication en PG 14 y subscription en PG 17.
3. Deja que sincronice semanas si hace falta.
4. En una ventana corta: para la app, confirma lag = 0, cambia el DSN de la app al PG 17, arranca la app.
5. Downtime real: minutos.

Checklist operativo

• [ ] Replication slots creados para todas las réplicas críticas.
• [ ] Monitorización de lag (pg_stat_replication / SHOW REPLICA STATUS).
• [ ] Alarmas si lag > umbral (ej. 30 segundos).
• [ ] WAL archiving configurado para PITR.
• [ ] Plan de failover documentado y probado.
• [ ] synchronous_commit elegido según RPO aceptable.
• [ ] Conexión cifrada (SSL) entre primary y réplicas.
• [ ] Contraseña del usuario de replicación en vault/secret manager.