Backups That Restore Empty Databases: Dumping Postgres and SQLite Before the Snapshot

The backup job is green every night. Then a disk dies, you restore the app’s folder to a new server, start the containers, and the app opens to an empty account, a database error, or a login screen that no longer recognises anyone. The files were all backed up. The database inside them was not, at least not in a state anything could use.

This is one of the most common homelab backup failures, and it comes from treating a live database like ordinary files. Most self-hosted apps, including photo servers, password managers, document systems and file sync tools, keep their important data in PostgreSQL, SQLite or MariaDB. This guide explains why copying their files while they run is unreliable, and the simple habits that make database restores work.

Why copying a live database fails

A database does not write data in one neat step. It updates several files and pages, keeps some changes in memory, and records others in write-ahead logs before they reach the main files. If a backup tool copies those files one after another while the database is running, it can capture some files from before a change and others from after it. The result is a snapshot of a state that never existed.

Sometimes the database can repair that on startup. Sometimes it refuses to start, or starts with missing or corrupted data. Because backups usually “succeed” either way, nobody notices until the restore.

The dump-then-backup pattern

The simplest reliable approach for a homelab is to create a database dump before the file backup runs, then back up the dump along with everything else:

1. A script runs a few minutes before the backup window.
2. It asks each database to write a consistent export to a file in a backup folder.
3. The normal backup tool, such as restic, Kopia or Borg, backs up that folder together with the app’s other data.
4. Old dumps are rotated so the folder does not grow forever.

Dumps are consistent by design: the database produces them from a single moment in time while continuing to run. They are also portable between versions in a way raw data folders are not, which matters when you restore to a newer container image.

Many backup tools can run the dump script themselves as a “before” hook, so the dump and the backup always happen together. The tools are compared in Kopia vs restic.

PostgreSQL in Docker

For an app with a PostgreSQL container, run pg_dump inside that container and write the output to a mounted folder, for example a compressed dump named by date. For several databases in one Postgres instance, pg_dumpall captures them all, including roles. Restoring means creating a fresh Postgres container of a compatible version and loading the dump.

Two details matter. First, match or exceed the Postgres version when restoring; a dump from an older version loads into a newer one, but not always the other way round. Second, some apps document their own backup procedure, including which database to dump and which files to keep with it. Immich, for example, has built-in automatic database dumps; use them and include the dump folder and the photo files in your backups.

SQLite: small, common and easy to get wrong

Many lighter apps, including Vaultwarden, Uptime Kuma, Mealie, Audiobookshelf and countless others, use SQLite: the whole database is a single file, often with companion -wal and -shm files when write-ahead logging is enabled. Copying only the main .db file while the app runs can miss recent changes held in the WAL file, or catch the file mid-write.

Safe options:

• Use SQLite’s online backup command, which produces a consistent copy while the app runs.
• Use VACUUM INTO to write a clean, compact copy to another file.
• Stop the container briefly, copy the database files together, and start it again. For a small app at 3 a.m., a few seconds of downtime is usually fine.

Some apps include their own backup or export feature. Use it as well as, not instead of, a database-level backup.

MariaDB and MySQL

Apps such as Nextcloud and many PHP-based tools often use MariaDB or MySQL. The equivalent of pg_dump is the dump tool shipped with the database, run inside its container with the option for a single consistent transaction, so tables using the InnoDB engine are captured at one moment without locking the app for long. Include routines and triggers if the app uses them. Restoring means loading the dump into a fresh container of the same or a newer version.

Notes for popular self-hosted apps

Several common apps have their own backup advice that is worth following alongside the general pattern:

• Nextcloud: turn on maintenance mode, dump the database, back up the data and config folders, then turn maintenance mode off, so files and database match exactly. The All-in-One deployment includes its own backup tool that does this.
• Paperless-ngx: its document exporter writes documents together with their metadata in a portable format, which is useful in addition to database dumps.
• Vaultwarden: back up the SQLite database with a safe method, plus the attachments, sends and key files in its data folder.
• Immich: keep its automatic database dumps enabled and back up the dump folder with the upload library.
• Home Assistant: use its built-in backup feature, which handles its databases and configuration together.

When the dump step itself fails

A dump script can fail quietly: the container was renamed, a password changed, the disk filled up, or the database was down. If the backup then runs anyway, it happily saves yesterday’s dump, or an empty file. Make the script exit with an error when the dump fails or produces a suspiciously small file, and have the backup job report failure in that case. A heartbeat check that is only pinged after both steps succeed catches this reliably.

Snapshots: when they are enough

File systems with snapshots, such as ZFS and Btrfs, or LVM snapshots, capture all files at exactly the same instant. A snapshot of a running database is “crash-consistent”: equivalent to the power being cut at that moment. PostgreSQL, SQLite in WAL mode and InnoDB are designed to recover from that, so restoring a snapshot usually works.

Two conditions apply: the snapshot must include all of the database’s files at the same instant, including logs, and you should still test a restore, because some apps keep related data in several places. Many people combine both: snapshots for quick rollbacks, plus dumps as the portable, version-independent copy that goes off-site.

Files and database belong together

Apps rarely keep everything in the database. Photo servers keep images on disk and their metadata in the database; document systems keep scanned files and their tags separately; password managers keep attachments next to the database. A restore needs both halves from roughly the same time. A database from Monday and files from Friday can produce missing thumbnails, orphaned files or broken links inside the app.

Back up each app’s database dump and its data folders in the same backup run, and keep the Compose file and configuration with them. The general approach is covered in Docker Compose backup strategies for beginners.

Proving the restore works

A backup you have not restored is a hope. A simple quarterly drill:

1. Pick one app with a database.
2. Restore its latest backup to a different folder or a test machine.
3. Start a fresh database container and load the dump, or restore the snapshot.
4. Start the app pointing at the restored data, on a different port.
5. Log in and check something real: recent photos, the newest document, a password saved last week.

Write down the steps as you go. The notes become your recovery plan, and the next drill takes half the time. Why green backup dashboards can still hide unrestorable data is examined in restic vs Borg for a 4 TB NAS.

A checklist for every app with a database

• Know which database it uses and where its files are.
• Dump it, or stop it, before the file backup runs.
• Back up the dump, the app’s data folders and its Compose file together.
• Keep several days of dumps, and an off-site copy.
• Alert if the dump step fails, not just the backup step.
• Restore it for real at least once.

Start with the apps whose loss would hurt most, usually photos, passwords and documents, and work down the list. Apps that only cache data, such as dashboards, can wait or simply be rebuilt.

Ten minutes per app turns “the backup was green” into “the app came back with all its data”, which is the only result that matters.