A successful backup job tells you that a job completed. It does not tell you how long recovery takes, whether application dependencies are available, or whether the restored data is usable.
Treat recovery as a process that can be rehearsed.
Agree on recovery expectations
Define the acceptable amount of lost data and the time available to restore service. These expectations shape the backup method, frequency, storage, and recovery procedure.
A logical dump is useful for many workflows, but it is not a complete substitute for every recovery strategy. Point-in-time recovery, cluster-wide roles, extensions, and the surrounding application environment need separate consideration.
Choose the exercise you are testing
PostgreSQL’s backup documentation describes SQL dumps, file-system backups, and continuous archiving as different approaches. The exercise below tests a logical dump of one database. It does not demonstrate point-in-time recovery or recovery of an entire deployment.
Write down the incident the rehearsal represents. Recovering an accidentally dropped table, replacing a failed database server, and rebuilding after a lost hosting account can require different resources and procedures. Identify where the backup, access credentials, and recovery instructions will be available during that incident.
Restore into an isolated environment
The following commands show a logical PostgreSQL backup and restore. Use an authorized source database and a disposable destination. The connection values are examples; configure credentials securely through your environment or a password file, rather than putting passwords into command history.
# Create a custom-format archive from an authorized source.
pg_dump --format=custom --file=backup.dump \
--dbname="host=SOURCE_HOST dbname=SOURCE_DB user=BACKUP_USER"
# Inspect the archive contents.
pg_restore --list backup.dump
# Create a separate, disposable database on an isolated test server.
createdb --host=TEST_HOST --username=TEST_USER recovery_test
# Restore into that test database; stop if an error occurs.
pg_restore --exit-on-error --no-owner --no-privileges \
--host=TEST_HOST --username=TEST_USER \
--dbname=recovery_test backup.dumpConfirm that TEST_HOST is the isolated destination before running the restore. These commands illustrate the PostgreSQL 18 tools. Required roles, extensions, and client/server version compatibility must be prepared for your environment. The commands have not been executed against a live database for this article. The example deliberately skips ownership and privilege restoration; it therefore does not test whether production permissions can be recovered.
Check more than row counts
A recovery exercise should verify:
- Important tables, constraints, and indexes are present.
- Representative application reads and writes behave as expected.
- Required extensions and dependent services are available.
- Permissions can be restored through an explicitly tested procedure.
- The restore and validation fit the agreed recovery window.
Measure the whole exercise, including retrieving the backup and preparing the destination. A fast import is not the same thing as a fast return to service.
Record the procedure and repeat it
Document commands, assumptions, access requirements, and failure handling. Record when the exercise ran, which backup was used, and what remained untested.
Repeat the exercise after material changes to schema, database versions, storage, or deployment. Recovery instructions are useful only while they still match the system.