Migrate your site from MySQL to PostgreSQL -- Part 2

In June 2001, I switched my Website's back-end database from MySQL to PostgreSQL. It only took me a day. Since that time, Postgres has worked flawlessly, confirming that I made the right choice.

In the first tutorial in this series, I explained how to convert data from MySQL to Postgres while avoiding potential pitfalls. I also explained the reasoning behind my move to Postgres. In this article, I'll guide you through the other half of the conversion process and explain how to take existing MySQL PHP code and make it Postgres-friendly.

Preparation

Before you go muddling around in code, you need to take some preparation steps. The conversion process will vary depending on your coding skills and site complexity, but it will certainly be noticeable if you try to change things on a live site. To avoid annoying errors while you work on the site, make a copy of your site in a working area of your Web server. In my case, I set up a special subdomain for testing while I modified the code. Since the site automatically calculates URLs based upon some basic settings in a configuration file, this was easy, but you may have to make some changes to your code to make it work whether you choose to place the work-in-progress version in a subdomain, in a sub-folder of your site, on another Web host, or on a local development machine.

Remember: if you put the work-in-progress copy on another machine, make sure that the Web server is configured just like the live site, or you may run into trouble. If your Web host only allows Postgres connections from the live Web server, you may have to copy your Postgres data over to the development server as well. For more information on copying a Postgres database, see the documentation for pg_dump and pg_restore on www.postgrsql.org.

Once you have copied your site and have tested it to make sure it works normally, you're ready to pull out the scalpel and start hacking away.