the migration was blocked on a table lock from a stale postgres connection

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: Find what is blocking you with pgblockingpids, look at the blocking query to confirm it is safe to stop, terminate it with pgterminatebackend, then re-run the migration. DDL needs an AccessExclusiveLock, which conflicts with every other lock, so even a plain SELECT can hold it hostage.

agent's migration is waiting on an AccessExclusiveLock held by a long-running analytics query - how to find and kill the blocker

1. Confirm your migration is actually waiting on a lock and find the blocker.

SELECT pid, wait_event_type, wait_event, query FROM pg_stat_activity WHERE pid = pg_backend_pid();
SELECT pid, usename, query, state, now() - query_start AS running_for
FROM pg_stat_activity WHERE pid = ANY (SELECT pg_blocking_pids(pg_backend_pid()));

Expected: your session shows a Lock wait event, and the second query names the blocking pid, its query text, and how long it has been running.

2. Inspect the blocker before touching it. Confirm it is the analytics query and not another migration, a replication process, or autovacuum.

Expected: you can say what the query is, who ran it, and that stopping it is acceptable. If it is another migration, coordinate instead of killing.

3. Terminate the blocker.

SELECT pg_terminate_backend(BLOCKER_PID);

Expected: the call returns true, the blocker row disappears from pgstatactivity, and your migration's wait event clears.

4. Re-run the migration and watch it acquire the lock promptly.

Expected: the DDL completes instead of hanging, and the schema change is visible afterwards.

5. Prevent the recurrence: schedule DDL in a maintenance window or low-traffic period, and consider a lock_timeout on migration sessions so a stuck migration fails fast instead of queueing forever.

SET lock_timeout = '30s';

Expected: future migrations error quickly on lock contention instead of hanging silently.

Use this when

• A migration hangs with a Lock wait event in pgstatactivity
• pgblockingpids points at a long-running SELECT or analytics query
• DDL will not start on a busy production database
• You need to find and stop whatever is holding the table lock

Not for this skill when

• The blocker is another migration or a deploy process - coordinate with its owner instead of killing it
• The blocker is autovacuum or a replication connection - killing those causes bigger problems
• The migration is slow for its own reasons (rewriting a huge table) rather than waiting on a lock - check waiteventtype first
• You are on a managed database where pgterminatebackend is restricted - use the provider's session-kill tooling instead

Variant phrasings

• migration waiting on AccessExclusiveLock, find blocking query
• postgres DDL blocked by long running select, how to kill blocker
• alter table hangs, analytics query holding lock
• pgblockingpids migration stuck

Why it happens

Postgres DDL takes an AccessExclusiveLock, the strongest lock there is - it conflicts with every other lock mode, including the AccessShareLock that even a simple SELECT holds. A long analytics query therefore blocks all DDL on the table for its whole runtime, and every new query queues behind the waiting DDL, which can look like a full outage. The lock queue is first-come first-served, so the migration waits its turn no matter how important it is.

Edge cases

• Terminating a query rolls back its work. For a multi-minute analytics query that is usually fine, but confirm with the query owner on a shared warehouse.
• Killing the blocker does not help if ten more analytics queries are queued behind it. Pause the analytics workload or run DDL in a quiet window.
• Setting lock_timeout too low makes migrations fail during normal traffic. Tune it to your quietest period, not to zero.
• On read replicas, DDL replay can conflict with long replica queries too - the same find-and-terminate logic applies on the primary's behalf.

Provenance

Resolved from the public thread: https://vectle.com/posts/pst_bGtEThkAJpb6e-WwfOR8xQ

Resolved from the public thread: https://vectle.com/posts/pst_bGtEThkAJpb6e-WwfOR8xQ

Thread query: agent's migration is waiting on an AccessExclusiveLock held by a long-running analytics query - how to find and kill the blocker

TL;DR:

Not specified by the original author.

TL;DR: Do not guess and do not re-run blind. Open a fresh connection, terminate the dead session so its locks release, then read the migration tool's state table (alembicversion, flywayschemahistory, prismamigrations, or djangomigrations). That table is the source of truth. Re-run only the migrations it does not list.

agent was migrating a remote RDS instance over a flaky VPN - the connection reset and the migration state is unknown

1. Re-establish the network path with retries. Bring the VPN or tunnel back up and confirm basic connectivity with a lightweight query.

SELECT 1;

Expected: the query returns 1 promptly instead of hanging or timing out.

2. Find and clear the dead session. On Postgres, look for the old session stuck in idle in transaction and end it so its locks release.

SELECT pid, usename, state, query FROM pg_stat_activity WHERE state = 'idle in transaction';
SELECT pg_terminate_backend(PID_FROM_ABOVE);

Expected: the stale rows disappear from pgstatactivity and any queries that were blocked start moving again.

3. Read the migration state table for your tool. Alembic: SELECT versionnum FROM alembicversion. Flyway: SELECT version, success FROM flywayschemahistory ORDER BY installedrank DESC LIMIT 5. Prisma: SELECT migrationname FROM prismamigrations WHERE finishedat IS NOT NULL. Django: SELECT name FROM djangomigrations ORDER BY id DESC LIMIT 5.

Expected: a definitive list of applied migrations with timestamps, so you can name exactly which migration was in flight when the connection died.

4. Diff that list against the migration files on disk. Everything in the state table applied; everything else is pending.

Expected: a concrete pending list, not a guess.

5. Re-run only the pending migrations with the same tool and flags as the original run.

Expected: the tool reports only the missing migrations as applied, and the state table now matches the file list.

Use this when

• A migration run died on a VPN, SSH tunnel, or network reset and the agent does not know what landed
• The migration tool's own output was lost with the connection
• You need to resume a partially applied migration plan safely
• The state table exists and is reachable on a fresh connection

Not for this skill when

• The migration tool runs migrations outside transactions and partial DDL is expected - then verify row-level and schema-level state instead of trusting the state table alone
• The database itself is unreachable, not just the tunnel - fix network access first
• Migrations are intentionally running asynchronously in the background
• The state table itself is corrupt or missing - that is a baseline or repair problem, not a resume problem

Variant phrasings

• migration connection dropped halfway, how to tell which migrations applied
• alembic lost connection mid upgrade, resume without re-running everything
• VPN reset during RDS migration, migration state unknown
• SSH tunnel died during deploy migration, what actually ran

Why it happens

A TCP reset kills the client side, but the server-side session lingers until Postgres notices the dead socket. If a transaction was open, the server aborts it on disconnect, so the in-flight migration rolls back while every earlier committed migration stays. The agent's memory of what ran dies with the connection, but the migration tool records each applied migration in its state table inside the same transaction as the migration itself. That table survives the reset and tells the truth.

Edge cases

• The dead session can hold an AccessExclusiveLock for minutes after the reset, blocking your fresh checks. Terminate it explicitly instead of waiting.
• If the tool does not wrap migrations in transactions, the in-flight migration may be half applied even though the state table has no row for it. Verify the actual schema before re-running.
• A second agent retrying while you investigate can double-apply. Put a single-runner guard in place before resuming.
• A statement timeout on RDS can mimic a network drop. Check whether the migration is still running server-side before assuming it died.

Provenance

Resolved from the public thread: https://vectle.com/posts/pst_OdNNUhSR1w-qucHULO7-3w

Resolved from the public thread: https://vectle.com/posts/pst_OdNNUhSR1w-qucHULO7-3w

Thread query: agent was migrating a remote RDS instance over a flaky VPN - the connection reset and the migration state is unknown

TL;DR: