Skip to content

Commit 8a9af01

Browse files
androndoclaude
andcommitted
feat(backups): support PostgreSQL point-in-time recovery (PITR)
Makes RestoreJob.spec.options.recoveryTime a genuinely supported, tested capability for the new backups.cozystack.io API (issue #2774). The recoveryTime -> CNPG bootstrap.recovery.recoveryTarget.targetTime plumbing already existed on the barman-cloud-plugin branch; this adds the reliability and verification layer the issue asked for: - Fail-fast for an unreachable target: when a RestoreJob has recoveryTime set and the recovery Cluster is not healthy, the driver reads the CNPG full-recovery pod logs and, once several distinct attempts have exited with PostgreSQL's "recovery ended before configured recovery target was reached" FATAL, fails the RestoreJob with conditions RecoveryConverged=False / Ready=False (reason RecoveryTargetUnreachable) instead of hanging to the restore deadline. Health is checked first, so a converged recovery always wins; counting distinct FATAL-ing attempts (not pod failures) tolerates a transient archive-lag miss on a near-now target and ignores transient recovery-pod crashes. Adds the pods/log RBAC grant the log read requires. - e2e (examples/backups/postgres/run-all.sh, wired into the Chainsaw postgres-2-backup-roundtrip suite): step 45 writes a before marker, captures the server timestamp, writes an after marker, restores to that timestamp and asserts the before row survived and the after row did not; step 46 submits a recoveryTime an hour ahead and asserts it fails fast with reason RecoveryTargetUnreachable. - Working, uncommented example (45-restorejob-pitr.yaml) and a docs section (docs/operations/backup-classes.md) covering the recoverable window, how to discover the earliest/latest restorable time from the backup catalog, the archive-lag tolerance, and idempotency under GitOps. Refs #2774 Co-Authored-By: Claude Opus 4.8 (1M context) <[email protected]> Signed-off-by: Andrey Kolkov <[email protected]>
1 parent b897b53 commit 8a9af01

12 files changed

Lines changed: 763 additions & 23 deletions

File tree

‎docs/operations/backup-classes.md‎

Lines changed: 57 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -199,3 +199,60 @@ spec:
199199
kind: Postgres
200200
name: orders-db
201201
```
202+
203+
## Point-in-time recovery (PostgreSQL)
204+
205+
A `RestoreJob` restores a `Postgres` application from a `Backup`. Omit `spec.options.recoveryTime` to recover to the latest point in the WAL archive; set it (RFC3339) to recover the database to an exact instant — a point-in-time recovery (PITR). Under the hood the CNPG barman-cloud plugin restores the newest base backup taken at/before that instant and replays archived WAL up to it, so the restored cluster reflects the database exactly as of `recoveryTime`; later writes are absent.
206+
207+
```yaml
208+
apiVersion: backups.cozystack.io/v1alpha1
209+
kind: RestoreJob
210+
metadata:
211+
name: orders-db-pitr
212+
namespace: tenant-acme
213+
spec:
214+
backupRef:
215+
name: orders-db-adhoc # a completed Backup of the source
216+
targetApplicationRef: # omit for a destructive in-place restore
217+
apiGroup: apps.cozystack.io
218+
kind: Postgres
219+
name: orders-db-copy # a pre-deployed, empty Postgres app
220+
options:
221+
recoveryTime: "2026-07-21T09:30:00Z"
222+
```
223+
224+
A full, scripted example (write a marker, capture the timestamp, restore to it, assert what survived) is in [`examples/backups/postgres/`](../../examples/backups/postgres/) — `45-restorejob-pitr.yaml`, driven by `run-all.sh`.
225+
226+
### The recoverable window
227+
228+
`recoveryTime` must fall inside the window the archive can reconstruct:
229+
230+
- **Earliest** — the completion of the oldest base backup still in the archive. You cannot recover to an instant before the first base backup: WAL replay always starts from a base backup, and retention (`barmanObjectStore.retentionPolicy`) eventually trims the oldest ones together with the WAL that predates them.
231+
- **Latest** — the timestamp of the most recent WAL segment shipped to object storage. While the source runs with `backup.enabled: true` it archives continuously, so the latest restorable time trails "now" only by the archive lag (seconds for a healthy cluster). Once the source is deleted, the latest restorable time is frozen at whatever WAL made it to S3 before it went away.
232+
233+
A `recoveryTime` **past the latest archived WAL cannot converge**: PostgreSQL replays every available WAL, never reaches the target, exits with `FATAL: recovery ended before configured recovery target was reached`, and CNPG re-creates the recovery instance in a loop. The driver detects that specific FATAL in the recovery pod's log and fails the RestoreJob fast — `status.phase: Failed`, conditions `RecoveryConverged=False` and `Ready=False` both with reason `RecoveryTargetUnreachable` — instead of hanging until the restore deadline. Widen the window (take a fresh backup, or restore closer to now) or pick a time inside it.
234+
235+
Restoring to a **very recent** instant is safe: the segment covering it may lag the archive by a few seconds, and the same FATAL fires transiently, but the driver only fails after several distinct recovery attempts have hit it — a lagging target FATALs once and then converges as soon as the WAL ships (the next attempt promotes and the cluster goes healthy), so only a target that keeps failing across attempts is declared unreachable. You do not need to leave a manual margin.
236+
237+
A `recoveryTime` **before the earliest base backup** is a *different* failure and is **not** caught by that fast-fail guard: PostgreSQL cannot begin replay before the base backup it restored, so recovery never reaches a consistent state and never emits the "recovery ended before …" FATAL the guard matches. That case surfaces when the restore deadline (`spec.options.restoreTimeoutSeconds`, default 30m) elapses — `status.phase: Failed` with reason `RestoreFailed`. Choosing a `recoveryTime` at/after the oldest base backup's completion (below) avoids it.
238+
239+
### Discovering the earliest / latest restorable time
240+
241+
CNPG's `Cluster.status.firstRecoverabilityPoint` exists in the status schema but is unreliable under the barman-cloud plugin — it was observed empty on every plugin-backed cluster on the dev7 test cluster (CNPG 1.28.1) — so read the window from the backup catalog instead. Each completed base backup records its WAL range and timestamps on the underlying `cnpg.io/Backup`:
242+
243+
```bash
244+
# Completed base backups, oldest first: STOP is the earliest instant that
245+
# backup alone can restore to; the oldest STOP is the window's lower bound.
246+
kubectl -n <ns> get backups.postgresql.cnpg.io \
247+
--sort-by=.status.stoppedAt \
248+
-o custom-columns=NAME:.metadata.name,PHASE:.status.phase,START:.status.startedAt,STOP:.status.stoppedAt,BEGINWAL:.status.beginWal,ENDWAL:.status.endWal
249+
250+
# The cozystack Backup points at its underlying cnpg.io/Backup and S3 prefix.
251+
kubectl -n <ns> get backup.backups.cozystack.io <name> -o jsonpath='{.spec.driverMetadata}'
252+
```
253+
254+
The upper bound — the latest archived WAL — is whatever the source has shipped so far; with archiving healthy, any time up to a few seconds ago is safe. To confirm the archive is current, check that the source cluster's WAL is flowing to S3 (the `barman-cloud` sidecar logs an upload per segment) before restoring to a near-now target.
255+
256+
### Idempotency under GitOps
257+
258+
An in-progress restore is safe to reconcile. The driver purges the target `Cluster` + PVCs exactly once per RestoreJob (guarded by the `TargetPurged` condition and a freshly-recovered check), suspends the target's HelmRelease across the purge so Flux cannot race the bootstrap swap, and resumes it once the recovery cluster is rendered. A Flux reconcile (or a controller restart) mid-restore therefore re-attaches to the recovering cluster rather than deleting it and starting over.

‎examples/backups/postgres/00-helpers.sh‎

Lines changed: 2 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -25,6 +25,8 @@ export STRATEGY_NAME="${STRATEGY_NAME:-cnpg-strategy-default}"
2525
export BACKUPCLASS_NAME="${BACKUPCLASS_NAME:-postgres-cnpg}"
2626
export BACKUPJOB_NAME="${BACKUPJOB_NAME:-pg-src-adhoc}"
2727
export RESTOREJOB_TOCOPY_NAME="${RESTOREJOB_TOCOPY_NAME:-pg-src-to-pg-target}"
28+
export RESTOREJOB_PITR_NAME="${RESTOREJOB_PITR_NAME:-pg-src-to-pg-target-pitr}"
29+
export RESTOREJOB_UNREACHABLE_NAME="${RESTOREJOB_UNREACHABLE_NAME:-pg-src-to-pg-target-unreachable}"
2830
export PLAN_NAME="${PLAN_NAME:-pg-src-daily}"
2931
# App user password baked into 05-postgres-src.yaml (REPLACE_WITH_PASSWORD).
3032
export PG_PASSWORD="${PG_PASSWORD:-Xai7Wepo0aeThie8}"

‎examples/backups/postgres/35-restorejob-in-place.yaml‎

Lines changed: 6 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -2,7 +2,12 @@
22
# back into pg-src. WARNING: deletes the live cnpg.io Cluster and its PVCs
33
# before re-bootstrapping from S3 - any data not yet streamed to WAL is lost.
44
#
5-
# Optional: spec.options.recoveryTime (RFC3339) for point-in-time recovery.
5+
# Optional: spec.options.recoveryTime (RFC3339) turns this into a point-in-time
6+
# recovery, same as the to-copy variant in 45-restorejob-pitr.yaml (which the
7+
# e2e drives with a real, in-window timestamp). Uncomment and set a timestamp
8+
# inside your recoverable window - see 45-restorejob-pitr.yaml and
9+
# docs/operations/backup-classes.md ("Point-in-time recovery") for how to find
10+
# the earliest/latest restorable time.
611
---
712
apiVersion: backups.cozystack.io/v1alpha1
813
kind: RestoreJob
Lines changed: 34 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,34 @@
1+
# Point-in-time recovery (PITR) to a copy.
2+
#
3+
# Restores the source's Barman archive into pg-target as of a specific instant
4+
# via spec.options.recoveryTime (RFC3339). The chart maps it onto CNPG's
5+
# bootstrap.recovery.recoveryTarget.targetTime: PostgreSQL restores the newest
6+
# base backup taken at/before that instant and replays archived WAL up to it,
7+
# so the copy reflects the database exactly as of recoveryTime - later writes
8+
# are absent.
9+
#
10+
# recoveryTime MUST fall inside the recoverable window: at/after the base
11+
# backup's completion and at/before the latest WAL shipped to object storage.
12+
# A target past the latest archived WAL cannot converge - the RestoreJob fails
13+
# with condition Ready=False/RecoveryTargetUnreachable instead of hanging. See
14+
# docs/operations/backup-classes.md ("Point-in-time recovery") for how to
15+
# discover the earliest/latest restorable time.
16+
#
17+
# run-all.sh substitutes REPLACE_WITH_RECOVERY_TIME with a timestamp it captures
18+
# between two marker writes, then asserts the pre-marker row is present and the
19+
# post-marker row is absent. Editing by hand, replace it with an RFC3339
20+
# timestamp inside your window, e.g. "2026-05-01T12:00:00Z".
21+
---
22+
apiVersion: backups.cozystack.io/v1alpha1
23+
kind: RestoreJob
24+
metadata:
25+
name: pg-src-to-pg-target-pitr
26+
spec:
27+
backupRef:
28+
name: pg-src-adhoc
29+
targetApplicationRef:
30+
apiGroup: apps.cozystack.io
31+
kind: Postgres
32+
name: pg-target
33+
options:
34+
recoveryTime: "REPLACE_WITH_RECOVERY_TIME"

‎examples/backups/postgres/README.md‎

Lines changed: 2 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -14,6 +14,7 @@ The numbered manifests are the documented flow; a human can read and apply them
1414
- `25-backupjob-adhoc.yaml` — an ad-hoc `BackupJob`.
1515
- `30-postgres-target.yaml` + `40-restorejob-to-copy.yaml` — restore into a fresh copy, leaving the source running.
1616
- `35-restorejob-in-place.yaml` — the destructive in-place restore variant.
17+
- `45-restorejob-pitr.yaml` — point-in-time recovery: restore into the copy as of a specific `spec.options.recoveryTime` (RFC3339).
1718

1819
## Placeholders and derived Secrets
1920

@@ -30,7 +31,7 @@ NAMESPACE=tenant-root examples/backups/postgres/run-all.sh
3031
NAMESPACE=tenant-root examples/backups/postgres/cleanup.sh
3132
```
3233

33-
`run-all.sh` writes a sentinel row into the source, waits for the `BackupJob` to reach `Succeeded`, restores to a copy, and asserts the sentinel round-tripped through S3 into the restored copy. Set `SKIP_RESTORE=1` to stop after a successful backup.
34+
`run-all.sh` writes a sentinel row into the source, waits for the `BackupJob` to reach `Succeeded`, restores to a copy, and asserts the sentinel round-tripped through S3 into the restored copy. It then runs a **point-in-time recovery**: it writes a `before` marker, captures the server timestamp, writes an `after` marker, and restores the copy to that timestamp (`45-restorejob-pitr.yaml`), asserting the `before` row survived and the `after` row did not. Finally it submits a RestoreJob with a `recoveryTime` an hour in the future and asserts it **fails fast** with `status.phase: Failed` and reason `RecoveryTargetUnreachable` (rather than hanging to the restore deadline). Set `SKIP_RESTORE=1` to stop after a successful backup, or `SKIP_PITR=1` to stop after the latest-point restore.
3435

3536
## Automated e2e
3637

‎examples/backups/postgres/cleanup.sh‎

Lines changed: 3 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -20,8 +20,9 @@ for app in "$PG_SRC_NAME" "$PG_TARGET_NAME"; do
2020
fi
2121
done
2222

23-
log_substep "Deleting RestoreJob..."
24-
kubectl -n "$NAMESPACE" delete restorejob.backups.cozystack.io "$RESTOREJOB_TOCOPY_NAME" --ignore-not-found
23+
log_substep "Deleting RestoreJobs..."
24+
kubectl -n "$NAMESPACE" delete restorejob.backups.cozystack.io \
25+
"$RESTOREJOB_TOCOPY_NAME" "$RESTOREJOB_PITR_NAME" "$RESTOREJOB_UNREACHABLE_NAME" --ignore-not-found
2526

2627
log_substep "Deleting Plan + BackupJob + derived Backup artefacts..."
2728
kubectl -n "$NAMESPACE" delete plan.backups.cozystack.io "$PLAN_NAME" --ignore-not-found

‎examples/backups/postgres/run-all.sh‎

Lines changed: 109 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -19,13 +19,20 @@ set -euo pipefail
1919
SCRIPT_DIR="$(cd "$(dirname "${BASH_SOURCE[0]}")" && pwd)"
2020
source "$SCRIPT_DIR/00-helpers.sh"
2121

22+
# Recovery target for the PITR phase; captured between two marker writes far
23+
# below. Defaulted here so subst() can reference it under `set -u` before the
24+
# PITR phase sets it (the placeholder only appears in 45-restorejob-pitr.yaml).
25+
RECOVERY_TIME="${RECOVERY_TIME:-}"
26+
2227
# Substitute the manifest placeholders. $BUCKET / $S3_HOST are resolved from the
23-
# Bucket below; $PG_PASSWORD is the app user's password.
28+
# Bucket below; $PG_PASSWORD is the app user's password; $RECOVERY_TIME is the
29+
# PITR target (empty for every manifest except 45-restorejob-pitr.yaml).
2430
subst() {
2531
sed \
2632
-e "s|REPLACE_WITH_COSI_BUCKET_NAME|${BUCKET}|g" \
2733
-e "s|REPLACE_WITH_S3_ENDPOINT|${S3_HOST}|g" \
2834
-e "s|REPLACE_WITH_PASSWORD|${PG_PASSWORD}|g" \
35+
-e "s|REPLACE_WITH_RECOVERY_TIME|${RECOVERY_TIME}|g" \
2936
"$SCRIPT_DIR/$1"
3037
}
3138

@@ -172,3 +179,104 @@ if [[ "$GOT" != "$SENTINEL_TOKEN" ]]; then
172179
exit 1
173180
fi
174181
log_success "Round-trip verified: '${PG_TARGET_NAME}' restored sentinel '${GOT}' from S3."
182+
183+
if [[ "${SKIP_PITR:-0}" == "1" ]]; then
184+
log_warning "SKIP_PITR=1: stopping after the latest-point restore."
185+
exit 0
186+
fi
187+
188+
print_header "Step 45: point-in-time recovery (restore as of a timestamp between two writes)"
189+
# The base backup (pg-src-adhoc) predates the two markers below; pg-src has
190+
# archived WAL continuously since install, so a PITR to a time BETWEEN the
191+
# markers must replay the 'before' write and stop short of the 'after' write.
192+
log_substep "Writing the 'before' marker into the source..."
193+
psql_exec "$PG_SRC_CLUSTER" demo "
194+
CREATE TABLE IF NOT EXISTS pitr_marker (id int PRIMARY KEY, label text);
195+
INSERT INTO pitr_marker (id, label) VALUES (1, 'before')
196+
ON CONFLICT (id) DO UPDATE SET label = EXCLUDED.label;"
197+
198+
# Capture the recovery target from the SERVER clock, right after 'before'
199+
# committed. WAL record timestamps (which recovery_target_time compares
200+
# against) come from the server, so the client clock must not be trusted.
201+
# Sub-second (.US) precision is REQUIRED: whole-second to_char floors, and the
202+
# 'before' write and this capture can land in the same wall-clock second, so a
203+
# truncated target would sort *before* the 'before' commit and drop it.
204+
RECOVERY_TIME=$(psql_exec "$PG_SRC_CLUSTER" demo \
205+
"SELECT replace(to_char(now() AT TIME ZONE 'UTC', 'YYYY-MM-DD HH24:MI:SS.US'), ' ', 'T') || 'Z';" \
206+
| tr -d '[:space:]')
207+
[[ -n "$RECOVERY_TIME" ]] || { log_error "failed to capture recovery target time"; exit 1; }
208+
log_success "Recovery target: ${RECOVERY_TIME}"
209+
210+
# Open a clear gap so the 'after' commit timestamp is strictly past the target,
211+
# then force the WAL holding both markers to object storage before restoring.
212+
sleep 5
213+
log_substep "Writing the 'after' marker (must NOT survive the PITR)..."
214+
psql_exec "$PG_SRC_CLUSTER" demo "
215+
INSERT INTO pitr_marker (id, label) VALUES (2, 'after')
216+
ON CONFLICT (id) DO UPDATE SET label = EXCLUDED.label;
217+
CHECKPOINT;
218+
SELECT pg_switch_wal();"
219+
# Give the barman-cloud WAL archiver time to ship the switched segment; without
220+
# the WAL up to the target in object storage the recovery cannot reach it.
221+
sleep 15
222+
223+
print_header "Step 45: restore '${PG_TARGET_NAME}' to ${RECOVERY_TIME} and wait for Succeeded"
224+
subst 45-restorejob-pitr.yaml | kubectl -n "$NAMESPACE" apply -f -
225+
wait_for_field restorejobs.backups.cozystack.io "$RESTOREJOB_PITR_NAME" \
226+
'{.status.phase}' Succeeded "$NAMESPACE" 1200 Failed
227+
wait_for_field clusters.postgresql.cnpg.io "$PG_TARGET_CLUSTER" \
228+
'{.status.phase}' 'Cluster in healthy state' "$NAMESPACE" 600
229+
230+
print_header "Step 45 verify: the copy stopped at the recovery target"
231+
BEFORE=$(psql_exec "$PG_TARGET_CLUSTER" demo \
232+
"SELECT label FROM pitr_marker WHERE id = 1;" | tr -d '[:space:]')
233+
AFTER_COUNT=$(psql_exec "$PG_TARGET_CLUSTER" demo \
234+
"SELECT count(*) FROM pitr_marker WHERE id = 2;" | tr -d '[:space:]')
235+
if [[ "$BEFORE" != "before" ]]; then
236+
log_error "PITR lost pre-target data: pitr_marker id=1 is '${BEFORE}', expected 'before'"
237+
exit 1
238+
fi
239+
if [[ "$AFTER_COUNT" != "0" ]]; then
240+
log_error "PITR kept post-target data: pitr_marker id=2 present (count=${AFTER_COUNT}), expected absent"
241+
exit 1
242+
fi
243+
log_success "PITR verified: '${PG_TARGET_NAME}' recovered to ${RECOVERY_TIME} (pre-target row present, post-target row absent)."
244+
245+
print_header "Step 46: a recoveryTime past the archive fails fast (not a 30m hang)"
246+
# Negative case for the PITR fail-fast guard: a recoveryTime an hour ahead of
247+
# the latest archived WAL can never be reached. The restore driver must detect
248+
# that from the recovery pod's log and fail the RestoreJob with reason
249+
# RecoveryTargetUnreachable, well before the restoreTimeoutSeconds deadline -
250+
# asserting the *reason* (not just phase=Failed) is what proves the fast-fail
251+
# guard fired rather than the deadline elapsing.
252+
UNREACHABLE_TIME=$(psql_exec "$PG_SRC_CLUSTER" demo \
253+
"SELECT replace(to_char((now() + interval '1 hour') AT TIME ZONE 'UTC', 'YYYY-MM-DD HH24:MI:SS'), ' ', 'T') || 'Z';" \
254+
| tr -d '[:space:]')
255+
[[ -n "$UNREACHABLE_TIME" ]] || { log_error "failed to capture unreachable target time"; exit 1; }
256+
log_success "Unreachable target: ${UNREACHABLE_TIME}"
257+
kubectl -n "$NAMESPACE" apply -f - <<EOF
258+
apiVersion: backups.cozystack.io/v1alpha1
259+
kind: RestoreJob
260+
metadata:
261+
name: ${RESTOREJOB_UNREACHABLE_NAME}
262+
spec:
263+
backupRef:
264+
name: pg-src-adhoc
265+
targetApplicationRef:
266+
apiGroup: apps.cozystack.io
267+
kind: Postgres
268+
name: ${PG_TARGET_NAME}
269+
options:
270+
recoveryTime: "${UNREACHABLE_TIME}"
271+
EOF
272+
# Expect a terminal Failed (fail fast on an unexpected Succeeded). Cap the wait
273+
# well under the 30m restore deadline so a pass here means the guard fired.
274+
wait_for_field restorejobs.backups.cozystack.io "$RESTOREJOB_UNREACHABLE_NAME" \
275+
'{.status.phase}' Failed "$NAMESPACE" 900 Succeeded
276+
REASON=$(kubectl -n "$NAMESPACE" get restorejob.backups.cozystack.io "$RESTOREJOB_UNREACHABLE_NAME" \
277+
-o jsonpath='{.status.conditions[?(@.type=="Ready")].reason}')
278+
if [[ "$REASON" != "RecoveryTargetUnreachable" ]]; then
279+
log_error "expected fail-fast reason RecoveryTargetUnreachable, got '${REASON}' (a RestoreFailed here means it hung to the deadline instead of fast-failing)"
280+
exit 1
281+
fi
282+
log_success "Fail-fast verified: recoveryTime ${UNREACHABLE_TIME} past the archive → RestoreJob Failed with reason RecoveryTargetUnreachable."

0 commit comments

Comments
 (0)