Convert WordPress from PostgreSQL to MySQL (to fix 4.2.2 compatibility issue)

After upgrading to WordPress 4.2.2, the PostgreSQL plugin broke. My blog stuck in infinite database update loop. If I logged out, I could not log back in, suck in infinite login loop. After looking at the nginx log at /var/log/nginx/error.log, I see a lot of database errors like the following. I remembered seeing the PostgreSQL plugin, PG4WP, does not have official support. Very likely that it is broken by WordPress 4.2.2. Then I decided to convert to MySQL and try upgrading again. And there you go, upgraded fine!

There are tools, like pg2mysql, MySQL WorkBench. However, I tried an easier and faster way using only stock PostgreSQL and MySQL clients.

Prerequisites
I have WordPress hosted on Heroku that is using the Heroku PostgreSQL addon. Get a MySQL database ready either by adding a MySQL addon or somewhere else. Having a local set up will definitely help.

Step 1: Install clean WordPress on MySQL
Prepare the new MySQL database, write up the database url and replace the DATABASE_URL with it. Then go to the url for your site as if you are installing a clean WordPress. The point is to get WordPress set up the database for you. Fill in the information on the first page and hit continue. Don’t have to proceed once the database is set up.

Step 2: Export PostgreSQL data and import to MySQL
Your DATABASE_URL is formatted in this way: [db_type]://[username]:[password]@[hostname]:[port]/[db_name]. You’ll need those information in this step.

Step 3: Complete the set up
Now change the DATABASE_URL to point to at your MySQL and you’re set!

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code class="" title="" data-url=""> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong> <pre class="" title="" data-url=""> <span class="" title="" data-url="">