Recover from a dirty schema_migrations row

Recover from a dirty `schema_migrations` row

Operator runbook for the boot-time error

vetrix: migrations: db: migrate up: Dirty database version <n>. Fix and force version.

The boot-time diagnostic also emits a schema_migrations dirty — boot aborted summary in error.log.

This runbook covers the shared dev DB drift case (the mydev_postgres container that backs every developer's mydev_vetrix build). The same recovery sequence works against production, but production drift is exceptional and should always be escalated to the on-call rotation before any force is run — see §5 — Production caveats below.

This runbook does not cover writing new migrations (see the migration authoring guidelines for the write-time prevention rules) or fixing a broken migration file (see internal/db/migrations_for913_test.go for the existing ALTER TABLE IF EXISTS guard pattern).

1. What "dirty" actually means

golang-migrate writes a single row to a schema_migrations table:

   version | dirty
   --------+-------
       129 | t

dirty=true means migrate-up began applying migration 129 and did not record success. It does not mean the database is corrupt. It does not tell you whether migration 129's effects (the table-creates, ALTERs, index-creates inside the file) are partially present, fully present, or fully absent. Recovery starts with finding out which.

vetrix itself never sets dirty=true on its own — only migrate-up does, and only as a side effect of a SQL statement failing inside a transaction. The only legitimate paths from clean → dirty are:

A migration file with a SQL error (most common — a missing IF EXISTS, a CREATE INDEX CONCURRENTLY inside the auto-wrapped transaction, a non-IMMUTABLE function in an index expression — see the migration authoring guidelines for the catalog).
A migration file referencing an extension or function not present on the target Postgres instance.
The Postgres server being killed mid-migration (OOM, container restart, network partition that kills the connection).
An operator manually UPDATE schema_migrations SET dirty=true (this happens — recovery scripts sometimes use it).

Whatever the path, the recovery sequence is the same.

2. Diagnose

Open a psql against the affected DB and run the four queries below in order. Together they tell you the dirty version, the physical schema position, and which sentinel tables for the dirty migration boundary are present or missing — the same information the boot-time diagnostic emits to error.log.

2.1 The dirty marker itself

SELECT version, dirty FROM schema_migrations;

You should get exactly one row. If dirty=true, note the version — this is the one the recovery sequence will operate on. If dirty=false the boot error was about something else (read the actual error message from error.log; this runbook does not apply).

2.2 The physical table count

SELECT count(*) FROM pg_tables WHERE schemaname='public';

This is a quick sanity check. The number alone is not diagnostic — the count varies as migrations add and drop tables — but a wildly out-of-band count (say, 0 or 5 against a 150+ migration history) means the schema is gone, not just dirty. If the count is in the expected hundreds, the schema is probably mostly intact and you proceed to §2.3.

2.3 Sentinel-table presence around the dirty boundary

For each of the dirty version and the next ~5 versions above it, ask Postgres whether the canonical table that migration created is present. Use to_regclass rather than EXISTS (SELECT ...) because to_regclass returns NULL for unknown tables instead of erroring, and the result reads naturally in psql.

SELECT to_regclass('public.analytics_events')             AS v128_analytics_events,
       to_regclass('public.analytics_git_operations')     AS v129_analytics_git_operations,
       to_regclass('public.analytics_pipeline_metrics')   AS v130_analytics_pipeline_metrics,
       to_regclass('public.analytics_sessions')           AS v131_analytics_sessions;

A row of analytics_events | (null) | (null) | (null) means migrations 0–128 succeeded and 129 failed before its table-create landed. A row of analytics_events | analytics_git_operations | (null) | (null) means migration 129 created the table but the dirty marker fired on a later statement inside the same migration (an index, an INSERT, a constraint).

The full sentinel set the boot diagnostic uses lives in internal/db/migdiag/migdiag.go (SentinelTables map). When adding a runbook entry for a different version range, take the canonical table names from there.

2.4 The expected vs. actual position

Read the highest migration file in db/migrations/:

ls db/migrations/ | sort | tail -1
# e.g. 000172_drop_page_spaces.up.sql

If the dirty version equals the highest, the partially-applied migration is at the head and recovery is in-place reconcile (§3 below). If the dirty version is lower than the highest, the operator's binary applied migrations past the dirty marker on a previous run that subsequently failed and rolled the marker back. That is unusual but not impossible — treat it the same way as in-place reconcile but be alert for later migrations whose tables are also missing.

3. In-place reconcile (preferred path)

Use this path when:

§2.3 shows the dirty version's table is present in the database (the migration finished its DDL but the marker did not commit), or
§2.3 shows the dirty version's table is missing but you have re-read the migration file and confirmed the failure was a transient SQL error (e.g., a CREATE INDEX that timed out under load) rather than a structural defect in the migration file itself.

In-place reconcile preserves the data already in the database — no DROP, no migrate down, no re-seed.

3.1 If the dirty version's effects ARE present

Mark the dirty version as applied and clean. From inside the vetrix repo, with the same DSN the server uses:

./vetrix-migrate force <dirty-version>

Note: the vetrix-migrate binary does not ship a force subcommand — the binary only wraps migrate up. Run migrate force directly with the upstream CLI:

migrate -path db/migrations \
        -database "$DATABASE_URL" \
        force <dirty-version>

(Install the upstream CLI with go install -tags 'postgres' github.com/golang-migrate/migrate/v4/cmd/migrate@latest if it is not already on the operator host.)

force is the canonical recovery operation in golang-migrate: it sets dirty=false and pins the version to the supplied number, without re-running any SQL. After force, run up again to apply any later migrations:

./vetrix-migrate

A clean exit means the schema is now caught up. Re-boot mydev_vetrix to confirm:

docker compose up -d mydev_vetrix
docker logs --tail=50 mydev_vetrix

3.2 If the dirty version's effects are NOT present

Manually re-apply the failing migration's SQL with psql:

docker exec -i mydev_postgres psql -U vetrix -d vetrix \
  < db/migrations/<NNNNNN>_<name>.up.sql

If the SQL succeeds — record the result, then force the version clean (§3.1) and up again. If the SQL still errors, you are looking at a structural defect in the migration file itself; do not keep forcing. File a backend bug with the failing SQL and Postgres' error message, and stop the recovery here. This runbook does not cover patching existing migrations in place — that pattern is forbidden.

4. Full re-seed (last resort)

Use this path only when:

In-place reconcile failed (the migration's SQL cannot be applied even by hand), AND
The data in the dev DB is disposable (no in-flight commits, no work-in-progress issues / MRs you care about), AND
The DB in question is dev/staging — never production.

If the corruption was caused by a destructive migration test suite (dropped table / schema_migrations desynced behind a physically-ahead schema / a duplicated renamed column), this is the correct path — but read §4.1 first for the fingerprints and why §3 will not converge.

The operation drops every table in the public schema, recreates the schema, and runs the migration history from migration 1.

# 1. Drop the whole public schema. Cascade so dependent objects
#    (constraints, indexes, sequences, MVs) drop with it.
docker exec mydev_postgres psql -U vetrix -d vetrix -c \
  "DROP SCHEMA public CASCADE; CREATE SCHEMA public; \
   GRANT ALL ON SCHEMA public TO vetrix; GRANT ALL ON SCHEMA public TO public;"

# 2. Confirm zero tables.
docker exec mydev_postgres psql -U vetrix -d vetrix -c \
  "SELECT count(*) FROM pg_tables WHERE schemaname='public';"
# expected: 0

# 3. Re-run all migrations from a clean slate.
./vetrix-migrate
# or:
migrate -path db/migrations -database "$DATABASE_URL" up

# 4. Boot vetrix to confirm.
docker compose up -d mydev_vetrix

Re-seed loses every row. There is no shortcut to a partial re-seed once the schema is wiped — pg_dump → drop → restore + replay is the only way to keep some data, and at that point you are back to §3 plus a manual surgery that is out of scope for this runbook.

4.1 Structural corruption from a destructive migration test

This is the case where the shared mydev_postgres was left dirty not by a normal migrate-up failure but by a destructive golang-migrate down→up test suite run against it — i.e. someone pointed TEST_DSN/DATABASE_URL at the shared dev DB and ran the internal/db integration suite (TestMigrate_*, the destructive UpDownUp migration-test family, and the email round-trip tests). Those tests deliberately drop tables, run raw .down.sql, and UPDATE schema_migrations SET version=… to drive a rewind — fine against an ephemeral DB, schema-bricking against the shared one.

These tests are guarded: internal/db's requireEphemeralMigrationDB t.Skip()s the whole destructive suite unless VETRIX_TEST_DESTRUCTIVE_MIGRATIONS=1 is explicitly set (CI sets it only for the ephemeral vetrix-it-pg container). This corruption class appears when an operator sets that opt-in against the wrong DSN. Do not re-run the suite against mydev_postgres — that is the bug, not the fix.

Why this is NOT a §3 in-place reconcile. The §2.3 sentinel queries can be misleading here: the physical schema is often ahead of (or skewed against) schema_migrations, not behind it. Typical fingerprints of a half-reverted destructive run:

schema_migrations.version is desynced far below the highest migration file (e.g. set to 127/131/146 by a test's rewind UPDATE), possibly with dirty=false — so the boot error may not even be the classic "Dirty database version" one; migrate up instead replays already-applied migrations and errors on a pre-existing object.
A renamed column exists twice: e.g. issues carries BOTH tokens AND input_tokens (and output_tokens), with all three token CHECK constraints (issues_tokens_check, issues_input_tokens_check, and the output check) coexisting — a down that recreated tokens without dropping the up's input_tokens.
A table the test dropped (page_spaces, analytics_*, email_*, email_verification_tokens) is simply gone while schema_migrations still claims its creating migration applied.

Confirm the duplicate-column / coexisting-constraint case explicitly — it is the unambiguous signature of a test down/up cycle, not an ordinary migrate-up failure:

SELECT column_name FROM information_schema.columns
 WHERE table_name = 'issues'
   AND column_name IN ('tokens','input_tokens','output_tokens')
 ORDER BY column_name;
-- A row for BOTH 'tokens' AND 'input_tokens' = half-reverted rename.

SELECT conname FROM pg_constraint
 WHERE conrelid = 'issues'::regclass AND contype = 'c'
   AND conname IN ('issues_tokens_check','issues_input_tokens_check')
 ORDER BY conname;
-- Both present together = the down recreated the old constraint
-- without the up's having been reverted.

force + up (§3.1) will silently skip the duplicated / missing objects and the next dependent migration will fail on the broken prerequisite — leaving you worse off (the §5 production warning about silent skips applies in dev too). Re-applying the failing migration by hand (§3.2) will error on the duplicate column / constraint that already exists. Neither path converges.

Recovery: go straight to the §4 full re-seed. The shared dev DB is re-seedable by definition (that is the whole point of mydev_postgres); a structurally-corrupt schema from a test down/up cycle is not worth hand-surgery. Run §4 steps 1–4 verbatim (DROP SCHEMA public CASCADE → recreate → ./vetrix-migrate → boot mydev_vetrix). If a teammate has un-pushed work in that DB, take a pg_dump of the specific tables first (the §4 closing note), re-seed, then restore just those rows — but the default action for this corruption class is the clean re-seed.

Prevention (so this section is needed less often): never set TEST_DSN/DATABASE_URL to mydev_postgres (or any shared DB) when running go test -tags integration ./internal/db/..., and never set VETRIX_TEST_DESTRUCTIVE_MIGRATIONS=1 outside a disposable/ephemeral DB. An ephemeral throwaway Postgres is required for exactly this reason.

5. Production caveats

Never force a production schema_migrations row without an on-call review. A force in production rewrites history. If the dirty version's effects are partially present, a force followed by up will skip them silently and the next migration will fail on the missing prerequisite — at which point the operator is worse off than before (two dirty layers, one of them invisible).
Never run §4 (full re-seed) against production. If you find yourself reaching for it, escalate.
Always take a pg_dump snapshot before any recovery in production. The shared dev DB does not need this — it is re-seedable by definition. Production does.

6. Worked example — `mydev_postgres` dirty=129

The recovery sequence end-to-end for the dirty=129 pattern.

6.1 Symptom

mydev_vetrix fails to start with:

vetrix: migrations: db: migrate up: Dirty database version 129. Fix and force version.

docker logs mydev_vetrix --tail=100 confirms boot terminated at the db.Migrate(...) call site in cmd/server/main.go.

6.2 Diagnose

schema_migrations:

 version | dirty
---------+-------
     129 | t

Physical table count:

 count
-------
   151

151 tables against a 172-migration history (some migrations drop tables, some add multiple, so the count is not 172) — the schema is mostly intact, not wiped. Sentinel-table presence:

SELECT to_regclass('public.analytics_events')           AS v128,
       to_regclass('public.analytics_git_operations')   AS v129,
       to_regclass('public.analytics_pipeline_metrics') AS v130;

        v128         |        v129        |        v130
---------------------+--------------------+---------------------
 analytics_events    | (null)             | (null)

Reading: migrations 0–128 succeeded, migration 129 began but the table-create did not commit (the table is missing), and migrations ≥130 never ran (transactional commit boundary at 129 stopped the chain). The dirty marker is at the right boundary — it accurately reflects the physical state.

6.3 Reconcile

Migration 129's effects are NOT present (§3.2 path), but the migration file itself was inspected and contained no structural defect — the failure was a one-time apply error (a Ctrl-C during the original migrate up left the marker dirty and the table un-created). So the recovery is: re-apply the file by hand, then force-clean.

docker exec -i mydev_postgres psql -U vetrix -d vetrix \
  < db/migrations/000129_analytics_git_operations.up.sql
# CREATE TABLE
# CREATE INDEX
# CREATE INDEX

migrate -path db/migrations \
        -database "$DATABASE_URL" \
        force 129
# (silent on success)

./vetrix-migrate
# vetrix-migrate: migrations applied successfully

docker compose up -d mydev_vetrix
docker logs --tail=20 mydev_vetrix
# vetrix: server starting on :3000

schema_migrations after recovery:

 version | dirty
---------+-------
     172 | f

Boot succeeds. Recovery time from first error to running server is ~4 minutes. The boot diagnostic removes the deciding step entirely: the sentinel table log lines tell the operator at a glance which path applies.

7. Cross-references

Migration write-time prevention — run new migrations against mydev_postgres before merging, use IF EXISTS / IF NOT EXISTS guards, and avoid CONCURRENTLY in transactional migrations.
internal/db/migrations_for913_test.go — the structural test enforcing ALTER TABLE IF EXISTS on every migration.
internal/db/migdiag/migdiag.go — the sentinel-table set and the boot diagnostic's exact log shape, so an operator can grep error.log for "sentinel table" and parse the version=/table=/status= fields without re-reading this runbook from scratch.