Use version-controlled, idempotent migrations with lifecycle hooks, test locally with kind, and enforce policy-driven rollouts to avoid downtime and data loss.

Actionable Workflow

1. Prepare Migrations Locally

   • Store migration scripts in version control with semantic versioning (e.g., v1.2.0__add_users_table.sql).
   • Use kind to simulate cluster behavior:

     kind create cluster --name dev-db  
     kubectl apply -f db-deployment.yaml  
     

   • Test migrations against a local database instance (e.g., PostgreSQL via Docker).

2. Integrate with Kubernetes Lifecycle Hooks

   • Use a pre-start hook in your database deployment to run migrations:

     spec:  
       initContainers:  
       - name: migrate-db  
         image: your-migration-tool  
         command: ["sh", "-c", "migrate --up"]  
     

   • Ensure idempotency: Migrations should safely retry if the pod restarts.

3. Deploy with Staging and Rollout Policies

   • Apply migrations to a staging environment first.
   • Use Argo Rollouts or native Kubernetes rollouts with readiness probes to ensure zero-downtime deployments.

4. Monitor and Validate

   • Check database health post-migration:

     kubectl exec db-pod -- psql -U dbuser -c "SELECT 1;"  
     

   • Alert on migration failures (e.g., via Prometheus alerts on custom metrics).

Policy Example

Migration Policy v1

• Versioning: Scripts named with semantic versions (e.g., v1.0.0__initial_schema.sql).
• Idempotency: All migrations must be safe to rerun (e.g., CREATE TABLE IF NOT EXISTS).
• Approval: Migrations require code review and automated testing in CI before merge.
• Rollback: Provide a --down script for every migration to revert changes.

Tooling

• Liquibase/Flyway: For version-controlled database schema management.
• Kubernetes Init Containers: To run migrations before the main database container starts.
• kind: For local testing of migration behavior in a cluster-like environment.
• Prometheus/Grafana: To monitor database health and migration success metrics.

Tradeoffs and Caveats

• Downtime vs. Zero-Downtime: Pre-start hooks may cause brief unavailability. Use blue/green deployments for critical services.
• Idempotency Overhead: Writing safe migrations requires careful SQL design (e.g., avoiding DROP in reversible scripts).
• Testing Complexity: Local kind clusters may not fully replicate production database performance or scale.

Troubleshooting Common Failures

• Migration Fails Mid-Run:
  • Check logs: kubectl logs db-pod migrate-db.
  • Manually rerun the failed migration or roll back using the --down script.
• Pod Stuck in CrashLoopBackoff:
  • Verify database connection strings and credentials in Secrets.
  • Ensure migration script exits with code 0 on success, non-zero on failure.
• Schema Drift:
  • Use a schema diff tool (e.g., pg_diff) to compare dev and prod environments.
  • Enforce policy that all changes go through migration scripts.

Final Note

Database migrations in Kubernetes require disciplined versioning, testing, and monitoring. Prioritize simplicity over cleverness—idempotent, version-controlled scripts with clear rollback paths save lives in production.

Source thread: How should I handle database migrations in a Kubernetes cluster? ( using kind for local dev)