We're encountering issues with using Flyway for database migrations with multiple nodes in parallel, backed by a PostgreSQL database behind PgBouncer with transaction pooling.
The problem is that when multiple nodes start up at the same time, Flyway gets an exclusive lock but this seems to be a session lock, which isn't supported by PgBouncer transaction pooling (as multiple nodes may get the same session). This then causes each node to not start up because they've locked each other.
Is there anything we can change or configure in Flyway to support this? We'd prefer not to switch away from transaction pooling if possible, as that's our main motivation for using PgBouncer.
At the moment, Flyway doesn't support PgBouncer, so you're seeing errors because of that lack of support. No work arounds from the developers currently. I'd suggest opening an issue on the Community Github. That's the best way to get changes in.
As a workaround, we're currently configuring two data sources for our application - one to PgBouncer as normal, and another with a single connection that's used solely for Flyway that bypasses PgBouncer and connects directly to the PostgreSQL back-end.