Còpies de seguretat

Importància del backup

Una còpia de seguretat és l'única protecció real contra la pèrdua de dades. Independentment de com de robust sigui el sistema, qualsevol dels seguents escenaris pot destruir les dades:

• Fallada de disc (el disc dur o SSD mor físicament)
• Error humà (un DROP TABLE o DELETE sense WHERE accidental)
• Ciberatac (ransomware que xifra les dades)
• Corrupció de dades (bug en el programari, tall de llum en el moment incorrecte)
• Desastre físic (incendi, inundació al centre de dades)

La regla 3-2-1

La regla bàsica de backup: tenir 3 còpies de les dades, en 2 tipus d'emmagatzematge diferents, amb 1 còpia fora del lloc físic (off-site). Per exemple: dades originals + backup local NAS + backup al cloud (S3, Azure Blob).

---

Com el SGBD garanteix la durabilitat

Abans d'entendre els tipus de còpia de seguretat, cal comprendre com un SGBD escriu les transaccions a disc. Aquesta arquitectura és la que determina quines estratègies de recuperació són possibles.

El problema: memòria vs. disc

Quan una transacció modifica dades, el SGBD no escriu immediatament cada canvi al disc de dades — és massa lent. En comptes d'això, manté les pàgines modificades en memòria (buffer pool o shared buffers) i les escriu al disc de forma asíncrona (quan el SGBD està menys saturat de peticions). Però si el servidor cau en aquest moment, els canvis en memòria es perden.

La solució és el Write-Ahead Log (WAL) o Redo Log: un registre seqüencial on s'anota cada canvi abans de modificar les pàgines de dades. En cas de fallada, el SGBD pot reconstruir l'estat consistent rellegint el log.

flowchart LR
    T["Transacció\n(COMMIT)"]
    L["Log / WAL\n(disc, seqüencial)"]
    B["Buffer Pool\n(memòria)"]
    D["Fitxers de dades\n(disc, aleatori)"]

    T -->|"1. Escriu primer al log\n(garantia de durabilitat)"| L
    T -->|"2. Modifica pàgines\nen memòria"| B
    B -->|"3. Checkpoint:\nflush asíncron"| D
    L -->|"Recovery:\naplicar redo"| D

Write-Ahead Log (WAL) i Redo Log

El fitxer de log és circular — quan s'omple, es reutilitza. Cada entrada conté:

• L'operació realitzada (INSERT, UPDATE, DELETE a nivell de pàgina física)
• Un número de seqüència únic i creixent que permet ordenar els canvis

PostgreSQLMySQL / MariaDBSQL ServerOracle

Fitxers: pg_wal/ (fins PostgreSQL 9.6: pg_xlog/)
Format:  segments de 16 MB per defecte (configurable amb wal_segment_size)
Nom:     000000010000000000000001, 000000010000000000000002, ...

-- Veure la posició actual del WAL (LSN)
SELECT pg_current_wal_lsn();

-- Veure els fitxers WAL actius
SELECT * FROM pg_ls_waldir() ORDER BY modification DESC LIMIT 10;

-- Configuració del WAL
SHOW wal_level;        -- minimal, replica, logical
SHOW archive_mode;     -- on/off

Redo Log (InnoDB): ib_logfile0, ib_logfile1 (roten entre ells)
Binary Log:        binlog.000001, binlog.000002, ... (per a PITR i replicació)
Undo Log:          dins de ibdata1 o fitxers undo*.ibu separats (MySQL 8.0+)

-- Veure l'estat del binary log
SHOW BINARY LOGS;
SHOW MASTER STATUS;

-- Veure la posició actual del redo log (LSN)
SHOW ENGINE INNODB STATUS\G
-- Busqueu la secció "LOG" amb "Log sequence number"

-- Configuració
SHOW VARIABLES LIKE 'innodb_log%';
SHOW VARIABLES LIKE 'log_bin%';

Transaction Log: fitxer .ldf (un per BD)
Format:          VLFs (Virtual Log Files) dins del .ldf

-- Veure l'estat del transaction log
DBCC SQLPERF(LOGSPACE);

-- Veure els VLFs actius
DBCC LOGINFO('gbd_practica');

-- Model de recuperació (determina si el log es pot truncar)
SELECT name, recovery_model_desc FROM sys.databases;
-- SIMPLE: el log es trunca automàticament (no permet PITR)
-- FULL:   el log es conserva fins al backup (permet PITR)
-- BULK_LOGGED: similar a FULL però operacions massives es loggen de forma compacta

Redo Log:      redo01.log, redo02.log, redo03.log (en grups multiplexats)
Archive Log:   ARC0000000001_xxx.dbf (quan archive log mode = ON)
Control Files: control01.ctl, control02.ctl, control03.ctl
Data Files:    system01.dbf, users01.dbf, ...

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

-- Veure els membres de cada grup
SELECT group#, member, status FROM v$logfile ORDER BY group#;

-- Estat del archive log
SELECT log_mode FROM v$database;
-- ARCHIVELOG o NOARCHIVELOG

-- Veure els archive logs generats
SELECT name, first_change#, next_change# FROM v$archived_log
ORDER BY first_change# DESC FETCH FIRST 10 ROWS ONLY;

---

Números de seqüència: LSN i SCN

Cada entrada del log porta un número de seqüència únic i creixent que permet al SGBD saber exactament en quin ordre van ocórrer les transaccions i on s'ha d'iniciar la recuperació.

Motor	Nom	Format	Descripció
PostgreSQL	LSN (Log Sequence Number)	0/1AB34CD (hex, offset en bytes)	Posició absoluta dins del flux WAL
MySQL/InnoDB	LSN (Log Sequence Number)	Enter de 8 bytes	Bytes escrits al redo log des del inici
SQL Server	LSN (Log Sequence Number)	(VLF_id:block:record)	Identifica VLF, bloc i registre dins del .ldf
Oracle	SCN (System Change Number)	Enter creixent	Número global de canvi del sistema; cada COMMIT obté un SCN únic

El SCN d'Oracle és especialment important: cada COMMIT incrementa el SCN, i tots els fitxers (datafiles, redo logs, control files) guarden el darrer SCN consistent. Quan la BD es recupera, compara els SCNs de tots els fitxers per detectar quins canvis cal aplicar.

-- Oracle: SCN actual
SELECT current_scn FROM v$database;

-- PostgreSQL: LSN actual
SELECT pg_current_wal_lsn();

-- MySQL: LSN actual (del redo log InnoDB)
SELECT @lsn := variable_value FROM performance_schema.global_status
WHERE variable_name = 'Innodb_redo_log_current_lsn';

---

Checkpoint

Un checkpoint és el moment en que el SGBD garanteix que totes les pàgines modificades en memòria fins a un cert LSN/SCN han estat escrites al disc de dades. Anota la posició del checkpoint al log.

En una recuperació post-crash, el SGBD només ha d'aplicar el redo log des de l'últim checkpoint — no des del principi del log — cosa que limita el temps de recuperació.

sequenceDiagram
    participant T as Transaccions
    participant L as WAL/Redo Log
    participant B as Buffer Pool
    participant D as Disc de dades

    T->>L: Escriu entrades (LSN 1000→2500)
    T->>B: Modifica pàgines en memòria
    Note over B,D: Checkpoint @ LSN 2000
    B->>D: Flush de pàgines modificades (LSN ≤ 2000)
    L->>L: Anota checkpoint LSN=2000
    Note over T: Servidor cau @ LSN 2300
    Note over L,D: Recovery: aplica redo des de LSN 2000 fins a 2300

PostgreSQLMySQL / MariaDBSQL ServerOracle

-- Forçar un checkpoint manualment (requereix superuser)
CHECKPOINT;

-- Veure quan va ser l'últim checkpoint
SELECT checkpoint_lsn, checkpoint_tli, prior_lsn
FROM pg_control_checkpoint();

-- Configuració
SHOW checkpoint_timeout;      -- temps màxim entre checkpoints (per defecte 5min)
SHOW max_wal_size;            -- mida màxima del WAL abans de forçar checkpoint

-- Forçar un checkpoint (flush de dirty pages)
SET GLOBAL innodb_fast_shutdown = 0;  -- durant l'aturada
-- O forçar flush de les pàgines:
SET GLOBAL innodb_buffer_pool_dump_now = ON;

-- Veure l'activitat de checkpoint
SHOW ENGINE INNODB STATUS\G
-- Secció "BUFFER POOL AND MEMORY" → Modified db pages

-- Configuració
SHOW VARIABLES LIKE 'innodb_io_capacity%';

-- Forçar un checkpoint
CHECKPOINT;
-- O per a una BD concreta:
CHECKPOINT 30;  -- amb un temps màxim de 30 segons

-- Veure l'últim checkpoint
SELECT last_log_backup_lsn, recovery_fork_begin_lsn
FROM sys.database_recovery_status
WHERE database_id = DB_ID('gbd_practica');

-- Forçar un checkpoint complet
ALTER SYSTEM CHECKPOINT;

-- Veure informació del checkpoint als control files
SELECT checkpoint_change#, checkpoint_time FROM v$datafile_header
WHERE file# = 1;

-- SCN del darrer checkpoint
SELECT checkpoint_change# FROM v$database;

---

Archive Log i Point-In-Time Recovery (PITR)

En mode normal, els fitxers de log es reutilitzen de forma circular i els canvis antics es perden. Per poder recuperar la BD a qualsevol moment en el temps (PITR), cal activar l'arxivament dels logs: cada vegada que un fitxer de log s'omple, es copia a una ubicació d'arxiu segura abans de ser reutilitzat.

Amb PITR, la recuperació funciona així:

1. Restaurar el darrer backup complet.
2. Aplicar els archive logs en ordre des del backup fins al moment desitjat.
3. La BD queda en l'estat exacte d'aquell instant.

PostgreSQLMySQL / MariaDBSQL ServerOracle

# Activar WAL archiving a postgresql.conf:
# wal_level = replica
# archive_mode = on
# archive_command = 'cp %p /arxiu/wal/%f'

# Verificar que l'arxivament funciona

SELECT * FROM pg_stat_archiver;
-- archived_count: fitxers arxivats correctament
-- failed_count:   fallades d'arxivament (crític!)
-- last_archived_wal: darrer fitxer arxivat

-- MySQL usa el Binary Log per a PITR (no el redo log)
-- Activar a my.cnf: log_bin = /var/log/mysql/binlog

-- Veure els binary logs disponibles
SHOW BINARY LOGS;

-- Veure el contingut d'un binary log
SHOW BINLOG EVENTS IN 'binlog.000003' LIMIT 20;

-- Recuperació PITR fins a un punt (fora de MySQL):
-- mysqlbinlog --start-datetime="2026-03-20 10:00:00" \
--             --stop-datetime="2026-03-20 14:30:00" \
--             binlog.000003 | mysql -u root -p

-- Activar mode FULL recovery (necessari per a PITR)
ALTER DATABASE gbd_practica SET RECOVERY FULL;

-- Backup del transaction log (cal fer-lo regularment per a PITR)
BACKUP LOG gbd_practica
TO DISK = '/backup/gbd_log_20260320_1030.trn';

-- Restaurar fins a un punt en el temps (en mode NORECOVERY fins l'últim pas)
RESTORE DATABASE gbd_practica FROM DISK = '/backup/full.bak'
WITH NORECOVERY;

RESTORE LOG gbd_practica FROM DISK = '/backup/log.trn'
WITH STOPAT = '2026-03-20 14:30:00', RECOVERY;

-- Verificar si la BD està en mode ARCHIVELOG
SELECT log_mode FROM v$database;

-- Activar ARCHIVELOG (requereix reinici)
SHUTDOWN IMMEDIATE;
STARTUP MOUNT;
ALTER DATABASE ARCHIVELOG;
ALTER DATABASE OPEN;

-- Configurar destinació dels archive logs
ALTER SYSTEM SET log_archive_dest_1 = 'LOCATION=/arxiu/oracle/arch';

-- Veure els archive logs disponibles
SELECT name, first_change#, next_change#, first_time
FROM v$archived_log
WHERE standby_dest = 'NO'
ORDER BY first_change#;

---

Els fitxers de control (Oracle)

Oracle manté uns fitxers binaris especials anomenats control files que contenen les metadades crítiques de la instància:

• Nom i identificador de la BD
• Ubicació i estat de tots els datafiles i redo log files
• SCN actual i del darrer backup
• Historial d'archive logs
• Informació de RMAN (Recovery Manager)

Si un control file es corromp o perd, Oracle no pot obrir la BD. Per això Oracle recomana multiplexar els control files a almenys 3 ubicacions físiques diferents.

-- Veure la ubicació dels control files
SELECT name FROM v$controlfile;

-- Contingut del control file (resum)
SELECT * FROM v$database;

-- Crear una còpia del control file (per a recuperació)
ALTER DATABASE BACKUP CONTROLFILE TO '/backup/control_backup.ctl';

-- Generar el script per recrear el control file
ALTER DATABASE BACKUP CONTROLFILE TO TRACE AS '/backup/recreate_controlfile.sql';

Multiplexació dels fitxers crítics a Oracle

Oracle permet (i recomana) tenir múltiples còpies dels fitxers crítics en ubicacions físiques separades:

• Control files: mínim 3, en discos separats. Si un falla, Oracle segueix funcionant amb els altres.
• Redo log groups: cada grup hauria de tenir almenys 2 membres en discos separats (multiplexació del redo log).
• Archive logs: configurar log_archive_dest_2 per tenir còpies dobles dels arxius.

---

Recuperació post-crash: el procés complet

Quan un SGBD arrenca després d'una caiguda brusca, segueix aquest procés automàtic:

flowchart TD
    A["Arrencada del SGBD\ndespres d'un crash"] --> B["Llegir el darrer\ncheckpoint del log"]
    B --> C["Fase REDO\n(Roll Forward):\naplicar totes les entrades\ndel log des del checkpoint"]
    C --> D["Fase UNDO\n(Roll Back):\ndesfer les transaccions\nsense COMMIT"]
    D --> E["BD en estat consistent\nllesta per a connexions"]

Fase	Operació	Objectiu
REDO (Roll Forward)	Aplica totes les entrades del log des del darrer checkpoint	Recuperar els canvis de transaccions confirmades que eren en memòria
UNDO (Roll Back)	Desfà les entrades de transaccions sense COMMIT	Eliminar canvis parcials que mai es van confirmar

PostgreSQLMySQL / MariaDBSQL ServerOracle

PostgreSQL anomena aquest procés "crash recovery".
S'executa automàticament en arrencar si detecta un shutdown brusc
(comprova el fitxer pg_wal/pg_control → state = "in production").
Els missatges apareixen a postgresql.log:
  LOG: starting point-in-time recovery to ...
  LOG: redo starts at 0/1AB34CD
  LOG: consistent recovery state reached at 0/1BC0000
  LOG: database system is ready to accept connections

InnoDB executa la recuperació automàticament en arrencar.
Es pot veure el progrés a /var/log/mysql/error.log:
  [Note] InnoDB: Starting crash recovery.
  [Note] InnoDB: Log scan progressed past the checkpoint lsn 12345678
  [Note] InnoDB: Applying a batch of 42 redo log records ...
  [Note] InnoDB: 1 transaction(s) which must be rolled back
  [Note] InnoDB: Starting in background the rollback of uncommitted transactions

SQL Server executa la recuperació en tres fases:
1. Analysis: llegeix el log per trobar el darrer checkpoint i les transaccions actives
2. Redo: aplica els canvis de transaccions confirmades
3. Undo: desfà transaccions no confirmades

Es pot veure a SQL Server Error Log:
  Recovery of database 'gbd_practica' (x%) is 50% complete
  (approximately 30 seconds remain)

-- Oracle diferencia entre Instance Recovery (crash) i Media Recovery (backup)

-- Instance Recovery (automàtic en arrencar):
-- Oracle llegeix el SCN dels control files i datafiles
-- Aplica redo logs des del checkpoint fins al darrer redo log
-- Desfa les transaccions sense COMMIT usant els undo segments

-- Veure el progrés de recovery (en cas de media recovery llarg)
SELECT sofar, totalwork, units, opname
FROM v$session_longops
WHERE opname LIKE '%Recovery%';

-- Comprovar l'estat dels datafiles despres d'un crash
SELECT name, status, checkpoint_change# FROM v$datafile;

AC0372/06/00 — Conceptes previs

Abans de fer el primer backup, comproveu l'estat del log al vostre contenidor de pràctiques:

1. PostgreSQL: Executeu SELECT pg_current_wal_lsn(); i SHOW wal_level;. Quina és la posició del WAL? Quin nivell de WAL hi ha configurat?
2. MySQL: Executeu SHOW BINARY LOGS; i SHOW VARIABLES LIKE 'log_bin';. Està activat el binary log?
3. Genereu algunes transaccions (INSERT, UPDATE) i torneu a consultar la posició del LSN/SCN. Ha canviat? Per quant?

---

Tipus de còpies de seguretat

Còpia completa (Full Backup)

Una còpia de seguretat completa fa una instantània de totes les dades en un moment donat. És la base de qualsevol estratègia de backup.

• Avantatge: Restauració simple i ràpida (només necessiteu un fitxer).
• Inconvenient: Temps i espai elevats. Fer un backup complet diari d'una BD de 1TB cada dia és prohibitiu.
• Freqüència típica: Setmanal o diaria (per a BD petites).

Còpia incremental (Incremental Backup)

Copia només les dades que han canviat des de l'últim backup (sigui complet o incremental).

• Avantatge: Molt ràpid i usa poc espai.
• Inconvenient: La restauració és complexa: cal aplicar el backup complet i després cadascun dels incrementals en ordre.
• Freqüència típica: Cada hora o cada dia.

Còpia diferencial (Differential Backup)

Copia les dades que han canviat des de l'últim backup complet (no des de l'últim backup de qualsevol tipus).

• Avantatge: Restauració més simple que l'incremental (només backup complet + darrer diferencial).
• Inconvenient: Cada diferencial és més gran que l'incremental equivalent.
• Freqüència típica: Diaria.

gantt
    title Estrategia de backup típica
    dateFormat YYYY-MM-DD
    section Setmana 1
    Backup complet (diumenge)     :done, 2025-10-05, 1d
    Diferencial (dilluns)        :done, 2025-10-06, 1d
    Diferencial (dimarts)        :done, 2025-10-07, 1d
    Diferencial (dimecres)       :done, 2025-10-08, 1d
    Diferencial (dijous)         :done, 2025-10-09, 1d
    Diferencial (divendres)      :done, 2025-10-10, 1d
    Diferencial (dissabte)       :done, 2025-10-11, 1d
    section Setmana 2
    Backup complet (diumenge)    :2025-10-12, 1d

Comparació: passos de restauració

La diferència clau entre les tres estratègies és quants fitxers cal aplicar per restaurar:

flowchart TD
    subgraph FULL["Còpia completa — restauració"]
        direction LR
        FC["Backup complet\n(diumenge)"] --> FR["BD restaurada"]
    end

    subgraph DIFF["Còpia diferencial — restauració (dijous)"]
        direction LR
        DC["Backup complet\n(diumenge)"] --> DD["Diferencial\n(dijous)"] --> DR["BD restaurada"]
    end

    subgraph INCR["Còpia incremental — restauració (dijous)"]
        direction LR
        IC["Backup complet\n(diumenge)"] --> I1["Incremental\n(dilluns)"] --> I2["Incremental\n(dimarts)"] --> I3["Incremental\n(dimecres)"] --> I4["Incremental\n(dijous)"] --> IR["BD restaurada"]
    end

    style FC fill:#1565c0,stroke:#0d47a1,color:#fff
    style DC fill:#1565c0,stroke:#0d47a1,color:#fff
    style IC fill:#1565c0,stroke:#0d47a1,color:#fff
    style DD fill:#00695c,stroke:#004d40,color:#fff
    style I1 fill:#00695c,stroke:#004d40,color:#fff
    style I2 fill:#00695c,stroke:#004d40,color:#fff
    style I3 fill:#00695c,stroke:#004d40,color:#fff
    style I4 fill:#00695c,stroke:#004d40,color:#fff

RPO i RTO

Dues mètriques clau per avaluar una estratègia de backup:

Mètrica	Significat	En català
RPO (Recovery Point Objective)	Màxima pèrdua de dades acceptable	Fins a quin moment en el passat puc recuperar?
RTO (Recovery Time Objective)	Temps màxim acceptable per restaurar	Quant puc trigar a tornar a estar operatiu?

flowchart LR
    A["Últim backup\n(RPO limit)"] -->|"Dades perdudes\nen cas de fallada"| B["Fallada"] -->|"Temps de restauració\n(RTO)"| C["Sistema\noperatiu"]

    style A fill:#1565c0,stroke:#0d47a1,color:#fff
    style B fill:#b71c1c,stroke:#7f0000,color:#fff
    style C fill:#1b5e20,stroke:#2e7d32,color:#fff

• RPO baix (poques dades perdudes) → calen backups freqüents + PITR amb WAL/binlog.
• RTO baix (restauració ràpida) → calen backups físics (més ràpids de restaurar que els lògics) i sistemes d'alta disponibilitat.

---

Configuració prèvia als SGBD

Abans de poder realitzar còpies de seguretat, cal configurar correctament cada SGBD. Les còpies lògiques (exportació en SQL o format propi) solen requerir poca configuració, però les còpies físiques (còpia dels fitxers de dades mentre el servidor està en marxa) necessiten que certs paràmetres estiguin activats.

Còpies lògiques

Les còpies lògiques exporten les dades en un format independent del sistema de fitxers: instruccions SQL, fitxers CSV, o formats binaris propis de cada eina. El servidor pot estar en marxa durant el backup.

PostgreSQLMySQL / MariaDBSQL ServerOracle

pg_dump no requereix cap configuració especial: qualsevol usuari amb permís de connexió i SELECT sobre les taules pot fer un backup lògic. Per a pg_dumpall cal el rol SUPERUSER o pg_read_all_data.

-- Comprovar permisos de l'usuari
\du gbd_user

-- L'usuari necessita com a mínim CONNECT i SELECT (o el rol pg_read_all_data)
GRANT CONNECT ON DATABASE gbd_nom_cognom TO gbd_user;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO gbd_user;

-- Per exportar procediments, triggers i seqüències, cal ser propietari o superuser
GRANT pg_read_all_data TO gbd_user;

# pg_hba.conf: l'usuari de backup ha de poder connectar-se
# (normalment ja configurat si el contenidor funciona)
host  gbd_nom_cognom  gbd_user  127.0.0.1/32  scram-sha-256

mysqldump requereix els privilegis SELECT, SHOW VIEW, TRIGGER i LOCK TABLES sobre les taules a exportar. Per a --all-databases es necessita PROCESS i RELOAD.

-- Crear un usuari dedicat per a backups lògics
CREATE USER 'backup_user'@'localhost' IDENTIFIED BY 'BackupPass2025!';

-- Permisos mínims per a backup d'una BD concreta
GRANT SELECT, SHOW VIEW, TRIGGER, LOCK TABLES
    ON gbd_nom_cognom.* TO 'backup_user'@'localhost';

-- Permisos addicionals si s'usa --single-transaction i --routines
GRANT PROCESS ON *.* TO 'backup_user'@'localhost';
GRANT SELECT ON mysql.proc TO 'backup_user'@'localhost';  -- MariaDB

FLUSH PRIVILEGES;

-- Verificar
SHOW GRANTS FOR 'backup_user'@'localhost';

Per a BACKUP DATABASE l'usuari necessita el rol db_backupoperator a la base de dades o el rol servidor sysadmin.

-- Afegir usuari al rol de backup
USE gbd_practica;
ALTER ROLE db_backupoperator ADD MEMBER backup_login;

-- O concedir el permís explícitament
GRANT BACKUP DATABASE TO backup_login;
GRANT BACKUP LOG TO backup_login;

-- Verificar que el directori de backup és accessible pel servei SQL Server
EXEC xp_fileexist '/var/opt/mssql/backup/';

-- Crear el directori si no existeix (des de bash del contenidor)
-- mkdir -p /var/opt/mssql/backup

L'eina nativa per a còpies lògiques és expdp (Data Pump Export). Requereix el privilegi EXP_FULL_DATABASE per a exports complets o CREATE SESSION + READ per a esquemes propis.

-- Crear un directori Oracle (apunta a un directori del sistema de fitxers)
CREATE OR REPLACE DIRECTORY backup_dir AS '/backup/oracle';
GRANT READ, WRITE ON DIRECTORY backup_dir TO gbd_user;

-- Permisos per a export del propi esquema
GRANT CREATE SESSION TO gbd_user;
-- (un usuari pot exportar el seu propi esquema sense privilegis addicionals)

-- Permisos per a export complet (tots els esquemes)
GRANT EXP_FULL_DATABASE TO backup_user;

-- Verificar el directori
SELECT directory_name, directory_path FROM dba_directories
WHERE directory_name = 'BACKUP_DIR';

---

Còpies físiques

Les còpies físiques treuen una instantània dels fitxers de dades mentre el servidor funciona (backup en calent) o aturat (backup en fred). Permeten restauracions molt més ràpides i, combinades amb els archive logs, habiliten el PITR. La configuració és obligatòria abans de fer el primer backup físic.

PostgreSQLMySQL / MariaDBSQL ServerOracle

L'eina de backup físic de PostgreSQL és pg_basebackup. Requereix que el WAL tingui el nivell replica o superior i que existeixi un slot de replicació o un usuari amb el rol REPLICATION.

# postgresql.conf — paràmetres mínims per a pg_basebackup i PITR
wal_level = replica            # mínim necessari (per defecte ja és replica des de PG10)
archive_mode = on              # arxivar els segments WAL completats
archive_command = 'cp %p /arxiu/wal/%f'   # comanda per arxivar (adapteu el path)
max_wal_senders = 3            # connexions de replicació/backup simultànies

-- Crear un usuari de backup amb permís de replicació
CREATE USER backup_user REPLICATION LOGIN PASSWORD 'BackupPass2025!';

-- O afegir el rol a un usuari existent
ALTER USER gbd_user REPLICATION;

-- pg_hba.conf: permetre connexions de replicació
-- host  replication  backup_user  127.0.0.1/32  scram-sha-256

-- Verificar que la configuració és correcta
SHOW wal_level;
SHOW archive_mode;
SHOW archive_command;
SELECT * FROM pg_stat_archiver;

# Recarregar la configuració sense reiniciar
SELECT pg_reload_conf();
# (wal_level i archive_mode requereixen reinici del servidor)

Reinici obligatori per a wal_level i archive_mode

Canviar wal_level o archive_mode requereix reiniciar el servei PostgreSQL. En un contenidor Docker: docker restart postgres-nom-cognom. Els canvis a archive_command sí que s'apliquen amb pg_reload_conf().

El backup físic en calent a MySQL/MariaDB s'aconsegueix amb MySQL Enterprise Backup, Percona XtraBackup (per a MySQL) o mariabackup (per a MariaDB). Totes aquestes eines requereixen el binary log activat i innodb_file_per_table.

# my.cnf — paràmetres mínims per a backup físic i PITR
[mysqld]
# Binary log (obligatori per a PITR i per a algunes eines de backup)
log_bin = /var/lib/mysql/binlog
binlog_format = ROW                # ROW és el format recomanat per a backup/replicació
expire_logs_days = 14              # retenir 14 dies de binlogs (MySQL 5.7)
# binlog_expire_logs_seconds = 1209600  # equivalent a MySQL 8.0+

# InnoDB: fitxers separats per taula (facilita la restauració selectiva)
innodb_file_per_table = ON         # per defecte ON des de MySQL 5.6

# Opcional: augmentar el redo log per a servidors de producció
innodb_log_file_size = 256M

-- Verificar la configuració
SHOW VARIABLES LIKE 'log_bin';
SHOW VARIABLES LIKE 'binlog_format';
SHOW VARIABLES LIKE 'innodb_file_per_table';
SHOW BINARY LOGS;

-- Permisos per a XtraBackup / mariabackup
CREATE USER 'xtrabackup'@'localhost' IDENTIFIED BY 'XtraPass2025!';
GRANT BACKUP_ADMIN, PROCESS, RELOAD, LOCK TABLES,
      REPLICATION CLIENT ON *.* TO 'xtrabackup'@'localhost';
FLUSH PRIVILEGES;

mariabackup vs XtraBackup

Per a MariaDB useu mariabackup (inclòs al paquet mariadb-backup). Per a MySQL 8.x useu Percona XtraBackup 8.x. Les versions han de coincidir exactament amb la versió del servidor.

SQL Server integra el backup físic directament a l'SQL. El requisit principal és el model de recuperació: cal FULL o BULK_LOGGED per a fer backups del transaction log i habilitar PITR.

-- 1. Establir el model de recuperació FULL (prerequisit per a PITR)
ALTER DATABASE gbd_practica SET RECOVERY FULL;

-- Verificar el model de totes les BD
SELECT name, recovery_model_desc, log_reuse_wait_desc
FROM sys.databases
ORDER BY name;

-- 2. Activar la compressió de backups per defecte (SQL Server 2008+)
EXEC sp_configure 'backup compression default', 1;
RECONFIGURE;

-- 3. Verificar que SQL Server pot escriure al directori de backup
EXEC xp_create_subdir '/var/opt/mssql/backup';

-- 4. Fer el primer backup complet després de canviar a FULL recovery
--    (fins que no es fa el primer full backup, el log no es pot truncar)
BACKUP DATABASE gbd_practica
TO DISK = '/var/opt/mssql/backup/gbd_practica_full_init.bak'
WITH COMPRESSION, INIT, STATS = 10;

Trampa del model FULL sense backup de log

Si poseu una BD en mode FULL però no feu backups regulars del transaction log, el fitxer .ldf creixerà indefinidament fins a omplir el disc. Sempre acompanyeu el model FULL amb una política de backup de log (com a mínim cada hora).

Oracle usa RMAN (Recovery Manager) com a eina oficial per a còpies físiques. El prerequisit fonamental és activar el mode ARCHIVELOG.

-- 1. Verificar l'estat actual
SELECT log_mode, db_unique_name FROM v$database;
-- Si retorna NOARCHIVELOG, cal activar-lo

-- 2. Activar ARCHIVELOG (requereix aturar la BD)
SHUTDOWN IMMEDIATE;
STARTUP MOUNT;
ALTER DATABASE ARCHIVELOG;
ALTER DATABASE OPEN;

-- 3. Configurar la destinació dels archive logs
ALTER SYSTEM SET log_archive_dest_1 = 'LOCATION=/arxiu/oracle/arch'
    SCOPE=BOTH;
ALTER SYSTEM SET log_archive_format = 'ARC%S_%R_%T.ARC'
    SCOPE=SPFILE;

-- 4. Configurar RMAN (connectar com a SYSDBA)
-- rman target /

# Sessió RMAN: configuració bàsica
RMAN> CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 7 DAYS;
RMAN> CONFIGURE BACKUP OPTIMIZATION ON;
RMAN> CONFIGURE DEFAULT DEVICE TYPE TO DISK;
RMAN> CONFIGURE CHANNEL DEVICE TYPE DISK FORMAT '/backup/oracle/%U';
RMAN> CONFIGURE CONTROLFILE AUTOBACKUP ON;

# Verificar la configuració
RMAN> SHOW ALL;

-- Verificar que ARCHIVELOG està actiu i els archive logs s'estan generant
SELECT log_mode FROM v$database;
SELECT dest_name, status, destination FROM v$archive_dest
WHERE status = 'VALID' AND target = 'PRIMARY';

RMAN i el catàleg de recuperació

Per a entorns de producció, RMAN pot emmagatzemar el seu repositori en una BD separada (Recovery Catalog) en comptes del control file local. Això permet gestionar backups de múltiples instàncies Oracle des d'un punt central i tenir l'historial de backups persistent fins i tot si es perd el control file.

---

AC0372/06/02 — Configuració de backup

RA6 · CA6.1, CA6.3

Comproveu la configuració de backup al vostre contenidor de pràctiques:

1. PostgreSQL: Executeu SHOW wal_level; i SHOW archive_mode;. Quin és el nivell actual? Què caldria canviar per poder fer un pg_basebackup?
2. MySQL: Executeu SHOW VARIABLES LIKE 'log_bin'; i SHOW VARIABLES LIKE 'innodb_file_per_table';. Està activat el binary log? Quin valor té innodb_file_per_table?
3. Identifiqueu, per a cada SGBD, quin és el mínim canvi de configuració necessari per habilitar backups físics en calent.

---

pg_dump i pg_restore (PostgreSQL)

pg_dump és l'eina oficial de PostgreSQL per a còpies de seguretat lògiques (exporta les dades en format SQL o binari):

# Backup en format SQL (llegible)
pg_dump -h localhost -U gbd_user -d gbd_nom_cognom > backup_$(date +%Y%m%d).sql

# Backup en format binari custom (més compacte, permet restauració selectiva)
pg_dump -h localhost -U gbd_user -d gbd_nom_cognom -F c -f backup_$(date +%Y%m%d).dump

# Backup d'una taula concreta
pg_dump -h localhost -U gbd_user -d gbd_nom_cognom -t clients > backup_clients.sql

# Backup només del esquema (sense dades)
pg_dump -h localhost -U gbd_user -d gbd_nom_cognom --schema-only > schema.sql

# Backup només de les dades (sense DDL)
pg_dump -h localhost -U gbd_user -d gbd_nom_cognom --data-only > dades.sql

Amb Docker:

# Backup des de fora del contenidor
docker exec postgres-nom-cognom pg_dump -U gbd_user gbd_nom_cognom > backup_$(date +%Y%m%d).sql

# Backup en format binari
docker exec postgres-nom-cognom pg_dump -U gbd_user -F c gbd_nom_cognom > backup_$(date +%Y%m%d).dump

Script de backup automatitzat:

#!/bin/bash
# backup_postgresql.sh

CONTAINER="postgres-nom-cognom"
DB="gbd_nom_cognom"
USER="gbd_user"
BACKUP_DIR="/backups/postgresql"
DATE=$(date +%Y%m%d_%H%M%S)

mkdir -p "$BACKUP_DIR"

docker exec "$CONTAINER" pg_dump -U "$USER" -F c "$DB" > "$BACKUP_DIR/${DB}_${DATE}.dump"

if [ $? -eq 0 ]; then
    echo "Backup OK: ${DB}_${DATE}.dump"
    # Eliminar backups de més de 30 dies
    find "$BACKUP_DIR" -name "*.dump" -mtime +30 -delete
else
    echo "ERROR: El backup ha fallat!" >&2
    exit 1
fi

---

mysqldump (MySQL/MariaDB)

mysqldump és l'equivalent de MySQL per a còpies de seguretat lògiques:

# Backup d'una BD
mysqldump -h localhost -u gbd_user -pgbd2025 gbd_nom_cognom > backup_$(date +%Y%m%d).sql

# Backup de totes les BD
mysqldump -u root -proot2025 --all-databases > backup_all_$(date +%Y%m%d).sql

# Backup amb opcions recomanades per a InnoDB
mysqldump -u gbd_user -pgbd2025 \
    --single-transaction \
    --routines \
    --triggers \
    --events \
    gbd_nom_cognom > backup_$(date +%Y%m%d).sql

Amb Docker:

docker exec mysql-nom-cognom mysqldump -u gbd_user -pgbd2025 \
    --single-transaction gbd_nom_cognom > backup_$(date +%Y%m%d).sql

L'opcio --single-transaction

Per a bases de dades InnoDB, sempre useu --single-transaction. Aquesta opcio fa el backup dins d'una sola transacció READ ONLY, la qual cosa garanteix la consistència de les dades sense bloquejar les taules durant el backup.

---

Backup a SQL Server

SQL Server usa el seu propi format de backup binari:

-- Backup complet
BACKUP DATABASE gbd_practica
TO DISK = '/var/opt/mssql/backup/gbd_practica_full.bak'
WITH COMPRESSION, STATS = 10;

-- Backup diferencial
BACKUP DATABASE gbd_practica
TO DISK = '/var/opt/mssql/backup/gbd_practica_diff.bak'
WITH DIFFERENTIAL, COMPRESSION;

-- Backup del transaction log
BACKUP LOG gbd_practica
TO DISK = '/var/opt/mssql/backup/gbd_practica_log.trn';

AC0372/06/01 — Miniactivitat

RA6 · CA6.1, CA6.2, CA6.3

Executeu un backup de la vostra BD de pràctiques amb pg_dump:

1. Backup en format SQL (.sql).
2. Backup en format binari (.dump).
3. Compareu la mida dels dos fitxers.
4. Inspeccioneu el contingut del fitxer SQL amb un editor de text. Que podeu identificar-hi?