Database migration rehearsal

Implementation

The recipe expands a populated SQLite schema and uses compatibility triggers to maintain the new key when the old application writes. New reads fall back to normalized legacy tags during the overlap.

Backfill commits in batches, with progress and data in the same transaction. After an injected interruption, a new database connection resumes from rows still missing a key.

Cutover requires a completed backfill and explicit retirement of the old contract. Contraction replaces the legacy column with required, nonempty, unique keys and checks that IDs and descriptions remain unchanged.

The operational rehearsal adds real process termination during backfill and contraction, controlled overlap writes, an archived-tag forward recovery, size/batch sweeps, and a separate theoretical PostgreSQL lock/index run.

Inputs and assumptions

• The reference case has 5,000 generated ASCII asset tags and 250-row batches. It interrupts after three committed batches.
• This recipe uses SQLite and a single asset table without dependent tables. The old-application contract is represented by explicit fixture metadata.
• Legacy tags are trimmed and converted to lowercase. Duplicate, null, and empty normalized keys are rejected.

Results

Report figures (1)

Open the full report ↗

Verification

14 regression tests and four fixture outcomes passed, plus 10 operational acceptance checks covering process kills, overlap traffic, forward recovery, 100,000-row fixtures, held locks and theoretical PostgreSQL lock/index recovery.

python verify.py

Project files

The ZIP contains the source, inputs, results, and instructions. These files are also available individually.

• Migration recipe and disposable runnerrehearse.py · 10 KB
• Reference schema and transformationsfixture-spec.json · 1 KB
• Restart, rollback, and contract teststest_rehearsal.py · 4 KB
• Successful run, schema, and timing recordresults/good/run.json · 3 KB
• Duplicate-key rejection recordresults/duplicate/run.json · 3 KB
• Executed scenario outcomesresults/summary.csv · 1 KB
• Migration sequence and run instructionsREADME.md · 5 KB
• Crash, operations and theoretical deployment reportoperations-report.html · 12 KB
• Executable operational rehearsaloperations.py · 18 KB
• Operational acceptance and source hashescompletion.json · 10 KB
• Expected and observed outcomesresults/operations/acceptance.csv · 4 KB
• Operating runbook and PostgreSQL analysisOPERATIONS.md · 7 KB

Run locally

Python 3.10 or newer with SQLite / Python standard library / Linux for the subprocess SIGKILL operational drills

Extract the ZIP and open a terminal in the migration-rehearsal folder. The README includes setup and usage details.

python rehearse.py --rows 5000 --batch-size 250 --interrupt-after 3 --out results/manual
python verify.py
python operations.py

Limits

• SQLite crash, traffic and size cases are executed. PostgreSQL table-lock and concurrent-index decisions are modeled from documented semantics, not observed server behavior.
• The synthetic recipe has one asset table. Timings and controlled writer behavior do not establish production concurrency or downtime guarantees.
• SIGKILL checks process recovery with normal host storage; power loss and faulty storage hardware are outside that evidence.
• Contraction removes original tag formatting. The executed forward recovery requires the retained tag archive and preserves newer content; it is not an automatic application downgrade.