迁移时移动schema_version：重命名Schema foo至bar后Flyway报错咨询

Great question! This is a common gotcha with Flyway when renaming schemas, since Flyway relies entirely on the schema_version table to track migration state. Let’s break down why your original attempt failed, then walk through two reliable solutions:

Why your migration failed #

When Flyway starts a migration run, it first looks up the schema_version table in the schema(s) defined in your schemas config parameter. In your case, it bound to foo.schema_version upfront. After you ran ALTER SCHEMA foo RENAME TO bar, the foo schema no longer exists—so when Flyway tried to update the foo.schema_version table to mark the migration as complete, it couldn’t find the table, causing the failure.

Solution 1: Adjust Flyway config + single migration script #

This is the simplest approach if your database supports schema renaming and your Flyway version handles multiple schemas correctly:

1. Update your Flyway configuration

   • Set the schemas parameter to bar, foo (comma-separated). Flyway will search for schema_version in the listed schemas in order, so it’ll fall back to foo.schema_version until the rename is done.
   • Keep flyway.table set to its default value (schema_version).

2. Create your migration script (e.g., V2__Rename_foo_to_bar.sql)

   -- Rename the schema
   ALTER SCHEMA foo RENAME TO bar;
   

3. Run the migration

   • Flyway starts by checking for bar.schema_version (doesn’t exist yet), so it uses foo.schema_version to get the current migration state.
   • It executes the rename statement, which moves all objects (including foo.schema_version) into the new bar schema.
   • When Flyway goes to update the schema_version table, it now finds bar.schema_version (since bar is first in your schemas list) and updates it successfully.

4. Cleanup (optional but recommended)

   • After the migration succeeds, update your Flyway config to set schemas back to just bar to avoid any future confusion.

Solution 2: Manual schema_version copy + two-step migration #

If Solution 1 doesn’t work for your database/Flyway version (e.g., your DB doesn’t allow renaming to an existing schema), use this more explicit approach:

1. First migration: Prepare the new schema (V1__Prepare_schema_rename.sql)

   -- Create the target schema if it doesn't exist
   CREATE SCHEMA IF NOT EXISTS bar;
   
   -- Copy the structure and data of foo.schema_version to bar
   CREATE TABLE bar.schema_version AS SELECT * FROM foo.schema_version;
   
   -- Recreate the default Flyway constraints/indexes (match these to your actual schema_version table)
   ALTER TABLE bar.schema_version ADD PRIMARY KEY (version_rank);
   CREATE INDEX schema_version_vr_idx ON bar.schema_version (version_rank);
   CREATE INDEX schema_version_ir_idx ON bar.schema_version (installed_rank);
   CREATE INDEX schema_version_s_idx ON bar.schema_version (success);
   

2. Update Flyway config

   • Change the schemas parameter from foo to bar—now Flyway will use the new bar.schema_version table for all future operations.

3. Second migration: Rename the original schema (V2__Rename_foo_to_bar.sql)

   -- Drop the temporary schema_version we created (since the real one is coming from foo)
   DROP TABLE bar.schema_version;
   
   -- Rename the original schema to bar
   ALTER SCHEMA foo RENAME TO bar;
   

4. Verify

   • Check bar.schema_version to confirm all historical migration records are present, including the two new migrations you just ran.

内容的提问来源于stack exchange，提问作者RikardA