OpenNomad's little corner of the web

Migrating Drupal from PostgreSQL to MySQL

I recently decided to move some websites, including this one, to a hosting provider. For this I chose Green Geeks, because their price is reasonable and they are offsetting their carbon foot print by 300%. As part of that I had to move my Drupal sites from my existing PostgreSQL to MySQL backend. That wasn't as easy as it should have been, so here is a rough shot atwhat I did.

Step 1: Install Drupal on the MySQL backend

That was easy, with the tools provided by Green Geeks, but if you don't have that available, just follow the install instructions and set it up against the MySQL database.

Step 2: Install all of the extra modules and themes

To make sure everything work, you should install all of the same themes and modules to the same location. In my case I stuck with once of the recommended locations: sites/all. As a side note you can just put them in one place and then symlink it into multiple sites. That gives you access to same modules and themes on all sites.

While you are at it, you should also copy over any extra content, like images etc, you may have. You can try to duplicate the locations, but keep in mind that if it changes you'll have to change some configurations at the end.

Step 3: Set Drupal up the same

Now log into the admin interface of the MySQL based Drupal install and select the same modules. That will create the right tables for the database import later. There will still be some issues to work through, but it reduces it a lot.

Step 4: Dump your PostgreSQL database to a file

Use the following command to back up your PostgreSQL

pg_dump --data-only -d -D -U USER DATABASE_NAME > DATABASE_NAME.sql That will give you the most portable output assuming you use the right USER and DATABASE_NAME.

Step 5: Tweak the database dump

SQL is so universal. Well, not in this case. You'll end up with a lot of errors if you try to just import the SQL dump. So the next step is tweak the output. For that I cobbled together some very quick Perl.

#!/usr/bin/perl
#
sub clean ($) {
        $line =~ s/(INSERT INTO) "(\\w+)"/$1 $2/;
        $line =~ s/ E'/ '/g;
        $line =~ s/(\\w)''/$1'/g;
        $line =~ s/''(\\w)/'$1/g;
        $line =~ s/ E'/ '/g;
        $line =~ /INSERT INTO '?\\w+'?\\s*(\\(.*\\)) VALUES/;
        $new = $1;
        $new =~ s/"//g;
        $line =~ s/(INSERT INTO '?\\w+'?\\s*)\\(.*\\)( VALUES)/$1$new$2/;
        return $line;
}

while ($line = <STDIN>) {
        if ($line =~ /^INSERT/) {
                $line = clean($line);
                print $line;
                while ($line !~ /;$/) {
                        $line = <STDIN>;
                        $line = clean($line);
                        print $line
                }
        }
}

It's not my greatest code, but I'm only trying to clean stuff and the above does most of the heavy lifting. Save the code into a script, say trans.pl, then make it executable and pipe your backup through it.

chmod u+x trans.pl cat DATABASE_NAME.sql | ./trans.pl > out.sql

Now you're close to the import, but there is one thing I could not get to work. Namely importing the data into the search tables. I worked around that by ripping that out of the import file with grep. So using the above out.sql in the next step, run

grep -v 'INSERT INTO search' out.sql > import.sql

Step 6: Import the data

I used phpMyAdmin but the mysql CLI should work as well. If everything went well you should not receive an error. If you do, the quickest way is to deal with it individually. You might need to remove all data from a certain table or edit a spot here and there. The MySQL error message should point you at the right direction.

Step 7: Rebuild the search data

Since we removed the search related tables from the import, it's now time to rebuild the search data. Simply use the Drupal admin interface by navigating to Adminster->Search Settings and click the Re-index site button. That may take a bit, but should do the trick.

Step 8: Cleanup Most things should be working now. But there is a good chance that some things aren't perfect. For example certain file or directory paths might be wrong. The best way to work through things that show up in the Administer->Status Report

That's it. Hope this helps someone or at least saves time.

\\@matthias

posts