36 Online Migration Without Data Loss
37 Online Migration Without Data Loss
37.1 Weak Request
Migrate users.birthdate from string to date.
37.2 Repaired Spell
ROLE: Act as a migration planner for a production PostgreSQL system.
OBJECTIVE: Move users.birthdate from VARCHAR to DATE without data loss and without breaking reads or writes during rollout.
CONTEXT: The table is large, writes continue during business hours, and some existing rows contain invalid or partial dates.
CONSTRAINTS: Use expand-and-contract. Preserve rollback until data quality is validated. Assume two application deploys are allowed.
OUTPUT CONTRACT: Return phased SQL and application steps: schema expand, dual write, backfill, validation queries, read switch, cleanup, rollback plan.
VERIFICATION: Include checks for invalid rows, null behavior, row-count parity, and post-cutover consistency.
FAILURE BEHAVIOR: If the data quality problem is too large for safe automatic cast, stop at quarantine and describe the manual decision boundary.37.3 Expected Delta
The repaired spell turns one risky command into a reversible campaign with validation and dirty-data handling.