Logische versus fysieke back-ups
pg_dump en base backups.
Logische versus fysieke back-ups is een gratis SQL Academy-les op CoddyKit. Dit is les 1 van 4. Je kunt de volledige les hieronder gratis lezen en daarna in de browser praktisch oefenen met een ingebouwde code-editor en een AI-begeleider die 24/7 beschikbaar is. Deze les maakt deel uit van het leertraject SQL Academy. Je voortgang wordt gesynchroniseerd op het web en in de CoddyKit-app. De cursus SQL Academy bevat in totaal 4 lessen.
Wat is een databaseback-up?
Een back-up is een kopie van je databasegegevens die kan worden gebruikt om het systeem te herstellen na gegevensverlies, beschadiging of een ramp. Zonder betrouwbare back-ups kan één hardwarestoring of een per ongeluk uitgevoerde DELETE maanden of jaren aan gegevens voorgoed vernietigen.
PostgreSQL biedt twee brede categorieën back-upstrategieën: logische back-ups en fysieke back-ups. Elke categorie heeft specifieke eigenschappen, toepassingsgebieden en afwegingen die elke databasebeheerder moet begrijpen.
Logische back-ups uitgelegd
Een logische back-up exporteert de database als door mensen leesbare SQL-instructies — CREATE TABLE, INSERT, COPY en vergelijkbare opdrachten. Het meest gebruikte hulpprogramma hiervoor in PostgreSQL is pg_dump.
Omdat de uitvoer uit gewone SQL bestaat, is een logische back-up overdraagbaar: je kunt deze terugzetten naar een andere PostgreSQL-versie, een ander besturingssysteem of zelfs selectief afzonderlijke tabellen of schema's terugzetten. De afweging is dat het maken en terugzetten van back-ups van grote databases langzaam kan zijn.
pg_dump gebruiken voor een logische back-up
Het hulpprogramma pg_dump wordt vanaf de opdrachtregel uitgevoerd, niet binnen SQL. Het maakt verbinding met een actieve PostgreSQL-server en exporteert de gekozen database. Je kunt gewone SQL, een aangepaste gecomprimeerde indeling of een mapindeling als uitvoer kiezen.
De onderstaande SQL simuleert wat een logische back-up vastlegt — de structuur en gegevens van een tabel als opnieuw uitvoerbare instructies.
-- Simulating what pg_dump produces for a table
-- (These statements are written by pg_dump into the backup file)
CREATE TABLE orders (
id SERIAL PRIMARY KEY,
customer TEXT NOT NULL,
amount NUMERIC(10,2),
created_at TIMESTAMPTZ DEFAULT now()
);
INSERT INTO orders (customer, amount, created_at) VALUES
('Alice', 149.99, '2024-01-15 09:30:00+00'),
('Bob', 89.50, '2024-01-16 14:00:00+00'),
('Carol', 210.00, '2024-01-17 11:15:00+00');Uitvoerindelingen van pg_dump
pg_dump ondersteunt vier uitvoerindelingen, elk geschikt voor verschillende herstelprocessen:
- plain — een gewoon SQL-script dat in elke teksteditor kan worden gelezen.
- custom — een gecomprimeerde binaire indeling; de meest flexibele indeling, die parallel herstel ondersteunt.
- directory — één bestand per tabel, met ondersteuning voor parallel exporteren en terugzetten.
- tar — een tar-archief van de mapindeling.
De indeling custom wordt aanbevolen voor grote databases, omdat pg_restore objecten parallel kan terugzetten met -j N werkprocessen.
-- Checking which databases exist before choosing what to back up
SELECT datname,
pg_size_pretty(pg_database_size(datname)) AS size
FROM pg_database
WHERE datname NOT IN ('template0', 'template1')
ORDER BY pg_database_size(datname) DESC;Een logische back-up terugzetten
Een logische back-up in gewone SQL wordt teruggezet met psql. Voor een back-up in de indeling custom heb je pg_restore nodig. Beide hulpprogramma's voeren de SQL-instructies opnieuw uit om tabellen, indexen, beperkingen en gegevens opnieuw te maken.
Omdat logische back-ups SQL bevatten, kun je ze vóór het terugzetten bewerken — bijvoorbeeld om slechts één tabel terug te zetten of een schemanaam te wijzigen. Deze flexibiliteit is een van de grootste voordelen van de logische aanpak.
-- After restoring a backup, verify row counts match expectations
SELECT
schemaname,
relname AS table_name,
n_live_tup AS estimated_rows
FROM pg_stat_user_tables
ORDER BY n_live_tup DESC;Fysieke back-ups uitgelegd
Een fysieke back-up (ook wel een basisback-up genoemd) kopieert de onbewerkte gegevensbestanden die PostgreSQL op schijf gebruikt — de pagina's, WAL-segmenten (Write-Ahead Log) en configuratiebestanden. Het resultaat is een binaire momentopname van het hele cluster op een bepaald tijdstip.
Fysieke back-ups zijn voor grote databases meestal veel sneller terug te zetten, omdat SQL niet opnieuw hoeft te worden uitgevoerd; PostgreSQL leest de bestanden eenvoudig terug naar hun oorspronkelijke locatie en speelt de WAL opnieuw af om een consistente toestand te bereiken.
pg_basebackup: een fysieke back-up maken
pg_basebackup is het standaardhulpprogramma van PostgreSQL voor fysieke back-ups. Het streamt de gegevensmap van een actieve primaire server via een replicatieverbinding. Je hebt een gebruiker met replicatierechten nodig en wal_level moet zijn ingesteld op replica of hoger.
In de database kun je de replicatie-instellingen opvragen om te bevestigen dat de server correct is geconfigureerd voordat je een basisback-up probeert te maken.
-- Verify WAL level and replication settings before a physical backup
SELECT name, setting, unit
FROM pg_settings
WHERE name IN (
'wal_level',
'max_wal_senders',
'archive_mode',
'archive_command'
)
ORDER BY name;WAL-archivering en herstel naar een bepaald tijdstip
Een basisback-up legt een moment vast. Om na die back-up naar een willekeurig tijdstip te kunnen herstellen, speelt PostgreSQL gearchiveerde WAL-segmenten opnieuw af — dit heet herstel naar een bepaald tijdstip (Point-in-Time Recovery, PITR).
Wanneer archive_mode = on is ingesteld en archive_command is geconfigureerd, kopieert PostgreSQL voltooide WAL-segmenten naar een archieflocatie. Tijdens het herstel haalt restore_command die segmenten weer op, zodat de server ze tot aan het gewenste tijdstip kan afspelen.
-- Inspect current WAL position and archive status
SELECT
pg_current_wal_lsn() AS current_lsn,
pg_walfile_name(pg_current_wal_lsn()) AS current_wal_file,
archived_count,
failed_count,
last_archived_wal,
last_archived_time
FROM pg_stat_archiver;Logische en fysieke back-ups vergelijken
De keuze tussen logische en fysieke back-ups hangt af van je vereisten:
- Logisch (pg_dump): overdraagbaar tussen versies, ondersteunt gedeeltelijk herstel en is leesbaar voor mensen, maar is langzaam voor grote databases en biedt geen granulariteit op subtransactieniveau.
- Fysiek (pg_basebackup + WAL): snel herstel voor grote clusters, ondersteunt PITR, is versiegebonden (moet naar dezelfde hoofdversie worden teruggezet) en zet het volledige cluster terug — je kunt geen afzonderlijke tabel terugzetten.
Productieomgevingen gebruiken meestal beide: elke nacht fysieke basisback-ups met continue WAL-archivering, plus periodieke logische exports voor overdraagbaarheid en gericht herstel.
De integriteit van back-ups controleren
Een back-up die nooit is getest, is geen back-up — het is hoop. Controleer back-ups altijd door ze terug te zetten in een testomgeving en de gegevens te verifiëren.
Voor logische back-ups is het tellen van rijen en vergelijken van controlesommen een snelle integriteitscontrole. Voor fysieke back-ups introduceerde PostgreSQL 14+ pg_verifybackup, dat het manifestbestand controleert dat door pg_basebackup is geschreven.
-- After a test restore, compare row counts across critical tables
SELECT
relname AS table_name,
n_live_tup AS live_rows,
pg_size_pretty(pg_total_relation_size(relid)) AS total_size
FROM pg_stat_user_tables
WHERE schemaname = 'public'
ORDER BY n_live_tup DESC
LIMIT 20;Back-ups bewaken en plannen
Het automatiseren en bewaken van back-ups is net zo belangrijk als het maken ervan. Houd bij wanneer back-ups voor het laatst zijn uitgevoerd, hoe lang ze duurden en of ze zijn geslaagd. PostgreSQL stelt hiervoor nuttige metagegevens beschikbaar.
Voor fysieke back-ups toont pg_stat_archiver de laatste geslaagde archivering en eventuele fouten. Voor logische back-ups kun je pg_dump vanuit een script aanroepen dat de begintijd, eindtijd, bestandsgrootte en afsluitcode naar een bewakingstabel of waarschuwingssysteem schrijft.
-- Create a simple backup log table to track logical backup runs
CREATE TABLE IF NOT EXISTS backup_log (
id SERIAL PRIMARY KEY,
backup_type TEXT NOT NULL CHECK (backup_type IN ('logical', 'physical')),
started_at TIMESTAMPTZ NOT NULL DEFAULT now(),
finished_at TIMESTAMPTZ,
size_bytes BIGINT,
status TEXT NOT NULL DEFAULT 'running',
notes TEXT
);
-- Record the start of a logical backup job
INSERT INTO backup_log (backup_type, status)
VALUES ('logical', 'running')
RETURNING id, started_at;Logische en fysieke back-ups: snelle check
Test je kennis van logische en fysieke back-upstrategieën in PostgreSQL.
Samenvatting van de les: logische en fysieke back-ups
In deze les heb je twee fundamentele PostgreSQL-back-upstrategieën verkend:
- Logische back-ups gebruiken
pg_dumpom databases als SQL-instructies te exporteren. Ze zijn overdraagbaar, leesbaar voor mensen en ondersteunen gedeeltelijk herstel, maar kunnen langzaam zijn voor zeer grote databases. - Fysieke back-ups gebruiken
pg_basebackupom onbewerkte gegevensbestanden te kopiëren. In combinatie met WAL-archivering maken ze snel herstel en herstel naar een bepaald tijdstip mogelijk, maar ze zijn versiegebonden en zetten altijd het volledige cluster terug. - Productiesystemen combineren doorgaans beide strategieën: fysieke basisback-ups met WAL-archivering voor snel herstel tot op detailniveau, plus periodieke logische exports voor overdraagbaarheid.
- Test altijd het terugzetten van je back-ups. Een niet-geteste back-up is in een echte rampsituatie niet betrouwbaar.
Het begrijpen van deze twee benaderingen is essentieel voor het ontwerpen van een robuust plan voor herstel na een ramp voor elke PostgreSQL-implementatie.
Leer SQL met een AI-tutor — gratis
Schrijf echte code en voer die uit in je browser, krijg direct hulp van een AI-tutor die 24/7 beschikbaar is en ga verder waar je gebleven bent op het web of in de app.
- Cursussen
- 46
- Lessen
- 183
Veelgestelde vragen
Is de les “Logische versus fysieke back-ups” gratis?
Ja — de volledige tekst van “Logische versus fysieke back-ups” kun je hier gratis op het web lezen. Als je interactief wilt oefenen met een ingebouwde code-editor en een AI-begeleider die 24/7 beschikbaar is, en de rest van de cursus SQL Academy wilt ontgrendelen, kun je upgraden naar CoddyKit PRO. De cursus SQL Academy bevat in totaal 4 lessen.
Wat leer ik in “Logische versus fysieke back-ups”?
pg_dump en base backups. Je oefent met SQL Academy door code rechtstreeks in de browser uit te voeren. Een AI-begeleider die 24/7 beschikbaar is beantwoordt je vragen terwijl je de les doorwerkt.
Heb ik ervaring nodig om met SQL Academy te beginnen?
Ervaring vooraf is niet nodig. SQL Academy op CoddyKit is opgebouwd voor beginners tot gevorderden, zodat je hier of bij het begin kunt starten en in je eigen tempo kunt leren. Dit is les 1 van 4.
Hoe lang duurt de les “Logische versus fysieke back-ups”?
De meeste lessen van CoddyKit duren ongeveer 5–10 minuten. Elke les is kort en interactief, zodat je gestaag vooruitgaat en op het web en in de app precies verdergaat waar je was gebleven.
Kan ik code schrijven en uitvoeren in deze les over SQL Academy?
Ja. Elke les over SQL Academy bevat een ingebouwde code-editor, zodat je rechtstreeks in je browser echte code kunt schrijven en uitvoeren en direct feedback van AI krijgt — lokale installatie is niet nodig.
Alle lessen in deze cursus
- Logische versus fysieke back-ups
- Herstel naar een bepaald tijdstip
- Uw herstelprocedures testen
- Planning voor disaster recovery