Còpies de seguretat i recuperació

Tipus de còpies de seguretat

Una estratègia de backup ben dissenyada combina múltiples tipus de còpies per equilibrar el cost d'emmagatzematge, la velocitat de recuperació i el RPO (Recovery Point Objective).

Full (completa)

Una còpia completa (full backup) captura totes les dades en un moment concret. És autosuficient: es pot restaurar directament sense dependre d'altres còpies.

Avantatge: Restauració ràpida i senzilla.
Desavantatge: Llarga durada, gran consum d'espai, gran finestra de backup.

Incremental

Una còpia incremental captura únicament els canvis des de l'última còpia (ja sigui full o incremental anterior).

Avantatge: Molt ràpida, ocupa poc espai.
Desavantatge: Restauració lenta i complexa (cal aplicar la full + tots els incrementals en ordre).

Diferencial

Una còpia diferencial captura els canvis des de l'última còpia full.

Avantatge: Restauració més ràpida que l'incremental (cal la full + el darrer diferencial).
Desavantatge: Creix progressivament al llarg de la setmana.

Lògica vs física

	Lògica	Física
Contingut	SQL (DDL + DML) o format binari intern però portable	Fitxers de dades del SGBD (blocs de dades tal com estan al disc)
Portabilitat	Alta: pot restaurar-se en una versió diferent, SO diferent	Baixa: ha de coincidir la versió del motor
Velocitat de backup	Lenta (ha de llegir i serialitzar totes les files)	Ràpida (còpia de fitxers)
Velocitat de restauració	Lenta (ha de re-executar tots els INSERT/DDL)	Molt ràpida
Granularitat	Per taula, per esquema, per base de dades	Per tota la instància (o tablespace)
PITR possible	No directament	Sí (combinant amb arxiu de WAL/binlog)

RPO i RTO per tipus de backup

graph LR
    subgraph "Estratègia 1 — Full diari"
        B1["Full\nDiumenge"]:::full --> B2["Full\nDilluns"]:::full
        B2 --> B3["Full\nDimarts"]:::full
    end

    subgraph "Estratègia 2 — Full+Incremental"
        C1["Full\nDiumenge"]:::full --> C2["Incr.\nDilluns"]:::incr
        C2 --> C3["Incr.\nDimarts"]:::incr
        C3 --> C4["Incr.\nDimecres"]:::incr
    end

    subgraph "Estratègia 3 — Full+Diferencial"
        D1["Full\nDiumenge"]:::full --> D2["Diff.\nDilluns"]:::diff
        D2 --> D3["Diff.\nDimarts"]:::diff
        D3 --> D4["Diff.\nDimecres"]:::diff
    end

    classDef full fill:#337ab7,color:#fff
    classDef incr fill:#5cb85c,color:#fff
    classDef diff fill:#f0ad4e,color:#333

Estratègia	RPO típic	RTO típic	Espai	Complexitat
Full diari	24 hores	Baix (1 còpia)	Molt alt	Baixa
Full setmanal + incremental diari	Hores	Alt (full + N incrementals)	Mig	Alta
Full setmanal + diferencial diari	Hores	Mig (full + 1 diferencial)	Mig-alt	Mitjana
Continu (PITR amb WAL/binlog)	Segons/minuts	Variable	Mig	Alta

Com el SGBD persisteix les dades: WAL, checkpoints i fitxers

Abans d'entrar en les còpies de seguretat, és essencial comprendre com el motor garanteix que les dades d'una transacció no es perdin mai davant d'una fallada — fins i tot si el servidor cau a mig escriure. Aquest mecanisme és la base sobre la qual es construeix qualsevol estratègia de recuperació.

Principi Write-Ahead Logging (WAL)

El WAL (Write-Ahead Logging, o redo log en terminologia d'Oracle i MySQL) és el principi fonamental de durabilitat de tots els SGBD moderns: abans d'escriure cap canvi als fitxers de dades, el motor sempre escriu primer el registre del canvi al log de transaccions.

D'aquesta manera, si el sistema cau a mig camí, el motor pot reconstruir exactament l'estat consistent simplement llegint el log.

graph LR
    TX["Transacció\nCOMMIT"]:::tx
    LB["Buffer del log\n(memòria)"]:::mem
    WF["WAL / Redo log\n(disc)"]:::disk
    BP["Buffer pool\ndirty pages\n(memòria)"]:::mem
    DF["Fitxers de dades\n(disc)"]:::disk
    CP["Checkpoint"]:::cp

    TX -->|"1 escriu primer"| LB
    LB -->|"2 flush síncron\nal COMMIT"| WF
    TX -->|"3 modifica\npàgines"| BP
    WF -->|"4 COMMIT confirmat\nal client"| TX
    BP -->|"5 dirty pages\nen memòria"| CP
    CP -->|"6 flush asíncron\nal disc"| DF

    classDef tx fill:#003747,stroke:#00e5ff,color:#fff
    classDef mem fill:#1a2a3a,stroke:#4a6a8a,color:#cdd9e0
    classDef disk fill:#1a2a0a,stroke:#4a7a2a,color:#b2dfb2
    classDef cp fill:#2a1a3a,stroke:#9a7aaa,color:#e0d0f0

El WAL es confirma al disc abans del COMMIT

El client rep el COMMIT OK només quan el WAL ha estat escrit físicament al disc. Fins i tot si el servidor cau immediatament després, la transacció no es perdrà: el log ja hi és al disc i el motor la reaplicarà automàticament a l'arrencada.

El buffer pool i les dirty pages

El motor no llegeix ni escriu directament als fitxers de dades per a cada operació. Carrega pàgines (blocs de 8 KB, 16 KB...) a la memòria en el buffer pool (buffer cache a Oracle, InnoDB Buffer Pool a MySQL):

Quan una transacció modifica una fila, modifica la pàgina al buffer pool → la pàgina queda marcada com a dirty (bruta).
La pàgina bruta no s'escriu immediatament al disc: es queda a memòria i el motor la bolcarà quan faci un checkpoint.
El motor sap que pot reconstruir qualsevol pàgina bruta a partir del WAL si cau.

Aquest desacoblament entre escriptura al log (ràpida, seqüencial) i escriptura a dades (lenta, aleatòria) és el que fa que els SGBD siguin ràpids.

Els checkpoints

Un checkpoint és el moment en el qual el SGBD garanteix que totes les dirty pages de memòria han estat escrites als fitxers de dades del disc. Actua com un punt d'ancoratge de recuperació:

Abans del checkpoint: el WAL és necessari per recuperar-se (les pàgines brutes podrien no estar al disc).
Després del checkpoint: els fitxers WAL anteriors ja no son necessaris per a la recuperació i es poden arxivar o eliminar.

graph LR
    CP1["Checkpoint\nLSN 1.000"]:::cp
    W1["TX modifica A\nLSN 1.020"]:::wal
    W2["TX modifica B\nLSN 1.045"]:::wal
    W3["COMMIT\nLSN 1.060"]:::commit
    CP2["Checkpoint\nLSN 1.060"]:::cp
    CRASH["Fallada"]:::crash
    REC["Recuperacio:\nreplica LSN 1000-1060"]:::rec

    CP1 --> W1 --> W2 --> W3 --> CP2
    W3 --> CRASH --> REC

    classDef cp fill:#2a1a3a,stroke:#9a7aaa,color:#e0d0f0
    classDef wal fill:#1a2a3a,stroke:#4a6a8a,color:#cdd9e0
    classDef commit fill:#003d35,stroke:#00bfa5,color:#fff
    classDef crash fill:#3a1010,stroke:#ff5252,color:#fff
    classDef rec fill:#003747,stroke:#00e5ff,color:#fff

El LSN (Log Sequence Number, o SCN a Oracle) és un número únic i creixent que identifica cada entrada del WAL. Permet saber exactament fins a quin punt s'han aplicat els canvis als fitxers de dades.

Configuració dels checkpoints per motor

PostgreSQLMySQL / MariaDBSQL ServerOracle

# postgresql.conf — paràmetres clau
checkpoint_timeout          = 5min   # temps màxim entre checkpoints automàtics
checkpoint_completion_target = 0.9   # reparteix l'escriptura al 90% del temps entre
                                     # checkpoints (evita el pic d'I/O al final)
max_wal_size                = 1GB    # WAL acumulat que pot forçar un checkpoint
min_wal_size                = 80MB   # mínim de WAL reservat al disc

log_checkpoints             = on     # registra cada checkpoint als logs del servidor

-- Veure l'estat actual del WAL i del darrer checkpoint
SELECT pg_current_wal_lsn(), pg_walfile_name(pg_current_wal_lsn());
SELECT checkpoint_lsn, checkpoint_completion_target
FROM pg_control_checkpoint();

# my.cnf — paràmetres clau d'InnoDB
innodb_log_file_size          = 512M  # mida de cada fitxer de redo log
                                       # (a MySQL 8.0+ es gestiona automàticament)
innodb_log_files_in_group     = 2      # nombre de fitxers en rotació
innodb_flush_log_at_trx_commit = 1    # 1 = màxima seguretat (WAL al disc en cada COMMIT)
                                       # 0 = rendiment màxim (risc de perdre 1s de dades)
                                       # 2 = compromís (flush cada segon)
innodb_io_capacity            = 200   # IOPS per als checkpoints (ajustar al disc real)
innodb_buffer_pool_size       = 4G    # buffer pool (normalment 70-80% de la RAM)

-- Veure estat del redo log i dels checkpoints
SHOW ENGINE INNODB STATUS\G
-- Buscar la secció "LOG" per veure LSN, checkpoints i bytes pendents

-- SQL Server realitza checkpoints automàticament i pot ser forçat manualment
CHECKPOINT;   -- força un checkpoint a la BD activa

-- Configurar l'interval màxim en minuts (0 = gestió automàtica, ~1 min)
EXEC sp_configure 'recovery interval (min)', 1;
RECONFIGURE;

-- Veure l'ús i l'estat del log de transaccions
SELECT
    log_reuse_wait_desc,
    log_size_mb    = size * 8.0 / 1024,
    log_used_mb    = FILEPROPERTY(name, 'SpaceUsed') * 8.0 / 1024,
    recovery_model_desc
FROM sys.databases WHERE name = DB_NAME();

-- Veure els VLF (Virtual Log Files) del .ldf
DBCC LOGINFO;

-- Oracle usa Redo Log Groups en rotació (mínim 2, recomanat 3+)
-- Quan un grup es omple, es produeix un "log switch" (similar a un checkpoint)

-- Veure els grups de redo log actuals
SELECT group#, members, bytes/1024/1024 AS mb, status FROM v$log;

-- Veure els fitxers físics de cada grup
SELECT group#, member FROM v$logfile ORDER BY group#;

-- Veure l'últim SCN i la freqüència de log switches
SELECT first_time, next_time,
       (next_time - first_time) * 24 * 60 AS durada_min
FROM v$log_history
ORDER BY first_time DESC FETCH FIRST 10 ROWS ONLY;

-- Configurar la Fast Recovery Area (destinació d'redo logs arxivats i backups)
ALTER SYSTEM SET db_recovery_file_dest      = '/fra'  SCOPE=BOTH;
ALTER SYSTEM SET db_recovery_file_dest_size = 50G     SCOPE=BOTH;
ALTER DATABASE ARCHIVELOG;   -- activar l'arxivament de redo logs (obligatori per PITR)

Simulació interactiva: cicle WAL → Checkpoint

    Simulació interactiva: cicle WAL → Checkpoint
    Pas 0 / 11
  
Simulació del cicle WAL → Checkpoint
Clica «Pas següent» per veure com el SGBD gestiona les transaccions.

Fitxers involucrats per motor

La persistència implica sempre múltiples fitxers amb rols ben diferenciats. Conèixer-los és imprescindible per diagnosticar problemes i dissenyar el backup correctament:

Rol	PostgreSQL	MySQL / InnoDB	SQL Server	Oracle
Dades	`base/<oid>/<relfilenode>`	`*.ibd` per taula	`.mdf`, `.ndf`	`*.dbf` (datafiles)
Redo log (WAL)	`pg_wal/*.wal` (16 MB)	`ib_logfile0/1`	`*.ldf` (VLFs)	Redo log groups
Log de canvis binari	WAL arxivat (`archive_command`)	`mysql-bin.*` (binlog)	Dins el `.ldf`	Archived redo logs
Undo (desfer TX)	Dins les pàgines (MVCC)	`undo_001`, `ibdata1`	Dins el `.ldf`	`undotbs01.dbf`
Control / metadades	`global/pg_control`	`ibdata1` (system TS)	BD `master`	Control files `.ctl`
Configuració	`postgresql.conf`	`my.cnf` / `my.ini`	`sys.configurations`	`SPFILE`, `PFILE`

PostgreSQLMySQL / MariaDBSQL ServerOracle

$PGDATA/                         ← directori de dades de la instància
├── base/                        ← una subcarpeta per BD (OID numèric)
│   └── 16384/                   ← la teva BD
│       ├── 16385                ← fitxer de dades d'una taula (relfilenode)
│       └── 16385_fsm            ← Free Space Map (espai lliure per inserció)
├── global/
│   └── pg_control               ← estat de la instància i LSN de l'últim checkpoint
├── pg_wal/                      ← Write-Ahead Log (fitxers de 16 MB cadascun)
│   ├── 000000010000000000000001
│   └── 000000010000000000000002
├── pg_wal/archive_status/       ← fitxers .done = WAL ja arxivats correctament
├── pg_xact/                     ← estat de cada transacció (commit / abort)
├── pg_subtrans/                 ← subtransaccions (SAVEPOINTs)
├── postgresql.conf              ← configuració del servidor
├── pg_hba.conf                  ← regles d'autenticació
└── postmaster.pid               ← PID del procés principal (existeix si el servidor corre)

Els fitxers WAL s'anomenen <timeline><segment> i fan exactament 16 MB. Quan archive_mode = on, l'archive_command els copia a una ubicació segura un per un, un cop completats.

/var/lib/mysql/
├── ibdata1                  ← system tablespace: metadades + undo (si shared)
├── ib_logfile0              ← redo log grup 1 (a MySQL 8.0+ s'anomena #ib_redo0...)
├── ib_logfile1              ← redo log grup 2 (rotació circular)
├── undo_001, undo_002       ← undo tablespaces separats (MySQL 8+)
├── mysql-bin.000001         ← binary log 1 (WAL lògic: per a PITR i rèplica)
├── mysql-bin.000002
├── mysql-bin.index          ← índex dels binlogs actius
├── #innodb_temp/            ← taules temporals d'InnoDB
├── nom_basedades/
│   ├── clients.ibd          ← dades + índexs de la taula (innodb_file_per_table=ON)
│   └── comandes.ibd
└── mysql/                   ← BD del sistema (usuaris, privilegis, grants)

innodb_flush_log_at_trx_commit = 1 és imprescindible a producció

Amb valor 0 o 2, MySQL no bolca el redo log al disc en cada COMMIT, sinó cada segon. Guanya rendiment però pots perdre fins a 1 segon de transaccions en un crash del SO o tall de llum. A producció, sempre = 1.

Fitxers de dades de la BD:
├── NomBD.mdf            ← Primary Data File (pàgina 0 = capçalera de BD)
├── NomBD_2.ndf          ← Secondary Data File (opcional, per a expansió o filegroups)
└── NomBD_log.ldf        ← Transaction Log (dividit internament en VLFs)

BD del sistema (no tocar mai directament):
├── master.mdf           ← catàleg del servidor: instàncies, logins, configuració
├── msdb.mdf             ← historial de jobs, backups, alertes del SQL Agent
├── model.mdf            ← plantilla: cada nova BD n'hereta el contingut
└── tempdb.mdf           ← BD temporal: es recrea des de zero a cada arrencada

El fitxer .ldf es divideix internament en VLF (Virtual Log Files). El log es pot reutilitzar (truncar) quan el WAL ja s'ha bolcat i s'ha fet un backup del log (mode de recuperació FULL). Si el log creix sense control, el motiu habitual és que no s'estan fent backups del log o que hi ha transaccions de llarga durada obertes.

/u01/app/oracle/oradata/PRODDB/
├── system01.dbf         ← SYSTEM tablespace: diccionari de dades (taules del sistema)
├── sysaux01.dbf         ← SYSAUX: AWR, estadístiques, Enterprise Manager
├── undotbs01.dbf        ← Undo tablespace: dades anteriors per ROLLBACK i MVCC
├── temp01.dbf           ← Temp tablespace: sort, hash joins, resultats intermedis
├── users01.dbf          ← Tablespace d'usuari (dades d'aplicació)
├── redo01.log           ← Redo log group 1 (en rotació amb els altres)
├── redo02.log           ← Redo log group 2
├── redo03.log           ← Redo log group 3
└── control01.ctl        ← Control file: SCN actual, localització de tots els fitxers

/u01/app/oracle/fast_recovery_area/PRODDB/
└── archivelog/
    └── 2024_01_15/
        └── o1_mf_1_42_xxxxx_.arc  ← Archived redo logs (per a PITR i RMAN)

El control file és crític: sense ell Oracle no pot arrancar. Per això sempre s'han de tenir almenys 2 còpies multiplexades en discs físics diferents.

Recuperació automàtica davant de fallades (crash recovery)

Quan el SGBD arrenca després d'una fallada inesperada (tall de llum, kernel panic, procés mort), realitza automàticament una recuperació en dues fases per tornar la base de dades a un estat consistent — sense cap intervenció manual:

graph TD
    A["Fallada del sistema\n(crash, tall de llum...)"]:::crash
    B["Reinici del SGBD"]:::start
    C["Llegeix pg_control / ibdata1 / control file\nLocalitza el LSN / SCN de l'ultim checkpoint"]:::read
    D["Fase REDO\nReplica totes les TX confirmades\ndes del checkpoint que no s'havien\nescrit als fitxers de dades"]:::redo
    E["Fase UNDO\nDesfà les TX sense COMMIT\n(interrompudes per la fallada)\nusant el log d'undo"]:::undo
    F["Base de dades consistent\ni disponible per a connexions"]:::ok

    A --> B --> C --> D --> E --> F

    classDef crash fill:#3a1010,stroke:#ff5252,color:#fff
    classDef start fill:#1a2a3a,stroke:#4a6a8a,color:#cdd9e0
    classDef read fill:#2a1a3a,stroke:#9a7aaa,color:#e0d0f0
    classDef redo fill:#003747,stroke:#00e5ff,color:#fff
    classDef undo fill:#3a2400,stroke:#ff9800,color:#fff
    classDef ok fill:#0d3320,stroke:#00e676,color:#fff

Fase	Acció	Per a qui s'aplica
REDO	Reaplicar canvis del WAL als fitxers de dades	TX amb `COMMIT` que no havien fet flush al disc en el moment de la fallada
UNDO	Desfer canvis parcials usant el log d'undo	TX sense `COMMIT` que estaven obertes quan el sistema va caure

La recuperació és completament automàtica

No cal cap acció manual per part de l'administrador. El temps de recuperació depèn directament de la freqüència dels checkpoints: menys freqüents → més WAL a reproduir → arrencada més lenta. Per això checkpoint_timeout i recovery interval son paràmetres crítics als SLA de disponibilitat.

El WAL arxivat és imprescindible per al PITR

El crash recovery automàtic només cobreix la pèrdua de les dirty pages des de l'últim checkpoint. Per recuperar-se d'errors lògics (un DELETE accidental), cal tenir el WAL arxivat des del backup base — i és per això que les còpies físiques (pg_basebackup, RMAN) sempre s'han de combinar amb l'arxivament del WAL.

Còpies lògiques

PostgreSQLMySQL / MariaDBSQL ServerOracle

# pg_dump: backup d'una base de dades en format SQL o binari
# Format SQL (text): llegible, però lent de restaurar
pg_dump -h localhost -U postgres -d nom_bd > /backups/nom_bd_$(date +%Y%m%d_%H%M%S).sql

# Format custom (-Fc): comprimit, permet restauració selectiva per taula
pg_dump -h localhost -U postgres -d nom_bd \
    -Fc \
    -f /backups/nom_bd_$(date +%Y%m%d_%H%M%S).dump

# Format directory (-Fd): paralel·litzable, bé per a BD grans
pg_dump -h localhost -U postgres -d nom_bd \
    -Fd \
    -j 4 \
    -f /backups/nom_bd_$(date +%Y%m%d_%H%M%S)/

# Backup d'una taula específica
pg_dump -h localhost -U postgres -d nom_bd \
    -t nom_taula \
    -Fc \
    -f /backups/taula_$(date +%Y%m%d).dump

# pg_dumpall: backup de totes les BD + rols + tablespaces (no s'aplica per usuari)
pg_dumpall -h localhost -U postgres > /backups/tot_$(date +%Y%m%d).sql

# pg_restore: restauració des de format custom o directory
pg_restore -h localhost -U postgres \
    -d nova_bd \
    -j 4 \
    /backups/nom_bd_20240115_020000.dump

# Restauració d'una taula específica des del dump
pg_restore -h localhost -U postgres \
    -d nom_bd \
    -t nom_taula \
    /backups/nom_bd_20240115_020000.dump

# mysqldump: backup lògic clàssic
mysqldump \
    -h localhost -u root -p \
    --single-transaction \       # Consistent snapshot sense bloquejar (InnoDB)
    --routines \                  # Inclou funcions i procediments
    --triggers \                  # Inclou triggers
    --events \                    # Inclou events
    nom_bd > /backups/nom_bd_$(date +%Y%m%d_%H%M%S).sql

# Backup de totes les bases de dades
mysqldump \
    -h localhost -u root -p \
    --all-databases \
    --single-transaction \
    > /backups/totes_bds_$(date +%Y%m%d).sql

# Backup comprimit
mysqldump -u root -p --single-transaction nom_bd \
    | gzip > /backups/nom_bd_$(date +%Y%m%d).sql.gz

# Restauració
mysql -h localhost -u root -p nom_bd < /backups/nom_bd_20240115.sql

# mysqlpump (MySQL 5.7+): paral·lel, millor compressió
mysqlpump \
    -u root -p \
    --default-parallelism=4 \
    --compress-output=ZLIB \
    --all-databases \
    > /backups/pump_$(date +%Y%m%d).sql.zlib

# MySQL Shell dump (MySQL 8.0+): molt ràpid, comprimit, validat
# mysqlsh -- util dumpInstance /backups/dump_$(date +%Y%m%d) \
#   --threads=4 --compression=zstd

-- Backup full de la base de dades
BACKUP DATABASE [NomBaseDades]
TO DISK = N'D:\Backups\NomBaseDades_Full_' + CONVERT(VARCHAR, GETDATE(), 112) + '.bak'
WITH
    FORMAT,              -- Sobreescriu qualsevol backup existent al fitxer
    MEDIANAME = N'Backup_NomBaseDades',
    NAME = N'NomBaseDades Full Backup',
    COMPRESSION,
    CHECKSUM,            -- Verifica la integritat al final
    STATS = 10;          -- Mostra % de progrés cada 10%

-- Backup diferencial
BACKUP DATABASE [NomBaseDades]
TO DISK = N'D:\Backups\NomBaseDades_Diff_' + CONVERT(VARCHAR, GETDATE(), 112) + '.bak'
WITH DIFFERENTIAL, COMPRESSION, CHECKSUM, STATS = 10;

-- Backup del log de transaccions
BACKUP LOG [NomBaseDades]
TO DISK = N'D:\Backups\NomBaseDades_Log_' + CONVERT(VARCHAR(8), GETDATE(), 112)
          + '_' + REPLACE(CONVERT(VARCHAR(5), GETDATE(), 108), ':', '') + '.trn'
WITH COMPRESSION, CHECKSUM;

-- Verificar la integritat del backup
RESTORE VERIFYONLY
FROM DISK = N'D:\Backups\NomBaseDades_Full_20240115.bak'
WITH CHECKSUM;

# expdp (Data Pump Export): backup lògic d'alta velocitat
# Full instance
expdp system/password \
    DIRECTORY=DATA_PUMP_DIR \
    DUMPFILE=full_$(date +%Y%m%d).dmp \
    LOGFILE=full_$(date +%Y%m%d).log \
    FULL=YES \
    COMPRESSION=ALL

# Per esquema
expdp system/password \
    SCHEMAS=NOM_ESQUEMA \
    DIRECTORY=DATA_PUMP_DIR \
    DUMPFILE=schema_$(date +%Y%m%d).dmp \
    LOGFILE=schema_$(date +%Y%m%d).log \
    COMPRESSION=ALL

# Per taula
expdp system/password \
    TABLES=NOM_ESQUEMA.NOM_TAULA \
    DIRECTORY=DATA_PUMP_DIR \
    DUMPFILE=taula_$(date +%Y%m%d).dmp

# impdp (Data Pump Import): restauració
impdp system/password \
    DIRECTORY=DATA_PUMP_DIR \
    DUMPFILE=full_20240115.dmp \
    LOGFILE=import_20240115.log \
    FULL=YES

# Restauració d'un esquema amb nou nom
impdp system/password \
    DIRECTORY=DATA_PUMP_DIR \
    DUMPFILE=schema_20240115.dmp \
    REMAP_SCHEMA=ESQUEMA_ORIGEN:ESQUEMA_DESTI

Còpies físiques

PostgreSQLMySQL / MariaDBSQL ServerOracle

# pg_basebackup: còpia física de tota la instància
pg_basebackup \
    -h localhost \
    -U replicant \
    -D /backups/basebackup_$(date +%Y%m%d) \
    -Ft \                    # Format tar (per compressió)
    -z \                     # Comprimir amb gzip
    -P \                     # Mostrar progrés
    -Xs \                    # Inclou WAL per a la consistència (streaming)
    --checkpoint=fast

# Barman (Backup and Recovery Manager for PostgreSQL):
# Eina professional per a backups de PostgreSQL
# barman backup main           → backup full
# barman list-backup main      → llistar backups disponibles
# barman recover main latest /var/lib/postgresql/data  → restaurar l'últim

# pgBackRest: alternativa moderna amb deduplicació i delta restore
# pgbackrest --stanza=main backup --type=full
# pgbackrest --stanza=main backup --type=incr
# pgbackrest --stanza=main restore

# Percona XtraBackup: backup físic d'InnoDB sense bloquejar les escriptures
# Backup full
xtrabackup \
    --backup \
    --user=root \
    --password=secret \
    --target-dir=/backups/xtrabackup_$(date +%Y%m%d)

# Preparar el backup (aplicar els logs de transaccions pendents)
xtrabackup --prepare --target-dir=/backups/xtrabackup_20240115

# Restaurar (el directori de dades ha d'estar buit o no existir)
xtrabackup --copy-back --target-dir=/backups/xtrabackup_20240115

# Backup incremental (basant-se en un backup full anterior)
xtrabackup \
    --backup \
    --user=root \
    --password=secret \
    --target-dir=/backups/xtrabackup_incr_$(date +%Y%m%d) \
    --incremental-basedir=/backups/xtrabackup_20240115

-- SQL Server usa la mateixa comanda BACKUP DATABASE per a backups "físics"
-- (copia els fitxers de dades de manera consistent)

-- Backup a múltiples fitxers (paral·lel, més ràpid per a BD grans)
BACKUP DATABASE [NomBaseDades]
TO
    DISK = N'D:\Backups\NomBD_Part1.bak',
    DISK = N'D:\Backups\NomBD_Part2.bak',
    DISK = N'D:\Backups\NomBD_Part3.bak'
WITH COMPRESSION, CHECKSUM, STATS = 5;

-- Backup a URL (Azure Blob Storage)
BACKUP DATABASE [NomBaseDades]
TO URL = N'https://compte.blob.core.windows.net/backups/NomBD_20240115.bak'
WITH CREDENTIAL = N'AzureStorageCredential',
     COMPRESSION, CHECKSUM;

# RMAN (Recovery Manager): eina nativa d'Oracle per a backups físics

# Connectar a RMAN
rman TARGET /

# Backup full comprimit
# RMAN> BACKUP AS COMPRESSED BACKUPSET DATABASE PLUS ARCHIVELOG;

# Backup incremental (nivell 0 = full per a incremental, nivell 1 = incremental)
# RMAN> BACKUP INCREMENTAL LEVEL 0 DATABASE;
# RMAN> BACKUP INCREMENTAL LEVEL 1 DATABASE;

# Configurar retenció
# RMAN> CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 7 DAYS;
# RMAN> CONFIGURE BACKUP OPTIMIZATION ON;

# Llistar backups disponibles
# RMAN> LIST BACKUP SUMMARY;

# Verificar la integritat
# RMAN> BACKUP VALIDATE DATABASE;

Exemples de backup amb timestamp i compressió

PostgreSQLMySQL / MariaDBSQL ServerOracle

#!/bin/bash
# Script de backup diari per a PostgreSQL

BACKUP_DIR="/backups/postgresql"
DB_NAME="producció"
TIMESTAMP=$(date +%Y%m%d_%H%M%S)
RETENTION_DAYS=30

mkdir -p "$BACKUP_DIR"

# Backup comprimit en format custom
pg_dump \
    -h localhost \
    -U postgres \
    -Fc \
    -f "${BACKUP_DIR}/${DB_NAME}_${TIMESTAMP}.dump" \
    "$DB_NAME"

if [ $? -eq 0 ]; then
    echo "[${TIMESTAMP}] Backup completat: ${DB_NAME}_${TIMESTAMP}.dump"
    # Eliminar backups antics
    find "$BACKUP_DIR" -name "${DB_NAME}_*.dump" -mtime +${RETENTION_DAYS} -delete
else
    echo "[${TIMESTAMP}] ERROR: el backup ha fallat!" >&2
    exit 1
fi

#!/bin/bash
# Script de backup diari per a MySQL

BACKUP_DIR="/backups/mysql"
DB_NAME="producció"
DB_USER="backup_user"
DB_PASS="contrasenya"
TIMESTAMP=$(date +%Y%m%d_%H%M%S)
RETENTION_DAYS=30

mkdir -p "$BACKUP_DIR"

# Backup comprimit
mysqldump \
    -u "$DB_USER" \
    -p"$DB_PASS" \
    --single-transaction \
    --routines \
    --triggers \
    "$DB_NAME" \
    | gzip -9 > "${BACKUP_DIR}/${DB_NAME}_${TIMESTAMP}.sql.gz"

if [ $? -eq 0 ]; then
    echo "[${TIMESTAMP}] Backup OK: ${DB_NAME}_${TIMESTAMP}.sql.gz"
    find "$BACKUP_DIR" -name "${DB_NAME}_*.sql.gz" -mtime +${RETENTION_DAYS} -delete
else
    echo "[${TIMESTAMP}] ERROR en el backup!" >&2
    exit 1
fi

-- Procediment emmagatzemat per a backup amb timestamp i compressió
CREATE PROCEDURE usp_BackupDiari
    @DbName     NVARCHAR(128),
    @BackupPath NVARCHAR(500) = N'D:\Backups\'
AS
BEGIN
    DECLARE @FileName NVARCHAR(600);
    DECLARE @DateStr  VARCHAR(8) = CONVERT(VARCHAR(8), GETDATE(), 112);
    DECLARE @TimeStr  VARCHAR(6) = REPLACE(CONVERT(VARCHAR(5), GETDATE(), 108), ':', '');

    SET @FileName = @BackupPath + @DbName + '_Full_' + @DateStr + '_' + @TimeStr + '.bak';

    BACKUP DATABASE @DbName
    TO DISK = @FileName
    WITH
        COMPRESSION,
        CHECKSUM,
        NAME = @DbName + ' Full Backup ' + @DateStr,
        STATS = 10;

    PRINT 'Backup completat: ' + @FileName;
END;
GO

-- Executar
EXEC usp_BackupDiari @DbName = N'Producció';

#!/bin/bash
# Script RMAN per a backup comprimit amb timestamp

export ORACLE_SID=PRODDB
export ORACLE_HOME=/u01/app/oracle/product/19c/dbhome_1
TIMESTAMP=$(date +%Y%m%d_%H%M%S)

rman TARGET / <<EOF
RUN {
    ALLOCATE CHANNEL c1 TYPE DISK FORMAT '/backups/oracle/bkp_%U_${TIMESTAMP}';
    ALLOCATE CHANNEL c2 TYPE DISK FORMAT '/backups/oracle/bkp_%U_${TIMESTAMP}';
    BACKUP AS COMPRESSED BACKUPSET DATABASE
        TAG '${TIMESTAMP}_FULL'
        INCLUDE CURRENT CONTROLFILE;
    BACKUP ARCHIVELOG ALL
        DELETE INPUT
        TAG '${TIMESTAMP}_ARCH';
    RELEASE CHANNEL c1;
    RELEASE CHANNEL c2;
}
DELETE NOPROMPT OBSOLETE;
EOF

Recuperació punt a punt (PITR — Point-In-Time Recovery)

El PITR permet restaurar la base de dades a un moment exacte en el passat, no únicament al moment de l'últim backup. Requereix:

Una còpia base (base backup).
Un arxiu continu dels fitxers de log (WAL per a PostgreSQL, binlog per a MySQL).

PITR a PostgreSQL (detallat)

# ─── Escenari ───────────────────────────────────────────────────────
# Backup base fet a les 02:00
# A les 14:37 un dev executa accidentalment: DELETE FROM comandes WHERE true;
# Volem recuperar les dades fins a les 14:36

# Pas 1: Aturar el servidor PostgreSQL
systemctl stop postgresql

# Pas 2: Fer una còpia del directori de dades actual (per seguretat)
cp -r /var/lib/postgresql/data /var/lib/postgresql/data_pre_restore

# Pas 3: Restaurar el backup base
rm -rf /var/lib/postgresql/data
pg_restore_basebackup -D /var/lib/postgresql/data  # o bé descomprimir el tar

# Pas 4: Configurar la recuperació a punt (postgresql.conf)
cat >> /var/lib/postgresql/data/postgresql.conf <<EOF
restore_command = 'cp /var/lib/postgresql/wal_archive/%f %p'
recovery_target_time = '2024-01-15 14:36:00+01'
recovery_target_action = 'promote'  # promoure quan arribi al punt objectiu
EOF

# Pas 5: Crear el fitxer senyal de recuperació
touch /var/lib/postgresql/data/recovery.signal

# Pas 6: Iniciar PostgreSQL (entrarà en mode de recuperació)
systemctl start postgresql

# Pas 7: Monitorar el progrés als logs
journalctl -u postgresql -f
# Cerca: LOG: recovery stopping before commit ...
# Cerca: LOG: database system is ready to accept connections

# Pas 8: Verificar i, si és correcte, confirmar la recuperació
psql -c "SELECT COUNT(*) FROM comandes;"  # Verificar que les dades son correctes
# psql -c "SELECT pg_wal_replay_resume();"  # Si recovery_target_action = 'pause'

PITR a MySQL (overview)

# Restaurar el backup full
mysql -u root -p < backup_full_20240115_020000.sql

# Aplicar els binlogs des del backup fins al moment desitjat
mysqlbinlog \
    --start-datetime="2024-01-15 02:00:00" \
    --stop-datetime="2024-01-15 14:36:00" \
    /var/log/mysql/mysql-bin.000001 \
    /var/log/mysql/mysql-bin.000002 \
    | mysql -u root -p

Validació de les còpies de seguretat

Una còpia de seguretat que no s'ha provat no és una còpia de seguretat. El moment d'una emergència no és el moment per descobrir que els backups estaven corruptes o que la restauració triga 8 hores.

Pràctiques recomanades:

Restauració periòdica de prova: restaura la còpia en un servidor separat almenys un cop al mes.
Verificació d'integritat automàtica: compara checksums, executa RESTORE VERIFYONLY (SQL Server) o pg_restore --list (PostgreSQL).
Mesura el temps de restauració: has de saber quant temps triga la recuperació per poder calcular si compleix el RTO.
Documenta el procediment: el procediment de recuperació ha d'estar documentat pas a pas i provat per persones que no el van escriure.
Emmagatzema off-site: mantén còpies fora de la mateixa localitat física (cloud, altre CPD).

Els backups no provats no son backups

El 2011, una empresa de hosting va descobrir que les seves còpies de seguretat havien estat fallant silenciosament durant mesos quan va necessitar restaurar dades d'un client. El backup existia (els fitxers eren allà), però el procés de restauració estava trencat i ningú ho havia provat. Automatitza la verificació i prova de restauració. Sempre.

Miniactivitat — AC0602 · AC0603 · AC0604 · AC0605

Objectiu: Implementar un pla de còpies de seguretat complet amb PostgreSQL i MySQL, i provar la recuperació PITR.

Part 1 — PostgreSQL:

Instal·la PostgreSQL localment o via Docker.
Crea una BD de prova amb dades (CREATE TABLE ... INSERT INTO ...).
Fes un backup pg_dump en format custom.
Esborra la BD: DROP DATABASE.
Restaura amb pg_restore. Verifica que les dades son correctes.

Part 2 — MySQL:

Fes un backup mysqldump --single-transaction de la BD de prova.
Comprimeix el backup: gzip o l'opció --compress-output de mysqlpump.
Esborra taules i restaura.

Part 3 — PITR PostgreSQL (opcional, avançada):

Configura archive_mode = on i archive_command per copiar el WAL a un directori.
Fes un backup base amb pg_basebackup.
Insereix dades noves, anota el timestamp.
Esborres les dades (DELETE FROM taula).
Realitza un PITR fins al timestamp anterior a l'esborrat.
Verifica que les dades han tornat.

Lliura: Script de backup complet (bash) amb rotació de backups antics, i captura de la restauració exitosa.