Database Backups Done Right: Dumps, WAL, and PITR

The backup that fails is almost never the one that did not run. It is the one that ran perfectly for eight months, filled a directory with reassuring nightly files, and turned out to be a copy of a database taken mid-write that no engine will reopen. You discover this at the worst possible moment, holding a corrupt production file and a folder of corrupt backups, having learned the oldest lesson in operations the hard way: an untested backup is a rumour. This article is about making the rumour true, and doing it with the three tools that actually matter — logical dumps, write-ahead log archiving, and point-in-time recovery — so you can pick the right level of protection for each database instead of applying the same tired nightly cp to all of them.

The reason to understand all three, rather than picking one and moving on, is that they protect against different disasters and cost different amounts. A homelab does not need airline-grade recovery for its Pi-hole query log, and it does need something better than a weekly snapshot for the database holding two years of home-automation history you can never regenerate. Matching the mechanism to the value of the data is the entire craft.

What “backup” can actually mean

“Backup” is three quite different operations wearing one word, and conflating them is where most homelab backup strategies go wrong.

A logical dump exports the content of the database as instructions to rebuild it — a stream of SQL CREATE TABLE and INSERT statements, or a compressed archive of the same. It is portable across versions and often across machines, it is human-readable when uncompressed, and it captures a single consistent moment. Its weakness is that restoring means replaying every statement, which for a large database is slow, and you only ever have the moments you happened to dump.

A physical backup copies the actual files the database stores on disk — the data pages themselves. It restores far faster because there is nothing to replay, and it is the foundation the next level builds on. On its own it captures one moment, exactly like a dump.

Point-in-time recovery, or PITR, is a physical base backup plus a continuous stream of every change made since, captured from the write-ahead log — the WAL. Every serious database writes changes to a sequential log before applying them to the main files; that is how it survives a crash. Archive that log as it is written and you can restore the base backup, then replay the WAL forward to any moment you choose — including “one second before I ran that DELETE without a WHERE clause”. This is the difference between losing a day and losing a second.

Level one: logical dumps

Start here for everything, because a dump is cheap, portable and trivially restorable, and for a great many homelab databases it is genuinely all you need. For Postgres the tool is pg_dump, and its custom format is worth knowing because it compresses, restores in parallel, and lets you cherry-pick individual tables out of the archive later:

1
2
3
4
5

# Postgres: consistent, compressed, restorable in parallel.
pg_dump --format=custom --compress=9 --file=/backups/app-$(date +%F).dump appdb

# Restore into a fresh database, four jobs at once:
pg_restore --jobs=4 --dbname=appdb_restored /backups/app-2025-03-31.dump

The --format=custom archive is the one to reach for over a plain SQL file: it is smaller, it restores faster, and pg_restore --list can show you exactly what is inside. For MySQL or MariaDB the equivalent is mysqldump --single-transaction, where that flag is load-bearing — without it, a dump of a busy InnoDB database is taken table by table without a consistent snapshot and can capture the data in a self-inconsistent state.

For SQLite, the dump is a one-liner and the online-backup form is safer than any file copy, a point I laboured in the case for SQLite as a real production database:

1
2
3

# SQLite: safe while the app is running, either form.
sqlite3 app.db ".backup '/backups/app-$(date +%F).db'"     # binary, exact
sqlite3 app.db ".dump" | gzip > /backups/app-$(date +%F).sql.gz   # portable SQL

Whatever the engine, the dump only counts once it has left the machine. A backup sitting on the same disk as the database protects you against a fat-fingered DELETE and against nothing else. A failed drive, a corrupted filesystem or a stolen box takes the database and the backup down together. Copy it to another host, an external drive, or object storage, and keep more than one generation so that a dump which silently started failing does not quietly overwrite your last good one.

Level two: WAL archiving

The moment a database matters enough that losing a whole day between dumps would sting, you graduate to archiving the write-ahead log. The idea is simple. You take one physical base backup, then you tell the database to hand every completed chunk of WAL to a command that files it away somewhere safe. In Postgres this is a couple of settings:

1
2
3
4

# postgresql.conf — turn the database into a source of archived WAL.
wal_level = replica
archive_mode = on
archive_command = 'test ! -f /archive/%f && cp %p /archive/%f'

The archive_command runs once per completed WAL segment; %p is the path to the segment, %f its filename. The test ! -f guard refuses to overwrite an existing archived segment, which is a cheap insurance against a misconfiguration silently clobbering your history. In a real setup you would point that command at object storage or another machine rather than a local directory, but the shape is the same. The base backup itself comes from pg_basebackup:

1
2

# One physical base backup to anchor the WAL stream.
pg_basebackup --pgdata=/backups/base-$(date +%F) --format=tar --gzip --progress

The payoff of doing this rather than just dumping is twofold. Your recovery granularity drops from “the last nightly dump” to “any moment covered by the archive”, and your restore is a fast file copy followed by a replay instead of a slow statement-by-statement reload. The cost is more moving parts and disk for the WAL, which is why you reserve it for the databases that have earned it.

For SQLite the same continuous-protection idea is packaged far more simply, because Litestream streams the WAL to object storage with a single config file and gives you point-in-time restore without any of the base-backup ceremony. If your important databases are SQLite, that is the whole of level two and three in one small binary.

Level three: actually recovering to a point in time

Archiving the WAL is only half of PITR; the half that matters is the restore, and the first time you should perform it is not during an outage. In Postgres, recovery means placing the base backup where the data directory belongs, providing the archived WAL, and telling the server how far to replay:

1
2
3

# postgresql.conf on the restore target
restore_command = 'cp /archive/%f %p'
recovery_target_time = '2025-03-31 14:59:30+00'

You then create a recovery.signal file in the data directory and start Postgres. It replays the archived WAL forward from the base backup and stops the instant it reaches your target time, leaving the database exactly as it was one second before the accident. Being able to name a wall-clock moment and land there is the entire reason WAL archiving is worth the trouble; it turns “we lost everything since midnight” into “we lost thirty seconds, and only the thirty seconds we wanted to lose”.

The mechanics differ per engine — MySQL calls its equivalent the binary log and replays it with mysqlbinlog — but the concept is identical everywhere: a physical anchor plus a replayable change log equals recovery to any moment. Once you have internalised that, every database’s PITR documentation reads as a variation on the same three steps.

The rule that outranks all of this

None of the above is a backup until you have restored from it. I mean that literally, as an operational rule with no exceptions: a backup you have never restored is a hypothesis, and hypotheses fail at the worst time. The single highest-value hour you can spend on this whole topic is a scheduled restore drill — pull last night’s dump, restore it into a throwaway database on a spare box, and run a query that proves the data is really there and really current.

Automate the check if you can. A tiny script that restores the newest backup into a scratch database, counts a few tables, compares the row counts against sane thresholds, and shouts if anything looks wrong will catch the silent-failure class of disaster that no amount of careful backup configuration prevents. Backups fail quietly; only a restore is loud. I schedule mine monthly, and every time it runs I trust the folder of backup files a little less and the tested-restore process a little more, which is the correct direction for that trust to flow.

Troubleshooting

pg_restore fails with role or ownership errors. The dump references roles that do not exist on the target. Restore with --no-owner --no-privileges to ignore ownership, or create the roles first. For a full-cluster move, pg_dumpall --globals-only captures the roles separately.

WAL is piling up and the disk is filling. Your archive_command is failing and Postgres refuses to discard un-archived WAL, which is it protecting you correctly. Check the server log for the failing command, fix the destination, and the backlog will drain. Never delete WAL by hand to free space while archiving is on.

The restored database is missing recent data. For a logical dump, you simply restored an old dump — check the file date and your rotation. For PITR, your recovery_target_time was earlier than you meant, or the WAL covering that window never made it to the archive. Confirm the archive is contiguous from the base backup up to the target.

mysqldump produced an inconsistent backup. You omitted --single-transaction on an InnoDB database, so tables were dumped at different moments. Add the flag; it takes a consistent snapshot without locking every table.

Restore drill works but is glacially slow. Large logical dumps replay slowly by nature. Restore in parallel with pg_restore --jobs=N, and for anything where restore time is itself the emergency, that slowness is the argument for keeping a physical base backup so recovery is a copy rather than a reload.

Is it worth it?

The honest answer scales with the data. For the throwaway databases — caches, logs you can regenerate, anything you would shrug at losing — a nightly logical dump copied off-box is proportionate and you should not gold-plate it. For the databases holding things you can never get back, the home-automation history, the notes, the photo metadata built up over years, WAL archiving and a rehearsed point-in-time restore are worth every minute of setup, because the alternative is discovering their value at the exact moment you can no longer recover them.

Whatever level you choose, spend the hour on the restore drill before you spend another minute on the backup. Fancier archiving on top of a restore you have never tested is decoration. A humble nightly dump that you have personally restored, off-box, last month, beats an elaborate PITR pipeline that has only ever run in one direction. Do the boring thing, prove it works, and then make it fancier.