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

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/

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()'

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

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"}'

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

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

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

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

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/

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

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

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"