Hoe maak je back-ups van PostgreSQL en hoe herstel je deze?

Question

Accepted Answer

PostgreSQL biedt verschillende **back-upmethoden** — logische back-ups (`pg_dump`) en fysieke back-ups (base backup + WAL voor **point-in-time recovery**). Een solide back-up- en herstelstrategie is essentieel voor bescherming tegen gegevensverlies, en cruciaal: **back-ups moeten worden getest**.

## Logische back-ups — 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` maakt een logische back-up (SQL of archief) van een database — draagbaar (herstelbaar naar verschillende versies/servers), geschikt voor kleinere databases, migraties en selectieve back-ups. Maar het is een point-in-time snapshot (geen continue recovery).

## Fysieke back-ups + 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** is krachtig: door WAL continu te archiveren, kun je naar elk moment herstellen — niet alleen de laatste back-up — wat essentieel is voor het minimaliseren van gegevensverlies bij rampen.

## Essentiële back-upstrategie

```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
```

## De belangrijkste regel: TEST je back-ups

```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.
```

## Waarom het belangrijk is

Back-up en herstel is **kritieke** kennis voor verantwoorde werking van PostgreSQL — bescherming tegen gegevensverlies (van hardwarestoringen, corruptie, onopzettelijk verwijderen, rampen) is één van de belangrijkste verantwoordelijkheden voor elke productiedatabase, dus inzicht in back-upstrategieën is essentiële kennis op senior niveau.

De methoden begrijpen is noodzakelijk: **`pg_dump`** (logische back-ups — draagbare SQL/archiefdumps, goed voor kleinere databases, migraties en selectieve/cross-versie back-ups, maar alleen point-in-time snapshots) en **fysieke back-ups met Point-in-Time Recovery (PITR)** (base backup + continu WAL-archivering, waarmee je naar *elk* moment kunt herstellen — wat krachtig is voor het minimaliseren van gegevensverlies, zoals herstellen vlak voordat een onopzettelijke `DROP TABLE`, essentieel voor kritieke gegevens).

De **essentiële strategieën** kennen (regelmatige geautomatiseerde back-ups, deze buiten de server en ter wereld opslaan, bewaarbeleid, base backups combineren met WAL voor PITR op kritieke gegevens, back-ups maken van replica's) is belangrijke operationele kennis.

Het meest kritiek is inzicht dat **back-ups moeten worden getest** — een ongeteste back-up is onbetrouwbaar en faalt vaak precies wanneer nodig (corruptie, onvolledige gegevens, onbekend herstelproces) — is een vitaal, vaak verwaarloosd principe: regelmatig testen van herstelacties is wat een back-upstrategie echt betrouwbaar maakt.

Omdat gegevensverlies catastrofaal kan zijn (en herstel alleen mogelijk is met werkende back-ups), en omdat een juiste strategie (passende back-upmethoden, PITR voor minimaal verlies, opslag ter wereld en cruciaal *geteste* herstelacties) is wat de gegevens van een organisatie beschermt, is inzicht in PostgreSQL back-up en herstel kritieke kennis op senior niveau voor verantwoorde databaseoperaties — een verantwoordelijkheid met hoge inzet waar het verschil tussen een geteste strategie en een ongeteste strategieën het verschil kan zijn tussen herstellen van een ramp en permanent gegevensverlies, waardoor het een essentieel, regelmatig relevant onderwerp is voor iedereen die productiedatabases beheert.