Recovering from schema_migrations.dirty = true

Recovering from `schema_migrations.dirty = true`

When the Vetrix container exits at boot with a log line like

vetrix: migrations: db: migrate up: migration failed: <reason> in line 0: <sql> (details: <pg error>)

the golang-migrate runner has marked the schema_migrations row dirty = true at the failing version. No further startup will succeed until an operator reconciles the DB by hand. This runbook is the canonical recipe.

Step 1 — identify the failing migration

SELECT version, dirty FROM schema_migrations;

dirty = t → the migration at version started but did not finish.
The log line's <sql> excerpt is the statement that tripped. Cross-reference it against db/migrations/<version>_*.up.sql to confirm which migration failed and why.

Step 2 — inspect the actual schema

Not every missing-table failure means the DB is far from the target state. Before rolling back, check which of the migration's pre-conditions are actually satisfied:

-- Which tables exist in the target area?
\dt schema_pattern_*

-- Is the specific table the migration expected still there?
SELECT to_regclass('public.analytics_pipeline_jobs');

If the pre-condition table is missing, the migration that was supposed to create it may have been rolled back or the table was dropped out-of-band. For example, if analytics_pipeline_jobs was created by 000130_analytics_pipeline_metrics.up.sql but dropped between runs, an unguarded ALTER TABLE in a later migration will hard-error.

Step 3 — clear the dirty flag and rewind

-- Clear the dirty flag; rewind version to the migration BEFORE
-- the one that failed. This makes migrate.Up() attempt the failing
-- migration again on the next boot.
UPDATE schema_migrations
   SET dirty = false,
       version = (SELECT version FROM schema_migrations) - 1;

The rewind step is important: if dirty is cleared without rewinding, the runner will advance past the failing migration and leave the schema in the incorrectly-migrated state at the higher version.

Step 4 — restore any missing pre-condition tables

If Step 2 showed that a pre-condition table is missing, apply the migration that creates it manually before restarting:

# Example: restore the analytics_pipeline_jobs table from 000130.
docker cp db/migrations/000130_analytics_pipeline_metrics.up.sql mydev_postgres:/tmp/130.sql
docker exec mydev_postgres psql -U vetrix -d vetrix -v ON_ERROR_STOP=1 -f /tmp/130.sql

Apply any earlier missing tables in ascending order. The analytics foundation chain is 000128 → 000129 → 000130 → 000131.

Step 5 — restart the container

docker compose restart mydev_vetrix
docker logs --tail=30 mydev_vetrix

migrate.Up() walks from the rewound version. With the pre-condition tables back in place, every subsequent migration (including the previously-failing one) applies cleanly and dirty flips back to false on success.

Step 6 — verify

SELECT version, dirty FROM schema_migrations;
-- Expect: version = (latest migration number in db/migrations/), dirty = f

\dt analytics_*
-- Expect: the full set from 128–131 is present.

Common causes

Unguarded ALTER TABLE. A migration that issues ALTER TABLE <name> without IF EXISTS will hard-error if the target table is missing (operator action, partial backup restore, stale dev DB, concurrent drop). Use ALTER TABLE IF EXISTS so the migration is idempotent against a missing target.
Column-name typo in an INSERT. A seed migration that references a column the table does not actually have produces ERROR: column "..." of relation "..." does not exist and lands the schema in dirty=true at that version. An ON CONFLICT DO NOTHING clause does not protect against this — the error fires before conflict resolution is considered.
A migration whose work is already covered upstream. If an earlier migration already seeds the same rows the failing one tries to insert, the failing migration may be deletable outright rather than fixed in place. Recovery in that case is to remove the bad migration files from the source tree and rewind schema_migrations.version to the prior version:
```
UPDATE schema_migrations SET dirty = false, version = <prev>;
```
After restart, migrate.Up() finds no pending migrations above <prev> and returns ErrNoChange, so the boot proceeds cleanly.