Migration from mysql to postgresql - A case study.

There are plenty of migrations guides filled with loads of theory, they are usually littered with 'you should have done it differently' statements. This is not one of them. If you are going to read this article, be prepared to roll up your sleeves and actually migrate a PHP application from mysql to postgres.

Our victim for this study is the Rad User Manager, an open source PHP/mysql user management system. At least that's what it is now, by the end of this article it will be a php/postgresql/mysql web application.

Before we go any further please download version 2.11 of the usermanager. You will need to make sure that your php engine is compiled with mysql and pgsql support. To state the obvious you will need access to installations of these databases as well and need to be familiar with at least one or the other.

After you have downloaded and installed the user manager, keep the database.sql file from the download handy, we are going to convert it into equivalent postgresql script in the next step.Reading some of the articles on this site that compare mysql and postgresql might help you along the way. If this is your first run in with postgresql you might want to read the rest of this page as well.

skip ahead

Let's see how we can connect to a pgsql database with the PHP. The next bit of code is what you need:

	$pgString = "host=$db_server dbname=$db_name user=$db_user";
	if($db_pass != '')
		$pgString .= " password=$db_pass";
	$con = pg_connect($pgString);

Yes it could have been done in one line of code if we hard-coded the username and password etc, but the whole idea is not to do that. Unlike the mysql_connect() function which expects a set of parameters, pgsql_connect accepts only a single parameter called the connection string, this parameters has to include the username, password hostname as well as the dbname. That obviously means there isn't a need for a postgresql counterpart of mysql_select_db();

Now let's move on to the next step


  Part 1:   Getting Started   ,   The Schema   ,   Queries
  Part 2:   Passwords   ,   Times Up   ,   Errors   ,   Download