Point-in-Time Recovery (PITR)

PITR gives you sub-minute RPO: instead of restoring the last daily dump, you restore to any timestamp within your WAL archive window. AnvilBase ships wal-g inside the Postgres image plus a wal-g orchestration sidecar.

WAL archiving is on by default (Phase 11), and it degrades gracefully: the Postgres image’s archive_command is a wrapper that no-ops when no object store is configured (WALG_S3_PREFIX empty), so a stack that hasn’t set up S3 still boots and runs — Postgres never wedges on failing archives, PITR is simply inactive until you point it at a bucket. Configure object storage to make it live.

PITR is instance-level — restores affect the whole instance

AnvilBase isolates each project in its own database, but WAL is shared across every project on the Postgres instance. A PITR restore replays that shared WAL, so it rolls back the entire Postgres instance to the target timestamp — every project, not just one. There is no per-project PITR with a shared WAL stream. The control plane therefore never runs the destructive restore in place: it stages the restore (validates feasibility, fetches the basebackup into an isolated directory, writes a recovery manifest) and the destructive cutover is an operator-gated runbook step you run during a maintenance window. Plan a PITR restore as an instance-wide event.

One-time setup

Point archiving at object storage in .env. Archiving is already on by default; you only need to give it a reachable bucket (the compose default targets the in-stack MinIO):
Terminal window
```
ANVILBASE_WALG_ENABLED=true                  # default; set false to disable
WALG_S3_PREFIX=s3://anvilbase-wal/           # empty => archive_command no-ops (PITR inactive)
WALG_AWS_ENDPOINT=http://minio:9000
WALG_LIBSODIUM_KEY=$(openssl rand -hex 32)   # KEEP SAFE — losing it loses recovery
```
If WALG_S3_PREFIX is left empty, the archive_command wrapper skips the push and returns success — the stack runs normally, but nothing is archived and PITR stays inactive until you set a prefix.

Create the WAL bucket (once):

docker compose exec minio mc alias set local http://127.0.0.1:9000 \
  "$MINIO_ROOT_USER" "$MINIO_ROOT_PASSWORD"
docker compose exec minio mc mb --ignore-existing local/anvilbase-wal

Restart Postgres to activate archive_mode (it’s a full-restart-only setting):

docker compose restart postgres
docker compose logs postgres | grep archive_mode    # expect: on

Seed the first base backup (nothing is restorable until this exists):
Terminal window
```
docker compose run --rm wal-g backup-push /var/lib/postgresql/data
docker compose run --rm wal-g backup-list --detail --json
```
You can keep doing this by hand, but most operators enable the automatic scheduler below so basebackups (and retention) run on their own.

Automatic scheduled basebackups

Continuous WAL archiving is always on, but PITR also needs periodic physical basebackups as restore starting points — until one exists, the restore-point list stays empty. AnvilBase can create them on a schedule for you. This is opt-in and off by default (a plain docker compose up never starts it), so an existing stack is unaffected until you turn it on. It complements WAL archiving: basebackups are the anchor, the WAL stream replays forward from there to any timestamp.

Compose

Enable the profile-gated wal-g-scheduler sidecar:

docker compose --profile wal-g-scheduler up -d wal-g-scheduler

It runs a loop that, on each interval, does wal-g backup-push $PGDATA and then prunes old FULL backups. Tune it in .env:

Variable	Default	Meaning
`ANVILBASE_BASEBACKUP_INTERVAL`	`86400` (24h)	Seconds between basebackups
`ANVILBASE_BASEBACKUP_RETAIN_COUNT`	`7`	Keep the N most-recent FULL backups (`0` disables pruning)
`ANVILBASE_BASEBACKUP_INITIAL_DELAY`	`0`	Seconds to wait before the first push

The scheduler runs in a wal-g sidecar (not the control plane) because backup-push needs filesystem access to the live $PGDATA, which only the wal-g sidecars mount. A failed push or prune is logged and the loop continues to the next interval (a transient object-store blip never permanently stops backups); SIGTERM stops it cleanly. It needs object storage configured (WALG_S3_PREFIX + AWS_* — the defaults target the in-stack MinIO bucket).

Disable it again by stopping the profiled service:

docker compose --profile wal-g-scheduler down

Helm

Set the (default-off) flag in your values:

postgres:
  walg:
    enabled: true
    s3Prefix: s3://anvilbase-wal/
    scheduledBasebackups:
      enabled: true          # off by default
      schedule: "0 2 * * *"  # daily at 02:00 (cron)
      retainCount: 7         # 0 disables pruning

This renders a CronJob that runs the same scheduler in run-once mode (one backup-push + prune per fire). Because the postgres PVC is ReadWriteOnce, the Job is pinned (pod affinity) to the postgres pod’s node so it can mount the live data dir — expected and handled on single-node and non-shared-storage clusters.

Verify PITR is live

curl -s -H "Authorization: Bearer $ANVILBASE_TOKEN" \
  http://localhost:39001/api/v1/projects/<id>/pitr/restore-points

Healthy response lists restore points; an empty list with a note describes what’s missing (usually the seed step or an unreachable bucket).

Grant the app role pg_monitor so PITR can compute data-loss estimates:
Terminal window
docker compose exec postgres psql -U supabase_admin -d anvilbase_platform \
  -c 'GRANT pg_monitor TO anvilbase;'
Images built after 2026-04-21 do this automatically.

Preview before you restore (always)

anvilbase pitr restore --project <id> \
  --target-time 2026-06-04T13:45:00Z --dry-run

The preview (GET /api/v1/projects/<id>/pitr/restore/preview) reports:

basebackup — the starting point (latest base backup at or before the target),
WAL window — the replay range (basebackup finish → target),
data loss — seconds of writes that would be rewound,
warnings — target before the earliest backup, beyond the archive tip, etc.

Trigger a restore

anvilbase pitr restore --project <id> --target-time 2026-06-04T13:45:00Z

This returns 202 Accepted and a restore-job id. The 202 body carries instance_wide: true and operator_cutover_required: true to remind you the eventual restore affects the whole instance and that you must run the cutover.

What the control plane does automatically (non-destructive)

A background restore worker drains the queued job and runs only the part it can do safely against the live, shared instance:

Validates feasibility — the target must fall within the wal-g window and have a viable basebackup (same checks as the preview).
Fetches the basebackup with wal-g backup-fetch into an isolated staging directory (ANVILBASE_PITR_STAGING_DIR, default /var/lib/anvilbase/pitr-staging/<job-id>) — never into the live $PGDATA.
Writes a recovery manifest (anvilbase-recovery-manifest.json) next to the staged basebackup, containing the exact recovery_target_time and the operator cutover steps.
Transitions the job to prepared (not completed — the database has not been recovered yet) and writes an audit event (pitr.restore.prepared). On any error the job is failed with an error_message and a pitr.restore.failed audit event.

The worker claims jobs with FOR UPDATE SKIP LOCKED, so it is safe to run multiple control-plane replicas. A job stuck in_progress past a TTL — measured from when it was claimed, not created — is automatically returned to pending and retried (a freshly-claimed, still-running job is never reclaimed, so two workers can’t double-stage into the same directory).

Staging volume needs capacity and pruning

Each staged restore fetches a full basebackup (tens to hundreds of GB) into ANVILBASE_PITR_STAGING_DIR. Failed stages are swept automatically, but a successful prepared stage is kept (you need it for the cutover) — prune it after you cut over (or abandon) the job, and size the volume for at least one full basebackup. Point ANVILBASE_PITR_STAGING_DIR at a roomy volume outside $PGDATA (the worker refuses any staging path that overlaps the live data dir).

The operator-gated cutover (destructive, instance-wide)

Because the cutover is destructive, instance-wide (shared WAL), and needs a Postgres restart the control plane can’t safely perform, you run it by hand during a maintenance window — guided by the staged recovery manifest:

# Stop app traffic + postgres
docker compose stop control-plane auth realtime webhooks
docker compose stop postgres

# Put the staged basebackup in place (the manifest records the staging dir),
# then add a recovery.signal + recovery_target_time = <manifest target>, and start.
# (The wal-g sidecar can also fetch directly if you prefer:)
docker compose run --rm wal-g backup-fetch /var/lib/postgresql/data LATEST
# add recovery.signal + recovery_target_time, then start postgres
docker compose start postgres

(The disaster-recovery runbook has the exact volume-path commands.) PITR is a roll-back mechanism — targets more than 5 minutes in the future are rejected.

Tuning RPO

archive_timeout = 60 closes a WAL segment every minute even when idle.
wal-g uploads on segment close; object-store latency adds a few seconds. So worst-case RPO ≈ archive_timeout + upload latency.
For tighter RPO, lower archive_timeout — at the cost of more bucket writes.

Retention

If you use the automatic scheduler, it already prunes FULL basebackups after each push (keeping ANVILBASE_BASEBACKUP_RETAIN_COUNT, default 7) via wal-g delete retain FULL <n> --confirm — you don’t need a separate cron for basebackup retention.

Otherwise (manual basebackups), wal-g doesn’t prune automatically — schedule a nightly cleanup yourself:

# Keep 7 base backups and 14 days of WAL
docker compose run --rm wal-g delete retain FULL 7 --confirm
docker compose run --rm wal-g delete before FIND_FULL \
  $(date -d '14 days ago' -u +%Y-%m-%dT%H:%M:%SZ) --confirm

The scheduler prunes basebackups only. WAL retention (delete before FIND_FULL …) is still a separate cleanup if you want to trim the WAL stream.

PITR vs logical backups

	Logical backup (`pg_dump`)	PITR (wal-g)
RPO	last dump (hours)	seconds
Restore granularity	the backup point	any timestamp in the window
Setup	none	one-time archiving setup
Storage cost	per-dump	continuous WAL

Run both: periodic logical backups for portability and a known-good baseline, PITR for tight RPO. See Backups & Restore.

Next: Monitoring.