{"record":{"id":"736fe911cbdd5c4a","repo":"multica-ai/multica","slug":"acquire-migration-connection-w","errorCode":null,"errorMessage":"acquire migration connection: %w","messagePattern":"acquire migration connection: %w","errorType":"console","errorClass":null,"httpStatus":null,"severity":"error","filePath":"server/cmd/migrate/main.go","lineNumber":370,"sourceCode":"\tif lockKey == 0 {\n\t\tlockKey = migrationAdvisoryLockKey\n\t}\n\n\t// pg_advisory_lock is scoped to a single session, so we must pin one\n\t// *pgxpool.Conn for the whole run — calling pool.Exec would attach the\n\t// lock to a random connection that pgxpool could hand back out before\n\t// the loop finishes, making the lock effectively a no-op. We use the\n\t// blocking pg_advisory_lock (not pg_try_*) so a late-arriving runner\n\t// queues behind the current one instead of crash-looping; once it\n\t// acquires the lock the EXISTS checks below turn finished migrations\n\t// into no-op skips.\n\t//\n\t// We deliberately do NOT wrap the loop in a single transaction: the\n\t// repo already ships migrations using CREATE INDEX CONCURRENTLY,\n\t// which Postgres rejects inside a transaction block.\n\tconn, err := pool.Acquire(ctx)\n\tif err != nil {\n\t\treturn fmt.Errorf(\"acquire migration connection: %w\", err)\n\t}\n\tdefer conn.Release()\n\n\tif _, err := conn.Exec(ctx, \"SELECT pg_advisory_lock($1)\", lockKey); err != nil {\n\t\treturn fmt.Errorf(\"acquire migration advisory lock: %w\", err)\n\t}\n\t// Best-effort explicit unlock on the success path. On error returns\n\t// the defer still runs; on os.Exit error paths in main() it does not,\n\t// but session-level advisory locks are released automatically when\n\t// the connection closes at process exit, so the next runner is never\n\t// permanently blocked.\n\tdefer func() {\n\t\tif _, err := conn.Exec(ctx, \"SELECT pg_advisory_unlock($1)\", lockKey); err != nil {\n\t\t\tslog.Warn(\"failed to release migration advisory lock\", \"error\", err)\n\t\t}\n\t}()\n\n\t// Create migrations tracking table.","sourceCodeStart":352,"sourceCodeEnd":388,"githubUrl":"https://github.com/multica-ai/multica/blob/2c0912b6ec764b373d44eeea1e80f0d9f11ab417/server/cmd/migrate/main.go#L352-L388","documentation":"pool.Acquire(ctx) failed while the migration runner pinned a dedicated session to hold the pg_advisory_lock for the whole loop. The pinning is essential: a session-scoped advisory lock on a random pooled connection could be silently handed back and the lock lost. Failure means ctx done, pool closed, or the pool cannot establish a backend connection.","triggerScenarios":"Context cancelled before acquisition; Postgres unreachable/at max_connections so no new backend can be created; pool.MaxConns reached with all connections busy (unusual in the dedicated migrate process).","commonSituations":"Migration run as a k8s initContainer/job while Postgres is not yet accepting connections; another migration replica holding connections; SIGTERM during pod startup.","solutions":["Verify connectivity/credentials: pg_isready and psql with the same DSN","If Postgres is still starting, add a readiness gate/initContainer wait, then re-run — the run is resumable by design","Check for too many connections: SELECT count(*) FROM pg_stat_activity vs max_connections"],"exampleFix":"# before: job races Postgres startup\n# after: wait for readiness first\nuntil pg_isready -h $PGHOST -p $PGPORT -U $PGUSER; do sleep 2; done\nmigrate up","handlingStrategy":"retry","validationCode":"if err := pool.Ping(ctx); err != nil { /* wait for DB readiness, then retry */ }","typeGuard":null,"tryCatchPattern":"conn, err := pool.Acquire(ctx)\nif err != nil {\n    return fmt.Errorf(\"acquire migration connection: %w\", err)\n}","preventionTips":["Put a pg_isready wait/initContainer in front of migration jobs","Monitor Postgres connection count vs max_connections","Re-running migrations is safe — advisory lock plus EXISTS checks dedupe work"],"tags":["go","pgx","connection-pool","migration","k8s"],"backgroundTag":null,"analyzedSha":"2c0912b6ec764b373d44eeea1e80f0d9f11ab417","analyzedAt":"2026-08-15T13:25:18.241Z","schemaVersion":2},"datasetVersion":"2026-08-15T17:31:12.345Z"}