Dirty database version: why one migration blocks all the next ones and how to clear the flag without losing data

2026-09-29 · By OtezVikentiy

The service does not come up after a deploy, and the log has a single line:

Dirty database version 17. Fix and force version.

It comes from golang-migrate, the most widely used migration tool in Go projects. The first answer you find for this line is almost always the same: run migrate force 17. Sometimes that is right. But in PostgreSQL force 16 is right more often, and force 17 leaves the schema without migration 17 — with no message to tell you so. Below is how to tell the two cases apart.

What the dirty flag means

golang-migrate keeps its state in the schema_migrations table: a version number and a dirty flag. It applies every migration in three steps:

1. write version N with dirty = true — “started migration N”;
2. run the file N_*.up.sql;
3. write version N with dirty = false — “finished”.

If anything happens between the first and the third step — an SQL error in the migration, an OOM kill, docker kill in the middle of a deploy, a dropped database connection — the table is left with N, dirty = true. From then on golang-migrate refuses to do anything but force: no up, no down, no next migration. That is not stubbornness: from one row in a table the tool cannot know whether migration N applied fully, partly or not at all. A human has to decide by looking at the schema.

To see where it is stuck:

SELECT version, dirty FROM schema_migrations;

What force actually does

migrate force N does not run a single line of SQL. It only rewrites the row in schema_migrations: version N, dirty = false. The command means “I checked, the schema matches version N”. So there are exactly two options:

• force N — migration N applied completely (or you finished it by hand). The next up starts from N+1.
• force N-1 — migration N did not apply (or you rolled back what was left of it by hand). The next up runs N again.

Erring towards force N when the migration is not in the database is dangerous because it does not show right away. The tool considers the migration done, the next ones apply on top, the service starts. It fails later and somewhere else — on the first insert into a column that does not exist, or with a slow query missing an index that does not exist.

PostgreSQL: the migration rolls back as a whole

This is where PostgreSQL differs from other databases, and it is exactly what makes the generic “force N” advice risky.

By default the golang-migrate PostgreSQL driver sends the whole migration file in a single Exec call with no parameters. For such a call pgx uses the simple query protocol, and PostgreSQL runs several statements that arrive in one simple query as a single implicit transaction. So if any statement in the migration fails — or the process is killed halfway through the file — PostgreSQL rolls back the entire migration: not a single table, column or index from that file stays in the database.

The practical takeaway: if the migration failed with an SQL error, the schema is exactly at N-1. The right move is force N-1, fix the migration and run up again. force N in that case marks as applied a migration that is not in the database.

It is still worth checking one object from file N:

-- did the migration add a column?
SELECT column_name FROM information_schema.columns
WHERE table_name = 'orders' AND column_name = 'archived_at';

-- did it create a table?
SELECT to_regclass('public.order_archive');

If the object is there, the migration managed to commit (this happens when the process died after its commit but before step three), and you need force N. If it is not, force N-1. A regular PostgreSQL migration has no half-applied state.

When “all or nothing” does not hold

• x-multi-statement is on. With this connection-string parameter the driver splits the file on ; and runs the statements one by one. Each is its own transaction, and the migration can apply halfway.

• The file has explicit BEGIN and COMMIT. Everything before the COMMIT is committed regardless of what fails after it.

• CREATE INDEX CONCURRENTLY. This statement cannot run inside a transaction, so it lives alone in its file, and rollback does not protect it. If the build is interrupted, PostgreSQL leaves a half-built index in the catalog marked invalid. Worst of all, re-running CREATE INDEX CONCURRENTLY IF NOT EXISTS sees an object with that name and silently skips creating it: the migration reports success, and there is no working index — the planner ignores invalid indexes. The check:

  SELECT indexrelid::regclass, indisvalid FROM pg_index WHERE NOT indisvalid;
  

  Drop the half-built index (DROP INDEX CONCURRENTLY index_name;), set the flag to N-1 and run the migration again.

ClickHouse and MySQL: compare against the schema

ClickHouse has no transactions for DDL, and in MySQL every DDL statement implicitly commits. In both, a migration with several statements can stop halfway, and the only way to find out where is to compare the migration file with the schema, SHOW CREATE TABLE for every table it touches. Finish what is missing by hand or roll back what was done, then force N or force N-1 accordingly.

The “one statement per migration file” rule makes this much easier: “halfway” then reduces to “applied or not”. ClickHouse has one more quirk — mutations. ALTER TABLE … UPDATE and ALTER TABLE … DELETE run asynchronously and keep going on the server even after the client has disconnected. Unfinished ones show up like this:

SELECT database, table, mutation_id, command FROM system.mutations WHERE NOT is_done;

Step by step

1. Find the number: SELECT version, dirty FROM schema_migrations; — call it N.
2. Open N_*.up.sql and work out what it was supposed to do.
3. Compare with the schema. In PostgreSQL, outside the exceptions above, one object is enough; otherwise check all of them.
4. Bring the schema to one of two clean states: migration N is there in full, or not at all.
5. If the migration failed because of an error in the SQL itself, fix the file — otherwise the next up leaves the same flag.
6. Clear the flag: migrate -path ./migrations -database "$DATABASE_URL" force N (or N-1) and run up.
7. Make sure SELECT version, dirty FROM schema_migrations; shows the latest version with dirty = false.

Why migrations break in production

An error in the SQL itself is usually caught on staging. In production a migration is more often cut short by its surroundings:

• The process was killed mid-migration. The orchestrator restarted the container without waiting for a long ALTER TABLE to finish, the OOM killer came, someone restarted at the wrong moment.
• The migration was waiting for a lock. An ALTER TABLE on a busy table queues behind a long transaction, and every other query to that table queues behind it. A timeout fires and the migration fails. SET lock_timeout = '5s'; at the top of the migration turns a long wait with downtime into a quick, clear error.

How Gotcha handles it

Gotcha, a self-hosted observability platform, applies its PostgreSQL and ClickHouse migrations with golang-migrate at startup, so it runs into this error like any Go service. The differences are in how it is presented:

• the startup error names which of the two databases is stuck and at which version, and links to the documentation section;
• the binary clears the flag itself, no separate migrate install needed: --migrate-force=N for PostgreSQL and --migrate-force-ch=N for ClickHouse;
• only the stuck version and the one before it are accepted. Any other number is treated as a typo — it would silently shift the baseline for every future migration;
• indexes on tables with data are built with CREATE INDEX CONCURRENTLY — 28 of the 98 PostgreSQL migrations work this way, one statement per file. An index on a table the migration itself creates empty is built with a plain CREATE INDEX in the same file: there is nothing to lock, and the migration stays atomic. The upgrade documentation has the same indisvalid check, worth running after any upgrade, even one that looked smooth.

Details are in the “If a migration was interrupted” section of the documentation.