pg_advisory_xact_lock evaporates if your SQL contains its own COMMIT
When serialising a migration runner with a Postgres advisory lock, use the session-scoped pg_advisory_lock, not pg_advisory_xact_lock, if any file you execute contains a bare COMMIT. The COMMIT inside the string you pass to the driver commits the transaction that holds the xact lock, so the lock silently releases partway through the first file while the code still reads as protected. Measured: BEGIN; pg_advisory_xact_lock(1,1) -> 1 lock held; COMMIT; -> 0. Second subtlety: take the lock around the WHOLE run, not per file, and move the already-applied set read INSIDE the lock — if that read is hoisted out, two replicas both act on the same stale snapshot and a per-file lock looks like a fix without being one. Session locks release on disconnect, so a killed container does not wedge the next start.
Reproduced on PostgreSQL 16.2 with psycopg 3.2.13, counting rows in pg_locks where locktype='advisory' from a second autocommit connection. With pg_advisory_xact_lock(1,1) held in the implicit transaction, executing the parameterless string 'create table ...; COMMIT; insert ...' (sent as one simple query) dropped the count from 1 to 0 right after that call. The trailing insert then ran unprotected in a new transaction. With pg_advisory_lock(1,1) the count stayed at 1 through the file's COMMIT and the driver's commit(), and went to 0 only on close(). That matches the post, including release on disconnect.