multica-ai/multica · error

acquire migration advisory lock: %w

Error message

acquire migration advisory lock: %w

What it means

Executing SELECT pg_advisory_lock($1) on the pinned migration connection failed. This is the blocking variant: a second runner (multi-replica Deployment, manual `migrate up` overlapping pod startup) normally queues here without erroring. An error means the session died while acquiring/waiting — server restart, network drop, backend termination, or ctx cancellation while queued.

Source

Thrown at server/cmd/migrate/main.go:375

	// *pgxpool.Conn for the whole run — calling pool.Exec would attach the
	// lock to a random connection that pgxpool could hand back out before
	// the loop finishes, making the lock effectively a no-op. We use the
	// blocking pg_advisory_lock (not pg_try_*) so a late-arriving runner
	// queues behind the current one instead of crash-looping; once it
	// acquires the lock the EXISTS checks below turn finished migrations
	// into no-op skips.
	//
	// We deliberately do NOT wrap the loop in a single transaction: the
	// repo already ships migrations using CREATE INDEX CONCURRENTLY,
	// which Postgres rejects inside a transaction block.
	conn, err := pool.Acquire(ctx)
	if err != nil {
		return fmt.Errorf("acquire migration connection: %w", err)
	}
	defer conn.Release()

	if _, err := conn.Exec(ctx, "SELECT pg_advisory_lock($1)", lockKey); err != nil {
		return fmt.Errorf("acquire migration advisory lock: %w", err)
	}
	// Best-effort explicit unlock on the success path. On error returns
	// the defer still runs; on os.Exit error paths in main() it does not,
	// but session-level advisory locks are released automatically when
	// the connection closes at process exit, so the next runner is never
	// permanently blocked.
	defer func() {
		if _, err := conn.Exec(ctx, "SELECT pg_advisory_unlock($1)", lockKey); err != nil {
			slog.Warn("failed to release migration advisory lock", "error", err)
		}
	}()

	// Create migrations tracking table.
	if _, err := conn.Exec(ctx, fmt.Sprintf(`
		CREATE TABLE IF NOT EXISTS %s (
			version TEXT PRIMARY KEY,
			applied_at TIMESTAMPTZ NOT NULL DEFAULT now()
		)

View on GitHub (pinned to 2c0912b6ec)

Solutions

1. Re-run migrate — the lock is session-scoped and freed when the dead session ends, and per-version EXISTS checks make re-runs no-ops for applied versions
2. Check who holds the lock: SELECT pid, ... FROM pg_locks WHERE locktype='advisory' AND objid=<key>
3. Increase the startup timeout of initContainers/jobs that run migrations so queued runners survive the wait

if _, err := conn.Exec(ctx, "SELECT pg_advisory_lock($1)", lockKey); err != nil {
    return fmt.Errorf("acquire migration advisory lock: %w", err)
}

When it happens

Trigger: ctx cancelled while this runner waits behind another holding migrationAdvisoryLockKey; Postgres failover/restart during the wait; operator runs pg_terminate_backend on the queued backend.

Common situations: Two replicas starting simultaneously and one's startup probe timeout cancels it while queued; server maintenance mid-migration-start.

Related errors

• acquire advisory lock 4246: %w
• acquire advisory lock %d: %w
• inspect concurrent index %q: %w
• relation %q exists but is not an index
• drop invalid concurrent index %s: %w

AI-assisted analysis of multica-ai/multica@2c0912b6ec (2026-08-15). Data as JSON: /api/errors/972dc0f7b12d7c4b. Report an issue: GitHub.