MySQL to PostgreSQL migration

kw's picture

On the final straight to release a project to a customer we meet up a nice surprise. Although marketing team was very pleased with our work, the head-on contact with IT team was quite sobering - we don't use mysql, all our services base on postgresql.

My previous experience proved that Drupal is so flexible to deal with strange databases like MS SQL, so Postgre sounded like a piece of cake. I did not want to engage development machine into this work so I started to think out some ways around to make a quick migration and get rid of Postgre after that.

Optimism - Let's try global way

I wanted to use a module DBTNG Migrator to transform every table from MySQL to PostgreSQL. Dev server was running MySQL and staging server was configured for PostgreSQL. At first I have installed a clean Drupal on PostgreSQL to see if it works - and it did, after that I wanted to transfer database from remote MySQL to local PostgreSQL. I set up two databases in settings.php, one for local server, second for remote MySQL database. This technique failed because dev server among other modules that this site realy utilizes, included much more modules that somehow intruded into the process. Although the migration showed success, there was a problem.

Realism - minimum uncertainty

After a few hours of struggle I decided to use the most straight method - just install PostgreSQL on dev machine and migrate everything as-is. I have changed all definitions in settings.php to local server, cleared remaining after unsuccessful trials and start over again. To my surprise I got positive message, the data sizes were similar and tester from that module also showed positive result. I was quite certain of success, and in fact that was true.

Lesson for future - repeated many times - use the most simple solutions.