MySQL -> PostgreSQL migration in 5 steps
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:
Dump mysql database.
mysqldump --compatible=postgresql --default-character-set=utf8 \ -r databasename_mysql.sql -u root databasename
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 unique indices migration
You can get it here.
Restore dump to PostgreSQL database.
psql -f databasename_pg.sql -d databasename
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.
(Optional) Lanyrd's script loses
booleancolumns 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).