Search code examples
postgresqlflyway

An early versioned migration is no longer valid SQL in an upgraded version of Postgres

In testing an upgrade to our Postgres database, we've discovered that one of our oldest versioned migration files is no longer valid SQL. This isn't an issue for the production database which (of course) has those migrations already in the schema_history_table, but standing up any new sandboxes is now made impossible by this broken V file.

What's the best way to bring an old V file into the modern world without forever orphaning our production database?

Solution

  • Of the top of my head I can think of a few possible options.

    1. Configure postgres to enable previous version compatibility. I'm no expert at this, but I think there are some options here.
    2. Just modify the historic migration scripts to they now work with the new version. This will mean that you can't stand up old versions any longer, but does this matter to you? I think that you'll need to run flyway repair after you do this, as Flyway will detect that the files have been tampered with.
    3. Create a parallel set of scripts, one for each version, putting them in different folders. Then use the flyway.locations option to specify different folders depending on the version of the target.