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

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.

1. 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.

1. 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.

1. 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.

1. 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