Occasionally you have to perform the onerous task of moving data from one RDBMS to another, and it can be a real pain if you’re a lackluster IT dilettante like myself. Different data types, different SQL syntax, different character enclosures, blah blah blah. This little post documents my recent efforts to move a large database from MySQL 5 to PostgreSQL 8.1.
Of course, there are a couple scripts out there that people have thrown together, but none of them are perfect:
|mysqldump||This is the handy dumping/archiving script that comes with MySQL, and you can actually give it an option like
|This script ships with Postgres and is probably too out of date to be usable. Poor data type translation|
|my2pg.pl||I think this also ships with Postgres. Decent data type conversion, implements
|This one’s a bout 2 years old, but does a good job on type conversions, and instead of weird C voodoo, it emulates
Since none of these did exactly what I wanted them to do, I decided to modify GBorg’s, which seemed like the best. My modifications were simple, but still frustrating since I don’t actually know Perl, and I am generally not on speaking terms with regular expressions. Basically, I
- Made it double-quote uppercase table names
- Kept it from double-quoting field names that already had quotes
- Added a
--noenumflag to opt out of making external tables for
enums(my data will be static when it gets to Postgres, so constraints are unnecessary)
- Fixed a bug wherein it removed the semicolons after table creation blocks (since this made this script completely unusable, I wonder if there isn’t a bigger better newer version somewhere…)
- Stopped it from converting
UNIQUE. At least in MySQL 5,
KEYis used to create indexes, so this didn’t really make any sense. I did not add the capability to make indexes in Postgres instead. Maybe later (hahahaha).
So, using my new script, I basically did this:
./kmysql2pgsql.pl --noenum my_dump.sql pg_dump.sql
Of course, I'm talking about a 30,000 row database, so it took a little while for all the inserts to run, but it worked. If you're having trouble, a good way to debug is to dump a schema-only version of the db with the
-d flag of
mysqldump. Run it through the script, eye-ball the results, maybe paste a few table definitions into psql or something.
I'll try and remember to post my changes or a diff file or something whenever I upgrade to WP2 and get file attachments going.