Field report

Testing database restores: what our restore drill measures and what it does not

Restore rehearsal on 30 Aug 2026: we restored both of Fortlauf's production databases (product data and sign-in) to coordinated points in time, in a separate test environment. Both were back in service 3 min 22 s after the restore configuration was applied. Not included: the time to decide and prepare, and files kept outside the databases.

This article shows how we set up the drill, where the timer started and stopped, what we checked besides the time, which mistakes the drill found in our own runbook, and what the number does not tell you. It ends with a checklist with commands for PostgreSQL on CloudNativePG.

1. A green tick is not a restore

A backup tool reports that a backup was written. It does not report whether you can get a running database out of it, how long that takes, or whether the application can work with the data afterwards. Only a restore that someone carries out and times with a clock shows that.

A restore drill answers three questions no status field answers:

  • How long does it take from starting the restore until the database is back in service?
  • After the restore, is everything there that the application needs: data, schema, permissions?
  • Does the runbook you would work from in an emergency work as written?

The third question is often forgotten. For us it produced most of the findings (section 5).

2. What we rehearsed

Two databases that belong together. Fortlauf has one database for the product data and one for sign-in. We restored both, because one on its own does not show whether people can sign in afterwards and see their data.

A separate test environment. The restore ran in its own environment next to production. Production carried on unchanged in the meantime. After the drill we deleted the test environment completely.

A target point in time, offset on purpose. Strictly speaking, we restored the sign-in database to a point 15 minutes after that of the product database. The reason: there is no shared transaction between the two databases. An account is created in the sign-in service first, and the matching entry in the product database only at the first sign-in. If the sign-in data is newer than the product data, at most one entry is missing, and the next sign-in creates it again. In the opposite case there would be entries that no longer match any account, and those people could not sign in. The offset makes sure only the harmless case can happen.

A target for “done”. The drill only counted as passed once a person had signed in to the restored application and retrieved their own records. The application connected with the same database role as in production, without superuser rights. That way the row-level security that separates the tenants in Fortlauf was in force during the drill too.

No write access to the production backups. A test environment that reads from the same backups must not write back into them. The restored cluster therefore had no backup and no archiving configuration. We did not infer that from the template; we read it on the generated object in the cluster. After the drill, the time of the last successful backup of both production databases was unchanged.

3. How we measured

Where the timer starts and stops. This definition decides what the number means:

Measurement Start End
Back in service the restore configuration is applied in the cluster the cluster reports a healthy state
Not in the measurement the decision to restore; finding the runbook; writing the configuration

What we saw, counted from applying the configuration:

After Event
0 restore configuration applied for both databases
3 min 01 s sign-in database in service
3 min 22 s product database in service; so both
Repeat run, later the same day both in service after about 3 min 30 s

The most recent base backup of both databases was from the same day. So hours of write-ahead log were replayed, not days.

The preparation took longer than the restore. Deciding, finding the runbook and writing the two configurations took longer on 30 Aug 2026 than the measured 3 min 22 s. On top of that came a first attempt that failed (section 5). The measured time only starts with the second, successful attempt. If you plan for an emergency, plan with the total time, not the measured one.

4. What we checked besides the time

“In service” only means the database is running. Whether it has the right data is shown by these checks:

  • Data. The most recent records match the target point in time, and the row counts of the important tables match production at that point.
  • Schema. The database migration state is the same as in production.
  • Permissions. The row-level security policies are all there, and they are enabled on the tables. This check is the most important one: a restore that brings back every row but not every policy looks flawless in a row count and shows every tenant the data of the others.
  • The two databases together. For every active entry in the product database there is an account in the sign-in service.
  • A person signs in. Signing in with the application’s role completes what the queries before it cannot prove.

5. What went wrong and what we changed

The drill found four mistakes, all in our own runbooks. None was down to the backup. We corrected all four in the runbooks the same day.

  • The first attempt found “no backup”. The restore configuration was missing the name under which the source cluster stores its backups (serverName). Without it, CloudNativePG looked under the name of the restoring cluster, found nothing there and reported no target backup found. The backup was there in full. In an emergency, exactly this message reads as “the backup is missing”, at the moment when nobody is judging calmly any more. We now always set the name explicitly.
  • A check checked nothing. The runbook was meant to prove that the test environment had no backup configuration, and queried cluster for it. Another tool in the cluster brings its own resource with the same name. The query ran against the wrong resource, returned nothing, and “nothing” looked like “passed”. We now write the full resource name (clusters.postgresql.cnpg.io) and additionally query a value that must not be empty. If it stays empty, the line checks nothing.
  • A comparison reported an account as orphaned that did not exist. The database sorted the list according to the rules of the locale setting; the comparison tool comm expects byte order. This was only noticed because the short list was visibly unsorted. We now compare sets, independent of the sort order. And we compare on the key that actually connects the databases, not on the e-mail address: two entries with the same address can pass the comparison even though one of them matches no account.
  • A runbook named a database that does not exist. An older runbook for taking a backup before a way back named the wrong database. It would only have failed in an emergency.

None of these mistakes would have come to light without a drill. Each would have cost time in an emergency, and two would have given false confidence.

6. What the number does not cover

  • Decision and preparation. See section 3: on the day of the drill they took longer than the restore.
  • Files outside the databases. Documents and other files that an application stores outside the database were not part of this drill. Restoring them needs a drill of its own.
  • Your database size. The duration depends on the size of the database and on how much write-ahead log has to be replayed since the last base backup. The further the target point lies behind the last base backup, the longer it takes. Do not transfer our 3 min 22 s to your database; measure it.
  • Possible data loss. This article gives no figure for it. How much data is missing in an emergency depends on how much is being written at the time of the outage and how often WAL files are archived. In a drill, that can only be measured meaningfully under write load. For planning, what counts is the archiving interval (in PostgreSQL archive_timeout) plus the time for the transfer.
  • Switching over to a replica. A restore from backup does not prove that a failover works, and vice versa. Our planned failover on 4 Oct 2026 is described in its own article: CloudNativePG failover drill.
  • Stress. The drill was planned and calm. A real outage comes unannounced, often with several problems at once.

7. When you should rehearse

  • Before you commit to a restore time, for example in a security questionnaire or a contract. A measured time with a date is a statement; an estimated one is a hope.
  • After every change to backup or archiving: a new backup target, a new operator, a new major version of PostgreSQL.
  • After a platform change. If you are moving from VMware to Proxmox VE, rehearse the first restore during the pilot, not after the last wave.
  • Regularly. A capability that was proven once and never checked again is an assumption again after a few changes. With a corrected runbook, every further drill is shorter than the first.

8. Checklist for your own drill

Before:

  • Target defined: which databases, which target point in time, what “done” means (at least: a sign-in with the application’s role).
  • With several databases that belong together: the direction of the offset chosen deliberately.
  • Separate test environment with its own namespace; no backup and no archiving configuration in it.
  • Name of the source cluster set explicitly in the configuration (serverName).
  • Time of the last successful production backup noted.
  • Start and end point of the measurement written down, start time in UTC in a log.
# Test environment: restore of my-cluster to a point in time,
# in its own namespace "probe", without backup and without WAL archiving.
apiVersion: postgresql.cnpg.io/v1
kind: Cluster
metadata:
  name: my-cluster-probe
  namespace: probe
spec:
  instances: 1
  storage:
    size: 20Gi
  bootstrap:
    recovery:
      source: origin
      recoveryTarget:
        targetTime: '2026-01-15T10:30:00Z' # always with a time zone
  externalClusters:
    - name: origin
      plugin:
        name: barman-cloud.cloudnative-pg.io
        parameters:
          barmanObjectName: my-cluster-store # ObjectStore resource in the namespace "probe"
          serverName: my-cluster # name of the source cluster; never leave it out
  # Deliberately no spec.backup and no spec.plugins:
  # the test environment does not write into the production backups.

During:

  • Apply the configuration and start the clock; log the status every few seconds with a timestamp.
kubectl apply -f my-cluster-probe.yaml

while true; do
  printf '%s ' "$(date -u +%T)"
  kubectl -n probe get clusters.postgresql.cnpg.io my-cluster-probe \
    -o jsonpath='{.status.phase} | ready={.status.readyInstances}/{.status.instances}{"\n"}'
  sleep 2
done | tee restore-run.log
  • Check on the generated object that it cannot write, with a positive control:
# Must be empty: no backup, no archiving in the test environment
kubectl -n probe get clusters.postgresql.cnpg.io my-cluster-probe \
  -o jsonpath='{.spec.backup}{.spec.plugins}{"\n"}'

# Positive control: must show the target point in time. If it stays empty,
# the query above read the wrong object and proves nothing.
kubectl -n probe get clusters.postgresql.cnpg.io my-cluster-probe \
  -o jsonpath='{.spec.bootstrap.recovery.recoveryTarget.targetTime}{"\n"}'

After:

  • Time to “in service” noted with start and end point; preparation time noted separately.
  • Most recent records, row counts of the important tables and migration state compared with production.
  • Row-level security policies counted and compared with production; enabled on the tables.
# Number of policies: must match production
kubectl cnpg psql my-cluster-probe -n probe -- -d app -At \
  -c "SELECT count(*) FROM pg_policies;"

# Tables with row-level security enabled
kubectl cnpg psql my-cluster-probe -n probe -- -d app -At \
  -c "SELECT relname FROM pg_class WHERE relkind = 'r' AND relrowsecurity ORDER BY 1;"
  • Databases that belong together compared on the real connecting key, as a set comparison:
# Sort both lists yourself in byte order, then compare
LC_ALL=C sort -u product-keys.txt > a.txt
LC_ALL=C sort -u signin-keys.txt  > b.txt
comm -23 a.txt b.txt   # in the product database, but without an account
  • A person signed in and saw their own data; the application used the production role, not a superuser.
  • Production unchanged: every instance ready, archiving active, last successful backup as noted before.
kubectl -n app get clusters.postgresql.cnpg.io my-cluster \
  -o jsonpath='{.status.phase} | ready={.status.readyInstances}/{.status.instances}{"\n"}'
# Last backup from the Backup objects: .status.lastSuccessfulBackup is not set for plugin backups
kubectl -n app get backups.postgresql.cnpg.io --sort-by=.status.stoppedAt \
  -o custom-columns=NAME:.metadata.name,CLUSTER:.spec.cluster.name,PHASE:.status.phase,STOPPED:.status.stoppedAt
kubectl -n app get clusters.postgresql.cnpg.io my-cluster \
  -o jsonpath='{.status.conditions[?(@.type=="ContinuousArchiving")].status}{"\n"}'
  • Test environment deleted, no volumes left; local exports with personal data (such as lists from the comparison) deleted.
kubectl delete namespace probe
kubectl get pvc -A -l cnpg.io/cluster=my-cluster-probe   # must be empty
rm -f a.txt b.txt product-keys.txt signin-keys.txt
  • Every runbook mistake corrected in the runbook the same day, not just noted in the log.

Where this fits

We build and run Kubernetes platforms with PostgreSQL on CloudNativePG, and we measure the restore before the handover: Kubernetes platforms, built and run. Where the data behind this drill lives, and our other dated measurements: Where your data lives.

German version: Datenbank-Wiederherstellung testen: was unsere Restore-Probe misst und was nicht.

Sources

Read on 12 Oct 2026.

  • CloudNativePG documentation: Recovery. Restore from an object store via the Barman Cloud plugin, recoveryTarget.targetTime with a time zone.

Want your restore measured?

The Disaster Recovery Drill covers a real restore or a planned failover of one agreed system, together with your team, every step timed. The result: a report with measured times, gaps and prioritised actions.