Database backups: RPO, RTO and restoration testing

A successful backup is not a file sitting in a bucket. It is a database restored into a clean environment, opened by the application and returned to service within the promised time. Until that scenario has been run, a team knows the date and size of a copy, not whether recovery works.

That distinction is critical for databases. Files change during a copy, log continuity matters, roles and extensions matter as much as tables, and faithful replication can instantly propagate a deletion. A credible strategy connects business risk, backup method and restoration evidence.

Choose the right protection at a glance

These mechanisms complement each other. A replica reduces interruption after hardware failure; it is not a historical copy. A dump helps recover one table; it may not replace a PITR chain for a large database.

Start with RPO and RTO

The recovery point objective, or RPO, is the maximum amount of data the business accepts losing. A fifteen-minute RPO requires transactions to be captured at least that frequently, often through log archiving rather than a complete dump every fifteen minutes.

The recovery time objective, or RTO, is the maximum time before the service is usable. It includes downloading, decrypting, restoring, checking, starting the application and deciding to reopen. Timing only pg_restore or mysql understates the actual recovery time.

One target for every database either wastes money or underprotects critical data. Business owners validate tolerable loss and downtime; the technical team then chooses an architecture capable of meeting them.

Back up everything that makes data usable

A database is more than business rows. The recovery inventory should cover:

• schemas, constraints, indexes, sequences and views;
• roles, privileges, owners and security policies;
• extensions and their versions;
• functions, procedures, events and triggers;
• essential engine settings;
• decryption secrets, stored separately from backups;
• external files referenced by the database;
• the engine image version and application migrations.

PostgreSQL, for example, separates databases from cluster-wide objects such as some roles and tablespaces. MySQL requires explicit options to include routines and events in mysqldump. A test that only checks the number of tables can therefore pass while the application remains unusable.

Logical dump or physical backup

A logical dump rebuilds the database using statements and data understood by the engine. It fits moderate volumes, selective restoration and compatible migrations. Its cost appears during recovery: recreating indexes, constraints and large datasets can take much longer than producing the file.

A physical backup copies the complete internal representation. It becomes useful when volume or RTO makes a dump too slow. In return, the runbook must document compatible versions, tablespaces, storage and the engine-specific procedure.

Do not decide from intuition. Time both restoration methods with production-like volume and real network throughput. A fast backup that cannot be downloaded within the RTO does not meet its objective.

Why copying a live Docker volume is dangerous

Docker documents how to archive and restore volume contents. That recipe suits ordinary files, but does not by itself create a consistent backup of a database receiving writes.

PostgreSQL states that a simple copy of its data directory requires the server to be shut down, unless a consistent snapshot or a supported physical-backup protocol is used. A tar, docker cp or copy of a mounted directory can read files at different moments and omit required logs.

For a containerised database, use the engine tool first. If the strategy relies on snapshots, coordinate them with the database and every relevant volume. Also document volume restoration, the exact engine image and file permissions.

PostgreSQL example with Docker Compose

For a small or medium database, a custom-format dump supports inspection and selective restoration:

mkdir -p backups
docker compose exec -T postgres \
  pg_dump -U app --format=custom --no-owner --file=- app \
  > "backups/app-$(date +%F-%H%M).dump"

Adapt postgres, app and authentication to your Compose project. Protect the file as soon as it is created and ensure the local redirection has enough free space. Always restore the test into a separate database:

docker compose exec -T postgres createdb -U app -T template0 app_restore
docker compose exec -T postgres \
  pg_restore -U app --dbname=app_restore --no-owner --exit-on-error \
  < backups/app-2026-08-01-1435.dump

--exit-on-error prevents a test being called successful after ignored errors. For roles and other global objects, add a pg_dumpall --globals-only backup and test it with appropriate privileges.

For a large database or a short RPO, use a supported base backup, such as one produced by pg_basebackup, and continuously archive WAL. PostgreSQL can then replay changes and stop just before an accidental deletion. The chain requires every segment from the base backup onwards: one gap can remove the expected recovery point.

MySQL example with Docker Compose

For transactional tables, --single-transaction generally produces a consistent dump without a prolonged global lock:

mkdir -p backups
docker compose exec -T mysql \
  mysqldump -u app --single-transaction \
  --routines --triggers --events app \
  > "backups/app-$(date +%F-%H%M).sql"

Do not put the password directly in shell history. Use the secret or authentication mechanism already injected into the container. Avoid schema changes while the dump runs: transactional consistency does not make DDL harmless.

docker compose exec -T mysql mysql -u app -e 'CREATE DATABASE app_restore'
docker compose exec -T mysql mysql -u app app_restore \
  < backups/app-2026-08-01-1435.sql

For PITR, retain one consistent full backup and the binary logs produced afterwards. Recovery loads the backup and replays events to a chosen position or time. Verify log continuity, retention and timestamps before relying on it during an incident.

Make copies resistant to compromise

A backup accessible through the same administrator account as production can disappear in the same attack. CISA recommends offline, encrypted and regularly tested copies, as well as immutable storage where appropriate.

1. Separate production and backup identities.
2. Give the writer minimum privileges, without permission to delete old versions.
3. Keep several generations under an explicit retention policy.
4. Replicate at least one copy into another failure domain.
5. Encrypt data and preserve the key through a separate channel.
6. Test access to emergency accounts, keys and procedures too.

Immutability protects against deletion, but does not detect an empty dump or already corrupted data. It complements checks and restoration; it does not replace them.

Run a real restoration exercise

A useful exercise starts from a scenario, not whichever file is newest. For example: "a migration deleted orders at 10:18; return to the last healthy state before that transaction and reopen within 60 minutes."

1. Start the clock when the alert is raised.
2. Select the recovery point without changing production.
3. Retrieve, verify and decrypt the copy.
4. Create isolated infrastructure with a compatible engine version.
5. Restore data, global objects, extensions and related files.
6. Start the application with external services disabled.
7. Check permissions, constraints, volumes, timestamps and business transactions.
8. Calculate the RPO and RTO actually achieved.
9. Safely destroy the test environment after retaining evidence.

Checks should include a connection using the application account, business queries, writing and reading a test record, asynchronous jobs and objects stored outside the database. Compare known aggregates too: daily order count, total balance, latest valid transaction or document count.

Monitor recoverability, not just the job

A green dashboard because a file was uploaded every night creates false confidence. At minimum, track:

• age of the last successful full backup;
• age of the last validated restoration;
• duration and size against their trends;
• WAL or binary-log continuity and lag;
• availability of the remote copy;
• encryption, integrity-check or immutability failures;
• observed RPO and RTO from the latest exercise.

Alert on quiet anomalies: size dropping by a factor of ten, a log that stopped advancing, shorter-than-planned retention or a postponed test. Our data pipeline observability guide provides a complementary method for ownership, freshness and actionable alerts.

The minimum runbook

The recovery document must remain available when the primary information system is not. It identifies the decision maker, operator, emergency access, system order, selected recovery point, validated commands and reopening criteria.

Add a rollback procedure in case the restored database fails validation. During a security incident, do not automatically reconnect the old environment: restoring data and removing the compromise are connected but separate workstreams. Our ransomware and backup guide covers the broader strategy, while the small-business cybersecurity guide puts recovery alongside other priorities.

A realistic thirty-day plan

The first restoration may fail because of a missing extension, forgotten secret or slow download. That is precisely the exercise's value: finding dependencies during a chosen window rather than during an incident.

A backup becomes a recovery capability when the team knows what to restore, to which point, within what time, with which access and under which checks. The useful indicator is not "backup completed", but "service restored and evidence validated".