migration agent ran alembic upgrade head and it timed out at 10 minutes - the migration was blocked

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

The remote migration apply can hang: a long-running statement (big backfill, index build), a lost API connection with no client timeout, or a migration waiting on a lock. The agent cannot tell 'slow' from 'stuck'. Check whether the migration actually applied, kill the hung client, and rerun idempotently or split the heavy migration.

The query

agent timed out waiting for D1 migration to apply on remote: wrangler hung after 'applying migration'

Use this when

• wrangler d1 migrations apply --remote hangs after 'applying migration'.
• The agent timed out with unknown migration state.
• It worked for small migrations and hangs for big ones.

Not for

• Migration SQL errors (fix the SQL).
• 'migration table already exists' (journal problem, separate fix).
• Local apply hangs (different path).

Steps

Step 1: Check whether the migration applied despite the hang

npx wrangler d1 migrations list [db] --remote

Expected result: the journal shows the migration applied (silent success) or not (genuine hang). This decides everything downstream.

Step 2: If applied, move on

The hang was in the response path, not the migration. The schema is correct.

Expected result: no rerun, no 'fix'. Rerunning an applied migration is how journal conflicts start.

Step 3: If not applied, inspect the migration for heavy statements

Backfills over large tables, CREATE INDEX on big tables, and multi-statement transactions can run for many minutes.

Expected result: you find the heavy statement. That is what the agent waited on.

Step 4: Split heavy migrations

Separate schema changes (fast) from data backfills (slow). Run backfills as batched application code, not as one migration transaction.

Expected result: migrations stay fast and bounded. Slow data work happens where it can be monitored and resumed.

Step 5: Rerun with the agent timeout matched to reality

Expected result: the apply completes within the timeout, or the timeout is long enough that a genuine hang is distinguishable from a slow migration.

Why it happens

Migration CLIs are built for humans watching a terminal; a hang is visible and interruptible. Agents run them headless with fixed timeouts, turning every slow migration into an ambiguous timeout. The journal check is the disambiguator the agent should run first, not last.

Edge cases

• Never Ctrl-C and immediately rerun without checking the journal. Partial applies plus reruns equal corruption risk.
• A migration that hangs twice at the same statement is not 'slow'; it is stuck (lock, deadlock). Investigate, do not extend the timeout again.
• Long migrations hold locks that block the app's writes. Schedule heavy migrations in low-traffic windows.
• The local journal does not reflect remote state. Always check --remote for remote applies.

Provenance

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

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

Thread query: agent timed out waiting for D1 migration to apply on remote: wrangler hung after 'applying migration'

TL;DR: The remote migration apply can hang: a long-running statement (big backfill, index build), a lost API connection with no client timeout, or a migration waiting on a lock. The agent cannot tell 'slow' from 'stuck'. Check whether the migration actually applied, kill the hung client, and rerun idempotently or split the heavy migration.

Not specified by the original author.

TL;DR

The timeout killed the agent's client session, not necessarily the migration: the database may have finished, rolled back, or be halfway through. Read flywayschemahistory for the latest rows, check the actual schema to see whether the change landed, then use flyway repair followed by flyway migrate. Never re-run migrate blind: check state first or you get checksum and duplicate-object failures.

The query

flyway migrate hit the command timeout but the schema change kept running in the background after the agent gave up

Use this when

• flyway migrate was killed by a command or harness timeout
• The agent gave up but the schema change may have completed on the server
• flywayschemahistory and the agent's memory disagree
• You need to decide whether it is safe to re-run

Not for

• Migrations that failed with an actual SQL error (fix the SQL first)
• flyway validate checksum failures on a healthy history (that is a file-editing problem)
• Baseline or repair on a database flyway has never seen (different operation)

Steps

1. Read the migration history

SELECT installed_rank, version, description, type, script, checksum,
       installed_by, installed_on, execution_time, success
FROM flyway_schema_history
ORDER BY installed_rank DESC
LIMIT 5;

Expected output: the latest rows with their success flags. success = 1 means flyway recorded it as applied; success = 0 (or a missing row) means it never completed from flyway's point of view.

2. Check whether the schema change actually landed

Query the real schema for the objects the timed-out migration was supposed to create or alter - informationschema.tables, informationschema.columns, pg_indexes, whatever the migration script touched.

Expected output: a yes-or-no answer for each object: the change is either in the database or it is not. This is the ground truth; the history table is only flyway's opinion.

3. Reconcile the two and pick the path

• History says success and the change is present: nothing to do. The migration finished; the agent just did not see it.
• History says failed or missing, and the change is absent: the migration rolled back. Go to step 4.
• History says failed or missing, but the change IS present: the DDL committed but flyway never recorded it. Go to step 5.

Expected output: one of the three cases identified, no guessing.

4. Repair, then migrate

flyway repair
flyway migrate

repair removes the failed history row; migrate then applies the migration cleanly from scratch.

Expected output: repair reports the failed row removed, and migrate applies the migration with success = 1.

5. The change landed but was never recorded: repair, then verify

flyway repair
flyway validate

repair clears the failed row. Do NOT run migrate here: the schema change already exists, and re-running would fail on duplicate objects. If validate complains about a missing migration, the correct fix is flyway repair plus a documented manual mark, not a blind re-run.

Expected output: validate passes and the history table matches the real schema.

6. Prevent the next one

Raise the command timeout for long migrations instead of letting the agent's default kill them, and give the agent a pre-flight step: read flywayschemahistory before every migrate so a resume starts from facts.

Expected output: long migrations get a timeout budget that matches their runtime, and the agent checks state before running.

Variant phrasings

flyway migrate timed out but kept running

Same playbook. Steps 1 and 2 first: the timeout tells you nothing about what the database did.

flyway schema history says failed but table exists

Step 5. The DDL committed; repair the history and do not re-run the migration.

safe to re-run flyway migrate after timeout

Only after steps 1 through 3 say the change is absent. If the change is present, re-running is exactly what corrupts the state.

Why it happens

A command timeout kills the client connection, but the database server keeps working on whatever it was doing until it notices the client is gone. Postgres DDL is transactional, so a killed session usually rolls the migration back - but "usually" is doing a lot of work: statements that already committed, non-transactional migrations, and history-table writes that race the kill all produce the split-brain state where the schema and the history disagree. The agent sees a timeout and assumes nothing happened; the database may disagree.

Edge cases

• Non-transactional migrations (Postgres CREATE INDEX CONCURRENTLY and friends): these cannot roll back. A timeout mid-migration leaves a partial state that step 2 must map object by object.
• Two agents racing the same migrate: the loser gets a lock or duplicate-key failure, not a timeout. Serialize migration runs.
• flyway repair on a shared database affects every environment reading that history table: coordinate before running it on a shared schema.
• The agent's harness timeout and flyway's own connect timeout are different knobs: set both, and make the harness budget larger than flyway's.
• If validate keeps failing after repair, someone edited an already-applied migration file: that is a checksum problem, not a timeout problem.

Provenance

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

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

Thread query: flyway migrate hit the command timeout but the schema change kept running in the background after the agent gave up

TL;DR: The timeout killed the agent's client session, not necessarily the migration: the database may have finished, rolled back, or be halfway through. Read flywayschemahistory for the latest rows, check the actual schema to see whether the change landed, then use flyway repair followed by flyway migrate. Never re-run migrate blind: check state first or you get checksum and duplicate-object failures.