E2E Test Case: in-cluster PostgreSQL major upgrade (v6 PostgreSQL 13 → v7 PostgreSQL 17)

A step-by-step end-to-end test for the majorUpgrade logical dump/restore mechanism (major-upgrade-pvc.yaml, major-upgrade-dump-job.yaml, major-upgrade-restore-job.yaml in the magda-postgres chart), run against a real cluster (e.g. minikube). It installs a v6 (PostgreSQL 13) release, seeds verifiable data, upgrades to a v7 (PostgreSQL 17) build with the migration enabled, and asserts the seeded data survived, the restore is idempotent on a repeat upgrade, and helm rollback returns to a working, intact PostgreSQL 13. This documents exactly the procedure for gating the v6 → v7 PostgreSQL upgrade — see the PostgreSQL major upgrade runbook for the operator-facing explanation of what each step does and why.

What it covers

  1. Install v6, seed data via the registry API and the auth API. In the default (useCombinedDb: true) topology this exercises two distinct databases even though only one is named auth: registry-api connects with no POSTGRES_DB set, so registry records land in the cluster’s default postgres database, not in a database named registry (there isn’t one) — see the note under step 1 below.
  2. Upgrade to v7 with majorUpgrade.enabled=true and a generous --timeout.
  3. Assert the migration ran correctly: both hook Jobs succeeded in the right order, the seeded rows are present in PostgreSQL 17 with the exact counts recorded in step 1, SELECT version() reports 17.x, the DB migrator Jobs ran after the restore without failing on pre-existing schema, the application is healthy, the old PostgreSQL 13 StatefulSet/PVC are untouched, and a repeat helm upgrade with the flag still on is a no-op (idempotency).
  4. (Optional) Re-enable in-cluster verify-full on the new instance: point global.postgresql.client.sslRootCertSecret.name at the PostgreSQL 17 generation’s -crt secret (combined-db-postgresql-pg17-crt, not the v6 combined-db-postgresql-crt, which no longer exists) and confirm every DB client verifies the server certificate against it. This exercises the runbook §4 / magda-postgres README “Client verification of the in-cluster CA” guidance — the generation-specific secret rename that an operator must apply as part of the upgrade.
  5. Assert rollback: helm rollback brings the PostgreSQL 13 StatefulSet back bound to its original PVC, with the seeded rows intact.

Prerequisites

export NS=pg-major-upgrade-e2e
export V6_VERSION=6.2.0            # the planned last v6 (PostgreSQL 13) release
export V7_VERSION=7.0.0-alpha.0    # the first v7 (PostgreSQL 17) alpha, cut after this work merges

1. Install v6 and seed data

kubectl create namespace "$NS"
helm install magda oci://ghcr.io/magda-io/charts/magda --version "$V6_VERSION" -n "$NS" \
  --wait --timeout 3600s
kubectl -n "$NS" rollout status statefulset/combined-db-postgresql --timeout=600s

--timeout 3600s matters here as much as it does for the upgrade in step 2. The five DB migrators are post-install hooks that run in sequence, and Helm’s default 5-minute timeout is not enough — especially on an emulated architecture (an x86_64 image on an ARM host), where a single migrator can take ~9 minutes on a cold image cache. A timeout here does not fail loudly and obviously: Helm reports Release "magda" failed: context canceled, leaves the release in failed state, and the migrators that never ran leave their databases missing — which looks like a partially working install rather than a timeout.

So verify the databases exist before seeding, rather than trusting Helm’s exit code:

kubectl -n "$NS" exec combined-db-postgresql-0 -- env PGPASSWORD="$PGPASSWORD" \
  psql -U postgres -h 127.0.0.1 -d postgres -tAc \
  "SELECT datname FROM pg_database WHERE datname NOT IN ('template0','template1') ORDER BY 1"

Expect auth, content, postgres and session (plus tenant if multi-tenancy is enabled). Remember the registry’s tables live in postgres — see step 3.

Expected: the v6 (PostgreSQL 13) instance, not the renamed one.

kubectl -n "$NS" get statefulset      # expect combined-db-postgresql (no -pg17)
kubectl -n "$NS" get pvc              # expect data-combined-db-postgresql-0

The DB migrator Jobs (registry-db-migrator, authorization-db-migrator, content-db-migrator, session-db-migrator, tenant-db-migrator — one per logical database, all pointed at the combined instance via their client-facing Service aliases) are post-install/post-upgrade hooks, so helm install already blocks until they succeed before returning; there is no separate wait needed. They are also deleted immediately on success (hook-delete-policy: hook-succeeded,before-hook-creation), so don’t expect to find them with kubectl get jobs afterwards.

Retrieve the DB password and mint an admin session JWT:

export PGPASSWORD=$(kubectl get secret -n "$NS" db-main-account-secret -o jsonpath='{.data.postgresql-password}' | base64 -d)
JWT_SECRET=$(kubectl get secret -n "$NS" auth-secrets -o jsonpath='{.data.jwt-secret}' | base64 -d)
yarn --silent acs-cmd jwt 00000000-0000-4000-8000-000000000000 "$JWT_SECRET" | tail -1 > /tmp/admin.jwt
kubectl -n "$NS" port-forward svc/gateway 18080:80 &

Seed a registry record:

DATASET_ID="pg-upgrade-e2e-$(date +%s)"
curl -s -X PUT "http://localhost:18080/api/v0/registry/records/$DATASET_ID" \
  -H "X-Magda-Session: $(cat /tmp/admin.jwt)" -H "Content-Type: application/json" -H "X-Magda-Tenant-Id: 0" \
  -d "{\"id\":\"$DATASET_ID\",\"name\":\"PG Upgrade E2E\",\"aspects\":{\"dcat-dataset-strings\":{\"title\":\"PG Upgrade E2E\"},\"publishing\":{\"state\":\"published\"}}}"

Seed a local auth user:

curl -s -X POST "http://localhost:18080/api/v0/auth/users" \
  -H "X-Magda-Session: $(cat /tmp/admin.jwt)" -H "Content-Type: application/json" \
  -d '{"displayName":"PG Upgrade E2E User","email":"[email protected]","source":"e2e-test","sourceId":"pg-upgrade-e2e-1"}'

Record exact counts in both databases — these are the numbers step 3 must reproduce:

kubectl -n "$NS" exec combined-db-postgresql-0 -- env PGPASSWORD="$PGPASSWORD" \
  psql -U postgres -h 127.0.0.1 -d postgres -tAc "SELECT count(*) FROM records;" | tee /tmp/registry-count-v6.txt
kubectl -n "$NS" exec combined-db-postgresql-0 -- env PGPASSWORD="$PGPASSWORD" \
  psql -U postgres -h 127.0.0.1 -d auth -tAc "SELECT count(*) FROM users;" | tee /tmp/auth-count-v6.txt

Note on -d postgres above: in the default (useCombinedDb: true) topology there is no database named registry — registry-api connects with POSTGRES_USER=client and no POSTGRES_DB, so registry-db-migrator’s Flyway migrations, and the registry’s actual data (records, aspects, events, recordaspects, webhooks, webhookevents, eventtypes), land in the connecting role’s default database: postgres. This is exactly the data the restore Job’s postgres database content check (added alongside this test case) exists to verify — RESTORED/EXPECTED in step 3a below deliberately exclude postgres from the whole-database count, so without that separate check this class of data loss would go unnoticed.

2. Upgrade to the v7 build with the migration enabled

helm upgrade magda oci://ghcr.io/magda-io/charts/magda --version "$V7_VERSION" -n "$NS" \
  --set magda-core.combined-db.magda-postgres.majorUpgrade.enabled=true \
  --timeout 3600s

Do not use --reuse-values for this upgrade. It reuses the v6 release’s computed values as the base, so the v7 chart’s own defaults never apply — and v7 deliberately restructured the PostgreSQL values contract (auth.*, primary.*, TLS). In practice the reused v6 tls shape leaves the new instance’s TLS listener off while clients still resolve sslmode: require, and the chart’s validate-tls guard aborts the upgrade before any hook runs. If you have custom values, re-supply them explicitly (-f my-values.yaml) rather than reusing the old release’s.

The magda-core. prefix is load-bearing. magda is an umbrella chart whose only real content is the magda-core subchart, so a value destined for magda-postgres has to be addressed through it. Omitting the prefix does not error — Helm accepts any --set path, known or not — it simply sets a value nothing reads. The migration is then silently skipped, the PostgreSQL 17 instance comes up empty, and the upgrade reports success. Verified against the published chart: combined-db.magda-postgres.majorUpgrade.enabled=true renders 0 migration objects; magda-core.combined-db.… renders 16. If you are templating magda-core directly rather than the umbrella (as the chart’s own render tests do), drop the prefix.

--timeout 3600s is deliberately generous — Helm’s default (5 minutes) is far too short for a real dump + restore, and it is Helm’s own --timeout, not majorUpgrade.waitTimeoutSeconds, that bounds this command.

3. Assert the migration ran correctly

a. Both hook Jobs succeeded, in order.

Both Jobs carry hook-delete-policy: before-hook-creation,hook-succeeded, so a Job that SUCCEEDS is deleted the moment it finishes and kubectl logs job/... will say “not found” once helm upgrade returns. (That policy is required for correctness: these Jobs’ pods mount the staging PVC, and a pod that outlives the release blocks the next upgrade’s PVC hook with pre-upgrade hooks failed: context deadline exceeded.) A Job that FAILS is not deleted, so failure logs are always available.

To capture a successful run’s logs, start this watcher before the helm upgrade in step 2 (it also works for step g below):

( for i in $(seq 1 3600); do
    for p in $(kubectl -n "$NS" get pods -l job-name -o name 2>/dev/null \
               | grep -E 'major-upgrade-(dump|restore)'); do
      ph=$(kubectl -n "$NS" get "$p" -o jsonpath='{.status.phase}' 2>/dev/null)
      [ "$ph" = "Succeeded" ] || [ "$ph" = "Failed" ] || continue
      f=/tmp/$(basename "$p").log
      [ -s "$f" ] || { echo "--- $p ($ph) ---" > "$f"; kubectl -n "$NS" logs "$p" >> "$f" 2>&1; }
    done
    sleep 1
  done ) &
WATCHER=$!

Then read /tmp/*major-upgrade-dump*.log and /tmp/*major-upgrade-restore*.log.

Expected: the dump log ends with Dump complete: <size> at /staging/dumpall.sql.gz; the restore log has, in order:

Remember kill $WATCHER at cleanup.

a2. The durable migration marker exists — this outlives the Jobs and is what makes a repeat upgrade a no-op:

kubectl -n "$NS" exec combined-db-postgresql-pg17-0 -- env PGPASSWORD="$PGPASSWORD" \
  psql -U postgres -h 127.0.0.1 -d postgres \
  -tAc "SELECT completed_at, databases_restored FROM public.magda_major_upgrade;"
# expect: exactly one row, databases_restored = N from the restore log above

b. The seeded rows are present in PostgreSQL 17 with the recorded counts — the actual point of the exercise:

kubectl -n "$NS" exec combined-db-postgresql-pg17-0 -- env PGPASSWORD="$PGPASSWORD" \
  psql -U postgres -h 127.0.0.1 -d postgres -tAc "SELECT count(*) FROM records;"
# expect: equals the value in /tmp/registry-count-v6.txt
kubectl -n "$NS" exec combined-db-postgresql-pg17-0 -- env PGPASSWORD="$PGPASSWORD" \
  psql -U postgres -h 127.0.0.1 -d auth -tAc "SELECT count(*) FROM users;"
# expect: equals the value in /tmp/auth-count-v6.txt
kubectl -n "$NS" exec combined-db-postgresql-pg17-0 -- env PGPASSWORD="$PGPASSWORD" \
  psql -U postgres -h 127.0.0.1 -d postgres -tAc "SELECT name FROM records WHERE recordid = '$DATASET_ID';" 2>/dev/null || true

c. The server is really PostgreSQL 17:

kubectl -n "$NS" exec combined-db-postgresql-pg17-0 -- env PGPASSWORD="$PGPASSWORD" \
  psql -U postgres -h 127.0.0.1 -tAc "SELECT version();"
# expect: PostgreSQL 17.5 ...

d. The DB migrator Jobs completed after the restore and did not fail on pre-existing schema. The migrator Jobs are post-upgrade hooks with hook-delete-policy: hook-succeeded,before-hook-creation, so on success Helm deletes them as part of processing the upgrade — by the time the helm upgrade command above returns, they are typically already gone, and a successful helm upgrade exit code already means every hook (including them) succeeded (a failed hook fails the release). Confirm they actually ran — rather than the restored data merely already matching the target schema — via Flyway’s own history table, which the restore brought over from v6 and the migrator must have appended to (or found already up to date) without any failed entries:

kubectl -n "$NS" exec combined-db-postgresql-pg17-0 -- env PGPASSWORD="$PGPASSWORD" \
  psql -U postgres -h 127.0.0.1 -d postgres -c \
  "SELECT installed_rank, version, description, success FROM flyway_schema_history ORDER BY installed_rank;"

Expected: the full migration history is present (nothing was lost by the dump/restore), and every row has success = t — in particular there is no row where a migrator, presented with schema+data restored from v6, tried to re-create objects that already existed and failed.

e. Application health:

curl -s -o /dev/null -w "%{http_code}\n" http://localhost:18080/api/v0/auth/users/whoami   # 200
curl -s "http://localhost:18080/api/v0/search/datasets?query=PG%20Upgrade%20E2E" \
  -H "X-Magda-Session: $(cat /tmp/admin.jwt)"   # expect the seeded dataset in the results

(If search doesn’t show the record immediately, allow a few seconds for the indexer to catch up — it consumes registry events, and the migration doesn’t change that.)

f. combined-db-postgresql-pg17 exists; the old instance and PVC are untouched:

kubectl -n "$NS" get statefulset   # expect ONLY combined-db-postgresql-pg17
kubectl -n "$NS" get pvc           # expect BOTH data-combined-db-postgresql-0 (old, untouched)
                                   # and data-combined-db-postgresql-pg17-0 (new)

The old StatefulSet is gone, not retained: v7 renders no combined-db-postgresql object, so Helm deletes it in the main pass. Its PVC survives, because StatefulSet-managed PVCs are not garbage-collected with the StatefulSet — and that is what makes step 5’s rollback work. You therefore cannot kubectl exec into the old pod to check the PostgreSQL 13 data at this point; verify it after the rollback in step 5 instead.

g. Idempotency — re-run helm upgrade with the flag still on:

Delete the captured logs from step (a) first so the watcher refills them, then:

rm -f /tmp/*major-upgrade-dump*.log /tmp/*major-upgrade-restore*.log
helm upgrade magda oci://ghcr.io/magda-io/charts/magda --version "$V7_VERSION" -n "$NS" \
  --set magda-core.combined-db.magda-postgres.majorUpgrade.enabled=true \
  --timeout 3600s
cat /tmp/*major-upgrade-dump*.log /tmp/*major-upgrade-restore*.log

Expected — the helm upgrade itself must succeed, which is the point of this step; a second upgrade used to fail here with pre-upgrade hooks failed: context deadline exceeded:

Then run it a third time and confirm it succeeds too — the failure mode this guards against only appeared from the second repeat onwards. Also note that the staging PVC’s uid changes on each of these upgrades (kubectl -n "$NS" get pvc combined-db-postgresql-pg17-major-upgrade -o jsonpath='{.metadata.uid}'): that is the delete-and-recreate completing, which is exactly what a leftover hook pod used to prevent.

4. (Optional) Re-enable in-cluster verify-full on the PostgreSQL 17 instance

This step proves the one piece of the runbook §4 verify-full guidance that a major upgrade actually exercises: after the upgrade, an operator who runs the magda-postgres README’s Client verification of the in-cluster CA recipe must re-point sslRootCertSecret.name at the new PostgreSQL generation’s -crt secret, because that secret name is tied to the PostgreSQL major version, not the Magda version, and the old one is gone.

Run it while still on PostgreSQL 17 — i.e. before the step 5 rollback, which tears this instance down. Reuse the live release from step 3; nothing here needs a fresh install.

Why this isn’t tested going into the upgrade. In-cluster client verify-full is a v7 feature (issue #3739), so the v6/PostgreSQL 13 source in step 1 could not have had it on — there is nothing to turn down for the dump window. The step 2 upgrade therefore already ran at the chart default sslmode: require, which is exactly what the runbook prescribes for the dump/restore Jobs: they only ever talk to the local instance, so server authentication is optional for them (issue #3739 confirms this). verify-full is a post-upgrade opt-in, which is what this step covers.

a. The generation-specific secret exists; the old one is gone. This rename is the whole hazard the recipe warns about — an operator who leaves sslRootCertSecret.name pointing at the v6 name after the upgrade points at a secret that no longer exists, and every DB client fails to start:

kubectl -n "$NS" get secret combined-db-postgresql-pg17-crt   # exists: holds ca.crt/tls.crt/tls.key
kubectl -n "$NS" get secret combined-db-postgresql-crt        # expect: NotFound (the v6 generation's secret is gone)

The chart auto-generates that certificate with SANs covering every logical Service name Magda dials (combined-db, authorization-db, content-db, registry-db, session-db) plus localhost/127.0.0.1, so verify-full works against it with no certificate wrangling — the CA the clients trust is the same -crt secret the StatefulSet already mounts.

b. Enable verify-full pointed at the new secret. Keep majorUpgrade off now (the migration is already done and recorded; leaving it on would only schedule no-op dump/restore Jobs):

helm upgrade magda oci://ghcr.io/magda-io/charts/magda --version "$V7_VERSION" -n "$NS" \
  --set global.postgresql.client.sslmode=verify-full \
  --set global.postgresql.client.sslRootCertSecret.name=combined-db-postgresql-pg17-crt \
  --timeout 3600s

sslRootCertSecret.key defaults to ca.crt — the key tls-secret.yaml writes — so it needs no override. This upgrade re-runs the DB migrator post-upgrade Jobs; their success is itself the proof that a verified connection works end-to-end — the migrators are the hybrid clients that use both psql (honouring PGSSLROOTCERT) and Flyway/pgjdbc (honouring the sslrootcert= URL parameter). A failed hook fails the release, so a green helm upgrade already means both verified-connection paths succeeded.

c. Spot-check the CA mount and confirm the server sees only TLS connections:

# CA mounted read-only at the fixed path in a DB-connecting workload:
kubectl -n "$NS" exec deploy/authorization-api -- ls -l /etc/magda/postgresql-ca/root.crt
# server side: no non-SSL client connection exists
kubectl -n "$NS" exec combined-db-postgresql-pg17-0 -- env PGPASSWORD="$PGPASSWORD" \
  psql -U postgres -h 127.0.0.1 -tAc \
  "SELECT count(*) FROM pg_stat_ssl s JOIN pg_stat_activity a USING (pid)
   WHERE a.usename IS NOT NULL AND NOT s.ssl;"
# expect: 0

And confirm the application still serves the seeded data over the now-verified connection (the port-forward from step 1 is still up):

curl -s "http://localhost:18080/api/v0/registry/records/$DATASET_ID" -H "X-Magda-Tenant-Id: 0"
# expect: the record seeded in step 1, served through registry-api's verify-full connection

d. (Optional negative) Prove verification is real, not silently disabled. Point the client at a CA that does not match the server and confirm it fails with a certificate error rather than connecting anyway. This mirrors the external-DB case’s negative check; keep it brief here since that case covers the wrong-CA path in depth:

openssl req -new -x509 -days 1 -nodes -subj "/CN=unrelated-ca" -out /tmp/wrong-ca.crt -keyout /tmp/wrong-ca.key 2>/dev/null
kubectl -n "$NS" create secret generic pg-ca-wrong --from-file=ca.crt=/tmp/wrong-ca.crt
helm upgrade magda oci://ghcr.io/magda-io/charts/magda --version "$V7_VERSION" -n "$NS" \
  --set global.postgresql.client.sslmode=verify-full \
  --set global.postgresql.client.sslRootCertSecret.name=pg-ca-wrong \
  --timeout 300s
# expect: the upgrade FAILS -- the post-upgrade migrator hooks error out
kubectl -n "$NS" logs job/authorization-db-migrator --tail=30 2>/dev/null | grep -iE "certificate|self-signed|verify failed"
# expect: an SSL/certificate-verification error, NOT connection-refused/timeout

Revert to the correct CA before continuing to the rollback:

helm upgrade magda oci://ghcr.io/magda-io/charts/magda --version "$V7_VERSION" -n "$NS" \
  --set global.postgresql.client.sslmode=verify-full \
  --set global.postgresql.client.sslRootCertSecret.name=combined-db-postgresql-pg17-crt \
  --timeout 3600s
kubectl -n "$NS" delete secret pg-ca-wrong
rm -f /tmp/wrong-ca.crt /tmp/wrong-ca.key

5. Assert rollback works

helm rollback magda 1 -n "$NS"
kubectl -n "$NS" rollout status statefulset/combined-db-postgresql --timeout=300s

Roll back to revision 1 explicitly, not with a bare helm rollback. Each idempotency repeat in step 3g, and the verify-full upgrade(s) in step 4, adds another v7 (PostgreSQL 17) revision, so a bare helm rollback (previous revision) would only land on another PostgreSQL 17 release, not the original v6/PostgreSQL 13 install. Revision 1 is that first install; confirm with helm history magda -n "$NS" if unsure.

Expected:

kubectl -n "$NS" get statefulset combined-db-postgresql   # back and Ready
kubectl -n "$NS" get pvc data-combined-db-postgresql-0     # same PVC, bound to the rolled-back StatefulSet
kubectl -n "$NS" exec combined-db-postgresql-0 -- env PGPASSWORD="$PGPASSWORD" \
  psql -U postgres -h 127.0.0.1 -d postgres -tAc "SELECT count(*) FROM records;"
# expect: still equals /tmp/registry-count-v6.txt -- rollback did not lose data
kubectl -n "$NS" exec combined-db-postgresql-0 -- env PGPASSWORD="$PGPASSWORD" \
  psql -U postgres -h 127.0.0.1 -d auth -tAc "SELECT count(*) FROM users;"
# expect: still equals /tmp/auth-count-v6.txt

Cleanup

kill %1 2>/dev/null   # the port-forward started in step 1
kill $WATCHER 2>/dev/null   # the hook-Job log watcher started in step 3a
helm uninstall magda -n "$NS"
kubectl delete namespace "$NS" --wait=true --timeout=180s
rm -f /tmp/admin.jwt /tmp/registry-count-v6.txt /tmp/auth-count-v6.txt
rm -f /tmp/*major-upgrade-dump*.log /tmp/*major-upgrade-restore*.log

Notes