Backup and restore

What exists, and what does not

Neon point-in-time recovery	✅ within the plan's retention window
Off-Neon dump to R2	⚠️ written, not yet running — needs a scoped credential
Asset backup (avatars, documents)	❌ none
Tested restore procedure	✅ executed 2026-08-28, results below

Neon's own retention covers "I deleted the wrong thing an hour ago". It does not cover "Neon is gone, or the account is" — one vendor holding both the data and its only backup is not a backup strategy, it is a single point of failure with good uptime.

The restore, actually performed

Not a procedure someone believes would work. This ran on 2026-08-28 against the live databases:

production before   namespaces=448  seeds=444  pricing=6  users=2
dump                155,824 bytes (pg_dump custom format)
restore into QA     clean, no errors
QA after            namespaces=448  seeds=444  pricing=6  users=2
production after    namespaces=448  seeds=444  pricing=6  users=2   (untouched)

Running it

pg_dump and pg_restore are not installed locally; run them from a pod in the cluster, where the postgres:18-alpine image is a pull away and the database is already reachable.

ssh root@appsterisk
kubectl -n appsterisk-prd run pgtools --rm -it --restart=Never \
  --image=postgres:18-alpine --command -- sh

Then, inside:

# sslrootcert=system is not optional. The connection string pins
# sslmode=verify-full, and pg_dump honours that strictly: without a trusted root
# it fails with "root certificate file /root/.postgresql/root.crt does not
# exist" — which reads like a missing file rather than a TLS policy.
PRD="postgresql://…?sslmode=verify-full&sslrootcert=system"

pg_dump --no-owner --no-privileges --format=custom "$PRD" > /tmp/prd.dump

Restore is destructive — --clean --if-exists drops what it replaces. Restore into appsterisk_qa unless you intend to overwrite production:

pg_restore --no-owner --no-privileges --clean --if-exists -d "$QA" /tmp/prd.dump

Verify by counting, not by the absence of errors:

psql "$QA" -tAc 'select count(*) from "Namespace"'

The scheduled backup

A daily CronJob dumps the database and uploads it to the appsterisk-backups R2 bucket under a date-partitioned key, pruning anything older than the retention window.

R2 rather than another Neon feature, because the point is a copy that survives Neon. It also depends on neither Hetzner nor Neon, so a failure in either leaves it readable.

Its own bucket, not a prefix inside appsterisk-assets. A dump contains every private field in the product; avatars are published to the world. One misconfigured policy should not reach both, and a prefix is not an access boundary.

:::warning Not running yet The bucket exists and the job is written. It needs an R2 API token scoped to appsterisk-backups — the existing R2 credential is scoped to appsterisk-assets and returns 403 here, which is the separation working rather than a problem to route around. :::

Knowing whether it ran

A backup nobody checks is a backup nobody has. A CronJob that fails is invisible on this cluster: nothing scrapes Job status, and a finished pod cannot be scraped.

So the job writes a _last-success marker object to the bucket after a successful upload. "Is the backup fresh?" becomes a single object listing, answerable by a person today and by a monitor when one exists.

That is weaker than Webhookr's approach, which pushes a heartbeat metric to a collector and alerts on the timestamp going stale. Appsterisk has no collector and no alerting at all, so nothing will page anyone — the marker only makes the answer checkable, not noticed. Closing that is the observability work, not this document.

What is not backed up

Avatars and claim documents in R2. They exist in exactly one place. R2 has no versioning enabled on these buckets, so a deletion is permanent.

The encryption key. It is in the cluster secret and in appsterisk-infra/.env — the same value twice under one operator's control, which is not a backup. Losing it makes every backup useless for the sealed columns, because a dump preserves ciphertext and nothing else. See key rotation.

This is the most valuable unaddressed risk in the system: a perfect database backup plus a lost key restores namespaces and ownership, and loses every private contact and every piece of claim evidence.