Database backups automatiseren met pg_dump

PostgreSQL 18 draait, en je 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 je database weg naar een bestand, en met `cron` of systemd laat je dat elke dag gebeuren zonder dat je eraan hoeft te denken. Dit artikel legt uit hoe je dat opzet, welk formaat je kiest, en wat het kost als je 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 je het later uitvoert met `psql`. Het resultaat is leesbaar, controleerbaar, en je kunt er met `grep` doorheen zoeken.

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

Stap 1: Kies het juiste formaat

Voor productieomgevingen gebruik je 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: je kunt er later losse tabellen uit terugzetten en parallel herstel draaien met `pg_restore`. Het bestand is niet leesbaar als tekst, dus controle achteraf doe je 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 je 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 je 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 <em> </em> * /usr/bin/pg_dump -Fc mydatabase > /backups/mydatabase_$(date +\%Y\%m\%d).dump

De back-up krijgt de datum in de bestandsnaam, zodat je 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 merk je de dump in je 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 je disk. Zet een retentiebeleid in dezelfde cron-regel:

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

De `find`-opdracht verwijdert alle dumps ouder dan zeven dagen. Zeven dagen is een beginschatting: bepaal zelf hoe ver je 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</p><p>[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</p><p>[Timer]
OnCalendar=<em>-</em>-* 02:00:00
Persistent=true</p><p>[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 je 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 je 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 combineer je 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 je een extra stap bij het terugzetten — je 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 je nooit terugzet, is geen back-up maar een bestand. Op het moment dat je hem nodig hebt, blijkt de dump corrupt of onvolledig.

Test regelmatig of je 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. Beheer je meerdere databases op één cluster, dan mis je 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 blijf je bij `pg_dump -Fc` en draai je `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` heb je 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 je 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. Gebruik je een andere `pg_dump`-versie dan de server, dan werkt het soms wel maar niet altijd. Installeer de clienttools die matchen met je 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 je de juiste databasenaam gebruikt en of de database niet leeg is: `psql -c "\dt" mydatabase` toont de tabellen.

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