What causes "database is dirty" in golang-migrate/migrate?

Inspect the version table (e.g. SELECT * FROM schema_migrations) and the recorded version vs your migration files

database is dirty

Error message

database is dirty

What it means

ErrDatabaseDirty is returned when the driver finds the migrations/version table in an inconsistent state — most commonly a recorded version in the migrations table that has no corresponding migration file, or a setDirty record from a previously failed migration. The pgx driver (like mysql, cassandra and others sharing this sentinel name) returns it from SetVersion/EnsureVersion paths, aborting further migrations until an operator intervenes.

Source

Thrown at database/pgx/pgx.go:51

	db := Postgres{}
	database.Register("pgx", &db)
	database.Register("pgx4", &db)
}

var (
	multiStmtDelimiter = []byte(";")

	DefaultMigrationsTable       = "schema_migrations"
	DefaultMultiStatementMaxSize = 10 * 1 << 20 // 10 MB
	DefaultLockTable             = "schema_lock"
	DefaultLockStrategy          = LockStrategyAdvisory
)

var (
	ErrNilConfig      = fmt.Errorf("no config")
	ErrNoDatabaseName = fmt.Errorf("no database name")
	ErrNoSchema       = fmt.Errorf("no schema")
	ErrDatabaseDirty  = fmt.Errorf("database is dirty")
)

type Config struct {
	MigrationsTable       string
	DatabaseName          string
	SchemaName            string
	LockTable             string
	LockStrategy          string
	migrationsSchemaName  string
	migrationsTableName   string
	StatementTimeout      time.Duration
	MigrationsTableQuoted bool
	MultiStatementEnabled bool
	MultiStatementMaxSize int
}

type Postgres struct {
	// Locking and unlocking need to use the same connection

View on GitHub (pinned to 01a9643f14)

Solutions

Inspect the version table (e.g. SELECT * FROM schema_migrations) and the recorded version vs your migration files
If the failed migration's effects should be kept, manually update the version row to the correct value (e.g. force to the version before the failure: UPDATE schema_migrations SET version=..., dirty=false)
If effects should be rolled back, manually revert the partial changes, then clear the dirty flag and re-run
Ensure migration files are never deleted/renamed after being applied across environments

Example fix

// before
psql -c "SELECT version, dirty FROM schema_migrations;" -- shows dirty=true
// after
psql -c "UPDATE schema_migrations SET dirty = false WHERE version = 12;" -- after manually verifying/rolling back migration 12's changes

Defensive patterns

Strategy: try-catch

Validate before calling

// before migrating, check for dirty state
var version int
var dirty bool
err := db.QueryRow("SELECT version, dirty FROM schema_migrations").Scan(&version, &dirty)
if err == nil && dirty {
    return fmt.Errorf("database is dirty at version %d; resolve manually before migrating", version)
}

Try / catch

if err := m.Up(); err != nil {
    var dbErr *database.Error
    if errors.As(err, &dbErr) && strings.Contains(err.Error(), " Dirty database version") {
        return fmt.Errorf("migration aborted, DB left dirty at recorded version; inspect schema_migrations and resolve manually: %w", err)
    }
    return err
}

Prevention

Always run migrations inside transactions/with a migration tool so failures are recorded consistently
Never delete or rename applied migration files
Alert on failed migration runs — dirty state requires manual resolution, retries will keep failing
Keep schema_migrations backed up/restorable consistently with the DB state

When it happens

Trigger: Running migrations after a previous migration run failed mid-way (dirty state recorded); the version row references a migration number not present in the source; a manual edit or partial restore of the schema_migrations table.

Common situations: A migration DDL was killed/timed out leaving partial changes; deploying new migration files while the DB records a higher version (out-of-order/deleted files); restoring a DB dump without the matching migration sources.

Related errors

AI-assisted analysis of golang-migrate/migrate@01a9643f14 (2026-09-02). Data as JSON: /api/errors/76455d25f981b98c. Report an issue: GitHub.