---
id: w1/m17/t001
title: Backups + PITR/recovery — CNPG backups → object storage + recovery-info/recover/exports
worker: worker1
status: done
estimate: 50m
depends_on: []
---

## Objective

Give managed Postgres real data protection: configure CNPG scheduled backups + WAL archiving to object storage so a `Database` can be recovered to any point in time, then expose Render's `recovery-info` / `recover` / `exports` surfaces over REST/GraphQL/MCP. `recover` restores to a **new** `Database` instance (never in place), matching Render, and `exports` produces logical `pg_dump`-style exports.

## Context

- CNPG backup mechanism is `spec.backup.barmanObjectStore` + `ScheduledBackup` (WAL archiving → PITR); see `docs/ADR009-postgresql-management.md` §5 "durability" axis and the deferred note (line ~128 of `docs/ADR006-bex-api.md`): PITR "needs backups".
- Object-storage precedent to reuse: `docs/ADR011-etcd-backup-restore.md` and `docs/ADR015-openbao-backup-restore.md` (nightly snapshot → object storage) — same bucket/credentials pattern.
- Postgres feature lives at `lego/backend/internal/postgres/` (`service.go` + `rest.go`, `graphql.go`, `mcp.go`); the `Database` CRD is `lego/types/v1alpha1/database_types.go`; the CNPG-projecting controller is `lego/operator/internal/controller/database_controller.go`.
- Render maps: `POST /postgres/{id}/recovery-info`, `POST /postgres/{id}/recover`, `GET/POST /postgres/{id}/exports`.
- Tier durability differs per plan (`lego/types/tiers/tiers.yaml`) — only backed-up plans have recovery info.

## Steps

1. Add backup config to the `Database` CRD/plan mapping and project it onto the CNPG `Cluster` (`spec.backup.barmanObjectStore` + a `ScheduledBackup`) in the database controller, keyed off the plan's durability axis; wire the object-store bucket/credentials the same way the etcd/OpenBao runbooks do.
2. Add `RecoveryInfo`, `Recover`, and `Exports` Core verbs to the postgres `Service`: `RecoveryInfo` returns earliest/latest recoverable timestamps + backup list; `Recover` creates a **new** `Database` restored from a source + target time (CNPG `bootstrap.recovery`); `Exports` triggers/lists logical dumps.
3. Add the thin adapter fragments in `rest.go` (`/v1/postgres/{name}/recovery-info`, `/recover`, `/exports`), `graphql.go` (queries/mutations), and `mcp.go` (tools), all delegating to the same Core verbs.
4. Update `docs/ADR009-postgresql-management.md` + the Postgres section of `docs/ADR006-bex-api.md` (move these out of "Deferred"); run prettier on changed markdown.

## Files

- `lego/types/v1alpha1/database_types.go`
- `lego/types/tiers/tiers.yaml`
- `lego/operator/internal/controller/database_controller.go`
- `lego/backend/internal/postgres/service.go`
- `lego/backend/internal/postgres/rest.go`
- `lego/backend/internal/postgres/graphql.go`
- `lego/backend/internal/postgres/mcp.go`
- `docs/ADR009-postgresql-management.md`, `docs/ADR006-bex-api.md`

## Acceptance criteria

- [ ] A backed-up plan's CNPG `Cluster` gets `barmanObjectStore` + a `ScheduledBackup`; WAL archiving is on and a base backup lands in object storage.
- [ ] `GET/POST /v1/postgres/{name}/recovery-info` returns earliest/latest recoverable time + the backup list; a free/no-backup plan returns an empty/disabled recovery info rather than an error.
- [ ] `POST /v1/postgres/{name}/recover` provisions a **new** `Database` restored to the requested point in time (source instance untouched); parity over GraphQL + MCP.
- [ ] `exports` produces a logical export and lists prior exports over all three adapters.
- [ ] `make test` green; docs updated + prettier-clean.

## Out of scope

- HA / read-replica-based recovery (deferred to `w1/013`).
- Dashboard Recovery tab (that's t004).
- Cross-region / cross-cluster restore.
