DEV Community: Muhammad Adnan Sarfraz

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

Muhammad Adnan Sarfraz — Fri, 23 Oct 2026 09:11:57 +0000

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:

Same set of tables.
Same row count for every table.
Same migration version (the alembic_version row).
Same database size, roughly.
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:

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.)
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...

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.
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

Create a recovery cluster from the original's serverName, with no spec.backup.
Wait for healthy; confirm every database is there.
Compare tables, row counts and migration versions against production.
Checksum every table; explain every mismatch.
Read pg_last_xact_replay_timestamp() and record your real RPO.
Dump one database from the restored cluster to prove single-database recovery.
Delete the test cluster and its volume.
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.

Leaving Cloud SQL: How We Moved 11 Postgres Databases onto One CloudNativePG Cluster

Muhammad Adnan Sarfraz — Fri, 16 Oct 2026 07:18:22 +0000

A practical playbook: one shared cluster, strict per-app isolation, a zero-disk copy pipeline, reversible cutovers, and a restore test that told us exactly how much data we could lose.
For a long time our microservice databases lived on Google Cloud SQL. It worked. But every one of our apps ran on Kubernetes in a different cloud, which meant every single query crossed the public internet, depended on cross-cloud firewall rules, and broke in surprising ways the day we changed our egress IPs.

So we brought the data home. Over about two weeks in September 2026, we moved eleven application databases (roughly 1.3 GB in total) off Cloud SQL and onto a single PostgreSQL 17 cluster run by CloudNativePG inside our production Kubernetes cluster.

This post is the playbook we ended up with: the design decisions, the exact commands, the mistakes we nearly made, and what's still on our list.

Why move at all?

Three reasons, in order of how much they hurt:
1. Keep data next to the apps. Every consumer of these databases ran in the same Kubernetes environment. A database in another cloud meant internet latency on every query, plus a fragile dependency on firewall allow-lists. When we migrated some services to new egress IPs, parts of the cluster simply lost their route to Cloud SQL.
2. One platform to run. **Storage, access, monitoring and deploys all go through Kubernetes, the same way as the applications. No separate console, no separate mental model.
**3. Cost and consolidation. One shared cluster instead of one managed instance per service.

What CloudNativePG gives you

CloudNativePG (CNPG) is a Kubernetes operator for PostgreSQL. You declare a single Cluster resource and the operator creates and looks after everything else: the Postgres pods, persistent volumes, TLS certificates (auto-renewed), and a set of Services:

<cluster>-rwalways points at the primary. This is what apps should use.
<cluster>-ro points at replicas only.
<cluster>-r points at any instance.

You can also ask CNPG to manage extra Services, such as a LoadBalancer for clients outside the cluster, so you never hand-edit them.

The design decisions that mattered
1. One shared cluster, many isolated databases

We chose a single cluster that holds every microservice database, instead of one cluster per service. Each application gets:

its own database,
its own login role, which owns that database (so it has full DDL inside it and nothing outside it),
and a single pg_hba rule that lets that role reach only that database.

Nothing is shared between apps except the server itself.

The app roles are plain LOGIN roles: no SUPERUSER, no CREATEDB, no CREATEROLE. For humans and tools like DBeaver there's a separate db_admin role with CREATEDB, CREATEROLE, pg_read_all_data, pg_write_all_data and pg_monitor. It still isn't a superuser, and it is only allowed in over TLS. The real postgres superuser is reachable only via kubectl exec into the pod (enableSuperuserAccess: false).

Isolation lives in pg_hba, and it's declarative

The access rules sit right in the Cluster spec:

spec:
  postgresql:
    pg_hba:
      - host survey_db      survey_user      all scram-sha-256
      - host payroll_db     payroll_user     all scram-sha-256
      - host finance_db     finance_user     all scram-sha-256
      - host scheduler_db   scheduler_user   all scram-sha-256  # reserved, db not created yet
      - hostssl all         db_admin         all scram-sha-256

Two things we learned:

A rule for a database or role that doesn't exist yet does nothing, so reserving lines ahead of time is harmless.
Never add a blanket host all all rule. One convenient line would quietly undo the isolation for every app.

When you change pg_hba and apply the Cluster object, CNPG reloads it without restarting Postgres.

Every database is added the same way

CNPG has a bootstrap import feature, but it only runs once, when a cluster is first created. Since we were adding databases one service at a time, we deliberately didn't use it. Every database goes through the same three steps instead:

Create the role and an empty database.
Load data with pg_dump | psql straight from Cloud SQL.
Switch the app.

Boring and repeatable beats clever.

Pin the image, and make storage outlive mistakes The image is pinned to an exact version, ghcr.io/cloudnative-pg/postgresql:17.6. Never a floating tag like :17, which can change under you on the next pod restart. We created a custom StorageClass with reclaimPolicy: Retain, so the data survives even if someone deletes a PersistentVolumeClaim. Data and WAL get separate volumes (200 Gi and 50 Gi). The StorageClass allows expansion, so volumes can grow later (they can never shrink).

Here's a trimmed version of the cluster definition:

apiVersion: postgresql.cnpg.io/v1
kind: Cluster
metadata:
  name: postgres
  namespace: service-db
spec:
  instances: 1
  imageName: ghcr.io/cloudnative-pg/postgresql:17.6
  enableSuperuserAccess: false

  storage:
    size: 200Gi
    storageClass: db-retain-fast
    resizeInUseVolumes: true
  walStorage:
    size: 50Gi
    storageClass: db-retain-fast

  resources:
    requests: { cpu: "2", memory: 4Gi }
    limits:   { cpu: "2", memory: 4Gi }

  postgresql:
    parameters:
      max_connections: "200"
      shared_buffers: 2GB
      effective_cache_size: 6GB
      maintenance_work_mem: 1GB
      log_connections: "on"
      log_disconnections: "on"
      log_min_duration_statement: "1000"   # log queries slower than 1s
    pg_hba:
      - host survey_db survey_user all scram-sha-256
      # ...one line per app...

  managed:
    services:
      additional:
        - selectorType: rw
          serviceTemplate:
            metadata:
              name: postgres-external
            spec:
              type: LoadBalancer
              # restrict who can reach it!
              loadBalancerSourceRanges:
                - 203.0.113.10/32

A note on memory: our first version set shared_buffers equal to the memory request with a higher limit. That works until the node is short on memory and Postgres gets OOM-killed. Setting requests equal to limits (Guaranteed QoS) is the safer choice for a database, which is what the snippet above shows.

Step 1: Create the role, database and Secret

We wrapped database creation in a small idempotent script, create-db.sh <database> <role> <secret-name>. It:

generates a random 64-hex-character password,
stores it in a Kubernetes Secret (username, password),
creates the role and the database owned by it,
tests a real network login through the -rw Service with TLS.

Because it's idempotent, re-running it is also the fix when a password drifts (more on that in troubleshooting).

To read a password later, without ever pasting it into a ticket or chat:

kubectl -n service-db get secret <secret-name> \
  -o jsonpath='{.data.password}' | base64 -d

Step 2: Copy the data without touching disk

This is the part I'm most happy with. The dump runs inside the Postgres pod itself, which already has pg_dump 17 and can reach Cloud SQL. It pipes straight into the new database, so nothing is written to disk, and passwords go in through stdin, never on the command line (where they'd show up in ps and shell history).


K="kubectl -n service-db"
NPW=$($K get secret <secret-name> -o jsonpath='{.data.password}' | base64 -d)

printf '%s\n%s\n' '<cloudsql-password>' "$NPW" | \
$K exec -i postgres-1 -c postgres -- bash -c '
  read -r SRC; read -r DST; set -o pipefail
  PGPASSWORD="$SRC" pg_dump "host=<cloudsql-ip> user=<cloudsql-user> dbname=<source-db>" \
      --no-owner --no-acl --no-publications --no-subscriptions \
    | grep -v "^SET transaction_timeout" \
    | PGPASSWORD="$DST" psql "host=localhost user=<role> dbname=<db> sslmode=require" \
      -v ON_ERROR_STOP=1 -q --single-transaction \
  && echo RESTORE OK'

unset NPW

Every flag is there for a reason:

--no-owner --no-acl: objects are created as the new app role, and Cloud SQL's own roles and grants (hello, cloudsqlsuperuser) are dropped instead of failing the restore.
grep -v "^SET transaction_timeout": pg_dump 17 writes this setting into the dump, and servers older than 17 reject it. Filtering it keeps the pipeline portable in both directions.
--single-transaction with ON_ERROR_STOP=1: all or nothing. A failed restore leaves the database empty, so it's always safe to re-run.

Then verify. Compare count(*) for every table and the alembic_version row (or whatever your migration tool uses) between source and target. Don't trust "it finished without errors".

At cutover, re-copy with writers stopped. The first copy is a rehearsal. For the real thing, stop every writer, then drop and reload with pg_dump --clean --if-exists and SET lock_timeout = '30s' so a forgotten idle session can't hang the whole operation.

Step 3: Switch the app (and keep the way back)

Our apps read their connection string from a DATABASE_URL in a Secret in their own namespace. The switch is:

Save the current URL somewhere private (a file with mode 0600). This is your rollback.
Patch the app's Secret with the new URL, keeping the app's URL scheme (postgresql:// vs postgresql+psycopg:// matters to some drivers):

inside the cluster: postgresql://<role>:<pw>@postgres-rw.service-db.svc:5432/<db>?sslmode=require
outside the cluster: postgresql://<role>:<pw>@<load-balancer-ip>:5432/<db>?sslmode=require
Restart: kubectl -n <ns> rollout restart deploy/<app> and wait on rollout status.
Verify: logs show a clean startup with no DB errors, and pg_stat_activity shows the role connected.
Rollback if needed: put the saved URL back and restart. We scripted this for the services we switched in bulk.

Here's a handy one-liner to see where an app currently points, with the password masked:

kubectl -n <ns> get secret <app-secret> -o jsonpath='{.data.DATABASE_URL}' \
  | base64 -d | sed -E 's#://([^:]+):[^@]+@#://\1:***@#'

A real example

For our finance service, the whole run looked like this:

The pg_hba rule was already in place (reserved earlier).
create-db.sh created the role, database and Secret, and tested login and DDL over TLS.
We restored from Cloud SQL (PostgreSQL 16) into the new PostgreSQL 17 database. Every object ended up owned by the app role; row counts matched.
The service runs in two Kubernetes clusters, so before switching the second one we launched a throwaway postgres:17-alpine pod there and read the data through the LoadBalancer. That proved the network path before any real traffic depended on it.
We patched DATABASE_URL in both clusters and restarted. Migrations ran, both pods came up, "Application startup complete".
The old URLs went into a 0600 backup file, so rollback was one patch away.

One honest loose end from that run: the app had been talking to one Cloud SQL endpoint, but our dump account used a different one. If those turn out to be different instances, the data could differ. The lesson: confirm the source you dump from is the source the app actually writes to, before you decommission anything.

Backups: configure them, then prove they work

The migration isn't done until you can restore. We set up CNPG's backups to S3-compatible object storage:

a daily base backup at 02:00 UTC via a ScheduledBackup,
continuous WAL archiving (gzip), so we can restore to any point in time since the first backup,
retentionPolicy: 30d, which lets CNPG itself delete base backups and WAL that are no longer needed. (If you also add a bucket lifecycle rule, make it comfortably longer than the retention window, or the bucket will delete WAL that CNPG still needs.)

apiVersion: postgresql.cnpg.io/v1
kind: ScheduledBackup
metadata:
  name: postgres-daily
  namespace: service-db
spec:
  schedule: "0 0 2 * * *"   # six fields: seconds first
  cluster:
    name: postgres
  backupOwnerReference: self

Taking an ad-hoc backup before risky maintenance is one small manifest:

cat <<EOF | kubectl apply -f -
apiVersion: postgresql.cnpg.io/v1
kind: Backup
metadata: {name: postgres-manual-$(date +%Y%m%d%H%M), namespace: service-db}
spec: {cluster: {name: postgres}, method: barmanObjectStore}
EOF

And to check health, the two conditions that matter are ContinuousArchiving=True and LastBackupSucceeded=True:

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

The restore test

CNPG always restores into a new cluster; it never overwrites the running one. You create a Cluster with a recovery bootstrap pointing at the original's backup location:

spec:
  bootstrap:
    recovery:
      source: origin
      # recoveryTarget: {targetTime: "2026-10-01 09:30:00+00"}  # optional PITR
  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}

On the day we enabled backups, we restored into a temporary cluster. It took about three minutes and contained all eleven databases. For one database we checked every table: all 23 had identical row counts, migration version and size. Row checksums matched on 20 tables; the other 3 differed only because of live writes after the restore point.

That gap was the most useful number we got out of the whole exercise. The newest restored change was about five minutes before the restore finished, which matches how often CNPG switches WAL segments. Our real-world recovery point is roughly five minutes of data. Now we know, instead of guessing.

We also ran pg_dump -Fc for a single database from the restored cluster. That's our path for "restore just one app's database" on a shared cluster. Then we deleted the test cluster and its volume.

One gotcha: if a restored cluster will itself back up to the same bucket, give it a different serverName or path, so it can't overwrite the original's archive.

Troubleshooting cheat sheet

The errors we actually hit, or expect to:

no pg_hba.conf entry for host ..., user X, database Y: no rule for that db/role pair, or a typo in the database name (finance_db vs financedb got us once). Check the live rules with kubectl get cluster postgres -o jsonpath='{.spec.postgresql.pg_hba}'.
password authentication failed: the app's DATABASE_URL and the source-of-truth Secret disagree. Re-running the idempotent create script re-applies the Secret's password to the role.
unrecognized configuration parameter "transaction_timeout": a pg_dump 17 dump loaded into an older server. Filter the SET line out.
role "cloudsqlsuperuser" does not exist: dump with --no-owner --no-acl.
too many clients already: usually a connection-pool leak or a scaled-out app. Find the culprit by grouping pg_stat_activity by role; fix the pool before raising max_connections (which needs a restart).
DROP/ALTER hangs forever: a lock held by another session, very often an idle DBeaver tab. Find it with pg_blocking_pids(), terminate it, and use SET lock_timeout for maintenance.
Pod stuck Pending: nodes are full, or the volume is zonal and free capacity is in a different zone.
ContinuousArchiving=False: treat this as urgent. Unarchived WAL piles up on the WAL volume, and a full WAL volume stops the database.

The queries we keep coming back to:

-- connections per database / role / client
select datname, usename, client_addr, state, count(*)
from pg_stat_activity where datname is not null
group by 1,2,3,4 order by 1;

-- database sizes
select datname, pg_size_pretty(pg_database_size(datname))
from pg_database order by pg_database_size(datname) desc;

-- who is blocking whom
select pid, usename, datname, wait_event_type, wait_event,
       pg_blocking_pids(pid), left(query, 80)
from pg_stat_activity where cardinality(pg_blocking_pids(pid)) > 0;

The rules we wrote in capital letters

Never delete the data or WAL PVCs, or the Cluster object, while it holds data.
Never add host all all to pg_hba.
Never apply a manifest from git without diffing it against the live cluster first. A copy of our cluster definition on the main branch was once missing a pg_hba rule that existed live; applying it would have silently cut off a production service. kubectl get cluster postgres -o yaml first, every time.
Never apply a whole secrets file "just to update one". It can reset live passwords to older values.

What's still on the list

A migration is a snapshot, not an ending. Here's what we'd tell anyone starting this today, most of which we're working through ourselves:

1. Plan for the Barman Cloud Plugin. CNPG's built-in Barman object-store support is deprecated in favour of the Barman Cloud Plugin and is slated for removal in a future release. Migrate before you upgrade the operator, or backups can stop without anything obviously breaking. And repeat the restore test on a schedule (we're going quarterly).
2. Add a replica. One instance means a node failure or an upgrade takes every database down at once. Once backups are proven, instances: 2 or 3 is a one-line change and a big win.
3. Lock down the LoadBalancer. If clients outside the cluster need access, use loadBalancerSourceRanges so only known IPs can reach port 5432. Passwords alone shouldn't be your only wall.
4. Keep secrets out of git. Use Sealed Secrets or External Secrets, give backups their own bucket-scoped key, and rotate anything that ever lived in a repo or a chat message.
5. Monitor before users tell you. Enable the CNPG PodMonitor and Grafana dashboard, and alert on disk usage, connection count and cluster phase.
6. Decommission the old system deliberately. Check Cloud SQL for any remaining connections, take a final dump, and only then shut it down.

Wrapping up

The whole migration came down to a handful of habits: one boring, repeatable path for every database; isolation enforced in one declarative place; copies that are all-or-nothing and verified by counting rows; cutovers with a saved way back; and backups proven by an actual restore, with a measured recovery point.

CloudNativePG made the Kubernetes side of this surprisingly pleasant. The operator handles the parts that used to be "managed" for us (certificates, services, failover-ready topology, backups), and everything else is YAML we can review in a pull request.