Even though MySQL and PostgreSQL seems to be a very popular open source RDBMS, not denying the fact that they both have rich set of administration tools and programming APIs, PostgreSQL appears to have some important advantages over MySQL. Some of these advantages are:
It supports various models of indexing
It supports both synchronous and asynchronous duplication
It offers asynchronous commit
It is fully complied with ANSI SQL standard.
With these distinguishing features, it is obvious why many businesses and organizations are migrating from MySQL to PostgreSQL.
Generally, the basic principle involved in migration of database include:
Extraction of table definitions from the source MySQL database in form of DDL SQL statements.
Conversion of the extracted statements into destination format and loading of the data into the target database.
Exporting data from MySQL database into CSV files or other intermediate storage formats.
Transformation of collected data into the destination format accordingly and loading of the transformed data into PostgreSQL database.
Extraction of stored procedures, views and triggers from MySQL database in form of SQL source code and statements.
Conversion of the extracted statements and source code into the destination format and loading of the extracts into PostgreSQL database.
Ensuring the complete and appropriate conversion of all database objects.
Apparently, migrating database from MySQL to PostgreSQL using manual method is quite tasking. Moreover, the probability of having a corrupted data or data loss during migration is very high due to human factor. Hence, it would be ideal to make use of special software to automate the conversion process.
An example of such conversion tools is “MySQL to PostgreSQL” software developed by Intelligent Converters – a software vendor that specializes in providing solutions to database conversion, migration and synchronization. They have been in business since 2001. Their “MySQL to PostgreSQL” converter is renowned for its high performance (approximately 10,000 records per second of average modern system). The converter can perform at such a high level because of its ability to directly read and write data without any middleware libraries or components. Moreover, it supports all versions of MySQL and PostgreSQL, including cloud solutions.
The program can also automate and schedule database conversion using command line. It does not only migrate MySQL data into new database, it can also merge or synchronize database with existing PostgreSQL database.
If you wish to migrate only some selected records to your new database, it is very easier using the “SELECT” queries in the software. This option allows you to select some particular columns and records to work with or even transform the data before converting it into PostgreSQL format.
Below are some examples of how to use the tool for diverse purpose.
To filter records:
To rename each column:
To skip NULL values:
There are some essential cases where the column type need to be changed in the resulting database. In such cases, “MySQL to PostgreSQL” software offers a feature called “custom column mapping”. This feature makes it possible to change the name, type, default value and NULL attribute of any column of a table. It can also segregate some certain columns of a table from conversion.
In addition, the program can export retrieved data into SQL script such that there won’t be problem if the target PostgreSQL server does not allow remote connection. Using this method, source database is exported into local file containing SQL statements to create tables that will contain all indexes and constraints as well as fill them with the extracted data. Afterwards, the database administrator can import the script file to PostgreSQL server using standard software like pSQL or phpPgAdmin.
