Come si esegue il backup e il recovery di PostgreSQL?

Question

Accepted Answer

PostgreSQL offre diversi metodi di **backup** — backup logici (`pg_dump`) e backup fisici (base backup + WAL per **point-in-time recovery**). Una solida strategia di backup e recovery è essenziale per proteggersi dalla perdita di dati, e crucialmente, **i backup devono essere testati**.

## Backup logici — pg_dump

```bash
# pg_dump — dumps a database as SQL (or a custom archive) — portable, flexible
pg_dump -U user -d mydb -F c -f backup.dump      # custom format (compressed, flexible restore)
pg_dump -U user -d mydb > backup.sql              # plain SQL
pg_dumpall > all.sql                              # ALL databases + roles

# restore
pg_restore -U user -d mydb backup.dump            # from custom format
psql -U user -d mydb -f backup.sql                # from plain SQL
```

`pg_dump` crea un backup logico (SQL o archive) di un database — portabile (ripristinabile a versioni/server diversi), buono per database più piccoli, migrazioni e backup selettivi. Ma è uno snapshot point-in-time (nessun recovery continuo).

## Backup fisici + Point-in-Time Recovery (PITR)

```text
For larger/critical databases: a BASE BACKUP (a copy of the data files) + continuous
archiving of the WAL (write-ahead log) → enables POINT-IN-TIME RECOVERY:
  ✓ Restore the base backup, then REPLAY WAL up to ANY point in time
  ✓ Recover to JUST BEFORE a disaster (e.g. an accidental DROP TABLE at 2:47pm)
→ Tools: pg_basebackup, WAL archiving, and tools like pgBackRest / Barman automate this.
```

**PITR** è potente: archiviando continuamente i WAL, puoi recuperare a qualsiasi momento — non solo l'ultimo backup — il che è essenziale per minimizzare la perdita di dati da disastri.

## Elementi essenziali della strategia di backup

```text
✓ Regular, AUTOMATED backups (scheduled, not manual/forgotten)
✓ Store backups OFF the database server (and offsite / cloud) — survive hardware failure
✓ Retention policy (keep daily/weekly/monthly as needed)
✓ Combine: periodic base backups + continuous WAL for PITR (critical data)
✓ Backups from a REPLICA to avoid loading the primary
```

## La regola più importante: TEST i tuoi backup

```text
⚠️ A backup you've never restored is NOT a reliable backup.
UNTESTED backups fail when you need them most (corrupt, incomplete, wrong process).
→ Regularly TEST restores to verify they actually work and you know the procedure.
```

## Perché è importante

Il backup e il recovery è una conoscenza **critica** per operare PostgreSQL responsabilmente — proteggere dalla perdita di dati (da guasto hardware, corruzione, eliminazione accidentale, disastri) è una delle responsabilità più importanti per qualsiasi database di produzione, quindi comprendere le strategie di backup è una conoscenza essenziale a livello senior.

Comprendere i metodi è necessario: **`pg_dump`** (backup logici — dump SQL/archive portabili, buoni per database più piccoli, migrazioni e backup selettivi/cross-version, ma solo snapshot point-in-time) e **backup fisici con Point-in-Time Recovery (PITR)** (base backup + archiving continuo WAL, abilitando il recovery a *qualsiasi* momento — che è potente per minimizzare la perdita di dati, come il recupero poco prima di un accidentale `DROP TABLE`, essenziale per dati critici).

Conoscere gli **elementi essenziali della strategia** (backup automatizzati regolari, archiviarli fuori dal server e offsite, politiche di retention, combinare base backup con WAL per PITR su dati critici, fare backup da repliche) è una conoscenza operazionale importante.

Più criticamente, comprendere che **i backup devono essere testati** — un backup non testato è inaffidabile e spesso fallisce esattamente quando necessario (corruzione, dati incompleti, processo di ripristino non familiare) — è un principio vitale e spesso trascurato: testare regolarmente i ripristini è ciò che rende una strategia di backup effettivamente affidabile.

Poiché la perdita di dati può essere catastrofica (e il recovery è possibile solo con backup funzionanti), e poiché una strategia appropriata (metodi di backup appropriati, PITR per perdita minima, storage offsite, e crucialmente *restore testati*) è ciò che protegge i dati di un'organizzazione, comprendere il backup e il recovery di PostgreSQL è una conoscenza critica a livello senior per operazioni database responsabili — una responsabilità ad alto rischio dove la differenza tra una strategia testata e una non testata può essere la differenza tra recuperare da un disastro e perdere i dati permanentemente, rendendola un argomento essenziale e frequentemente rilevante per chiunque operi database di produzione.