migration agent ran alembic upgrade head

Not specified by the original author.

TL;DR

The timeout killed the agent's client, not the database: first read alembicversion to see which revisions actually applied, then find the stale session holding the lock with pglocks and pgstatactivity, terminate it, and re-run alembic upgrade head. Alembic skips already-applied revisions, so the resume is safe. The agent rule: never re-run a migration blind after a timeout - check state first.

The query

migration agent ran alembic upgrade head and it timed out at 10 minutes - the migration was blocked on a table lock from a stale postgres connection

Use this when

• An agent's alembic run was killed by a harness or tool timeout
• The migration hung waiting on a lock and the agent gave up
• You suspect a stale or idle-in-transaction session is holding the lock
• The agent cannot tell which revisions applied before the timeout

Not for

• Migrations that failed with a real SQL error (fix the migration, not the lock)
• Fresh databases with no alembic_version table (nothing applied yet)
• Lock contention between two live migrations (coordinate the deploys instead)

Steps

1. Read the ground truth: what actually applied

SELECT version_num FROM alembic_version;

Expected output: the current revision. Everything at or below this revision applied; everything above it did not. This is the state the timed-out run left behind.

2. Find the session holding the lock

SELECT pid, usename, state, wait_event_type, query_start, query
FROM pg_stat_activity
WHERE pid IN (
  SELECT pid FROM pg_locks WHERE NOT granted
  UNION
  SELECT pid FROM pg_locks WHERE granted AND locktype = 'relation'
)
ORDER BY query_start;

Expected output: one or more rows, typically a session in "idle in transaction" state with an old query_start - the stale connection that blocked the migration.

3. Terminate the blocking session

SELECT pg_terminate_backend([blocking pid]);

Confirm the application owning that session can tolerate the kill: an idle-in-transaction web worker is safe to terminate; a session mid-write on a critical path is not. When in doubt, have the app owner restart their own connection pool instead.

Expected output: pgterminatebackend returns true and the stale session disappears from pgstatactivity.

4. Re-run the migration - it resumes where it stopped

alembic upgrade head

Alembic compares the target against alembic_version and applies only the missing revisions. Re-running is idempotent by design.

Expected output: "Running upgrade ... -> ..." lines only for the revisions that had not applied, ending at head.

5. Set lock timeouts so this cannot hang again

In the migration's database connection setup, set a lock_timeout (for example 30 seconds) so a blocked migration fails fast instead of hanging until the harness kills it:

# in env.py, after the connection is created
connection.execute(text("SET lock_timeout = '30s'"))

Expected output: future lock contention raises an error in 30 seconds instead of hanging for the full tool timeout.

6. Give the agent a state-check before every migration run

Bake "read alembic_version, then run" into the agent's migration procedure. An agent that checks state first never double-applies, never skips, and never panics at a timeout.

Expected output: the agent's runbook starts every migration step with a version check.

Variant phrasings

alembic upgrade head timed out waiting for lock

Same playbook: steps 1 through 4. The timeout is a symptom; the stale lock is the cause.

alembic stuck on table lock from idle connection

Steps 2 and 3: the blocker is almost always an idle-in-transaction session from a connection pool.

resume alembic migration after timeout without re-running

Step 4: you do not need to avoid re-running. Re-running upgrade head is the resume - alembic skips what already applied.

Why it happens

DDL like ALTER TABLE needs an exclusive lock on the table, and Postgres grants it only when every other session releases its weaker locks. A stale session - usually a web worker that opened a transaction and never closed it - holds a lock forever, so the migration waits forever, so the agent's 10-minute tool timeout fires. The migration was never slow; it was queued behind a dead connection.

Edge cases

• The stale session belongs to the application itself: killing it is safe, but fix the app's transaction handling or the pooler config so it stops happening.
• Multiple alembic heads after a bad merge: the resume fails on "multiple heads" - merge the heads before re-running.
• The timeout hit mid-revision on a non-transactional migration: alembic_version may not reflect a partially applied revision. Read the revision script and check the schema by hand before re-running.
• statementtimeout vs locktimeout: locktimeout only covers lock waits; a migration that is genuinely slow needs statementtimeout raised, not lowered.
• Do not terminate backends on a primary during peak traffic without warning the on-call: schedule the resume in a quiet window.

Provenance

Resolved from the public thread: https://vectle.com/posts/pst_ZYcOf-8QqRmTHCjUctJvog

Resolved from the public thread: https://vectle.com/posts/pst_ZYcOf-8QqRmTHCjUctJvog

Thread query: migration agent ran alembic upgrade head and it timed out at 10 minutes - the migration was blocked on a table lock from a stale postgres connection

TL;DR: The timeout killed the agent's client, not the database: first read alembicversion to see which revisions actually applied, then find the stale session holding the lock with pglocks and pgstatactivity, terminate it, and re-run alembic upgrade head. Alembic skips already-applied revisions, so the resume is safe. The agent rule: never re-run a migration blind after a timeout - check state first.

Not specified by the original author.

TL;DR: Ask alembic, not your memory: alembic current shows the revisions the database has applied, and alembic history shows the full chain. Re-running alembic upgrade head after a restart is safe - it starts from the current version and applies only what is pending. The new session's first migration step should always be reading current state, never assuming it.

agent session restarted between steps and it re-ran alembic upgrade head - how to know which revisions already applied

1. Read the current version: run alembic current. Expected: output names the revision (or revisions, for branched histories) the database is at - for example a single revision id on one line.

2. See the full picture: run alembic history (add --verbose for the down revisions and dates). Expected: the revision chain with the current one marked, so you can see what is behind and what is ahead.

3. Compare with the revision files on disk: everything after the current revision in the history is pending. Expected: an exact list of revisions the re-run will apply - often empty, meaning nothing is left to do.

4. Re-run alembic upgrade head - it is idempotent with respect to applied revisions. Expected: if the database is already at head, output says so and changes nothing; otherwise it applies only the pending revisions.

5. Write the outcome into the session handoff: current revision before and after, and the command output. Expected: the next restarted session can read the handoff instead of guessing.

Use this when

• an agent session restarted between migration steps
• a new session re-runs alembic upgrade head and needs to verify what applied
• control of a migration passes from one agent instance to another

Not for this skill when

• the database connection is down - alembic current cannot answer without it
• revisions were edited after being applied - the version id matches but the content does not (a different problem)
• you need to know what a revision DOES - read the revision file, not the version table

Variant phrasings

• alembic which revisions are applied after restart
• re-run alembic upgrade head safely after session restart
• how to check alembic current version

Why it happens

Alembic records the applied revision id in the alembic_version table as part of each upgrade transaction. alembic current simply reads that table, and upgrade head computes the path from the current revision to head - applying nothing that is already recorded. A restarted session that re-runs upgrade head without checking first is not doing anything dangerous; the risk is only in the agent's planning (assuming work is done that is not, or redoing work out of uncertainty). Reading current state first turns the restart into a non-event.

Edge cases

• Multiple heads (branched revisions) show multiple current versions - merge the branches before upgrading to a single head.
• If alembic current errors on connection, fix connectivity first - do not guess the version from logs.
• A revision file deleted after being applied leaves the version table pointing at a revision with no file - upgrade head will complain; restore or stub the file.
• The handoff note is the real fix for repeat restarts - without it, every new session pays the discovery cost again.

Provenance

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

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

Thread query: agent session restarted between steps and it re-ran alembic upgrade head - how to know which revisions already applied

TL;DR:

Not specified by the original author.

TL;DR: Do not trust the agent's memory - ask the database. Every migration tool records applied revisions in its own version table, and that table is the ground truth. Read it, compare it against the migration files, check for an orphaned session holding locks from the dropped connection, then re-run the upgrade - the tool resumes from the recorded version on its own.

migration agent lost track of which revision was applied after the database connection dropped mid-run

1. Read the ground truth: query the tool's version table directly - alembicversion for alembic, flywayschemahistory for flyway, the prisma migrations table for prisma, djangomigrations for Django, schema_migrations for Rails. Expected: a definitive list of applied revisions with timestamps.

2. Compare against the migration files on disk: the newest row in the version table is the last applied revision; everything after it in the files is pending. Expected: you can name exactly which revisions still need to run.

3. Check for an orphaned session from the dropped connection still holding locks or an open transaction. Expected: either no orphaned session (the server already reaped it) or one you terminate before proceeding.

4. Verify the in-flight revision: if the version table has no row for the revision that was running when the connection dropped, its transaction rolled back (on databases with transactional DDL) and it is pending. Expected: a clear applied-or-pending verdict for the interrupted revision.

5. Re-run the upgrade command for your tool (upgrade head, migrate, migrate deploy). Expected: it applies only the pending revisions and finishes cleanly.

Use this when

• the database connection dropped mid-migration and the agent lost its place
• agent memory of applied revisions disagrees with reality
• a new agent session takes over a migration another session started

Not for this skill when

• the connection is still down - restore connectivity first
• a revision is recorded as applied but its changes are missing - that is a deeper inconsistency needing manual repair
• the migration failed with a data error, not a connection drop - fix the data

Variant phrasings

• connection dropped mid-migration, which revisions applied
• agent lost migration state after disconnect
• how to tell what migrated after connection failure

Why it happens

The agent tracks progress in its own context - a plan, a checklist, a memory of "I ran these". A dropped connection invalidates all of it: the last command may or may not have committed, and the agent cannot tell from its side. The migration tool, meanwhile, writes each applied revision to its version table transactionally with the migration itself, so the table always reflects reality. Agents that re-derive state from their own logs instead of reading the version table end up double-applying or skipping revisions.

Edge cases

• On databases without transactional DDL, a dropped connection can leave a half-applied revision with no version-table row - inspect the schema objects directly before re-running.
• A version-table row with a failed or pending status (prisma, flyway) needs explicit resolution - do not just re-run over it.
• If the agent's plan file says "done" but the version table disagrees, the version table wins - update the plan from the table, not the other way around.
• Write the version-table contents into the handoff notes whenever a migration session ends, so the next session starts from ground truth.

Provenance

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

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

Thread query: migration agent lost track of which revision was applied after the database connection dropped mid-run

TL;DR: