Skip to content

feat: report database migration outcome - #31849

Open
kirstaeff wants to merge 7 commits into
kirstaeff/ent-237-migration-logging-server-integrationfrom
kirstaeff/ent-236-migration-outcome
Open

kirstaeff wants to merge 7 commits into
kirstaeff/ent-237-migration-logging-server-integrationfrom
kirstaeff/ent-236-migration-outcome

Conversation

@kirstaeff

@kirstaeff kirstaeff commented Nov 6, 2026 •

Copy link
Copy Markdown
Collaborator

Migration progress is logged since the previous PRs in this stack, but nothing says how a run ended. On failure, the CLI prints the PostgreSQL error together with the failing SQL, without saying which migration failed or whether anything was saved.

After the migrations return, the reporter now asks PostgreSQL how the migration transaction ended (pg_xact_status on the transaction ID captured when it started, plus the saved schema version) and logs exactly one outcome line: committed, rolled back, committed before a failure, commit outcome unknown, batch lock timeout, or dirty schema. Asking PostgreSQL instead of trusting the Go error tells a commit that was saved apart from one that was not, for example when the connection breaks at the wrong moment.

When a migration fails, the error names it and gives PostgreSQL's short message and SQLSTATE. It no longer includes the SQL or PostgreSQL's detail text, which can contain row values.

Output from upgrading a v2.37.0 database (schema 585, 54 pending migrations) on a disposable PostgreSQL. A successful run, with another session holding a lock on templates (steps 2 to 54 omitted):

[info]  migrations: database migration applied, pending commit  version=586  name=template_allow_workspace_renames  position=1  total=54  duration_ms=28960
[info]  migrations: committed database migrations  status=success  count=54  from_version=585  to_version=639  transaction_status=committed  total_duration_ms=29141

A failing run, where the column migration 586 adds already exists:

[info]  migrations: applying update 1 of 54  version=586  name=template_allow_workspace_renames  position=1  total=54
[erro]  migrations: migration failed, transaction rolled back, no changes saved  status=failure  count=0  version=586  name=template_allow_workspace_renames  schema_version=585  sqlstate=42701  transaction_status=aborted  total_duration_ms=15  failed_duration_ms=2
error: connect to postgres: connect to postgres: migrate up: up: migration 586 (template_allow_workspace_renames): migration failed: column "allow_workspace_renames" of relation "templates" already exists (sqlstate=42701)

Differences from RFC 1:

  • "database migration outcome: no change" is logged at debug level, like "database schema is up to date", so a start with nothing to migrate adds no line to the startup output.
  • The rolled back line also names the failed migration (version, name).
  • The outcome is kept in an unexported result value instead of the exported Result type. It can be exported when upgrade telemetry needs it.

Closes ENT-236

This PR was written with Claude Code. I reviewed every change and ran the upgrades above myself, including a stop request during an upgrade, which still committed fully.

After the migrations return, ask PostgreSQL how the migration
transaction ended (pg_xact_status on the transaction ID captured when it
started, and the saved schema version) and log exactly one outcome line:
committed, rolled back, committed before a failure, commit outcome
unknown, batch lock timeout, or dirty schema. "No change" is logged at
debug level so starts with nothing to migrate stay quiet.

A failed migration's error now names the migration and gives
PostgreSQL's short message and SQLSTATE, without the SQL or PostgreSQL's
detail text, which can contain row values. The upgrade guide lists the
outcome line.

Part of ENT-236.
@kirstaeff
kirstaeff added this pull request to stack #31791 November 6, 2026 15:59
@linear-code

linear-code Bot commented Nov 6, 2026

Copy link
Copy Markdown

ENT-236

… into kirstaeff/ent-236-migration-outcome

# Conflicts:
#	docs/install/operate/upgrade/best-practices.md
… into kirstaeff/ent-236-migration-outcome

# Conflicts:
#	docs/install/operate/upgrade/best-practices.md
… into kirstaeff/ent-236-migration-outcome

# Conflicts:
#	coderd/database/migrations/migrate.go
#	coderd/database/migrations/reporter.go
#	coderd/database/migrations/reporter_test.go
Comment thread coderd/database/migrations/outcome.go Outdated
case outcomeCommitted:
fields := []slog.Field{slog.F("status", "success"), count}
fields = append(fields, versionField("from_version", res.FromVersion)...)
fields = append(fields, versionField("to_version", res.SchemaVersion)...)

Copy link
Copy Markdown
Collaborator

Choose a reason for hiding this comment

The reason will be displayed to describe this comment to others. Learn more.

These field names are inconsistent and confusing. Sometimes this is called "schema_version" sometimes "to_version". We should always call it the same thing. "schema_version" is imprecise because all these versions are versions of the schema. How about "final_version"

Comment thread coderd/database/migrations/outcome.go Outdated
// migrations.
type runResult struct {
StartedAt time.Time
FinishedAt time.Time

Copy link
Copy Markdown
Collaborator

Choose a reason for hiding this comment

The reason will be displayed to describe this comment to others. Learn more.

Logs automatically come with timestamps. Why do we need to separately have timestamps in this result struct?

Comment thread coderd/database/migrations/outcome.go Outdated
SchemaVersion int
// Applied lists the migrations that ran in the migration transaction.
// They are only saved if it committed.
Applied []appliedMigration

Copy link
Copy Markdown
Collaborator

Choose a reason for hiding this comment

The reason will be displayed to describe this comment to others. Learn more.

We only ever log the count of these. We don't need to store all this extra information about them

Comment thread coderd/database/migrations/outcome.go Outdated
}
fields = append(fields, versionField("schema_version", res.SchemaVersion)...)
if res.Failed != nil && res.Failed.pgErr != nil {
fields = append(fields, slog.F("sqlstate", string(res.Failed.pgErr.Code)))

Copy link
Copy Markdown
Collaborator

Choose a reason for hiding this comment

The reason will be displayed to describe this comment to others. Learn more.

You don't have to explicitly cast field values, i.e. here string() is redundant. slog uses reflection to figure out what to do with them.

Comment thread coderd/database/migrations/outcome.go Outdated
slog.F("status", "failure"), count, slog.F("schema_version", dirty.Version), total, dirtyErr)

case outcomeFailedBeforeStart:
// The error is reported by the caller, as without reporting.

Copy link
Copy Markdown
Collaborator

Choose a reason for hiding this comment

The reason will be displayed to describe this comment to others. Learn more.

The error is reported by the caller in all error cases, why is this one special in that we don't log a report?

Comment thread coderd/database/migrations/outcome.go Outdated
case outcomeCommittedBeforeFailure:
fields := []slog.Field{slog.F("status", "failure"), count}
fields = append(fields, versionField("from_version", res.FromVersion)...)
fields = append(fields, versionField("to_version", res.SchemaVersion)...)

Copy link
Copy Markdown
Collaborator

Choose a reason for hiding this comment

The reason will be displayed to describe this comment to others. Learn more.

why don't we include the error here?

Comment thread coderd/database/migrations/outcome.go Outdated
if res.Failed != nil {
fields = append(fields, slog.F("failed_duration_ms", res.Failed.duration.Milliseconds()))
}
r.logger.Error(ctx, "migration failed, transaction rolled back, no changes saved", fields...)

Copy link
Copy Markdown
Collaborator

Choose a reason for hiding this comment

The reason will be displayed to describe this comment to others. Learn more.

why don't we include the err here?

require.Equal(t, "up: timeout: can't acquire database lock", err.Error())
require.Equal(t, outcomeLockTimeout, res.Outcome)

line := sink.Entries(func(e slog.SinkEntry) bool {

Copy link
Copy Markdown
Collaborator

Choose a reason for hiding this comment

The reason will be displayed to describe this comment to others. Learn more.

these tests should be converted to golden files

t.SkipNow()
}

t.Run("Committed", func(t *testing.T) {

Copy link
Copy Markdown
Collaborator

Choose a reason for hiding this comment

The reason will be displayed to describe this comment to others. Learn more.

These tests should also be converted to golden files.

@kirstaeff
kirstaeff requested a review from a team November 19, 2026 08:02
@kirstaeff

Copy link
Copy Markdown
Collaborator Author

Reworked on top of the new reporter from #31789 before your review landed, so some lines you commented on have moved.

This branch has not been deployed

No deployments
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment

Labels

None yet

Projects

None yet

Development

Successfully merging this pull request may close these issues.

2 participants