A backup you have never restored is a hypothesis, not a safety net. For a small application running PostgreSQL with Docker volumes, the gap between 'we take backups' and 'we can bring the service back' is usually found at the worst possible time. This article walks through a restore drill you can run on a laptop or a spare VM, explains the decisions behind it, and is candid about what such a drill cannot prove. It is deliberately narrow: one database, one application, Docker volumes, no cloud operating model.
Start by naming what you are recovering from
A drill needs a scenario, otherwise it tests nothing in particular. For a small stack, three scenarios cover most realistic losses:
- Lost or corrupted volume. The host disk fails, the volume is deleted by mistake, or a bad migration damages data and you want the previous state.
- Lost host. The whole machine is gone and you must rebuild from backups plus your Compose configuration.
- Logical mistake. Someone ran a destructive statement against production. The database is healthy, but the data is wrong.
Your backup method determines which of these you can actually survive. The PostgreSQL documentation describes three fundamentally different approaches to backing up data: SQL dump, file system level backup, and continuous archiving, each with its own strengths and weaknesses (https://www.postgresql.org/docs/17/backup.html). The documentation stresses that the procedure is simple in principle but that you need a clear understanding of the underlying techniques and assumptions. A drill is how you check those assumptions against your own setup.
Write down, before you start, two targets in plain words: how much recent data you could tolerate losing, and how long the service could be down. The sources do not give you numbers for these, and neither will this article. They are business decisions. The drill's job is to measure what your current setup actually delivers so you can compare it against what you want.
Choose the backup method the drill will exercise
For a small application, the realistic choice is usually between a logical dump with pg_dump and a more advanced continuous archiving setup. The drill should test whichever one you really run.
Logical dumps with pg_dump. According to the pg_dump reference, it makes consistent backups even while the database is in concurrent use, and it does not block other users, readers or writers (https://www.postgresql.org/docs/17/app-pgdump.html). That makes it practical for a small live system. It has limits worth knowing:
- It dumps a single database. Roles and tablespaces are global objects shared across a cluster, and the documentation points to pg_dumpall for backing those up or for backing up an entire cluster. If your application depends on specific roles and you only dump the one database, a restore on a fresh server may fail on ownership or grants. This is one of the most common surprises a drill uncovers.
- Output comes in script or archive formats. Plain-text script dumps are restored by feeding them to psql; the archive formats must be restored with pg_restore.
- The custom format (-Fc) and directory format (-Fd) are the most flexible: they allow selecting and reordering archived items, support parallel restoration, and are compressed by default. The directory format is the only one that supports parallel dumps.
For a drill, the custom or directory format is usually the better pick because pg_restore lets you examine an archive and choose what to restore, which is handy when you only want to verify a few tables first.
Continuous archiving and point-in-time recovery. The documentation's chapter outline lists WAL archiving, base backups, recovery from a continuous archive, and timelines as part of this approach (https://www.postgresql.org/docs/17/backup.html). It is the route to recovering to a moment just before a logical mistake, but it is also more machinery to configure and, crucially, more machinery to test. If you use it, your drill must include actually performing a recovery, not just confirming that archive files are accumulating.
File system level backup. Copying a volume's files is tempting with Docker, and it is one of the three approaches the documentation names. The trade-off is that you need to be sure the copy is in a state PostgreSQL can recover from; read the file system backup section of the documentation before relying on a raw copy of a live data directory, and make your drill prove it works rather than assuming.
Build a scratch environment that cannot touch production
The most important design rule for a restore drill is isolation. You want to be certain that a typo cannot point a restore at the live database.
Practical measures:
- Use a separate Compose project name so container names, networks and volumes do not collide with production. Docker volumes are the storage mechanism the Docker documentation covers for persisting container data (https://docs.docker.com/engine/storage/volumes/); a scratch project should get its own freshly created volumes rather than reusing or mounting the production one.
- Do not publish the scratch database on the same host port as production. Better still, publish no port at all and run verification commands inside the container network.
- Use obviously different credentials and a different database hostname in the scratch configuration so a misdirected connection fails loudly.
- Run the drill from a machine or user that does not have production credentials in its environment. The pg_dump documentation notes that connection defaults can come from environment variables such as PGDATABASE, which is a reminder that ambient environment settings can silently choose your target.
If your production setup uses a specific PostgreSQL image tag, use the same tag in the drill. A restore into a different major version is a different test (an upgrade test), and mixing the two makes failures hard to interpret.
Run the drill step by step
Here is a sequence for the pg_dump path. Adapt the commands to your own Compose file; the structure matters more than the exact syntax.
1. Fetch the backup the way you would in a real incident. Do not grab it from the production host's local directory if your real plan is to retrieve it from another machine or storage location. If retrieval requires a credential or a person who is on holiday, you have found a failure mode. 2. Start an empty scratch PostgreSQL container with a new, empty volume. 3. Create the roles the application expects before restoring, or restore globals if you captured them with pg_dumpall. 4. Restore. For a plain-text dump, feed the script to psql. For custom or directory archives, use pg_restore. The pg_dump documentation describes both paths. Read the output rather than only checking the exit code: for the archive formats the documentation notes that you should examine output for warnings, and restore errors for missing objects can be easy to overlook in scrolling text. 5. Inspect the archive before trusting it. The documentation notes that non-plain-text dumps can be inspected with pg_restore's --file option, which writes out the SQL rather than executing it. This is also a safety measure: the documentation warns that restoring a dump causes the destination to execute arbitrary code chosen by the source superusers, and that partial restores do not limit that. For your own backups from your own server this is a low risk, but if a backup could have passed through untrusted hands, inspect it first. 6. Time the restore from start to finish, including retrieval and role setup. Record the number. It is your measured baseline, not an estimate. 7. Start the application against the scratch database and exercise it, rather than stopping at 'the database came up'.
Verify that the restored data is actually usable
A restore that completes without errors can still produce an unusable system. Verification should have layers.
Structural checks. Compare the list of tables, indexes and extensions with production. The pg_dump documentation includes a caution about extensions: when dumping selected extensions, there is no guarantee the result restores into a clean database on its own, because dependencies are not automatically included. Even for a full dump, confirm that any extensions your app needs are available in the image you restore into.
Content checks. Pick a few tables and compare row counts or recent record timestamps against what you expect. The point is to confirm the backup is as fresh as you believe. A nightly job that has been silently failing for weeks will look fine until you check the newest row.
Large objects and sequences. If your application stores large objects, note that the pg_dump documentation says they are included by default except when options like --schema, --table or --schema-only are specified. If someone later narrowed the dump command with a table filter, large objects could quietly drop out. Likewise, sequence values are dumped with data; check that new inserts after the restore do not collide with existing keys.
Application checks. Log in, create a record, read it back, run the migration tooling in dry-run or status mode. This catches mismatches between the restored schema and the application version in your repository.
Failure modes this kind of drill tends to expose
These are patterns to look for. They are reasoning aids, not claims about any specific incident.
- Backups that exist but cannot be retrieved because they live only on the host that failed.
- Missing globals. The database dump restores, but roles, passwords or tablespaces are absent, since pg_dump covers one database and pg_dumpall covers cluster-wide objects.
- Wrong tool for the format. Trying to feed an archive-format file to psql, or expecting pg_restore to read a plain script.
- Silent partial dumps. Options that limit the dump scope, producing a file that looks normal but omits data.
- Version drift. The scratch image differs from production, or the application code expects a newer schema than the backup contains.
- Untested recovery procedure for continuous archiving. Archive files accumulate for months, but no one has performed a recovery or understood timelines, so the first attempt happens during an outage.
- Docker volume assumptions. Believing that because data is in a named volume it is automatically backed up. A volume is persistent storage on the host, not a copy of it elsewhere.
A realistic checklist
Use this as a starting template and tailor it.
Before the drill: - Written scenario, and the data-loss and downtime you want to achieve. - Scratch Compose project with separate name, volumes, credentials and no shared ports. - Same PostgreSQL image tag as production.
During the drill: - Backup retrieved by the same route a real recovery would use. - Roles and globals handled explicitly. - Restore output read in full, warnings noted. - Total elapsed time recorded. - Structural, content and application checks completed.
After the drill: - Every surprise written down with an owner and a fix. - Backup and restore commands updated in the runbook exactly as run. - Next drill date set, and also triggered by changes such as a PostgreSQL upgrade, a new extension or a change in backup tooling.
Limitations of what a drill proves
Be honest about the boundaries. A drill on a small dataset says little about restore time for a large one; the PostgreSQL chapter includes a section on handling large databases for a reason. A scratch restore also cannot prove that your production host's disk, network or permissions will behave the same way under real stress. A passing drill shows that this backup, on this day, restored with these steps. It does not guarantee tomorrow's backup is good, which is why the content-freshness check and a recurring schedule matter more than any single run.
This article draws only on the PostgreSQL backup and pg_dump documentation and Docker's volumes documentation. It does not cover encryption of backups, retention policy, managed database services or compliance obligations, and the provided sources do not support specific claims in those areas. Treat the steps above as a framework to adapt, and validate every command against the documentation for your exact PostgreSQL and Docker versions.
Make the drill routine
The value of a restore drill compounds when it is boring. Script the scratch environment so a drill is one command, keep the runbook in the same repository as your Compose file, and rerun the drill whenever something about the data layer changes. The first drill will almost certainly find something. That is the point: you want to find it on a quiet afternoon rather than during an incident.