multica-ai/multica · error

acquire migration connection: %w

Error message

acquire migration connection: %w

What it means

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.

Source

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

	if lockKey == 0 {
		lockKey = migrationAdvisoryLockKey
	}

	// pg_advisory_lock is scoped to a single session, so we must pin one
	// *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.

View on GitHub (pinned to 2c0912b6ec)

Solutions

1. Verify connectivity/credentials: pg_isready and psql with the same DSN
2. If Postgres is still starting, add a readiness gate/initContainer wait, then re-run — the run is resumable by design
3. Check for too many connections: SELECT count(*) FROM pg_stat_activity vs max_connections

Example fix

# before: job races Postgres startup
# after: wait for readiness first
until pg_isready -h $PGHOST -p $PGPORT -U $PGUSER; do sleep 2; done
migrate up

if err := pool.Ping(ctx); err != nil { /* wait for DB readiness, then retry */ }

conn, err := pool.Acquire(ctx)
if err != nil {
    return fmt.Errorf("acquire migration connection: %w", err)
}

When it happens

Trigger: 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).

Common situations: Migration run as a k8s initContainer/job while Postgres is not yet accepting connections; another migration replica holding connections; SIGTERM during pod startup.

Related errors

• acquire advisory-lock connection: %w
• connect to database: %w
• ping database: %w
• inspect concurrent index %q: %w
• relation %q exists but is not an index

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