Kennisbank › Databases › Databankback-ups automatiseren met pg_dump

databases gemiddeld 07 September 2026

Databankback-ups automatiseren met pg_dump

PostgreSQL 18 draait, en u hebt nog geen geautomatiseerde back-up. Dat wordt een probleem op de dag dat iemand per ongeluk een `DROP TABLE` uitvoert of een

PostgreSQL 18 draait, en u hebt nog geen geautomatiseerde back-up. Dat wordt een probleem op de dag dat iemand per ongeluk een `DROP TABLE` uitvoert of een disk het begeeft. `pg_dump` schrijft uw database weg naar een bestand, en met `cron` of systemd laat u dat elke dag gebeuren zonder dat u eraan moet denken. Dit artikel legt uit hoe u dat opzet, welk formaat u kiest, en wat het kost als u stappen overslaat.

Vereisten

Een Linux-server met PostgreSQL 16 of nieuwer (de voorbeelden werken ook met oudere versies, maar de documentatie verwijst naar versie 18).
Toegang tot een database-account met voldoende rechten om te dumpen. Dat is doorgaans de eigenaar van de database of een superuser.
Root-toegang of sudo-rechten om cron of systemd te configureren.
Basiskennis van de commandoregel.

Het basiscommando

`pg_dump` leest de inhoud van één database en schrijft die naar stdout. De eenvoudigste vorm:

pg_dump mydatabase > /backups/mydatabase.sql

Dit produceert een SQL-script dat de database volledig herbouwt als u het later uitvoert met `psql`. Het resultaat is leesbaar, controleerbaar, en u kunt er met `grep` doorheen zoeken.

Voor kleine databases is dit prima. Voor grotere databases loopt het vast: het script is ongestructureerd, u kunt geen losse tabellen terugzetten, en het herstel duurt lang omdat alles sequentieel gebeurt.

Stap 1: Kies het juiste formaat

Voor productieomgevingen gebruikt u niet de platte SQL-dump maar het custom format:

pg_dump -Fc mydatabase > /backups/mydatabase.dump

`-Fc` staat voor custom format. Het bestand is gecomprimeerd, waardoor het kleiner is dan de SQL-variant. Belangrijker: u kunt er later losse tabellen uit terugzetten en parallel herstel draaien met `pg_restore`. Het bestand is niet leesbaar als tekst, dus controle achteraf doet u met `pg_restore -l`, niet met `less`.

Het directory format (`-Fd`) splitst de back-up op in meerdere bestanden binnen een map en maakt parallelle dumps mogelijk:

pg_dump -Fd -j 4 -Z0 mydatabase -f /backups/mydatabase_dir

`-j 4` laat vier tabellen tegelijk dumpen, `-Z0` schakelt compressie uit. Dit is sneller bij databases met veel tabellen, maar u betaalt in opslagruimte. Azure Document Database for PostgreSQL Flexible Server gebruikt dit formaat in zijn voorbeelden, maar voor een zelfstandige server is `-Fc` meestal de betere keuze: één bestand, gecomprimeerd, flexibel terug te zetten.

Stap 2: Automatiseer met cron

Handmatig back-ups draaien werkt tot u het vergeet. Een cronjob die dagelijks om 02:00 uur draait, zit in het crontab-bestand van de postgres-gebruiker:

crontab -e -u postgres

Voeg deze regel toe:

0 2   * /usr/bin/pg_dump -Fc mydatabase > /backups/mydatabase_$(date +\%Y\%m\%d).dump

De back-up krijgt de datum in de bestandsnaam, zodat u achteraf weet welke dump van wanneer is. Het tijdstip 02:00 uur is een keuze, niet een wet: kies een moment waarop de database weinig belasting heeft, anders merkt u de dump in uw query-tijden.

Let op de escape `\%` in de cron-regel. Zonder die backslash interpreteert cron het `%`-teken als een newline en draait de job niet zoals bedoeld.

Stap 3: Ruim oude back-ups op

Een back-up van elke dag, onbeperkt bewaard, vult uw disk. Zet een retentiebeleid in dezelfde cron-regel:

0 2    /usr/bin/pg_dump -Fc mydatabase > /backups/mydatabase_$(date +\%Y\%m\%d).dump && find /backups -name "mydatabase_.dump" -mtime +7 -delete

De `find`-opdracht verwijdert alle dumps ouder dan zeven dagen. Zeven dagen is een beginschatting: bepaal zelf hoe ver u terug wilt kunnen. Elke extra dag kost opslagruimte; elke dag minder kost herstelmogelijkheid. Een bedrijf dat aan auditors moet laten zien dat het backups van een jaar bewaart, heeft aan zeven dagen niets.

Stap 4: Automatiseer met systemd in plaats van cron

Moderne Linux-distributies draaien op systemd, en systemd timers bieden een alternatief voor cron met betere logging en afhankelijkheidsbeheer. Een timer bestaat uit twee bestanden.

Maak `/etc/systemd/system/pg_backup.service`:

[Unit]
Description=PostgreSQL backup with pg_dump[Service]
User=postgres
ExecStart=/usr/bin/pg_dump -Fc mydatabase > /backups/mydatabase_$(date +\%Y\%m\%d).dump
ExecStartPost=/usr/bin/find /backups -name "mydatabase_*.dump" -mtime +7 -delete

Maak vervolgens `/etc/systemd/system/pg_backup.timer`:

[Unit]
Description=Run PostgreSQL backup daily at 02:00
[Timer]
OnCalendar=--* 02:00:00
Persistent=true[Install]
WantedBy=timers.target

Activeer de timer:

systemctl daemon-reload
systemctl enable --now pg_backup.timer

Het verschil met cron zit in de controle: `systemctl status pg_backup.timer` laat zien wanneer de timer voor het laatst draaide en wanneer hij de volgende keer gaat. `journalctl -u pg_backup.service` toont de uitvoer van de dump. Bij cron moet u in logbestanden graven die vaak niet eens bestaan.

De `Persistent=true`-regel is het voordeel dat cron niet kent: als de server om 02:00 uur uit stond, draait de back-up direct na het opstarten. Cron slaat die dag gewoon over.

Stap 5: Beveilig de back-ups

Back-upbestanden bevatten dezelfde gevoelige data als uw database. Een dump van een klantendatabase op een wereldleesbare locatie is een datalek dat wacht op een `chmod`.

Zorg dat de opslagmap alleen toegankelijk is voor de gebruiker die de back-up draait:

chown postgres:postgres /backups
chmod 700 /backups

`pg_dump` ondersteunt zelf geen encryptie. Voor versleuteling combineert u het met `gpg`:

/usr/bin/pg_dump -Fc mydatabase | /usr/bin/gpg --encrypt --recipient back-up@example.com > /backups/mydatabase_$(date +\%Y\%m\%d).dump.gpg

Dit kost u een extra stap bij het terugzetten — u moet eerst ontsleutelen — maar het maakt een gestolen back-upbestand waardeloos. Voor een database met persoonsgegevens is dat geen overweging maar een verplichting onder de AVG.

Stap 6: Test het herstel

Een back-up die u nooit terugzet, is geen back-up maar een bestand. Op het moment dat u hem nodig hebt, blijkt de dump corrupt of onvolledig.

Test regelmatig of uw dumps bruikbaar zijn. Zet de back-up terug in een aparte database:

/usr/bin/createdb -U postgres restore_test
/usr/bin/pg_restore -U postgres -d restore_test /backups/mydatabase_20260825.dump

Draait dit zonder fouten, dan is de dump intact. Verwijder de testdatabase daarna weer:

/usr/bin/dropdb -U postgres restore_test

Plan deze test in, net als de back-up zelf. Een maandelijkse restore-test vangt de meeste problemen op: een disk die stilletjes corrupte blokken produceert, een `pg_dump`-versie die niet meer matcht met de server, een script dat een leeg bestand schrijft omdat het wachtwoord van de database-gebruiker veranderd is.

Stap 7: Back-up alle databases met pg_dumpall

`pg_dump` maakt een back-up van één database. Beheert u meerdere databases op één cluster, dan mist u met losse dumps de globale objecten: rollen, gebruikers, tablespaces. Die staan niet in een individuele database maar op clusterniveau.

`pg_dumpall` vangt dat op:

/usr/bin/pg_dumpall > /backups/cluster_$(date +\%Y\%m\%d).sql

Dit commando dumpt alle databases én de globale objecten in één SQL-script. Het nadeel: het formaat is altijd platte SQL, dus geen custom format, geen parallel herstel. Voor een volledige clusterback-up vóór een upgrade of migratie is het prima. Voor dagelijkse back-ups van individuele databases blijft u bij `pg_dump -Fc` en draait u `pg_dumpall` apart, bijvoorbeeld wekelijks.

Stap 8: Beperk de rechten van de back-up-gebruiker

De gebruiker die de back-up draait, heeft toegang tot alle data in de database. Een gecompromitteerd back-upaccount is daarmee een gecompromitteerde database.

Maak een aparte gebruiker aan die alleen mag dumpen, niet schrijven:

CREATE USER backup_user WITH PASSWORD 'een_lang_wachtwoord';
GRANT CONNECT ON DATABASE mydatabase TO backup_user;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO backup_user;

Deze gebruiker kan niets wijzigen, alleen lezen. Voor `pg_dumpall` hebt u meer rechten nodig — die moet alle databases en rollen lezen — dus gebruik die alleen waar nodig.

Verificatie

Controleer of de back-up daadwerkelijk is aangemaakt en leesbaar is:

ls -lh /backups/
pg_restore -l /backups/mydatabase_$(date +\%Y\%m\%d).dump | head -20

De eerste opdracht toont de bestanden met hun grootte. De tweede toont de inhoud van de dump: als u hier tabellen en data ziet, is het bestand geen leeg script.

Voor een systemd-timer:

systemctl status pg_backup.timer

Dit toont de volgende geplande run en de laatste uitvoering.

Veelgestelde problemen

De cronjob draait, maar er wordt geen bestand aangemaakt. De kans is groot dat de postgres-gebruiker geen schrijfrechten heeft op de doelmap, of dat het wachtwoord van de database-gebruiker niet klopt. Controleer de uitvoer door de cron-regel handmatig uit te voeren als postgres-gebruiker en kijk naar de foutmelding. Cron zelf logt vaak niets bruikbaars.

`pg_dump` waarschuwt over een versieverschil. Gebruikt u een andere `pg_dump`-versie dan de server, dan werkt het soms wel maar niet altijd. Installeer de clienttools die matchen met uw serverversie, of gebruik `pg_dump` vanaf de server zelf.

De back-up is veel kleiner dan verwacht. Een dump van een lege database is een paar kilobyte. Controleer of u de juiste databasenaam gebruikt en of de database niet leeg is: `psql -c "\dt" mydatabase` toont de tabellen.

Zelf hosten betekent dat u de helpdesk bent. Een back-upstrategie die u niet automatiseert en niet test, is een strategie die u pas ontdekt op het moment dat hij faalt. En dat moment komt altijd op een zondagavond.

← Terug naar kennisbank