Why do I receive ActiveRecord::ConcurrentMigrationError when running Rails migrations using PgBouncer?

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:

1. Open a psql session to your Postgres database, and use SELECT 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 either heroku pg:kill <PID>, or SELECT pg_terminate_backend(<PID>);.
2. Disable advisory locks in database.yml for Rails 6+ (older versions need a patch):

production:
  adapter: postgresql
  advisory_locks: false
  ...

3. Use session pooling for PgBouncer, where available.

What options are suitable depend on your use cases.