Evaluate CNPG Barman Cloud backups vs current pg_dump (D.6) — re-verify the Hetzner restore bug FIRST

[matthias476]

Dedicated tracker for architecture-plan Task D.6 (split out of #131). Replacing the daily pg_dump CronJobs with CNPG-native physical backups (Barman Cloud Plugin) would give real RPO + PITR + an operator-native restore. But we deliberately chose pg_dump over Barman once before, for reasons that mostly still hold — so this must start as a spike, not an implementation.

Today

• hearthly-db and keycloak-db are backed up by hand-rolled pg_dump CronJobs (daily 02:00 / 03:00) → hearthly-backups S3 bucket (custom format, SHA256, 30-day lifecycle).
• Logical dump, once/day → RPO up to ~24h, and the restore path has never been drilled here.

Why revisit at all

1. RPO: WAL archiving → minutes/seconds instead of ~24h.
2. Tested, operator-native restore: a fresh CNPG cluster bootstraps directly from the store (spec.bootstrap.recovery); the migration includes an actual restore drill.
3. PITR now has value — when pg_dump was chosen the DB was empty with no users; that's no longer true. (This is the one original reason that has flipped.)

⚠️ Why we chose pg_dump originally (from docs/project-summary.md §"pg_dump over CNPG native backups")

We evaluated Barman thoroughly and rejected it:

• Hetzner S3 has a documented Barman restore byte-count mismatch — backups succeed but restores fail. The deprecated in-tree barmanObjectStore has the same bug.
• The Barman Cloud Plugin was pre-1.0 (v0.11.0) with active memory-leak / resource-consumption issues.
• Hetzner CSI has no VolumeSnapshots (open since Jan 2025) — blocks the snapshot-based CNPG backup path and Velero too.
• pg_dump + aws-cli uses a simple S3 PUT, sidestepping barman's broken restore path.

Status of those blockers as of 2026-05 (checked while filing this)

• Restore byte-count bug: STILL UNRESOLVED. cloudnative-pg#6645 (and barman#1063) are closed as "not planned" with no fix/workaround. Error: N read, but total bytes expected is M — restore reads ~10 MB of a ~2 GB object then aborts.
• Plugin still pre-1.0: latest is v0.12.0 (2026-04-14) — one minor bump from the rejected v0.11.0; still 0.x, not GA. The plugin wraps the same barman-cloud restore code, so it does not obviously fix the Hetzner restore failure.
• Hetzner CSI VolumeSnapshots: still need to confirm, but no fix was expected.

Net: the decisive reason (restores fail on Hetzner) still stands. A Barman migration that can back up but not restore is strictly worse than what we have.

Proposed approach — SPIKE FIRST (do not implement blind)

1. Reproduce/verify the restore bug on current versions. On a throwaway CNPG cluster + a throwaway bucket prefix: install plugin v0.12.0, take a base backup of a multi-GB dataset, then restore it. If the byte-count mismatch is gone → Barman on Hetzner is viable; proceed with the full D.6 sequence. If it still fails → do not migrate.
2. If Hetzner restore is still broken, pick a fallback (decide in this issue):
   • (a) Harden pg_dump instead — add an automated restore drill + integrity verification to the existing approach (closes the "untested backup" gap, the real risk, without Barman). Lowest-risk.
   • (b) Point Barman at a different S3 that restores cleanly (AWS S3 / Backblaze B2 / self-hosted MinIO) → get PITR at the cost of a second storage vendor.
   • (c) Wait for the plugin to reach GA and/or the Hetzner bug to be fixed; revisit later.

Full migration sequence (only if the spike passes)

Per the architecture plan, using the plugin (not the deprecated in-tree barmanObjectStore): install plugin → ObjectStore CRs → spec.plugins WAL archiver (isWALArchiver: true, barmanObjectName) → ScheduledBackup (method: plugin, 6-field cron) → restore drill → remove the two cronjob-db-backup.yaml → repeat for keycloak-db.

Acceptance criteria

• Spike result documented: does plugin v0.12.0+ restore cleanly from Hetzner S3? (yes/no + evidence)
• Decision recorded (proceed with Barman / harden pg_dump / different S3 / wait) and docs/project-summary.md §"pg_dump over CNPG native backups" updated to reflect the re-evaluation
• Whichever path: a passing, automated restore drill exists (the actual goal — backups are not trusted until a restore is proven)
• If migrated: pg_dump CronJobs removed; infrastructure/CLAUDE.md backup section updated

Refs

• Architecture plan Task D.6 (Cluster resilience hardening + current frozen state (post-2026-05 outage tracker) #131) · prior decision in docs/project-summary.md
• cloudnative-pg#6645 — Hetzner restore exception (closed not-planned)
• EnterpriseDB/barman#1063 — Restore failure on Hetzner
• plugin-barman-cloud releases (v0.12.0, 2026-04)