MySQL -> PostgreSQL migration in 5 steps

2014-09-17

If you have a relatively small database and downtime is acceptable, you can migrate from MySQL to PostgreSQL through dump conversion.

Step by step guide:

1. Dump mysql database.

   mysqldump --compatible=postgresql --default-character-set=utf8 \
             -r databasename_mysql.sql -u root databasename
   

2. Convert it using lanyrd's script

   python db_converter.py databasename_mysql.sql databasename_pg.sql
   

   I wanted activerecord schema compatible migration, so I tweaked the script in two places:

   • removed varchar limit doubling
   • removed unique indices migration

   You can get it here.

3. Restore dump to PostgreSQL database.

   psql -f databasename_pg.sql -d databasename
   

4. Then you need to add missing indices.

   Good thing that there is a script from Gitlab team for this.

   ruby add_index_statements.rb db/schema.rb > add_indices.rb
   

   It generates script that should be executed against your production database.

5. (Optional) Lanyrd's script loses default values for boolean columns so if you have such you need to recover them after conversion.

   ruby add_bolean_default.rb db/schema.rb > add_boolean_default_values.rb
   

   Than you need to do the same as in previous step.

   That's it! Your pg database is schema-compatible with mysql one (hopefully).