PostgreSQL Backups: Create an Archive and Prove You Can Restore It

A backup job can finish successfully while your recovery plan is still incomplete. The useful test is whether you can restore the archive into a separate database and perform the application operations that matter. This walkthrough uses a PostgreSQL custom-format dump and a deliberately separate test database.

Scope: a small PostgreSQL deployment on a Linux host where you have authorized access to the postgres operating-system account and local database authentication is configured. Replace appdb with your database name. Managed services, containers, and remote authentication need adjusted connection options. Never point the restore command at production.

1. Define what recovery must achieve

Write down two targets: how much data you can lose and how long an outage can last. A nightly archive may leave almost a day of changes outside the backup. If that exceeds your tolerance, plan a more frequent schedule or continuous recovery strategy. Also inventory uploaded files, application configuration, secrets, extensions, and database roles; recovering a database alone may not recover your service.

2. Create a protected custom-format archive

sudo install -d -m 700 -o postgres -g postgres /var/backups/postgresql
sudo -u postgres pg_dump --version
sudo -u postgres psql -d appdb -c 'SHOW server_version;'
backup_file="/var/backups/postgresql/appdb-$(date -u +%Y%m%dT%H%M%SZ).dump"
sudo -u postgres pg_dump --format=custom --file="$backup_file" appdb

Check the command exits successfully before treating the archive as complete. Use a pg_dump client compatible with the server; a client cannot dump a newer server major version than its own. The custom format works with pg_restore and includes compression. A dump provides a consistent view of one database while it remains in use, but it does not synchronize an external uploads directory. Coordinate file and database backups when your application requires that. See pg_dump.

The directory above is restricted to the database service account. Keep the same shell open for the following commands, which reuse $backup_file. Avoid putting database passwords in command arguments or public scripts.

3. Inspect the archive and record a checksum

sudo -u postgres pg_restore --list "$backup_file" | head -n 25
sudo sha256sum "$backup_file"

A readable table of contents is a useful format check, but not proof that every table can be restored. Record the checksum alongside the archive and compare it after copying to a separate, encrypted backup destination. A checksum detects changed bytes; it does not prove the source data was correct.

4. Restore into a new database

The following test database must not already exist. Run each command only after the previous one succeeds. For valuable or large workloads, use a separate restore host to avoid adding disk and CPU pressure to production. Install required extensions there first.

sudo -u postgres createdb --template=template0 appdb_restore_check
sudo -u postgres pg_restore --exit-on-error --no-owner --no-privileges \
  --dbname=appdb_restore_check "$backup_file"
sudo -u postgres psql -d appdb_restore_check -c "ANALYZE;"
sudo -u postgres psql -d appdb_restore_check -c "\dt"

This drill intentionally omits original ownership and grants. It checks schema and data recovery, not production permissions. --exit-on-error stops on the first restore error; a failed restore can leave a partially populated test database. Investigate the error and recreate only the disposable test target before trying again. See pg_restore.

5. Validate business data, not just table names

Choose several checks relevant to your application: a known customer record, a recent completed order, an attachment reference, and a representative report. Compare values to what was expected at backup time; a live database may have changed since then. Start an isolated application instance against the restored database with email, payment, webhooks, and scheduled jobs disabled. Confirm it can read the expected records without sending real notifications.

Record archive creation time, archive size, copy location, restore duration, and test results. If the recovery time exceeds your outage budget, find out now rather than during an incident.

6. Turn the drill into an operating routine

Schedule backups with failure alerts, protect the backup account separately from the production account, and retain multiple restore points. A single replaced archive gives you little protection against unnoticed corruption. Repeat a restore after major upgrades and on a regular schedule. PostgreSQL roles and other cluster-wide objects require separate handling, while point-in-time recovery needs a physical backup and WAL strategy; review the official backup and restore overview before designing that workflow.

Once the exercise is recorded, remove the disposable database only after verifying its name and confirming no test client is using it. Keep the verified archive according to your retention policy. If you are planning a larger deployment, our VPS versus dedicated server guide can help you decide where the recovery environment should live.