Migrating from MySQL to PostgreSQL

http://www.openlogic.com/wazi/bid/274352/migrating-from-mysql-to-postgresql


MySQL is a fine database, but the open source community has been nervous about its future since it was acquired by Oracle in 2009. Some users have sought alternatives in MySQL forks such as MariaDB. Others have considered migration to PostgreSQL, a mature database with a track record of more than 15 years. If you want to explore PostgreSQL, here's how to get started.
You can install and run PostgreSQL on the same server as MySQL, which makes the migration easier. A stable version of PostgreSQL is available through the package managers of all major Linux distributions. If you want to be sure to get the latest features and best performance, you can install the most recent version, which is usually available from third-party sources.
To install the current version of PostgreSQL, 9.2, on CentOS, first add PostgreSQL's repository to your .repo file with the command rpm -ivh http://yum.postgresql.org/9.2/redhat/rhel-6-x86_64/pgdg-redhat92-9.2-7.noarch.rpm. You can then install the PostgreSQL packages with the command yum groupinstall "PostgreSQL Database Server PGDG".
Before you start PostgreSQL for the first time, you have to have to initialize a new server instance and its database with the command service postgresql-9.2 initdb. After that you can start the server with the command service postgresql-9.2 start.
To configure PostgreSQL to start and stop with the system, run the command chkconfig postgresql-9.2 on.

Migrating data from MySQL to PostgreSQL

Working with PostgreSQL PostgreSQL provides a command-line client, /usr/bin/psql, that's similar to the MySQL client /usr/bin/mysql. By default, PostgreSQL allows only the system user postgres to access the server, and not the root user. To use /usr/bin/psql you should switch to the postgres user with the command su postgres. If you try to use /usr/bin/psql as root, you will get the error psql: FATAL: role "root" does not exist. This means the root user has not been given a PostgreSQL role, meaning it has no rights. While you could create a role for root, it's not recommended from a security point of view; stick to the special postgres user. For more information, check the PostgreSQL documentation for database roles.

Once you have PostgreSQL installed you can start your migration. To illustrate how you can migrate data from MySQL to PostgreSQL I will use the freely available sample employees database, which offers large amounts of diverse data. To import it into MySQL, first download the full archive, extract it, and run the command mysql -t < employees.sql.
Plenty of tools are available for converting MySQL data to PostgreSQL. mysql2pgsql, for instance, is a Perl script that takes as input a MySQL data dump file and converts it to PostgreSQL-compatible format. You can find other tools in the official PostgreSQL documentation about converting from other databases.
To begin the conversion, first create a dump of your MySQL data. For the employees database, run the command mysqldump employees > employees_mysql.sql. When the dump completes, start the PostgreSQL conversion with the command ./mysql2pgsql.perl employees_mysql.sql employees_postgre.sql. This command creates a new file called employees_postgre.sql that contains all the statements needed to import your old data into PostgreSQL.
To perform the data import, first create the employees database in PostgreSQL with the Linux command /usr/bin/createdb employees. Then use the command /usr/bin/psql employees < employees_postgre.sql to import the previously converted dump.
Once the import completes you should find that the databases in MySQL and PostgreSQL are the same. You can make a simple check by looking at the number of rows in the salaries table. First check it in MySQL:

mysql> select count(*) from salaries;
+----------+
| count(*) |
+----------+
|  2844047 |
+----------+

Then in PostgreSQL:

employees=# select count(*) from salaries;
  count  
---------
 2844047

You can try more complex comparisons, such as looking for specific records inside the employees table. In MySQL an example query looks like:

mysql> select * from employees where first_name = 'Ioana' and last_name = 'Tsukuda';
+--------+------------+------------+-----------+--------+------------+
| emp_no | birth_date | first_name | last_name | gender | hire_date  |
+--------+------------+------------+-----------+--------+------------+
| 496865 | 1961-09-17 | Ioana      | Tsukuda   | M      | 1994-10-13 |
+--------+------------+------------+-----------+--------+------------+

Its analogue in PostgreSQL with exactly the same syntax should return the same result:

employees=# select * from employees where first_name = 'Ioana' and last_name = 'Tsukuda';
 emp_no | birth_date | first_name | last_name | gender | hire_date  
--------+------------+------------+-----------+--------+------------
 496865 | 1961-09-17 | Ioana      | Tsukuda   | M      | 1994-10-13

Connecting your application to PostgreSQL

Once you ensure your data is properly migrated to PostgreSQL you can proceed with connecting your application to it.
Some applications, such as Drupal, support PostgreSQL by default, while others, such as WordPress, require you to install an additional plugin. In any case, PostgreSQL is widely supported, and it should be relatively easy to find support for your application and its programming language.
When you create applications that use a database, you should design them to allow later developers to easily migrate from one database to another. If your applications use a database abstraction level, you should be able to just change the database driver and the connection string and start using a different database, such as PostgreSQL. For example, if you are using PHP on CentOS, install the php-pgsql package to get the PostgreSQL database module (driver) for PHP. Once you have it installed (yum install -y php-pgsql), you can establish a PostgreSQL connection in PHP with a connection string similar to $connection=pg_connect('dbname=employees user=test password=example');
If your application lacks a database abstraction layer, all SQL statements might be hard-coded with MySQL specifics. In such a case you'll have a hard time moving away from MySQL because almost every SQL statement may require adjustment. But nothing is impossible with enough determination, time, and budget. Even if immediate migration looks difficult, it's good to know that there is an alternative such as PostgreSQL to which you can turn in future.