Cut over production Balancer from AWS RDS to CloudNativePG, then decommission RDS

Sub-issue of #440. Follow-up to #464, which was closed on PR #514 — that changed config defaults only, so the production cutover it listed never happened.

**Production Balancer still reads from AWS RDS. The instance cannot be spun down yet.**

## State as of 2026-08-13

| | |
|---|---|
| Sandbox | On cnpg since 2026-06-23 (cfp-sandbox-cluster#171, #174) |
| Production | Still on RDS. No cnpg on `cfp-live-cluster`; sealed `SQL_HOST` unchanged since 2025-12-06 |
| pgvector | Confirmed available — `vector 0.8.2` on sandbox, via `Database.spec.extensions` |
| Data copied from RDS | Never. Sandbox schema was built fresh by Django migrations |
| Backups | None, on either cnpg cluster. RDS automated snapshots are the only copy |

Backups come before the data does. Migrating first trades RDS snapshots for nothing.

---

## Action items

### 1. Pin sandbox to v1.1.7, bump image off the dev build

PR: **[cfp-sandbox-cluster#193](https://github.com/CodeForPhilly/cfp-sandbox-cluster/pull/193)** — ready to merge, independent of everything below.

```bash
gh pr diff 193 --repo CodeForPhilly/cfp-sandbox-cluster
gh pr merge 193 --repo CodeForPhilly/cfp-sandbox-cluster --squash

# after the k8s-manifests build lands
kubectl -n balancer get deploy balancer -o jsonpath='{.spec.template.spec.containers[0].image}{"\n"}'
kubectl -n balancer get pods
curl -so /dev/null -w '%{http_code}\n' https://balancer.sandbox.k8s.phl.io/admin/login/
```

### 2. Install the cnpg operator + `shared-cluster` on live

PR: **[cfp-live-cluster#182](https://github.com/CodeForPhilly/cfp-live-cluster/pull/182)** *(draft — wants @themightychris)*

```bash
gh pr ready 182 --repo CodeForPhilly/cfp-live-cluster
gh pr merge 182 --repo CodeForPhilly/cfp-live-cluster --squash

kubectl -n cloudnative-pg get pods
kubectl -n cloudnative-pg get cluster shared-cluster
kubectl -n cloudnative-pg exec shared-cluster-1 -- psql -tAc 'select version()'
```

### 3. Seal `balancer-db-credentials`

**Blocks [cfp-live-cluster#183](https://github.com/CodeForPhilly/cfp-live-cluster/pull/183).** cnpg errors on a role whose `passwordSecret` is absent. Not deliverable as a PR by itself — needs the cluster's sealing cert.

```bash
kubectl -n sealed-secrets get deploy # confirm controller name/namespace first

PW="$(openssl rand -base64 32 | tr -dc 'A-Za-z0-9' | head -c 32)"
kubectl create secret generic balancer-db-credentials \
--namespace cloudnative-pg \
--type kubernetes.io/basic-auth \
--from-literal=username=balancer \
--from-literal=password="$PW" \
--dry-run=client -o yaml \
| kubeseal --controller-name sealed-secrets --controller-namespace sealed-secrets --format yaml \
> cloudnative-pg.secrets/balancer-db-credentials.yaml
```

Keep `$PW` — step 8 needs it. Commit the sealed file to `cfp-live-cluster` (it can ride on #183 or go in its own PR).

### 4. Add the managed role + `Database/balancer`

PR: **[cfp-live-cluster#183](https://github.com/CodeForPhilly/cfp-live-cluster/pull/183)** *(draft, stacked on #182, blocked by step 3)*

```bash
gh pr ready 183 --repo CodeForPhilly/cfp-live-cluster
gh pr merge 183 --repo CodeForPhilly/cfp-live-cluster --squash

# cnpg ignores a newly-created passwordSecret on periodic resync — force it
kubectl annotate cluster -n cloudnative-pg shared-cluster \
cnpg.io/reconciliationLoop="$(date +%s)" --overwrite

kubectl -n cloudnative-pg get database balancer -o jsonpath='{.status.applied}{"\n"}'
kubectl -n cloudnative-pg get database balancer -o jsonpath='{.status.extensions[0].applied}{"\n"}'
```

### 5. Read the production RDS connection, record snapshot retention

No PR — the value is SealedSecret ciphertext and only the cluster has it.

```bash
for k in SQL_HOST SQL_PORT SQL_DATABASE SQL_USER SQL_PASSWORD; do
printf '%s=%s\n' "$k" "$(kubectl -n balancer get secret balancer-config -o jsonpath="{.data.$k}" | base64 -d)"
done

# instance id is the first label of SQL_HOST, e.g. balancer-jj
aws rds describe-db-instances --db-instance-identifier "$RDS_ID" \
--query 'DBInstances[0].{Retention:BackupRetentionPeriod,Window:PreferredBackupWindow,Class:DBInstanceClass,Storage:AllocatedStorage,Engine:EngineVersion}'
aws rds describe-db-snapshots --db-instance-identifier "$RDS_ID" \
--query 'DBSnapshots[].{Id:DBSnapshotIdentifier,Type:SnapshotType,Created:SnapshotCreateTime}' --output table
```

### 6. Dump RDS

No PR. This is the artifact that answers "does anyone have a backup?", and it doubles as the local-dev bootstrap @themightychris proposed in #464.

```bash
kubectl -n balancer run pgdump --rm -i --quiet --restart=Never \
--image=ghcr.io/cloudnative-pg/postgis:18-3-system-trixie \
--env="PGPASSWORD=$SQL_PASSWORD" -- \
pg_dump --format=custom --no-owner --no-privileges \
-h "$SQL_HOST" -p "$SQL_PORT" -U "$SQL_USER" -d "$SQL_DATABASE" \
> "balancer-$(date +%F).dump"

pg_restore --list balancer-*.dump | head # non-empty = readable dump
ls -lh balancer-*.dump
```

Upload to the bucket from step 9 once it exists.

### 7. Restore into cnpg and check parity

No PR.

```bash
kubectl -n cloudnative-pg exec -i shared-cluster-1 -- \
pg_restore -d balancer --no-owner --no-privileges < balancer-*.dump

# exact row counts, not n_live_tup estimates
COUNTS="select relname, (xpath('/row/c/text()',
query_to_xml(format('select count(*) as c from %I.%I', schemaname, relname),
false, true, '')))[1]::text::int as rows
from pg_stat_user_tables order by relname;"

PGPASSWORD="$SQL_PASSWORD" psql -h "$SQL_HOST" -U "$SQL_USER" -d "$SQL_DATABASE" -tAc "$COUNTS" > /tmp/rds.txt
kubectl -n cloudnative-pg exec shared-cluster-1 -- psql -d balancer -tAc "$COUNTS" > /tmp/cnpg.txt
diff /tmp/rds.txt /tmp/cnpg.txt && echo "row counts match"

kubectl -n cloudnative-pg exec shared-cluster-1 -- psql -d balancer -tAc \
'select extname, extversion from pg_extension'
kubectl -n balancer exec deploy/balancer -- python manage.py migrate --check
```

### 8. Cut production over

Needs a PR to `cfp-live-cluster` — re-sealed secret plus the image bump, one commit, no PR open yet because it depends on steps 3–7.

```bash
kubectl create secret generic balancer-config \
--namespace balancer \
--from-literal=SQL_HOST=shared-cluster-rw.cloudnative-pg.svc.cluster.local \
--from-literal=SQL_PORT=5432 \
--from-literal=SQL_DATABASE=balancer \
--from-literal=SQL_USER=balancer \
--from-literal=SQL_PASSWORD="$PW" \
--from-literal=SQL_ENGINE=django.db.backends.postgresql \
--from-literal=SECRET_KEY="$SECRET_KEY" \
--from-literal=OPENAI_API_KEY="$OPENAI_API_KEY" \
--from-literal=PINECONE_API_KEY="$PINECONE_API_KEY" \
--dry-run=client -o yaml \
| kubeseal --controller-name sealed-secrets --controller-namespace sealed-secrets --format yaml \
> balancer.secrets/balancer-config.yaml
```

Carry every existing key across — read them off the live Secret first with the loop from step 5, or the app comes up missing `SECRET_KEY`. Bump `newTag: "1.1.5"` in `balancer/app/kustomization.yaml` in the same commit.

```bash
kubectl -n balancer rollout status deploy/balancer
kubectl -n balancer exec deploy/balancer -- printenv SQL_HOST
curl -so /dev/null -w '%{http_code}\n' https://balancerproject.org/
```

### 9. Object store + `ScheduledBackup` on both clusters

No PR yet — needs a bucket and credentials that do not exist. **This gates step 10.**

```bash
linode-cli obj mb cfp-cnpg-backups
linode-cli obj-sts key-create --label cnpg-backups --bucket cfp-cnpg-backups

kubectl create secret generic cnpg-backup-creds \
--namespace cloudnative-pg \
--from-literal=ACCESS_KEY_ID="$AK" \
--from-literal=ACCESS_SECRET_KEY="$SK" \
--dry-run=client -o yaml \
| kubeseal --controller-name sealed-secrets --controller-namespace sealed-secrets --format yaml \
> cloudnative-pg.secrets/cnpg-backup-creds.yaml
```

Then add `backup.barmanObjectStore` to `shared-cluster.yaml` plus a nightly `ScheduledBackup`, in **both** cluster repos, and confirm the first run completes:

```bash
kubectl -n cloudnative-pg get scheduledbackup
kubectl -n cloudnative-pg get backup -w
```

### 10. Decommission RDS

**Destructive and irreversible. Only after steps 7 and 9 have both passed, and production has run on cnpg long enough to trust.** Take the final snapshot and confirm it reports `available` before deleting anything. Keep the step-6 dump for roughly 90 days regardless.

```bash
SNAP="balancer-final-$(date +%Y%m%d)"
aws rds create-db-snapshot --db-instance-identifier "$RDS_ID" --db-snapshot-identifier "$SNAP"
aws rds wait db-snapshot-available --db-snapshot-identifier "$SNAP"

aws rds delete-db-instance --db-instance-identifier "$RDS_ID" --final-db-snapshot-identifier "${SNAP}-final"
```

---

## Notes

No balancer-main code change is needed: `deploy/manifests/balancer/base/` already defaults to the cnpg host, and `settings.py` branches on `.svc.cluster.local` at runtime.

The blue-green split from #464 is dropped — it needs dual-write or read-only tolerance to mean anything, and Balancer is a single-replica Django app. A maintenance window with the step-6 dump as rollback is smaller and safer.

#162's closing comment says the RDS instance "is now unused, coordinate to spin it down." True for sandbox only. Acting on it takes production offline.

Two more gotchas already paid for in sandbox (cfp-sandbox-cluster#162), beyond the reconcile annotation in step 4: the `balancer` role has no SUPERUSER, so extensions must go through `Database.spec.extensions` (cnpg >= v1.27); and #507 moved the Service port `8000 -> 80` (targetPort still 8000), so HTTPRoute `backendRefs.port` has to track it.

## Open questions

- Who has AWS credentials for steps 5, 6 and 10 — @taichan03 or @sahilds1?
- Maintenance window acceptable, or does someone want blue-green?
- Does live get a shared multi-tenant `shared-cluster` (@themightychris's stated goal) or a Balancer-only one?