Migration Job

Migration Job

A Database Migration Job for BookNest

db-init creates the schema; later releases change it with numbered SQL files, applied by a Job named after the release (a Job's template is immutable) before the new API rolls out. The first indexes the genre filter:

db/migrations/002-genre-index.sql: the first schema change after 1.4SQL

CREATE INDEX IF NOT EXISTS books_genre_idx ON books (genre);

k8s/db-migrate-job.yaml: apply the migrations with psqlYAML

apiVersion: batch/v1
kind: Job
metadata: { name: db-migrate-1-5, labels: { app: booknest } }
spec:
  activeDeadlineSeconds: 300
  ttlSecondsAfterFinished: 86400
  template:
    spec:
      restartPolicy: Never
      containers:
      - name: migrate
        image: localhost:33500/postgres:18
        command: [sh, -c, 'for f in /migrations/*.sql; do psql -v ON_ERROR_STOP=1 -f "$f"; done']
        envFrom: [{ configMapRef: { name: api-config } }]
        env:
        - name: PGUSER
          valueFrom: { secretKeyRef: { name: db-credentials, key: username } }
        - name: PGPASSWORD
          valueFrom: { secretKeyRef: { name: db-credentials, key: password } }
        volumeMounts: [{ name: migrations, mountPath: /migrations }]
      volumes: [{ name: migrations, configMap: { name: db-migrations } }]

Shipping the migrations as a ConfigMap and running the JobShell

kubectl create configmap db-migrations --from-file=db/migrations/
kubectl apply -f k8s/db-migrate-job.yaml
kubectl wait --for=condition=Complete job/db-migrate-1-5 --timeout=120s
kubectl logs job/db-migrate-1-5
git add db/migrations k8s/db-migrate-job.yaml
git commit -qm "Add a migration Job and a genre index"

Output

configmap/db-migrations created
job.batch/db-migrate-1-5 created
job.batch/db-migrate-1-5 condition met
CREATE INDEX

IF NOT EXISTS makes a retry harmless. Migration tools such as node-pg-migrate 1,487 , Flyway 14,820 , Liquibase 51,363 and Atlas also record which files have run. What matters most is order: the Job must finish before new API Pods start, whether a pipeline runs it first (Jenkins), Helm 29,435 as a pre-upgrade hook (Hooks and Dependencies) or Argo CD 126 as a PreSync hook (GitOps with Argo CD). Keep migrations backward-compatible so old Pods work during the rollout.

MENU