Make PostgreSQL Point-in-Time Recovery (PITR) actually work and tested in the new backups API

[myasnikovdaniil]

Important

Needs verification. The failure modes listed below are based on field observation and may not all reproduce on current main. Before implementation, verify each against the latest backups controller and CNPG strategy — confirm which still hold and which are already fixed.

Summary

The new backups API (backups.cozystack.io/v1alpha1) exposes a RestoreJob.spec.options.recoveryTime field for PostgreSQL PITR, but PITR is not functionally supported today: there is no end-to-end test, the example keeps it commented out, and known failure modes make a real point-in-time restore unreliable. This issue tracks making postgres PITR a genuinely supported, verified capability.

What exists today

• RestoreJob.spec.options.recoveryTime (RFC3339) is intended to map onto CNPG bootstrap.recovery.recoveryTarget.targetTime.
• examples/backups/postgres/35-restorejob-in-place.yaml ships with recoveryTime commented out.
• The only backup e2e test (hack/e2e-apps/backup.bats.disabled) is disabled and never exercises a point-in-time restore.

Why it isn't really supported yet

Observed failure modes that prevent a reliable PITR (see Needs verification notice above):

• Recovery gets killed mid-flight. The restore init-job has no "bootstrap already in progress / completed" skip guard, so a GitOps reconcile (Flux) re-triggers and deletes the healthy in-progress recovery cluster before it finishes — recovery never converges.
• Target ahead of available WAL. A recoveryTime past the last archived WAL leaves the restore hanging with no clear surfaced error.
• No validation/feedback loop telling the user the valid recoverable window (earliest/latest restorable timestamp).

Goals / acceptance criteria

• A real PITR restore converges and is idempotent under GitOps reconcile — the restore init-job must skip when bootstrap is already in progress/completed, instead of deleting the recovering cluster.
• Enabled e2e test: back up → write marker data → restore with options.recoveryTime between the two writes → assert pre-marker data present and post-marker data absent.
• A recoveryTime past the available WAL fails fast with a clear status/condition rather than hanging.
• Working, uncommented recoveryTime example in examples/backups/postgres/.
• Docs state the supported recovery window and how to discover earliest/latest restorable time.

Out of scope

• PITR for engines using logical backups (MariaDB, ClickHouse).