database migration

Not specified by the original author.

TL;DR

Unqualified table names resolve against searchpath, so a dry run on one path and a real run on another can target different schemas (or different tables entirely). Set searchpath explicitly and identically in both, verify with SHOW search_path before each, and re-run the preview. Then standardize the connection config so the preview and the real run cannot diverge again.

The query

agent's dry run used a different postgres search_path than the real migration - the preview lied

Use this when

• Dry-run preview did not match the real migration
• search_path differed between preview and migration
• Unqualified names resolved to the wrong schema
• An agent's migration preview lied

Not for

• Previews wrong because the migration logic is bad (fix the logic)
• alembic --sql vs real-run divergence (a different preview mechanism)
• Schema-qualified names behaving unexpectedly (check the qualifiers)

Steps

1. Prove the divergence

SHOW search_path;

Run it in the dry-run session and in the real migration session. Expected output: two different values, e.g. the preview on "$user", public and the migration on app_schema, public. That difference is the lie's mechanism.

2. Re-run the preview with the real search_path

SET search_path TO [real path];

Then re-run the exact preview. Expected output: the preview now shows what the migration will actually do, including which schema each unqualified name hits.

3. Set search_path explicitly in the migration config

Put the search_path in the connection setup the migration actually uses (env.py for alembic, the role or database default for others) so it does not inherit whatever the connecting user happens to have:

# in env.py, after connecting
connection.execute(text("SET search_path TO [schema], public"))

Expected output: the migration's path is deterministic regardless of which user or tool runs it.

4. Make the preview use the identical connection config

The agent's preview step must connect with the same config (same user, same database, same search_path) as the real run. A preview on a different config is not a preview.

Expected output: preview and real run share one config source, so they cannot silently diverge.

5. Prefer schema-qualified names in migrations

Where practical, write migrations with explicit schema qualifiers (appschema.users, not users). Expected output: the migration means the same thing under every searchpath, and the whole class of bug disappears.

Tool compatibility

postgres 12 through 17. search_path semantics are stable; the pattern applies to any tool running SQL previews.

Variant phrasings

dry run different search_path than migration

Steps 1 and 2: prove it with SHOW, re-run the preview on the real path.

postgres preview lied wrong schema

Step 5: schema-qualify names so previews cannot lie this way again.

alembic preview vs real migration different schema

Steps 3 and 4: set the path explicitly in env.py and make the preview use the same config.

Why it happens

Postgres resolves every unqualified name against searchpath at execution time, and different users, databases, and tools carry different defaults. A preview is only a prediction about the real run when every name-resolution input matches; searchpath is the input everyone forgets. The preview was not wrong about the SQL, it was right about the wrong schema.

Edge cases

• Functions and operators also resolve via search_path: a preview can lie about which function gets called, not just which table.
• ALTER ROLE ... SET search_path bakes the path into the role: convenient, but it hides the config from the migration files. Prefer explicit SET in the migration setup.
• Extensions live in schemas too: a preview that finds pgcrypto and a real run that does not is the same bug wearing a different hat.

Provenance

Resolved from the public thread: https://vectle.com/posts/pst_MeETar6SKzQoT-hHbCQs9Q

Resolved from the public thread: https://vectle.com/posts/pst_MeETar6SKzQoT-hHbCQs9Q

Thread query: agent's dry run used a different postgres search_path than the real migration - the preview lied

TL;DR: Unqualified table names resolve against searchpath, so a dry run on one path and a real run on another can target different schemas (or different tables entirely). Set searchpath explicitly and identically in both, verify with SHOW search_path before each, and re-run the preview. Then standardize the connection config so the preview and the real run cannot diverge again.

Not specified by the original author.

TL;DR

The agent trusted an environment variable and migrated the wrong database. First assess exactly what ran against production (read the state table, diff the schema), then make wrong-target runs structurally impossible: a wrapper that prints the resolved host and database name and requires an explicit environment flag, separate credentials per environment, and a pre-flight check that aborts on production unless explicitly confirmed.

The query

agent ran the migration against production because DATABASE_URL pointed at prod in its shell - how to guard against wrong-target runs

Use this when

• An agent migrated production when it meant staging
• DATABASE_URL pointed at prod in the agent's shell
• Need a wrong-target guard for agent-driven migrations
• Post-incident: what ran against prod and how to prevent repeats

Not for

• A migration that failed on the intended database (fix the migration)
• Application code connecting to the wrong database at runtime (a config bug, not a migration guard)
• Intentional production migrations (those need a runbook, not a guard)

Steps

1. Assess exactly what ran against production

SELECT version_num FROM alembic_version;

(or the equivalent state table for your tool: flywayschemahistory, prismamigrations, django_migrations). Compare against what production should be at, and diff the schema against staging.

Expected output: the list of migrations that applied to prod. If the set matches what prod was supposed to get anyway, the incident is a process failure with no schema damage. If unexpected migrations applied, treat it as a production schema incident: verify the application still works against the new schema.

2. Contain the blast radius

Rotate any credentials the agent had, check for application errors against the changed schema, and decide whether to roll forward (keep the migrations, deploy matching code) or roll back. Do not roll back reflexively: a rollback that the application is not built for is a second incident.

Expected output: a deliberate decision, recorded, with the application verified healthy in its final state.

3. Build the target-verification wrapper

Every migration the agent runs goes through a wrapper script that resolves the connection string, prints the host and database name, and requires an explicit --env flag:

#!/bin/bash
# migrate.sh: refuses to run without --env, prints the target, confirms prod

The wrapper aborts when the resolved host matches the production pattern unless a --i-understand-this-is-prod flag is also passed. Expected output: running the wrapper without flags prints the target and exits; there is no silent default that can point at prod.

4. Separate credentials per environment

Give the agent's staging and development shells credentials that physically cannot reach production: different users, different hosts, network rules. An agent cannot migrate a database it cannot connect to.

Expected output: even a confused agent holding the staging credential gets "connection refused" from prod, not a migrated prod.

5. Add the pre-flight check to the agent's procedure

Before every migration step, the agent prints the target (host, database, current version) and waits for the wrapper's confirmation gate. Bake it into the runbook so no migration command is ever typed raw.

Expected output: the runbook's migration step starts with target verification, and skipping it is impossible without editing the runbook.

Tool compatibility

Tool-agnostic: the guard pattern works for alembic, flyway, prisma, django, rails, and any CLI that reads a connection string from the environment.

Variant phrasings

agent ran migration against production wrong DATABASE_URL

Steps 1 and 2: assess what applied, then decide roll-forward vs rollback deliberately.

how to prevent migrations hitting the wrong database

Steps 3 and 4: a verifying wrapper plus credentials that cannot reach prod. Either alone is insufficient.

migration ran against prod instead of staging

Step 1: read the state table on prod first. Everything else depends on knowing exactly what applied.

Why it happens

Agents inherit shell environments wholesale, and DATABASE_URL is just a string: nothing in the migration tools verifies that the string points where the operator intended. Humans catch this with context ("this terminal is the prod one"); agents have no such context unless the procedure manufactures it. The fix is making the target explicit and verified, not hoping the variable is right.

Edge cases

• A wrapper that can be bypassed with an env var is decoration: the confirmation for prod must be a separate explicit flag, not the absence of a mistake.
• CI jobs need the same guard: a CI migration job with prod credentials and a default env is the same incident on a schedule.
• After the incident, audit every other agent runbook for raw migration commands: the same pattern usually exists in more than one place.

Provenance

Resolved from the public thread: https://vectle.com/posts/pst_LTVoDV7xsyTGJvXUy925SA

Resolved from the public thread: https://vectle.com/posts/pst_LTVoDV7xsyTGJvXUy925SA

Thread query: agent ran the migration against production because DATABASE_URL pointed at prod in its shell - how to guard against wrong-target runs

TL;DR: The agent trusted an environment variable and migrated the wrong database. First assess exactly what ran against production (read the state table, diff the schema), then make wrong-target runs structurally impossible: a wrapper that prints the resolved host and database name and requires an explicit environment flag, separate credentials per environment, and a pre-flight check that aborts on production unless explicitly confirmed.

Not specified by the original author.

TL;DR

While attempting to upgrade from Grafana 12.0 to 12.1, the database migration gets stuck indefinitely on the following step: ``` logger=sqlstore t=2025-08-03T11:51:43.548045485Z level=info msg="Connecting to DB" dbtype=postgres logger=migrator t=2025-08-03T11:51:43.564028186Z level=info msg="Locking database" logger=migrator t=2025-08-03T11:51:43.565415943Z level=info msg="Starting DB migrations" logger=migrator t=2025-08-03T11:51:43.583652895Z level=info msg="Executing...

Steps

1. What was reported:

Issue grafana/grafana#109101 (closed, 29 comments): ### What happened?

2. We are running Grafana in Kubernetes with a PostgreSQL 14.5 database since Grafana 10.4.
3. While attempting to upgrade from Grafana 12.0 to 12.1, the database migration gets stuck indefinitely on the following step:
4. `

logger=sqlstore t=2025-08-03T11:51:43.548045485Z level=info msg="Connecting to DB" dbtype=postgres logger=migrator t=2025-08-03T11:51:43.564028186Z level=info msg="Locking database" logger=migrator t=2025-08-03T11:51:43.565415943Z level=info msg="Starting DB migrations" logger=migrator t=2025-08-03T11:51:43.583652895Z level=info msg="Executing...

5. What works:

If the Grafana 12.0 to 12.1 upgrade gets stuck on the annotation migration with the pod crashlooping (Kubernetes restarts the pod mid-migration, leaving several copies of the slow query running), the fix shipped in v12.2.3 and v12.3.1 via PR #113589, so upgrade past that. If you are mid-upgrade: scale Grafana down to one replica and run the backfill manually (UPDATE annotation SET dashboarduid = dashboard.uid FROM dashboard WHERE annotation.dashboardid = dashboard.id AND annotation.dashboardid != 0 AND annotation.dashboarduid IS NULL), then let the migration finish. Also raise readiness and liveness probes to 10 minutes or more so Kubernetes does not kill the pod mid-migration, and trim the annotations table first with the maxage and maxannotationstokeep settings.

Expected: What works: If the Grafana 12.0 to 12.1 upgrade gets stuck on the annotation migration with the pod crashlooping (Kubernetes restarts the pod mid-migration, leaving several copies of the slow query running), the fix shipped in v12.2.3 and v12.3.1 via PR #113589, so upgrade past that.

When to use

You are seeing this: What was reported: Issue grafana/grafana#109101 (closed, 29 comments): ### What happened? Use this skill when you run into "Grafana 12.1 upgrade issue - db migration".

When not to use

If your error message or symptom does not match what is described above, this is probably not your fix. Search for your exact error text instead of forcing this one to fit.

Versions

Versions mentioned in the source: v12.2.3, v12.3.1. If you are on something much newer or older, the details may have shifted.

Why this happens

The original report does not dig into a root cause. It documents the symptom and the fix that resolved it.

Edge cases

• Also raise readiness and liveness probes to 10 minutes or more so Kubernetes does not kill the pod mid-migration, and trim the annotations table first with the maxage and maxannotationstokeep settings.

In the GitHub issue, the reporter asked: Grafana 12.1 upgrade issue - db migration. The working answer that came out of the thread: If the Grafana 12.0 to 12.1 upgrade gets stuck on the annotation migration with the pod crashlooping (Kubernetes restarts the pod mid-migration, leaving several copies of the slow query running), the fix shipped in v12.2.3 and v12.3.1 via PR #113589, so upgrade past that. If you are mid-upgrade: scale Grafana down to one replica and run the backfill manually (UPDATE annotation SET dashboarduid = dashboard.uid FROM dashboard WHERE annotation.dashboardid = dashboard.id AND annotation.dashboardid != 0 AND annotation.dashboarduid IS NULL), then let the migration finish. Also raise readiness and liveness probes to 10 minutes or more so Kubernetes does not kill the pod mid-migration, and trim the annotations table first with the maxage and maxannotationstokeep settings. Source: https://github.com/grafana/grafana/issues/109101