Database Backup and Restore Explained

Quick answer

A database backup is useful only when it can be restored into a trustworthy, usable system within the business recovery target. A production strategy starts with RPO—the maximum acceptable data loss measured in time—and RTO—the maximum acceptable time to restore service.

Use multiple recovery layers: automated snapshots or physical backups for speed, continuous transaction-log archiving for point-in-time recovery (PITR), logical exports for portability and selective recovery, and independent copies protected from the same account or operator failure. Regularly restore into an isolated target, validate data and application behavior, record the elapsed time, and fix the procedure.

Start with failure scenarios

“We take a nightly backup” is not a recovery plan. Name the events:

A table is accidentally deleted.
A bad deployment corrupts rows over twenty minutes.
The primary storage volume is lost.
Credentials are compromised and backups in the same account are deleted.
A region is unavailable.
Encryption keys or backup metadata are lost.
One tenant needs selective recovery without rewinding everyone.
A schema version is incompatible with the restored data.

Each scenario needs a recovery source, owner, target environment, validation method, and decision about lost writes. One backup format rarely optimizes all of them.

RPO and RTO

If the RPO is five minutes, the organization accepts at most five minutes of lost committed data. A backup every 24 hours cannot satisfy it. Continuous WAL/binlog archiving, synchronous replication, or another durable change stream may be required.

If the RTO is thirty minutes, downloading a multi-terabyte archive and replaying a day of logs in sequence is unlikely to qualify. Faster snapshots, pre-provisioned infrastructure, parallel restore, or a warm recovery environment may be necessary.

Targets should be service-specific and tested. A payment ledger may need near-zero RPO and a short RTO, while a rebuildable analytics table may tolerate hours. Stronger targets cost money and operational complexity, so connect them to business impact.

RPO is not automatically achieved because a dashboard says backups are enabled. Measure the newest recoverable point. RTO is not the time until the database process starts; it includes infrastructure, secrets, restore, migrations, validation, traffic cutover, and communicating degraded state.

Logical backups

Logical tools export schema definitions and rows through the database’s logical interface. PostgreSQL pg_dump creates a consistent export of one database and supports plain SQL or archive formats. pg_restore can selectively and concurrently restore archive formats.

Advantages include portability across some version changes, human-inspectable SQL, selective object restore, and usefulness for small or medium datasets. Limitations include slower dump/restore for large databases, separate handling for cluster-wide roles/settings, extension dependencies, and the risk that a dump alone misses changes after it started.

A safe non-production example:

pg_dump --format=custom --file=app_2026-07-25.dump app_production
createdb staging_restore_target
pg_restore --exit-on-error --clean --if-exists --no-owner `
  --dbname=staging_restore_target app_2026-07-25.dump

--clean drops objects in the specified destination. The literal destination here is deliberately staging_restore_target; verify connection parameters before running any restore. Do not copy a command with an ambiguous default database into a production shell.

Physical backups and snapshots

Physical backups copy database storage in an engine-consistent way. PostgreSQL base backups plus WAL can reproduce a cluster. Storage snapshots can be fast to create and restore, but crash consistency, multi-volume coordination, database flush behavior, and provider semantics matter.

Physical methods are often faster for large estates and preserve exact engine structures. They are usually tied to compatible database versions, architecture, extensions, and configuration. A raw filesystem copy of a running database is not automatically consistent.

Managed database snapshots reduce operational work, but verify retention, encryption key ownership, cross-region/account copy options, deletion protection, export limits, and restore behavior. Test using the same provider API and permissions expected during an incident.

Point-in-time recovery

PITR restores a base backup and replays an ordered stream of transaction logs until a chosen time or log position. In PostgreSQL, continuous WAL archiving provides this capability.

Suppose a destructive statement committed at 14:07:32. The team can restore a base backup into an isolated cluster and replay WAL to just before that transaction. Choosing the timestamp requires trustworthy clocks and incident evidence; a log sequence number or named restore point can be safer.

PITR depends on an unbroken log archive. Monitor archive failures and verify that every required segment can be retrieved and decrypted. Retention must cover the desired recovery window, while storage alarms must prevent unarchived WAL from filling the primary disk.

PITR rewinds the entire recovered database timeline. Writes accepted after the target point need reconciliation. For a single-tenant error, restoring to a side cluster and selectively exporting corrected rows may be safer than replacing production wholesale.

The 3-2-1 principle and isolation

A useful baseline is three copies of important data, on two types or failure domains, with one copy off-site or otherwise isolated. Cloud design adapts this idea with cross-region and cross-account copies, object-lock retention, separate credentials, and independently protected encryption keys.

Immutability reduces the blast radius of ransomware and operator mistakes. Ensure the identity that administers production cannot silently shorten retention or delete every recovery copy. Audit backup policy changes and recovery access.

Encryption protects backup contents, but losing the key is equivalent to losing the backup. Back up key metadata and access procedures, use least privilege, rotate safely, and include decryption in drills.

What a restore drill proves

A restore drill should answer:

Can automation find and authenticate to the intended backup?
Can it provision an isolated database with compatible engine settings?
Can it restore without touching production?
Are roles, extensions, sequences, jobs, and configuration present?
Do structural and business validations pass?
Can the application connect and serve critical read/write paths?
How long did every stage take?
What data lies between the recovered point and the incident cutover?

Record backup identifier, recovery point, checksums, tool versions, operator, start/end timestamps, validation results, and cleanup. A screenshot of “restore succeeded” is insufficient.

Validation after restore

Begin with mechanical checks: restore exit status, expected schemas and tables, constraint validity, extension versions, row-count ranges, and database logs.

Then verify domain invariants: account balances reconcile, every order total matches its items, required tenants exist, and referenced objects are present. Run application smoke tests against the isolated target with outbound email, payments, and webhooks disabled.

Compare a known canary record or backup-time manifest. For large tables, use sampled or partition-level checksums where a full comparison is too slow. Validate that new writes, background jobs, and migrations work on the recovered schema.

Finally, rehearse traffic cutover and rollback. Connection strings, TLS certificates, DNS, secrets, firewall rules, pool recycling, and read replicas are part of RTO.

PostgreSQL operating pattern

A robust PostgreSQL approach may combine:

Provider or pg_basebackup physical base backups.
Continuous WAL archive to versioned object storage.
Daily logical pg_dump for selected critical schemas.
Cross-account copies and retention lock.
Automated restore into staging_restore_target.
pg_restore --list review and application smoke tests.

pg_dump does not include every cluster-global object; pg_dumpall --globals-only or infrastructure-as-code may capture roles and tablespaces. Secrets should not be embedded in scripts or logs.

For PITR, follow the current PostgreSQL recovery configuration for the deployed major version. Old tutorials may refer to obsolete recovery files and parameters. Test version upgrades alongside recovery because physical backup compatibility is intentionally limited.

Backups during schema changes

A backup is not the first rollback mechanism for an ordinary deployment. Restoring the whole database to undo a migration can discard unrelated writes.

Prefer backward-compatible expand-and-contract migrations, feature flags, and forward fixes. Before a high-risk change, confirm a recent recoverable point, record a restore point if supported, and estimate replay and reconciliation work. Keep old application versions compatible long enough to roll application code back without rewinding data.

When a destructive migration is unavoidable, archive the affected data separately, verify the archive, and define a selective repair procedure. Test it against production-scale data.

Common mistakes

The first mistake is treating successful backup jobs as proof of recovery. Only restores prove the recovery path.

The second is keeping every copy in one account with one administrator and one encryption key.

The third is confusing replicas with backups. Replication copies harmful writes quickly.

The fourth is defining RTO only as database restore time while ignoring application cutover and validation.

The fifth is restoring over an existing target without verifying the connection. Always use an explicit isolated destination and destructive-command guardrails.

The sixth is never testing old backups. Retention is valuable only if the old formats, keys, and logs remain usable.

Practical checklist

Define scenario-specific RPO and RTO with business owners.
Inventory databases, object stores, queues, secrets, and external dependencies.
Combine fast physical recovery, PITR logs, and useful logical exports.
Store recovery copies across failure and credential boundaries.
Protect retention and encryption keys from production compromise.
Monitor backup age, archive continuity, failures, size, and restore duration.
Use explicit isolated restore targets such as staging_restore_target.
Disable outbound side effects during application validation.
Check schema, constraints, row ranges, and business invariants.
Measure the full recovery timeline through traffic readiness.
Document writes that may require reconciliation.
Run scheduled drills and incident-style surprise exercises.
Review procedures after engine, schema, provider, or ownership changes.

Frequently asked questions

How often should I test restores?

Match frequency to risk and change rate. Critical systems commonly automate frequent restore checks and run broader human-led disaster exercises several times per year.

Is `pg_dump` enough?

It can be sufficient for smaller, less demanding systems, but low RPO or large databases usually need physical backups and continuous WAL archiving too.

What is the difference between a snapshot and a backup?

A snapshot is one backup mechanism, often storage/provider-specific. A recovery strategy includes independent retention, logs, permissions, validation, and procedures around that mechanism.

Can I restore one table with PITR?

PITR normally recovers a database cluster to a point. Restore into an isolated side cluster, then carefully export and reconcile the required table or rows.

Should backups be encrypted?

Yes, in transit and at rest, with tested access to the keys. Encryption without recoverable key management can make data permanently unavailable.

Sources and further reading

PostgreSQL, Backup and Restore.
PostgreSQL, pg_dump.
PostgreSQL, Continuous Archiving and Point-in-Time Recovery.
NIST, Contingency Planning Guide for Federal Information Systems.

Read Database Migration Rollback Explained for deployment recovery, Database Transactions Explained for atomic changes, and Database Read Replicas Explained to understand why replication is not a backup. Database Sharding Explained covers the extra recovery inventory created by multiple shards.