MySQL to PostgreSQL Converter
Lanyrd's MySQL to PostgreSQL conversion script. Use with care.
This script was designed for our specific database and column requirements - notably, it doubles the lengths of VARCHARs due to a unicode size problem we had, places indexes on all foreign keys, and presumes you're using Django for column typing purposes.
GitLab-specific changes
The gitlab branch of this fork contains the following changes made for
GitLab.
- Guard against replacing '0000-00-00 00:00:00' inside SQL text fields.
- Replace all MySQL zero-byte string literals
\0. This is safe as of GitLab 6.8 because the GitLab database schema contains no binary columns. - Never set 'NOT NULL' constraints on datetimes.
- Drop sequences before creating them.
- Preserve default values of boolean (originally
tinyint(1)) columns. - Import all indexes.
- Import index names.
- Drop tables before creating.
- Drop indexes before creating.
How to use
First, dump your MySQL database in PostgreSQL-compatible format
mysqldump --compatible=postgresql --default-character-set=utf8 \
-r databasename.mysql -u root gitlabhq_production -p
Then, convert it using the dbconverter.py script.
python db_converter.py databasename.mysql databasename.psql
It'll print progress to the terminal
Now we have a DB dump that can be imported but the dump will be slow due to existing indexes. We use 'ed' to edit the DB dump file and move the 'DROP INDEX' statements to the start of the import. Ed is not the fastest tool for this job if your DB dump is multiple gigabytes. (Patches to the converter are welcome!)
ed -s databasename.psql < move_drop_indexes.ed
Next, load your new dump into a fresh PostgreSQL database using:
psql -f databasename.psql -d gitlabhq_production
More information
You can learn more about the move which this powered at http://lanyrd.com/blog/2012/lanyrds-big-move/ and some technical details of it at http://www.aeracode.org/2012/11/13/one-change-not-enough/.