pal-e-docs

SOP: Database Migration Recovery

sop-db-migration-recovery Sop

sop active
Project

SOP: Database Migration Recovery

Purpose: Teach agents how to diagnose and recover from failed Alembic migrations, data inconsistency after deployments, and rollback scenarios. Covers both the legacy SQLite failures and current Postgres operations.
Traceability: plan-pal-e-agency → Phase 5 (Error Recovery SOPs)

Critical Principle

Merged does NOT equal deployed does NOT equal data consistent. A migration PR that merges successfully can still fail at deployment (pod crash on startup) or succeed at deployment but leave data in an inconsistent state (e.g., new columns exist but old rows have no data). Always verify all three layers.

Failure Modes

Symptom Likely Cause Recovery Steps
Pod in <strong>CrashLoopBackOff</strong> after migration PR merge Alembic migration failed on startup. The app runs <code>alembic upgrade head</code> at boot. 1. Get pod logs: <code>kubectl logs -n NAMESPACE POD --previous</code>. 2. Look for Alembic error messages (duplicate column, missing table, constraint violation). 3. If Postgres: the migration is transactional — it should have rolled back cleanly. Check if <code>alembic_version</code> table still shows the old revision. 4. Fix the migration script and redeploy.
<strong>SQLite partial migration</strong> — "duplicate column name" on every restart (LEGACY) SQLite cannot do transactional DDL. Each ALTER TABLE auto-commits immediately. A multi-step migration that fails mid-way leaves the DB partially altered with <code>alembic_version</code> not stamped. This caused two production outages (PR #29, PR #61). 1. This should no longer occur — pal-e-docs migrated to Postgres. 2. If hit on another SQLite-backed service: manually stamp the alembic version and fix the schema via <code>sqlite3</code>. 3. Long-term fix: migrate to Postgres. SQLite + Alembic is a known-bad combination for DDL.
<strong>Data inconsistency</strong> — migration ran but old rows missing new data Migration added new columns/tables but did not backfill existing data. Or: notes created between the backfill and the deployment have no blocks (the 58-note gap from 7e). 1. Identify the gap: query for rows where the new column is NULL or the new table has no matching rows. 2. Write and run a backfill script. 3. For pal-e-docs: use <code>kubectl exec</code> into the pod, noting that the pod uses <code>PALDOCS_DATABASE_URL</code> (not <code>DATABASE_URL</code>). Backfill scripts need: <code>sh -c 'DATABASE_URL="$PALDOCS_DATABASE_URL" python /tmp/script.py'</code>.
<strong>pgvector extension missing</strong> — migration fails with "extension not found" <code>CREATE EXTENSION</code> requires superuser. The app user (<code>paledocs</code>) cannot create extensions. 1. Create the extension as superuser: <code>kubectl exec -n postgres pal-e-postgres-1 -- psql -U postgres -d paledocs -c "CREATE EXTENSION IF NOT EXISTS vector;"</code>. 2. Then retry the migration. 3. Long-term: CNPG CRD <code>postInitSQL</code> should handle this (deferred to namespace migration plan).
<strong>Alembic version mismatch</strong> — "Target database is not up to date" The deployed code expects a newer migration revision than what the DB has. 1. Check current DB revision: <code>kubectl exec -n NAMESPACE POD -- alembic current</code> (or query <code>alembic_version</code> table directly). 2. Check what revision the code expects: look at the latest migration file in <code>alembic/versions/</code>. 3. If DB is behind: let the app run its startup migration. 4. If DB is ahead (rare): you deployed an older image. Check the image tag.
<strong>Rollback needed</strong> — migration deployed but must be undone Migration caused a production issue that cannot be fixed forward. 1. Check if the migration has a <code>downgrade()</code> function: read the Alembic migration file. 2. If yes: <code>kubectl exec -n NAMESPACE POD -- alembic downgrade -1</code>. 3. If no downgrade function: you must restore from backup. See <code>sop-postgres-restore</code>. 4. <strong>Force a WAL switch before restore</strong> to capture the latest data: <code>kubectl exec -n postgres pal-e-postgres-1 -c postgres -- psql -U postgres -c "SELECT pg_switch_wal();"</code>.
<strong>CI secrets stale</strong> — build-and-push fails with UNAUTHORIZED after DB migration Database migration (e.g. Woodpecker SQLite→Postgres) wiped stored CI secrets. Harbor push credentials, repo secrets, and API tokens are now missing or stale. 1. Verify which secrets are missing: check Woodpecker repo settings for affected repos. 2. Re-provision from terraform state: <code>tofu output ci_robot_usernames</code> + <code>tofu output ci_robot_passwords</code> (in pal-e-platform). 3. Update Woodpecker repo secrets via UI or API. 4. Re-trigger the failed pipeline. 5. <strong>Pattern:</strong> Any DB migration that involves data loss (SQLite→Postgres, full restore) requires CI secrets re-verification across all repos.

Decision Tree

When a migration fails:
  • Get the error: Pod logs (kubectl logs --previous) or Alembic output.
  • Is it a permission issue? (e.g., CREATE EXTENSION) → Fix with superuser access, then retry.
  • Is it a schema conflict? (duplicate column, missing table) → Check alembic_version to see where the DB thinks it is. Fix the migration script or manually reconcile.
  • Is it a data issue? (constraint violation on existing data) → Fix the migration to handle existing data, or backfill before the constraint.
  • Is it Postgres? The migration should have rolled back transactionally. Verify with alembic current. Fix the script and redeploy.
  • Do you need to rollback? Check for downgrade(). If not available, use sop-postgres-restore.
  • After any fix: Verify data consistency. Query for gaps. Do not assume "migration ran" equals "data is correct."

Post-Migration Verification Checklist

  • Pod is Running (not CrashLoopBackOff)
  • alembic current shows the expected revision
  • New columns/tables exist with correct types
  • Existing rows have been backfilled (if applicable)
  • Application health endpoint returns 200
  • A smoke test of the affected feature passes
  • CI secrets verified: If migration involved data loss (SQLite→Postgres, full restore), verify all Woodpecker repo secrets are intact. Pattern: tofu output ci_robot_usernames + tofu output ci_robot_passwords (in pal-e-platform) → compare against Woodpecker repo settings. Re-provision any missing credentials before declaring migration complete.

Escalation Criteria

Escalate immediately (do NOT self-correct) when:
  • Migration requires superuser access and you do not have it
  • Data loss has occurred (rows deleted, columns dropped unexpectedly)
  • Rollback is needed and no downgrade() function exists (requires backup restore)
  • The migration involves a shared database that other services depend on
  • You are unsure whether existing data will be affected by a migration change
  • CNPG Cluster health is degraded (not a migration issue — infrastructure issue)
  • sop-postgres-restore — full CNPG backup restore SOP with PITR
  • deployment-lessons — SQLite Alembic migration danger, two production outages
  • incident-2026-03-02-sqlite-migration-crash-pr61 — detailed incident report
  • sop-deploy-recovery — when the pod crash is not migration-related