Flyway迁移因null version_rank受阻，PostgreSQL9.5+Flyway5.0.7环境求助

Hey there, let's break down why your version_rank column isn't showing up or isn't populated properly. This field is a core part of Flyway 5.x's schema_version table (it handles sorting migration versions), and Flyway should create and maintain it automatically. When things go wrong, it's usually tied to one of these scenarios:

1. You manually modified the schema_version table #

If you've ever tweaked this table by hand—like deleting fields, changing constraints, or messing with data—you might have broken Flyway's expected structure. That can throw off its ability to write to or recognize version_rank during your latest migration.

Fix steps:

• First, back up all data in your schema_version table (you don't want to lose your migration history!)
• Recreate the table to match Flyway 5.0.7's standard structure for PostgreSQL with this SQL:

CREATE TABLE IF NOT EXISTS schema_version (
    version_rank INT NOT NULL,
    installed_rank INT NOT NULL,
    version VARCHAR(50) NOT NULL,
    description VARCHAR(200) NOT NULL,
    type VARCHAR(20) NOT NULL,
    script VARCHAR(1000) NOT NULL,
    checksum INT,
    installed_by VARCHAR(100) NOT NULL,
    installed_on TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    execution_time INT NOT NULL,
    success BOOLEAN NOT NULL,
    PRIMARY KEY (version)
);

• Restore your backed-up migration records, and make sure to set version_rank values in order (start at 1 for the oldest migration, increment by 1 for each subsequent one).

2. Your latest migration crashed mid-execution #

If the migration was interrupted unexpectedly—like a dropped database connection, insufficient permissions, or a locked table—Flyway might not have finished updating the schema_version table's structure. That could leave version_rank missing or unpopulated.

Fix steps:

• Check Flyway's execution logs first—look for errors about altering the schema_version table (like permission denied or table locks).
• If permissions are the issue, make sure the database user Flyway uses has ALTER TABLE and UPDATE privileges on the schema.
• If version_rank is missing entirely, add it manually and populate the values with these SQL commands:

-- Add the missing column
ALTER TABLE schema_version ADD COLUMN version_rank INT;

-- Populate version_rank based on the order migrations were installed
UPDATE schema_version 
SET version_rank = sub.rn
FROM (
    SELECT version, ROW_NUMBER() OVER (ORDER BY installed_rank) AS rn
    FROM schema_version
) AS sub
WHERE schema_version.version = sub.version;

-- Add the NOT NULL constraint Flyway expects
ALTER TABLE schema_version ALTER COLUMN version_rank SET NOT NULL;

3. Flyway configuration is off #

If you changed Flyway's configuration—like specifying a custom table name with the table parameter, or enabling settings that skip table structure checks—Flyway might not be maintaining the schema_version table correctly.

Fix steps:

• Double-check your Flyway config (whether it's a flyway.conf file or code-based settings) to make sure the table parameter is set to the default schema_version.
• Make sure you haven't enabled skipDefaultCallbacks or similar settings that would prevent Flyway from updating the table structure.
• Run Flyway's repair command to let it automatically fix the migration table's structure:

flyway repair -url=jdbc:postgresql://your-db-host:5432/your-db-name -user=your-db-user -password=your-db-pass

4. PostgreSQL environment quirks #

While Flyway 5.0.7 supports PostgreSQL 9.5, specific environment settings (like non-UTF8 character sets, enabled extensions that conflict, or corrupted database metadata) could cause the column creation to fail silently.

Fix steps:

• Check PostgreSQL's server logs for errors related to creating or altering the schema_version table.
• Confirm your database is using UTF-8 character set (Flyway works best with this).
• Test your migration scripts on a fresh PostgreSQL 9.5 instance to rule out issues with your current environment.

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