How to work with Rails ActiveRecord and Postgres incompatibility on a legacy project?

Question

I am working with a project that has been running for a while against MySQL. There are migrations such as this:

add_index "things", ["one", "two", "three", "andevenmorelongnamedfields"]

Which causes an index named like this index_things_on_one_and_two_and_three_and_andevenmorelongnamedfields. This is OK for MySQL but too long for PostgreSQL.

This question recommends altering existing migrations.

add_index "things", ["one", "two", "three", "andevenmorelongnamedfields"], :name => "index_things_on_one_and_two_and_three_and_more"

That would solve it superficially.

However the codebase is already running against MySQL so changing past migrations would be a bad idea. I'm trying to install from scratch against PostgreSQL which (I think) means running all migrations?

I may have missed something here (I'm more familiar with Django), but am I correct in thinking that ActiveRecord isn't compatible with PostgreSQL in this specific instance?

How can I solve this (i.e. get it working with both Postgres and MySQL) without creating integrity problems for others?

Answer 1

One way would be to just create and run new migrations on the MySQL side that rename the indexes to something shorter. Then you can run rake db:schema:load on the Postgres side and shouldn't have a problem.

Also note the warning at the top of db/schema.rb:

Note that this schema.rb definition is the authoritative source for your database schema. If you need to create the application database on another system, you should be using db:schema:load, not running all the migrations from scratch. The latter is a flawed and unsustainable approach (the more migrations you'll amass, the slower it'll run and the greater likelihood for issues).

This is definitely an example of the "flawed and unsustainable" bit...