Migrate your site from MySQL to PostgreSQL -- Part 1

When my site, Design Harbor, started up (then as Abscissa Tech), there was one sensible choice for a PHP-driven, database-backed Website: MySQL. Then, in the summer of 2001, I ported the site to PostgreSQL (also known simply as Postgres) and haven't looked back.

This first in a two-part series describes my motivation for switching to Postgres, and explains step-by-step how to convert existing MySQL data to Postgres. The second part will explain how to change the PHP backend to ensure that it works with the new database system.

The Motivation To Switch

I first read about Postgres in an article at PHPBuilder. It compared Postgres and MySQL, which I used exclusively at the time. But, after I read the article, Postgres became lost in the information repositories of my brain -- it was an interesting fact, but one which was irrelevant to my Web design.

I continued to use MySQL and attributed the MySQL failures and crashes to the incompetence of my hosting service, which I couldn't change at the time. As soon as I was able to, I changed Web hosts. This new host's service and philosophy was very different from my previous one, as they had a higher commitment to security and stability than did my old host. The new company tried to convince me to use Postgres, suggesting that it was more stable, but I passed off the idea and pointed out that my entire site was already coded for MySQL. They relented and installed MySQL specifically for my site. That's when the problems began.

My first job was to copy my old MySQL data from the old server to my new Web host's MySQL server. First, I dumped the existing data to an SQL file, which I transferred to the new Web server. I then proceeded to import the SQL file. At the sight of the several thousand line file, MySQL promptly crashed. When the company restarted MySQL, about half of my data was there, but MySQL worked only intermittently. Finally, they had to delete the information that was imported so I could try again. MySQL crashed again. This cycle continued several more times, until I finally decided to split up my SQL file into several pieces. I had to try several times, but I was eventually able to import most of my data into the new MySQL server. Everything was fine, and I sighed with relief.

Over the next several moths, MySQL crashed almost bi-weekly, culminating in a really bad crash near the end of June 2001. This time, the data stored by MySQL was hopelessly corrupted and unrecoverable. I had an SQL backup file, but in no way did I relish the task of restoring the data from that backup, based upon my previous experiences with importing large amounts of data into MySQL. About that time, the hosting company encouraged me once again to port the site, which was written in PHP, to Postgres. Frustrated with MySQL, I finally decided to do it.