Phase 4: Postgres Backup Verification + Restore SOP

phase-postgres-4-backup-restore Phase

Project

Goal: Verified backup/restore pipeline. Documented SOP. Tested point-in-time recovery.

Owner: Lucas + Betty Sue (operational + docs)

Status: COMPLETED (2026-03-06)

Progress

Step	Status
1. Fix WAL archiving	<strong>DONE</strong> — cleared stale WAL from MinIO
2. Verify WAL files accumulating	<strong>DONE</strong> — 16+ segments, ContinuousArchiving: True
3. Create ScheduledBackup (base backups)	<strong>DONE</strong> — daily at 02:00 UTC, first manual backup verified (3s, 5.6MB)
4. Test restore from backup	<strong>DONE</strong> — full recovery cluster from MinIO, all data intact
5. Test point-in-time recovery	<strong>DONE</strong> — PITR marker written after backup, found in restored DB
6. Write Postgres restore SOP	<strong>DONE</strong> — see <code>sop-postgres-restore</code>
7. Archive old Litestream SOP + cleanup	<strong>DONE</strong> — <code>sop-litestream-restore</code> archived

WAL Archiving Fix (2026-03-06)

Problem: ContinuousArchiving: False since cluster creation (March 2). barman-cloud-check-wal-archive failing with "Expected empty archive".

Root cause: Stale WAL file left in MinIO from initial cluster bootstrap. barman requires empty archive when starting fresh.

Fix:

Archiving recovered within 60 seconds. 16 WAL segments flushed.

Restore Test Results (2026-03-06)

Method: Created a CNPG recovery Cluster CRD bootstrapping from MinIO barman archive.

Key findings during testing:

serverName required: External cluster must specify serverName: pal-e-postgres to match the backup path in MinIO. Without it, barman looks under wrong directory.
imageName must match source: Default CNPG image is now Pg 18. Source cluster is Pg 17.4. Recovery cluster MUST specify imageName: ghcr.io/cloudnative-pg/postgresql:17. Pg 18 cannot read Pg 17 data dirs.
Pg 17.4-1 image has old barman: The :17.4-1 tag has barman-cloud that's incompatible with CNPG 1.28.1 CLI arg generation. Use :17 (latest 17.x) which has compatible barman.
PITR target time: If target time exceeds available WAL, Postgres fails with recovery ended before configured recovery target was reached. For full restore, omit recoveryTarget entirely.

Verification:

Table	Source	Restored	Match
notes	246	246	YES
tags	58	58	YES
note_revisions	597	597	YES
sprints	2	2	YES
repos	24	24	YES
PITR marker note	created 90s after backup	FOUND	YES

Working Recovery CRD Template

Deprecation Warning: Barman Cloud Plugin

CNPG 1.29 will remove native barman-cloud support. Must migrate to the Barman Cloud Plugin before upgrading the operator. Tracked in subphase: phase-postgres-4a-barman-plugin-migration.

ScheduledBackup

Applied via kubectl apply (not in Terraform yet — platform-managed CRD, should be codified):

Lessons Learned

Stale WAL in MinIO blocks archiving. Clear bucket contents if barman reports "Expected empty archive".
Pin imageName on recovery clusters. CNPG defaults to latest Postgres. Major version mismatch = instant failure.
Use :17 not :17.4-1. Fixed tags may have old barman-cloud incompatible with newer CNPG operator.
serverName is mandatory in externalClusters when backup server name differs from external cluster name.
Omit recoveryTarget for full restore. PITR target time past last WAL = fatal error.
Force WAL switch before PITR test. SELECT pg_switch_wal(); ensures recent writes are archived.

Infrastructure Inventory

Resource	Status
MinIO bucket <code>postgres-wal</code>	Active — WAL + base backups
MinIO user <code>cnpg</code>	Active
k8s secret <code>cnpg-s3-creds</code>	Active
ScheduledBackup <code>pal-e-postgres-daily</code>	Active — 02:00 UTC daily
MinIO bucket <code>litestream-backups</code>	STALE — can be deleted
MinIO user <code>litestream</code>	STALE — can be deleted

Subphases

phase-postgres-4a-barman-plugin-migration — Migrate from native barman-cloud to Barman Cloud Plugin before CNPG 1.29