Replicació

Què és la replicació i per a què serveix

La replicació és el procés de copiar i mantenir sincronitzades les dades d'una instància de SGBD (el primari o primary/master) en una o més instàncies addicionals (les rèpliques o replicas/standbys/slaves).

Objectius principals

Objectiu	Explicació
Alta disponibilitat (HA)	Si el primari falla, una rèplica pot assumir el rol de primari (failover) amb pèrdua mínima de dades.
Escalabilitat de lectura	Les consultes SELECT es distribueixen entre les rèpliques, descarregant el primari.
Distribució geogràfica	Una rèplica en una altra regió ofereix baixa latència a usuaris remots.
Còpies de seguretat sense impacte	Executar pg_dump o mysqldump sobre una rèplica evita afectar el rendiment del primari.
Proves i migracions	Es pot promoure una rèplica per fer proves de migració sense risc.

---

Diagrama: primari → rèpliques

sequenceDiagram
    participant App as Aplicació
    participant P as Primari (escriptura)
    participant R1 as Rèplica 1 (lectura)
    participant R2 as Rèplica 2 (lectura)

    App->>P: INSERT / UPDATE / DELETE
    P->>P: Escriu al WAL/binlog
    P-->>R1: Envia canvis (streaming/binlog)
    P-->>R2: Envia canvis (streaming/binlog)
    R1->>R1: Aplica canvis (lag possible)
    R2->>R2: Aplica canvis (lag possible)
    App->>R1: SELECT (lectura)
    App->>R2: SELECT (lectura)
    Note over R1,R2: Replication lag = temps entre<br/>el canvi al primari i la seva<br/>aplicació a la rèplica

---

Tipus de replicació

Replicació síncrona vs asíncrona

	Asíncrona	Síncrona
Com funciona	El primari confirma la transacció sense esperar la rèplica	El primari espera que almenys una rèplica confirmi que ha rebut (i potencialment aplicat) els canvis
Latència d'escriptura	Igual al primari	Augmentada (espera xarxa)
RPO en cas de fallada	Potencial pèrdua de dades (les no enviades)	RPO = 0 (cap pèrdua)
Cas d'ús	Escalabilitat de lectura, HA amb RPO acceptable	Transaccions crítiques, RPO = 0

Statement-based vs row-based vs mixed (MySQL)

Mode	Descripció	Avantatges	Desavantatges
Statement-based	Replica les sentències SQL tal com son	Log compacte	No determinista (funcions com NOW(), RAND())
Row-based	Replica les files afectades (before/after)	Determinista, correcte sempre	Log pot ser gran per a UPDATE massius
Mixed	Usa statement-based quan és segur, row-based quan cal	Equilibri	Complexitat

Replicació física vs lògica (PostgreSQL)

	Física (Streaming Replication)	Lògica (Logical Replication)
Unitat de replicació	Blocs de pàgines WAL (byte a byte)	Canvis lògics (INSERT/UPDATE/DELETE)
Versió del motor	Idèntica al primari	Pot diferir (PostgreSQL 10+)
Granularitat	Tota la instància	Per taula o conjunt de taules
Cas d'ús	HA, hot standby, PITR	Migració, replicació selectiva, actualitzacions de versió

---

Configuració de la replicació per motor

PostgreSQLMySQL / MariaDBSQL ServerOracle

# ─── Al servidor PRIMARI ───────────────────────────────────────────────

# postgresql.conf
wal_level = replica          # o 'logical' per a replicació lògica
max_wal_senders = 5          # nombre màxim de processos de streaming
wal_keep_size = 1024         # MB de WAL a conservar (per si la rèplica va endarrere)
synchronous_commit = on      # 'on' per a asíncrona, 'remote_apply' per a síncrona estricta
# Per a replicació síncrona (opcional):
# synchronous_standby_names = 'standby1'

# pg_hba.conf — permetre la connexió de replicació
# host  replication  replicant  192.168.1.101/32  scram-sha-256

-- Crear l'usuari de replicació al primari
CREATE ROLE replicant WITH REPLICATION LOGIN PASSWORD 'contrasenya_segura';

# ─── Al servidor RÈPLICA ───────────────────────────────────────────────

# 1. Fer una còpia base des del primari
pg_basebackup \
    -h 192.168.1.100 \
    -U replicant \
    -D /var/lib/postgresql/data \
    -P \
    -Xs \
    --checkpoint=fast

# 2. Crear el fitxer senyal (PostgreSQL 12+)
touch /var/lib/postgresql/data/standby.signal

# 3. Configurar la connexió al primari (postgresql.conf a la rèplica)
# primary_conninfo = 'host=192.168.1.100 port=5432 user=replicant password=contrasenya_segura'
# restore_command = 'cp /var/lib/postgresql/wal_archive/%f %p'  # si uses arxiu WAL

-- Monitorar la replicació al primari
SELECT
    client_addr,
    usename,
    application_name,
    state,
    sent_lsn,
    write_lsn,
    flush_lsn,
    replay_lsn,
    (sent_lsn - replay_lsn) AS lag_bytes,
    write_lag,
    flush_lag,
    replay_lag,
    sync_state
FROM pg_stat_replication;

# ─── my.cnf al servidor PRIMARI ───────────────────────────────────────

[mysqld]
server-id               = 1
log_bin                 = /var/log/mysql/mysql-bin.log
binlog_format           = ROW            # ROW és el recomanat
binlog_expire_logs_seconds = 604800      # 7 dies
max_binlog_size         = 100M
binlog_row_image        = FULL           # FULL, MINIMAL, NOBLOB
# gtid_mode             = ON            # Global Transaction IDs (recomanat)
# enforce_gtid_consistency = ON

-- Crear usuari de replicació al primari
CREATE USER 'replica_user'@'192.168.1.101' IDENTIFIED BY 'contrasenya_segura';
GRANT REPLICATION SLAVE ON *.* TO 'replica_user'@'192.168.1.101';
FLUSH PRIVILEGES;

-- Obtenir la posició actual del binlog (sense GTIDs)
SHOW BINARY LOG STATUS;
-- Exemple de sortida:
-- File: mysql-bin.000003, Position: 154

-- Fer un dump del primari (per a la còpia inicial de la rèplica)
-- mysqldump --all-databases --single-transaction --master-data=2 \
--   -u root -p > dump_primari.sql

# ─── my.cnf al servidor RÈPLICA ───────────────────────────────────────

[mysqld]
server-id    = 2
relay_log    = /var/log/mysql/mysql-relay-bin.log
read_only    = ON
# super_read_only = ON  # Impedeix que fins i tot l'usuari root escrigui

-- Configurar la rèplica (MySQL 8.0+ usa CHANGE REPLICATION SOURCE)
CHANGE REPLICATION SOURCE TO
    SOURCE_HOST     = '192.168.1.100',
    SOURCE_PORT     = 3306,
    SOURCE_USER     = 'replica_user',
    SOURCE_PASSWORD = 'contrasenya_segura',
    SOURCE_LOG_FILE = 'mysql-bin.000003',
    SOURCE_LOG_POS  = 154;

-- Iniciar la replicació
START REPLICA;

-- Verificar l'estat
SHOW REPLICA STATUS\G
-- Comprovar: Replica_IO_Running: Yes, Replica_SQL_Running: Yes
-- i Seconds_Behind_Source: 0 (o proper a 0)

Always On Availability Groups (SQL Server 2012+):
─────────────────────────────────────────────────
Es tracta de la solució HA principal de SQL Server. Permet tenir:
- Fins a 9 rèpliques secundàries (5 síncrona a SQL Server 2022).
- Failover automàtic amb les rèpliques síncrones.
- Rèpliques llegibles (per a descàrrega de consultes).
- Listener: un nom DNS/IP virtual que sempre apunta al primari.

Configuració bàsica (requereix WSFC — Windows Server Failover Cluster):

-- Habilitar Always On (PowerShell o SQL Server Configuration Manager)
-- Enable-SqlAlwaysOn -ServerInstance "NODE1" -Force

-- Crear l'Availability Group
CREATE AVAILABILITY GROUP [AG_Producció]
    WITH (
        AUTOMATED_BACKUP_PREFERENCE = SECONDARY,
        FAILURE_CONDITION_LEVEL = 3,
        HEALTH_CHECK_TIMEOUT = 30000,
        DB_FAILOVER = ON,
        DTC_SUPPORT = NONE,
        CLUSTER_TYPE = WSFC
    )
    FOR DATABASE [BaseDades1], [BaseDades2]
    REPLICA ON
        N'NODE1' WITH (
            ENDPOINT_URL = N'TCP://NODE1.domini.local:5022',
            FAILOVER_MODE = AUTOMATIC,
            AVAILABILITY_MODE = SYNCHRONOUS_COMMIT,
            SEEDING_MODE = AUTOMATIC,
            SECONDARY_ROLE (ALLOW_CONNECTIONS = READ_ONLY)
        ),
        N'NODE2' WITH (
            ENDPOINT_URL = N'TCP://NODE2.domini.local:5022',
            FAILOVER_MODE = AUTOMATIC,
            AVAILABILITY_MODE = SYNCHRONOUS_COMMIT,
            SEEDING_MODE = AUTOMATIC,
            SECONDARY_ROLE (ALLOW_CONNECTIONS = READ_ONLY)
        );

-- Listener (nom DNS virtual per als clients)
ALTER AVAILABILITY GROUP [AG_Producció]
ADD LISTENER N'ag-listener' (
    WITH IP ((N'192.168.1.50', N'255.255.255.0')),
    PORT = 1433
);

Log Shipping (solució més senzilla, no HA automàtica):
───────────────────────────────────────────────────────
Còpia periòdica dels fitxers de log de transaccions al secundari.
- RTO: minuts (restauració manual del log).
- RPO: igual a la freqüència de còpia (típicament 15-60 minuts).
- Configurable des de SSMS: Propietats de la BD → Transaction Log Shipping.

Oracle Data Guard:
──────────────────
Solució oficial d'Oracle per a DR i HA. Inclou:
- Physical Standby: rèplica física byte a byte (equivalent a streaming replication).
- Logical Standby: rèplica lògica (SQL Apply).
- Active Data Guard (licència addicional): rèplica llegible mentre aplica canvis.

Modes de protecció:
- Maximum Performance (per defecte): asíncrona, sense impacte de latència.
- Maximum Availability: síncrona, degrada a asíncrona si la xarxa falla.
- Maximum Protection: síncrona estricta, el primari s'atura si no pot enviar redo.

-- Al primari: activar arxivament i configurar Data Guard
ALTER SYSTEM SET log_archive_mode = ARCHIVELOG SCOPE=SPFILE;
ALTER SYSTEM SET log_archive_dest_2 =
    'SERVICE=standby_db ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=standby'
    SCOPE=BOTH;
ALTER SYSTEM SET fal_server = standby_db SCOPE=BOTH;
ALTER SYSTEM SET fal_client = primary_db SCOPE=BOTH;
ALTER SYSTEM SET standby_file_management = AUTO SCOPE=BOTH;

-- Crear el fitxer de control per al standby
ALTER DATABASE CREATE STANDBY CONTROLFILE AS '/tmp/standby.ctl';

-- Al standby: iniciar la recuperació gestionada
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE
    USING CURRENT LOGFILE DISCONNECT FROM SESSION;

-- Verificar l'estat (al primari)
SELECT dest_id, status, target, archiver, schedule,
       destination, error FROM v$archive_dest WHERE dest_id <= 2;

-- Verificar lag (al standby)
SELECT name, value, unit FROM v$dataguard_stats
WHERE name IN ('apply lag', 'transport lag', 'estimated startup time');

---

Monitoratge de l'estat de la replicació

PostgreSQLMySQL / MariaDBSQL ServerOracle

-- Al primari: veure les rèpliques connectades i el seu lag
SELECT
    application_name,
    client_addr,
    state,
    sync_state,
    replay_lag,
    write_lag,
    flush_lag,
    pg_size_pretty(sent_lsn - replay_lsn) AS lag_aproximat
FROM pg_stat_replication;

-- A la rèplica: veure informació del receptor WAL
SELECT
    status,
    receive_start_lsn,
    received_lsn,
    last_msg_send_time,
    last_msg_receipt_time,
    sender_host,
    conninfo
FROM pg_stat_wal_receiver;

-- Lag en temps (rèplica)
SELECT now() - pg_last_xact_replay_timestamp() AS lag_temps;

-- Verificar si estem en mode standby
SELECT pg_is_in_recovery();

-- Estat complet de la rèplica
SHOW REPLICA STATUS\G

-- Camps clau:
-- Replica_IO_Running: Yes    → connexió al primari OK
-- Replica_SQL_Running: Yes   → aplicació de canvis OK
-- Seconds_Behind_Source: 0   → lag en segons (0 = sincronitzat)
-- Last_Error: (buit)         → sense errors

-- Veure el binlog actual al primari
SHOW BINARY LOG STATUS;
SHOW BINARY LOGS;

-- Veure el procés de replicació
SHOW PROCESSLIST;
-- Buscar threads amb Command: Binlog Dump (primari)
-- i Command: Slave_IO, Slave_SQL (rèplica)

-- Estadístiques de rendiment de la rèplica (Performance Schema)
SELECT * FROM performance_schema.replication_applier_status_by_worker;
SELECT * FROM performance_schema.replication_connection_status;

-- Estat de les rèpliques en un Availability Group
SELECT
    ar.replica_server_name,
    drs.database_name,
    drs.synchronization_state_desc,
    drs.synchronization_health_desc,
    drs.log_send_queue_size,
    drs.log_send_rate,
    drs.redo_queue_size,
    drs.redo_rate,
    drs.secondary_lag_seconds
FROM sys.dm_hadr_database_replica_states drs
JOIN sys.availability_replicas ar ON drs.replica_id = ar.replica_id;

-- Estat general de les rèpliques
SELECT
    replica_server_name,
    join_state_desc,
    operational_state_desc,
    recovery_health_desc,
    synchronization_health_desc,
    connected_state_desc
FROM sys.dm_hadr_availability_replica_states
JOIN sys.availability_replicas ON dm_hadr_availability_replica_states.replica_id = availability_replicas.replica_id;

-- Estadístiques de Data Guard
SELECT name, value, unit, time_computed
FROM v$dataguard_stats
ORDER BY name;

-- Arxius de redo enviats vs aplicats
SELECT
    dest_id,
    archived_thread#,
    archived_seq#,
    applied_thread#,
    applied_seq#,
    gap_status
FROM v$archive_dest_status
WHERE status = 'VALID' AND target = 'STANDBY';

---

Promoció d'una rèplica a primari (failover)

PostgreSQLMySQL / MariaDBSQL ServerOracle

# Promoure el standby a primari (PostgreSQL 12+)
# Mètode 1: fitxer trigger
touch /var/lib/postgresql/data/promote.signal  # PostgreSQL detecta el fitxer i promou

# Mètode 2: pg_ctl
pg_ctl promote -D /var/lib/postgresql/data

# Mètode 3: funció SQL (amb privilegis superuser)
# SELECT pg_promote();

-- Verificar que la promoció ha tingut lloc
SELECT pg_is_in_recovery();  -- Hauria de retornar FALSE

-- A la rèplica: aturar la replicació i promoure-la a primari
STOP REPLICA;
RESET REPLICA ALL;

-- Ara la rèplica és un servidor independent
-- Desactivar read_only si estava activat
SET GLOBAL read_only = 0;
SET GLOBAL super_read_only = 0;

-- Actualitzar la configuració my.cnf per eliminar la configuració de rèplica
-- i afegir log_bin si es vol que accepti rèpliques noves

-- Les altres rèpliques hauran de reconectar-se al nou primari:
CHANGE REPLICATION SOURCE TO
    SOURCE_HOST = 'nou_primari_ip',
    SOURCE_USER = 'replica_user',
    SOURCE_PASSWORD = 'contrasenya_segura',
    SOURCE_AUTO_POSITION = 1;  -- Si s'usen GTIDs
START REPLICA;

-- Failover manual a una rèplica síncrona (sense pèrdua de dades)
ALTER AVAILABILITY GROUP [AG_Producció] FAILOVER;

-- Failover forçat a una rèplica asíncrona (possible pèrdua de dades!)
ALTER AVAILABILITY GROUP [AG_Producció] FORCE_FAILOVER_ALLOW_DATA_LOSS;

-- Veure el nou rol de cada rèplica
SELECT replica_server_name, role_desc
FROM sys.dm_hadr_availability_replica_states
JOIN sys.availability_replicas ON dm_hadr_availability_replica_states.replica_id = availability_replicas.replica_id;

-- Failover a l'standby (Data Guard Broker — recomanat)
-- DGMGRL> FAILOVER TO standby_db;

-- Switchover (planned failover, sense pèrdua de dades)
-- DGMGRL> SWITCHOVER TO standby_db;

-- Manual (sense Data Guard Broker):
-- Al primari (si accessible):
ALTER DATABASE COMMIT TO SWITCHOVER TO STANDBY WITH SESSION SHUTDOWN;
-- A l'standby:
ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY WITH SESSION SHUTDOWN;
ALTER DATABASE OPEN;

---

Replicació asíncrona i pèrdua de dades

Amb replicació asíncrona, si el primari cau de cop (hard crash), les transaccions que van ser confirmades al primari però no enviades a la rèplica es perden. Això és el "replication lag" en el seu pitjor cas. Per a sistemes crítics amb RPO = 0, usa replicació síncrona, però accepta el cost de latència addicional.

---

Miniactivitat — AC0606

Objectiu: Configurar i verificar la replicació streaming de PostgreSQL entre dos contenidors Docker.

Passos:

1. Crea un fitxer docker-compose.yml amb dos serveis PostgreSQL (primary i replica).

2. Configura el primari amb wal_level=replica, max_wal_senders=3.

3. Crea l'usuari replicant al primari i afegeix-lo a pg_hba.conf.

4. Usa pg_basebackup des del contenidor rèplica per fer la còpia base.

5. Crea el fitxer standby.signal i configura primary_conninfo.

6. Inicia la rèplica i verifica amb SELECT pg_is_in_recovery(); (ha de retornar t).

7. Insereix files al primari i verifica que apareixen a la rèplica.

8. Consulta pg_stat_replication al primari per veure el lag.

Lliura: Captura de pantalla del pg_stat_replication mostrant la rèplica connectada i el lag proper a zero.