Salta el contingut
 

Còpies de seguretat

Joan Puigcerver Ibáñez

j.puigcerveribanez@edu.gva.es

Llicència: CC BY-NC-SA 4.0

(Reconeixement - NoComercial - CompartirIgual) 🅭

License

Introducció

Un dels aspectes més importants de la seguretat de les dades és garantir la disponibilitat de la informació i la capacitat de recuperació en cas de fallada. Per això, pràcticament tots els gestors de bases de dades incorporen ferramentes per fer còpies de seguretat de les dades i restaurar-les quan siga necessari.

Hi ha dos mecanismes bàsics que permeten recuperar les dades:

  • Còpies de seguretat: ferramentes que permeten exportar la informació de la base de dades a un suport extern al SGBD i importar-la de nou, de manera que es puga recuperar en cas de fallada.
  • Diari de transaccions (en PostgreSQL, WAL): registre de totes les operacions que modifiquen les dades. A mesura que es fan transaccions, s'escriuen en aquest diari. Quan es confirmen amb COMMIT, els canvis es fan permanents, mentre que, si es desfan amb ROLLBACK, s'eliminen els registres corresponents.

Una bona pràctica és separar físicament les còpies de seguretat de les dades originals per evitar pèrdues en cas de fallada del disc, corrupció o atacs.

El procés de recuperació d'una base de dades combina tots dos mecanismes i es basa en tres tasques:

  • Backup: fer còpies de seguretat periòdiques de les dades.
  • Restore: restaurar les dades a partir de les còpies de seguretat.
  • Recovery: aplicar els canvis del diari de transaccions per actualitzar les dades fins a un punt concret en el temps.

Per garantir que aquest procés funciona quan cal, l'administrador de la base de dades s'encarrega de:

  • Definir la política de còpies de seguretat: freqüència, tipus de còpia, emmagatzematge, etc.
  • Definir el procés de restauració i recuperació: com es recuperen les dades en cas de fallada i, si és possible, automatitzar-lo.
  • Planificar simulacres de fallada: comprovar periòdicament que els procediments funcionen.

Tipus de còpies de seguretat

Les còpies de seguretat es poden classificar segons diferents criteris:

  • Segons què es copia:
    • Total: inclou tots els objectes, fitxers, el diari de transaccions, etc.
    • Parcial: només inclou els objectes concrets que es volen copiar.
  • Segons l'abstracció de les dades:
    • Lògica: es copien els objectes de la base de dades (taules, índexs, etc.), com a sentències SQL o en formats propis.
    • Física: es copien els fitxers físics que contenen les dades (directori de dades, pg_wal, etc.).
  • Segons l'abast temporal:
    • Completa: es copien totes les dades de la base de dades.
    • Incremental: només es copien les dades que han canviat des de l'última còpia, cosa que redueix el temps i l'espai necessaris.
  • Segons l'estat de la base de dades:
    • En fred (offline): la base de dades està aturada durant la còpia. Garanteix la consistència de les dades, però requereix un temps d'inactivitat.
    • En calent (online): la base de dades està en funcionament durant la còpia. Permet mantindre el servei actiu, però requereix mecanismes addicionals, com el WAL, per garantir la integritat.

Còpies de seguretat en PostgreSQL

PostgreSQL proporciona diverses ferramentes natives per fer còpies de seguretat i recuperar-les:

  • pg_dump / pg_restore: fan còpies de seguretat lògiques d'una base de dades concreta.
  • pg_dumpall: fa còpies de seguretat lògiques de tot el clúster, incloent-hi els rols i els tablespaces.
  • pg_basebackup: fa còpies de seguretat físiques completes del clúster. S'utilitza habitualment com a base per a la replicació i per a la recuperació a un punt en el temps (PITR).

La taula següent resumeix quins tipus de còpia permet cada ferramenta:

Ferramenta Totals / Parcials Completes / Incrementals En fred / En calent Lògiques / Físiques
pg_dump Ambdues Completes En calent Lògiques
pg_dumpall Total Completes En calent Lògiques
pg_basebackup + WAL Ambdues Completa + incremental (WAL) En calent Físiques

Ferramentes de còpia de seguretat en PostgreSQL

Ferramentes de tercers

Per a entorns de producció, és habitual utilitzar ferramentes especialitzades com pgBackRest o Barman, que ofereixen un catàleg de còpies, paral·lelisme i gestió incremental de les còpies físiques.

Còpia lògica (pg_dump i pg_restore)

L'ordre pg_dump exporta una base de dades a un fitxer, que després es pot restaurar amb psql o amb pg_restore, segons el format. Com que és una còpia lògica, es pot restaurar en una altra versió de PostgreSQL o en un altre servidor.

pg_dump -U <usuari> -d <base_de_dades> [-F <format>] -f <fitxer_eixida> [opcions]
  • -U <usuari>: rol amb què es connecta al servidor.
  • -d <base_de_dades>: base de dades que s'exporta.

  • [-F <format>]: (opcional) format del fitxer d'eixida.

    Valors de -F
    Valor Format Significat
    p plain Fitxer de text amb sentències SQL, executable amb psql (valor per defecte)
    c custom Format binari comprimit, necessari per fer restauracions selectives amb pg_restore
    d directory Com custom, però en un directori. Permet l'exportació en paral·lel amb -j
    t tar Fitxer tar

  • -f <fitxer_eixida>: fitxer on es guarda la còpia.

A més, algunes opcions permeten filtrar què s'exporta:

  • -t <taula>: exporta només la taula indicada. Es pot repetir.
  • -n <esquema>: exporta només l'esquema indicat.
  • --data-only: exporta només les dades, sense l'estructura.
  • --schema-only: exporta només l'estructura, sense les dades.

Documentació oficial: pg_dump – PostgreSQL

Preparació de l'entorn d'exemple

Els exemples d'aquests apunts s'executen a partir de l'escenari següent:

  1. Crea l'usuari backup_user amb permisos de creació de taules:

    CREATE ROLE backup_user LOGIN PASSWORD 'password';
GRANT CREATE ON DATABASE postgres TO backup_user;

  2. Crea la taula productes i insereix-hi algunes dades:

    SET ROLE backup_user;

CREATE TABLE productes (
    id      SERIAL PRIMARY KEY,
    nom     VARCHAR(100),
    preu    NUMERIC(10,2)
);

INSERT INTO productes (nom, preu) VALUES
    ('Producte A', 10.50),
    ('Producte B', 20.00),
    ('Producte C', 15.75);
Exemple: Exportar els productes amb preu superior a 15

pg_dump no permet filtrar files amb una condició directament. Per això, per exportar un subconjunt de files, es crea prèviament una vista amb la condició desitjada.

  1. Es crea una vista amb els productes de preu superior a 15:

    CREATE VIEW productes_cars AS
SELECT * FROM productes WHERE preu > 15;

  2. S'exporta la vista amb pg_dump en format custom:

    pg_dump -U backup_user -d postgres -F c -t productes_cars -f productes.dump

    pg_dump: reading table structure
pg_dump: reading data for table "public.productes_cars"
pg_dump: dumping contents of table "public.productes_cars"

  3. Es comprova que s'ha creat el fitxer de bolcat:

    ls -lh productes.dump

Restauració amb pg_restore

Els fitxers en format custom, directory o tar es restauren amb l'ordre pg_restore:

pg_restore -U <usuari> -d <base_de_dades> [opcions] <fitxer>
  • -U <usuari>: rol amb què es connecta al servidor.
  • -d <base_de_dades>: base de dades on es restaura la còpia.
  • <fitxer>: fitxer generat per pg_dump.

Documentació oficial: pg_restore – PostgreSQL

Exemple: Importar la vista productes_cars

Es restaura la vista exportada en l'exemple anterior.

  1. S'elimina la vista productes_cars per simular la pèrdua:

    DROP VIEW productes_cars;

  2. Es restaura el contingut des del fitxer de bolcat:

    pg_restore -U backup_user -d postgres productes.dump

  3. Es comprova que s'ha restaurat correctament:

    SELECT * FROM productes_cars;

Còpia física (pg_basebackup)

L'ordre pg_basebackup fa una còpia física de tot el directori de dades del clúster, connectant-se al servidor com a client de replicació. S'utilitza com a punt de partida per a la recuperació física i per a la recuperació a un punt en el temps (Point-in-Time Recovery, PITR).

pg_basebackup -U <usuari_replicacio> -D <directori_desti> [-F <format>] [-X stream] [-P]
  • -U <usuari_replicacio>: rol amb permís de replicació.
  • -D <directori_desti>: directori on es guardarà la còpia.
  • [-F <format>]: (opcional) format d'eixida, plain (p) o tar (t).
  • [-X stream]: (opcional) inclou els WAL generats durant la còpia, de manera que siga consistent.
  • [-P]: (opcional) mostra el progrés de la còpia.

Documentació oficial: pg_basebackup – PostgreSQL

Recuperació a un punt en el temps (PITR)

Una còpia física només permet tornar al moment en què es va fer. Per poder recuperar la base de dades en un punt concret posterior, cal guardar també tots els WAL generats des de la còpia. Això s'aconsegueix configurant l'arxivament de WAL a postgresql.conf:

archive_mode = on
archive_command = 'cp %p /path/to/archive/%f'

Per restaurar la base de dades en un moment concret, cal seguir aquests passos:

  1. Es restaura la còpia base (pg_basebackup) al directori de dades.

  2. Es crea un fitxer postgresql.auto.conf (o recovery.signal, segons la versió) amb les instruccions de recuperació:

    restore_command = 'cp /path/to/archive/%f %p'
recovery_target_time = '2026-01-22 10:00:00'

  3. S'inicia el servidor, que aplica els WAL arxivats fins al moment indicat i s'obri per a l'ús normal.

Documentació oficial: Continuous Archiving and Point-in-Time Recovery (PITR) – PostgreSQL

📌 Aquest document pot quedar desactualitzat després d’imprimir-lo. Pots consultar la versió més recent a la pàgina web.
🌿 Abans d’imprimir aquest document, considera si és realment necessari. Redueix el consum de paper i ajuda a protegir el nostre entorn.
 
CC BY-NC-SA 4.0 (Reconeixement - NoComercial - CompartirIgual) 🅭 Joan Puigcerver Ibáñez