Migrate MySQL to Postgres
Posted 5 March 2015
As part of some recent Drupal work, I've had need to migrate a Drupal install from MySQL to Postgres. It turns out that converting between databases is something a lot of people have tried to do, and so there are lots of scripts and tools around to help out.
Initially I tried to use a generic database migration tool, which I downloaded from Guthub. It actually did a great job of recreating the schema (after a few minor mods), but struggled on the data. MySQL's XML backup format doesn't handle the NULL character very well, which actually results in invalid XML. Thankfully, it was pretty easy to just edit out the NULLs as they weren't important. However, it struggled again with some of the serialised data that Drupal likes to store. In fairness, this is a hard problem to solve - not insurmountable, but it really needs a specialised solution to suit the exact type of data being migrated.
For Drupal migrations, I found the very excellent DBTNG Migrator module. It's able to migrate to and from a wide variety of database types. It uses Drupal's own database access capabilities, so it seems to be able to make an exact copy of the database. It's amazing to do it and repoint Drupal at the new database to find it all working without a hitch.
There are people on the Drupal site who's used the DBTNG Migrator module to migrate off something like MySQL and onto SQLite. If you've got a read-only site, this could be a great way to cut the resource overhead of it - it also means you can literally just zip up a couple of directories and transplant the install somewhere else - very handy for moving between machines and hosting providers. In that sense, it makes an old Drupal install a lot like a files-based CMS.
More blog posts:Previous Post: Drupal and Postgres in Production | Next Post: Puppet Beaker