Database Intact, Files Missing: A Backup Gap

A database backup is incomplete when attachment files are excluded. The database can retain filenames and paths while the actual photos or PDFs disappear from storage. In this hotel operations case, roughly 400 files were affected and only about 45% returned. Recovery started by comparing database references, actual files and backup copies.

Every backup green. Every migration applied. Every row intact. But photos attached to maintenance reports, incident evidence, and housekeeping documentation were simply gone. Nobody noticed until a user opened an old report and got a broken image.

This is a mismatch between database references and stored files. Strictly, an orphaned blob is an existing file with no database reference; a reference to a missing file is the opposite failure. Both need reconciliation.

How files vanish while rows survive

The database is transactional. The file store isn't part of that transaction. That separation permits several common failure modes:

• A storage migration moves files between servers or buckets and drops some on the way, with no error surfacing anywhere the application looks.
• A cleanup script scoped slightly wrong deletes files that active rows still reference.
• A bucket lifecycle rule expires objects someone assumed were permanent.
• An upload fails after the row was already committed, leaving a reference to a file that never existed at all.

These failures can occur without a database error. Picture the database and the file store as two clerks who never talk to each other: one keeps the guest list, the other keeps the room keys. Referential integrity inside PostgreSQL is enforced by the engine. Referential integrity between PostgreSQL and the file store is enforced by nobody — unless you build the job that enforces it.

The reconciliation manifest

Recovery starts with knowing the exact shape of the damage. The tool for that is a three-way manifest, built from the only sources of truth available:

List every file path the rows reference. List every object storage actually holds. List everything the backup mirrors contain.

The joins classify each file into one of four buckets, and a vague "some images are broken" turns into a precise work order: restore these, write off those, review the orphans.

What recovery actually looked like

About 45% of the missing files came back from backup mirrors — older copies of the storage volume that happened to predate the loss. The remaining 55% were not recovered from the available copies. The regular backup regime covered only the database.

The rows describing each file — filename, upload date, which report it belonged to — were preserved immaculately. The files themselves had not been included in the regular backup scope. The system had been diligently protecting the pointers while ignoring what they pointed at.

This was the result in this case; it is not a general recovery rate. Without the manifest, even the recoverable fraction would have been lost, because nobody would have known which files to look for in which mirror.

The lessons that generalize

Object storage belongs inside the backup scope. Files referenced by database rows are business data with different storage economics — nothing more. Back them up on the same schedule and retention as the database, and verify both sides in the same restore test. The discipline for that is the subject of your pg_dump is not a backup until you've restored it.

Write files first, commit rows second. The upload-then-commit pattern reduces broken-reference risk: if the upload fails, no row ever references the missing file; if the commit fails, you get an orphaned blob — wasted space, not a broken reference.

Deletion runs the same logic in reverse: remove the row first, let a cleanup job collect the unreferenced file later. Between the two failure modes, orphans are far cheaper than dangling references, so order these operations to favor reviewable orphans. Later storage loss, deletion, and lifecycle rules still need reconciliation.

Run the reconciliation continuously. The manifest that powered the recovery should never have been a one-off. As a weekly job it costs a listing and a set-diff, and it turns silent drift into an alert while the losses are still small and still inside backup retention. A missing-file count that ticks up from zero is an early-warning system for every failure class listed above.

Media is business data. The instinct that lost these files is the belief that "the real data is in the database" and files are decoration. On a hotel management system, a maintenance photo is the evidence a dispute is settled with. An incident attachment is what the insurance claim rests on.

Rows and files are one dataset that happens to live in two places. Every part of the system, from backups to integrity checks, has to treat them that way.