As mentioned in the previous post, I decided to consolidate my data in a single database (PostgreSQL) and attempt to migrate the WordPress content to Drupal. Here's how it went (and how you could go about it).

First of all, I exported the WordPress data as a WXR (WordPress eXtented RSS) file. I then unpacked Drupal 7.10 under my web root folder.

To make Drupal truly useful as a blog platform, I needed to install a (fairly large) number of "modules" (the equivalent of WordPress plugins). The simplest way to install Drupal add-ons is to configure FTP access to its folder. Anonymous FTP will not work; Drupal needs an FTP user that can cd to its installation path. Luckily the FTP server need only listen on the loopback interface (127.0.0.1) — there's no need to open up a writable directory to the entire Internet.

The first modules to install were Token, Pathauto, Migrate, Migrate Extras and WordPress Migrate. The important ones are Pathauto, which will help preserve friendly URLs (instead of Drupal's cryptic internal URL's based on numerical node IDs), and of course WordPress Migrate.

After installing the modules, I enabled them and tried to use Administration > Content > WordPress Migration, only to run into a couple of SQL errors in the Migrate module. The first one is due to attempting to insert a float into an integer column (which MySQL unsurprisingly tolerates), and is fixed by a simple patch:

--- ./sites/all/modules/migrate/includes/base.inc	2011-12-19 05:59:27.000000000 -0600
+++ ./sites/all/modules/migrate/includes/base.inc.orig	2011-09-10 13:37:15.000000000 -0500
@@ -734,3 +734,3 @@
                        'process_type' => $newStatus,
-                       'starttime' => microtime(TRUE) * 1000,
+                       'starttime' => round (microtime(TRUE) * 1000),
                        'initialHighwater' => $this->getHighwater(),
@@ -767,3 +767,3 @@
           ->fields(array(
-            'endtime' => microtime(TRUE) * 1000,
+            'endtime' => round (microtime(TRUE) * 1000),
             'finalhighwater' => $this->getHighwater(),

The second error was caused by the non-portable MySQL syntax INSERT IGNORE in migrate/plugins/destinations/comment.inc; simply dropping the IGNORE fixed the problem.

After WordPress Import completed successfully, I was left with a site that contained all the original data, but looked nothing like the original blog. First of all, Drupal does not support a WordPress Categories menu out of the box. I had to install the Taxonomy Menu module for that. Then, for friendly taxonomy URL's, I configured PathAuto to generate links from the "vocabulary" (the old WordPress categories). I also installed Global Redirect, since otherwise feeds would still have internal Drupal names (and subscribers would be left in the dark). One nice thing was that Digg, Reddit and other social news per-post buttons could be automatically added via the Service Links module.

This might seem like an easy enough process, but unfortunately gathering the required information is hampered by the sparsity of the documentation, the multiplicity of Drupal versions, and the evolving nature of the multiple modules required to tweak Drupal. For example PathAuto used to support automatically renaming taxonomy feeds, but later moved this feature to GlobalRedirect — so many posts I found by around the web were unhelpful (if not misleading). This kind of thing seems to be part of the experience of learning Drupal.

The final step was to create a Drupal theme that mimicked the appearance of the WordPress theme. I will talk about that in a separate post.

In a quest to reduce reduce duplicate functionality on my VPS (and gain some RAM space for better uses), I have decided to consolidate my database services on PostgreSQL. The problem: WordPress is very MySQL-centric — it does not currently support, and probably never will support PostgreSQL (or other databases). This is understandable: MySQL's "be liberal in what you accept" philosophy encourages less-than-portable SQL to proliferate — and once you have a successful MySQL-bound codebase, it's hard to justify the cost of rewriting it for portability.

As it turns out, there is a plugin (PG4WP) that enables WordPress to run over PostgreSQL. It does so by replacing the wp-includes/wp-db.php database abstraction layer with a PG-specific one — which means the author must revise it for every new WordPress version. It currently supports WordPress up to 3.2.1, the previous WP release as of this writing.

As with any piece of software developed by a single author and used by a relatively limited audience, there are no guarantees about the future of the project. But even if that could be overlooked, there's a bigger problem: it's doubtful that other plugins will work with a PostgreSQL back-end, as few if any authors will test outside of MySQL.

I have therefore started to look for other CMS options that are more PostgreSQL-friendly (at least in their descriptions), while also having a large community and being under active development. After looking around I have decided to try Drupal, a popular PHP system that claims to support several databases.

I will talk about the technical details in another post; to make a long story short: it took more time and effort than I expected, but it worked. I was able to closely reproduce the original WordPress site in Drupal running over PostgreSQL.