Mattermost HA migration lock coordination with PostgreSQL advisory locks during leader failover

Migration leadership and lock ownership

In a Mattermost HA cluster (v8.x) the first node that starts obtains a PostgreSQL advisory lock and runs all pending database migrations while other nodes wait. PostgreSQL advisory locks are session‑scoped and are automatically released when the backend process terminates, so a crashed node drops the lock even if migration statements have not finished.

Confirmed facts

• Lock release is automatic; no manual cleanup is required.
• The migration runner is documented to run only on the leader, but its code assumes the migration scripts are idempotent.
• If the leader fails after acquiring the lock but before completing the transaction, the lock disappears and the migration history table may show partial or duplicate entries.

Likely explanation (speculative)

When the leader crashes, the advisory lock vanishes, allowing any surviving node to reacquire it and start the migration process again. If the SQL scripts do not guarantee safe restart (e.g., they contain non‑idempotent DROP/CREATE statements), re‑running the migration can corrupt the schema or create duplicate objects, leading to inconsistency with the migration_history table.

Steps required for this case

1. Verify that the advisory lock is absent after the crash by querying pg_locks or pg_stat_activity on the primary database.
2. Inspect the Mattermost migration_history table for rows that indicate an incomplete or duplicated migration step.
3. If the lock is missing and the migration_history shows incomplete progress, confirm that no stray sessions hold the lock (they should not, as the backend died).
4. If inconsistencies are found, either run a manual repair of the schema (e.g., re‑apply failed statements) or restart the entire HA cluster to let the new leader reacquire the lock and re‑run migrations safely.

Open questions needing clarification

Are the migration scripts explicitly idempotent? If they are not, the recommendation to perform a full cluster restart becomes necessary. Additionally, does Mattermost version 8.0 differ from 9.x in how it handles advisory lock acquisition after a crash?