Your Backups Are a Hypothesis: Restore-Testing CloudNativePG and Measuring Real RPO

A backup you've never restored is a theory. Here's how we tested ours: the method, the numbers, and the one setting that could have quietly overwritten our entire archive.

In my previous post I wrote about moving eleven microservice databases off Google Cloud SQL and onto a single shared CloudNativePG cluster. Near the end, one paragraph covered the restore test we ran the day backups were switched on.

That paragraph deserved its own article. The restore test turned out to be the most useful thing we did in the whole migration. It didn't just tell us that backups worked. It told us how much data we would actually lose on a bad day, and that number was not the one anybody would have guessed from our backup schedule.

Most teams have backups they've never restored

If you ask a team "do you have backups?", the answer is almost always yes. Ask "when did you last restore one?" and the room goes quiet.

That's not laziness. It's how the incentives line up:

• Configuring backups gives you a green checkmark. There's a dashboard, a cron schedule, a bucket filling up. It looks finished.
• Restoring is awkward. It needs spare capacity, a place to put the copy, and someone to decide what "it worked" means. None of that is on anyone's sprint board.
• Nothing forces the question. Until the day you need it, a broken backup and a working one look exactly the same.

And backups break in quiet ways: credentials get rotated, a bucket lifecycle rule deletes WAL the backup tool still needs, the archive command fails while the database runs happily, or the backup contains everything except the one database somebody added last month.

On a managed service like Cloud SQL, the provider absorbs a lot of this for you. When you bring Postgres onto Kubernetes, you become the provider. So we made a rule for ourselves: backups don't count as "enabled" until a restore has passed.

How CloudNativePG recovery actually works

The first thing to understand is that CNPG never restores over a running cluster. Recovery is always a bootstrap method for a brand-new Cluster. You don't "roll back" postgres. You create postgres-restore (or whatever you call it) and tell it to start life from someone else's backups.

This is a feature, not a limitation:

• The original keeps running. A restore test needs no downtime and doesn't touch production.
• A bad restore can't make things worse. If you pick the wrong timestamp, you delete the new cluster and try again.
• Cutover is a separate, explicit step. When you do restore for real, you check the new cluster's data first, then point apps at its -rw Service.

Under the hood, CNPG pulls the most suitable base backup from object storage, then replays archived WAL on top of it until it reaches the end of the archive (or the point in time you asked for). Then it promotes the new cluster and starts serving.

The manifest has two parts: a bootstrap.recovery section that says "start from a backup", and an externalClusters entry that says where that backup lives.

apiVersion: postgresql.cnpg.io/v1
kind: Cluster
metadata:
  name: restore-test
  namespace: service-db
spec:
  instances: 1
  imageName: ghcr.io/cloudnative-pg/postgresql:17.6   # same as the original

  storage:
    size: 10Gi
    storageClass: <your-storage-class>

  bootstrap:
    recovery:
      source: origin
      # recoveryTarget:
      #   targetTime: "2026-10-01 09:30:00+00"    # optional point-in-time

  externalClusters:
    - name: origin
      barmanObjectStore:
        serverName: postgres                       # the ORIGINAL cluster's name
        destinationPath: s3://<bucket>/<prefix>/
        endpointURL: https://<s3-endpoint>
        s3Credentials:
          accessKeyId:     {name: backup-creds, key: ACCESS_KEY_ID}
          secretAccessKey: {name: backup-creds, key: ACCESS_SECRET_KEY}
        wal:
          maxParallel: 8

  # Deliberately NO spec.backup section. See "The serverName trap" below.

A few details worth calling out:

• serverName is the original cluster's name. CNPG stores each cluster's backups under <destinationPath>/<serverName>/. The restore needs to read from the original's folder, so this must match the original, not the new cluster.
• The data volume can be much smaller than production's. Our production volume is 200 Gi, but the data inside is about 1.3 GB. A 10 Gi volume was plenty for the test.
• Pin the same image. Restoring into a different major version won't work, and a different minor version adds a variable you don't need in a test.

Apply it and watch:

kubectl -n service-db apply -f restore-test.yaml
kubectl -n service-db get cluster restore-test -w
kubectl -n service-db logs -f restore-test-1-full-recovery-<hash>   # the recovery job

Note on versions: CNPG's built-in Barman Cloud support (barmanObjectStore) is deprecated in favour of the Barman Cloud Plugin. The recovery concepts in this post are the same either way; only the configuration shape changes. If you're on the plugin, the externalClusters entry points at the plugin and an ObjectStore resource instead.

The test, and what "it worked" means

Our restore took about three minutes for roughly 1.3 GB across eleven databases. The new cluster reported healthy, and \l+ listed all eleven databases.

That's where most restore tests stop. "It came up and the databases are there" is a much weaker claim than it sounds. A restore can come up healthy and still be missing data, still be hours stale, or still have tables silently truncated. So we defined "passed" more strictly, and checked one database (survey_db, 23 tables) thoroughly against production:

1. Same set of tables.
2. Same row count for every table.
3. Same migration version (the alembic_version row).
4. Same database size, roughly.
5. Same row contents, checked by checksum.

The first four are quick. The fifth is the one that actually proves the data is right.

The checksum comparison

Row counts can match while contents differ: an UPDATE changes data without changing the count. So for each table we computed a checksum over every row, in a fixed order, on both sides:

-- run the same query against production and the restored cluster
select md5(string_agg(t::text, '|' order by t::text)) as checksum,
       count(*)                                       as row_count
from   public.<table_name> t;

Casting the whole row to text (t::text) catches a change in any column, and the order by makes the result independent of physical row order, which will differ between the two clusters. For large tables, order by the primary key instead; it's much cheaper than sorting on the full row text.

To run it for every table without writing 23 queries, generate them from the catalog:

select format(
  'select %L as tbl, count(*) as rows, md5(string_agg(t::text, ''|'' order by t::text)) as checksum from %I.%I t;',
  table_name, table_schema, table_name)
from information_schema.tables
where table_schema = 'public' and table_type = 'BASE TABLE'
order by table_name;

Save the output, run it with psql -At -f checks.sql against each side, and diff the two files. Every line that differs is a table to look at.

What we found: 20 matched, 3 didn't

All 23 tables had identical row counts, the migration version matched, and the sizes matched. Twenty of the 23 checksums matched exactly. Three did not.

That's the moment where a restore test either earns its keep or gets hand-waved. "Close enough" is the wrong answer. So we looked at the three tables.

All three were tables the application writes to continuously. In each one, the rows that differed had been changed after the newest change in the restored copy. Production had simply kept moving after the point the restore stopped at. Every row older than that point matched.

That's the expected result, and it's exactly the signal you want: the backup is consistent up to a point in time, and the only differences are writes that happened after it. If a difference had been in an old row, or a table that nobody writes to, that would have been a real problem.

Which raises the obvious question: how far after?

Measuring your real RPO

RPO, the recovery point objective, is how much data you can afford to lose: the gap between the last change you can recover and the moment disaster strikes.

Teams usually quote their backup schedule as their RPO. "We back up daily" sounds like "we lose at most a day". With continuous WAL archiving, people go the other way and say "we can restore to any point in time", which sounds like zero.

Neither is your real RPO. Your real RPO is the gap between the newest change you can actually restore and the newest change that existed when you lost the primary. You can measure it directly.

Step 1: find the newest change in the restored cluster

Postgres records the last transaction it replayed during recovery, and after promotion that value stays frozen at the end of recovery:

-- on the restored cluster
select pg_last_xact_replay_timestamp();

The recovery log says the same thing in plain words. Look for a line like last completed transaction was at log time ...:

kubectl -n service-db logs restore-test-1-full-recovery-<hash> | grep -i "last completed transaction"

You can cross-check from the data itself if your tables carry timestamps, e.g. select max(updated_at) from <busy_table>.

Step 2: compare with when you started the restore

For our test:

• Newest change in the restored copy: 13:43:52 UTC
• Restore finished (and production was still writing): 13:48 UTC

Production was busy the whole time, so the roughly four minutes of writes between those two timestamps were exactly the changes behind the three mismatched checksums. Had the primary died at 13:48, those writes would have been gone.

Step 3: understand where the number comes from

That gap isn't random. Postgres archives WAL one segment (16 MB by default) at a time, and only a finished segment gets uploaded. Under a light write load, a segment can take a long time to fill. To stop it sitting there forever, Postgres has archive_timeout, which forces a switch to a new segment after a set time. CNPG sets it to 5 minutes by default.

So with continuous archiving and no replicas, the worst case is roughly:

RPO ≈ archive_timeout + upload time ≈ 5 minutes

Our measured four-ish minutes sits right inside that. Now we can say "we lose at most about five minutes of writes" and point at evidence, instead of "we have daily backups" and hope.

Making the number smaller

Once you know your RPO, you can decide whether it's acceptable:

• Lower archive_timeout (e.g. to 1min). More, smaller uploads to object storage; WAL segments are compressed, so mostly-empty segments are cheap, but the request count goes up.
• Add replicas. With instances: 2 or more, a node failure is handled by failover to a replica that already has almost everything. For near-zero loss on failover, CNPG supports synchronous replication.
• Accept it. For many internal services, five minutes is fine. The point is that it's now a decision, not an accident.

Replicas protect you from losing a node; object-storage backups protect you from losing the cluster, the namespace or the data itself (a bad migration, a DELETE without a WHERE). You want both, and each has its own RPO.

The serverName trap: how a restore can overwrite your archive

This is the gotcha that's easy to miss, because it doesn't hurt you during the test. It hurts you after a real restore.

Recall that CNPG writes backups to <destinationPath>/<serverName>/, and serverName defaults to the cluster's name.

Now picture a real incident. You restore production into a new cluster. To keep things simple, you copy the original manifest, which includes the original spec.backup section with the same bucket and path. Maybe you even name the new cluster postgres again, because that's what all the apps' connection strings expect.

The restored cluster comes up and starts archiving WAL and taking scheduled backups, into the same folder as the original. It began a new timeline from an older point, and now it's writing into the archive you might still need: the one containing the original's history, including whatever you were trying to recover from.

Recent CNPG versions have a safety net: before archiving its first WAL file, a cluster checks that the target archive is empty and refuses to write if it isn't. That's good, but look at what it means in practice: your freshly restored production cluster comes up with ContinuousArchiving=False, mid-incident, and someone under pressure "fixes" it. Don't build your process on the safety net.

The rules we follow:

1. Restore tests have no spec.backup section at all. A test cluster has nothing to archive. (Ours didn't, which is why this never bit us.)
2. A restored cluster that will become production gets its own backup location. Either a different serverName or a different destinationPath:

   spec:
     backup:
       barmanObjectStore:
         destinationPath: s3://<bucket>/<prefix>/
         serverName: postgres-restored-20261001   # NOT the original's name
         # ...credentials as before...

1. In externalClusters, serverName is the original's name. In spec.backup, it's always a new name. Same field, opposite rule, depending on whether you're reading or writing. That asymmetry is exactly why it's easy to get wrong.
2. Never put a short lifecycle rule on the backup bucket. Let CNPG's retentionPolicy manage deletion, and keep any bucket rule well beyond it, so old archives are still there when you need to restore from before an incident.

Restoring one database out of a shared cluster

Our cluster holds eleven databases for eleven different applications. That creates a new kind of problem: what happens when one app has a bad day?

Say a migration in the finance service drops a column it shouldn't have. The other ten apps are fine. You definitely don't want to roll the whole cluster back an hour and lose everyone else's writes.

CNPG restores whole clusters, not single databases. But a whole-cluster restore into a temporary cluster gives you everything you need. The recipe:

1. Restore to just before the incident into a temporary cluster, using a point-in-time target:

  bootstrap:
    recovery:
      source: origin
      recoveryTarget:
        targetTime: "2026-10-01 09:29:00+00"   # just before the bad migration

2. Dump just the one database from the temporary cluster, in custom format:

kubectl -n service-db exec restore-test-1 -c postgres -- \
  pg_dump -Fc -d finance_db > finance_db.dump

In our test, dumping one database from the restored cluster this way produced a 13 MB file with all 23 tables. That's the proof that the single-database path works, not just the whole-cluster one.

3. Restore it into production next to the live one, not on top of it. Create an empty finance_db_restored owned by the app's role, then:

kubectl -n service-db exec -i postgres-1 -c postgres -- \
  pg_restore --no-owner --role=finance_user -d finance_db_restored < finance_db.dump

4. Decide how to bring the data back. You now have the damaged database and a clean copy side by side, which gives you options:

• Copy back only what was lost, for example the dropped column's data or a set of deleted rows, and keep everything written since. Usually the best outcome.
• Swap databases entirely: stop the app's writers, rename finance_db to finance_db_broken and finance_db_restored to finance_db, then restart. Simple, but you lose that app's writes since the restore point.

5. Delete the temporary cluster and its volume when you're done.

The other ten applications never notice.

Making it a habit

One passing restore test is a snapshot. Backups break over time, and you want to find out on a quiet Tuesday rather than during an outage. What we settled on:

• Repeat the test on a schedule. We're doing it quarterly, and also before any operator upgrade (the move to the Barman Cloud Plugin in particular).
• Write down the numbers each time: restore duration, measured RPO, tables checked, checksums matched. A restore that took 3 minutes last quarter and 40 minutes this quarter is telling you something.
• Watch the two conditions that matter, continuously, not just on test day:

  kubectl -n service-db get cluster postgres -o \
    jsonpath='{range .status.conditions[*]}{.type}={.status} {.message}{"\n"}{end}'
  # ContinuousArchiving=True and LastBackupSucceeded=True

and alert when either turns False.

• Clean up. Delete the test cluster and its volume afterwards, especially if your storage class uses reclaimPolicy: Retain, where deleting the PVC leaves the underlying disk behind.

The checklist

1. Create a recovery cluster from the original's serverName, with no spec.backup.
2. Wait for healthy; confirm every database is there.
3. Compare tables, row counts and migration versions against production.
4. Checksum every table; explain every mismatch.
5. Read pg_last_xact_replay_timestamp() and record your real RPO.
6. Dump one database from the restored cluster to prove single-database recovery.
7. Delete the test cluster and its volume.
8. Write down the numbers.

Wrapping up

Before the test, our backup story was "daily backups plus continuous WAL archiving, 30-day retention". All true, and none of it answered the question you actually care about during an incident.

After the test, we could say: a full restore takes about three minutes, we'd lose at most about five minutes of writes, we know where that number comes from, and we can recover a single application's database without touching the others. That's a very different sentence to say to your team.

If you haven't restored your backups lately, pick a quiet afternoon this week and do it. The worst outcome is that you find a problem while it's still cheap.

This is part two of a series on moving Postgres from Cloud SQL to CloudNativePG. Part one covers the migration itself: shared-cluster design, the zero-disk copy pipeline and reversible cutovers.

Comments

[Andrei Ovcharenko]

The RPO calculation needs a separate simulated failure timestamp. Subtracting the last replayed commit from restore completion also includes time spent restoring: a one-minute recovery gap plus a three-minute restore can look like four minutes of lost writes.

For a repeatable drill, record the failure cutoff first and recover from only the WAL already archived at that cutoff. Compare the recovered commit with the cutoff, and measure restore duration separately. Otherwise the live primary can keep feeding newer WAL into the test while recovery runs.

Also, archive_timeout forces a segment switch; it does not guarantee an upload deadline. Five minutes plus a successful upload is an estimate under healthy archiving, not an upper bound during an archiver/storage failure. PostgreSQL's description makes that distinction useful here.