Rails 5 db migration: how to fix ActiveRecord::ConcurrentMigrationError

Advisory locking was added in Rails 5 to prevent unplanned concurrency errors during migration. The fix is to clear the DB lock that was left in place.

Review the locks by running this SQL against your DB:

SELECT DISTINCT age(now(), query_start) AS age, pg_stat_activity.pid,pg_locks.granted,pg_stat_activity.application_name,pg_stat_activity.backend_start, pg_stat_activity.xact_start, pg_stat_activity.state_change, pg_stat_activity.waiting, pg_stat_activity.state, pg_stat_activity.query_start, left(pg_stat_activity.query, 60)FROM pg_stat_activity, pg_locksWHERE pg_locks.pid = pg_stat_activity.pid

To clear a lock, run this SQL against your DB:

select pg_advisory_unlock({the pid of the lock you want to release})

So in my case the query was different

SELECT DISTINCT age(now(), query_start) AS age, pg_stat_activity.pid,pg_locks.granted,pg_stat_activity.application_name,pg_stat_activity.backend_start, pg_stat_activity.xact_start, pg_stat_activity.state_change, pg_stat_activity.state, pg_stat_activity.query_start, left(pg_stat_activity.query, 60)    FROM pg_stat_activity, pg_locks    WHERE pg_locks.pid = pg_stat_activity.pid

That will basically tell you the pids

0 years 0 mons 0 days 0 hours 0 mins -0.01005 secs    360    true    PostgreSQL JDBC Driver    2019-04-03 16:57:16.873609    2019-04-03 16:58:00.531675    2019-04-03 16:58:00.541727    active    2019-04-03 16:58:00.541725    SELECT DISTINCT age(now(), query_start) AS age, pg_stat_acti    17272    true    ""                        <insufficient privilege>    22640    true    ""                        <insufficient privilege>    29466    true    ""                        <insufficient privilege>

and after that you could simply unlock the pid with the following command:select pg_advisory_unlock(#{target_pid})

e.g.:

select pg_advisory_unlock(17272)select pg_advisory_unlock(22640)select pg_advisory_unlock(22640)select pg_advisory_unlock(360)

Cheers!

For me it was resolved in this way:

Select advisory locks:

SELECT pid, locktype, mode FROM pg_locks WHERE locktype = 'advisory';

SELECT pg_terminate_backend(<PID>);