Issue
You receive ActiveRecord::ConcurrentMigrationError
when running Rails databases migrations, using a connection that is pooled by PgBouncer (either through the PgBouncer buildpack, or Connection Pooling).
Resolution
Rails 5.2 introduced new handling of releasing an advisory lock, obtained when performing migrations, to prevent concurrent migrations (ref: https://github.com/rails/rails/pull/31189). When the lock fails to be released, an exception is raised with an error message to that effect.
As advisory locks are session-local, there is an incompatibility with PgBouncer being used in the transaction
pooling mode, which is the default for both the PgBouncer buildpack (ref: https://github.com/heroku/heroku-buildpack-pgbouncer), and Connection Pooling (ref: https://devcenter.heroku.com/articles/postgres-connection-pooling).
As a result, migrations may fail due to an advisory lock is not being found, and therefore is unable to be released. This incompatibility can also leave advisory locks active.
There are three options to resolve this:
- Open a
psql
session to your Postgres database, and useSELECT pid, locktype, mode FROM pg_locks WHERE locktype = 'advisory'
to identify the PID of Postgres backends that are currently holding an advisory lock. You can then terminate these using eitherheroku pg:kill <PID>
, orSELECT pg_terminate_backend(<PID>);
. - Disable advisory locks in
database.yml
for Rails 6+ (older versions need a patch):
production:
adapter: postgresql
advisory_locks: false
...
- Use
session
pooling for PgBouncer, where available.
What options are suitable depend on your use cases.