Database migration from MySQL to PostgreSQL

MySQL and PostgreSQL are both very popular open source RDBMS having rich set of administration tools and programming APIs.

PostgreSQL Advantages

PostgreSQL, however, has multiple important advantages over MySQL:

• it is fully complied with ANSI SQL standard
• supports several models of indexing
• provides asynchronous commit
• supports both synchronous and asynchronous replication

That is why many businesses and organizations consider migrating their databases from MySQL to Postgres.

In general, database migration consists of the following steps:

• Extract table definitions from the source database in form of DDL SQL statements
• Convert these statements into the destination format and load into the target database
• Export data from the source database into an intermediate storage (for example, csv files)
• Transform the data according to the destination format and load it into Postgres database
• Extract views, stored procedures and triggers from MySQL database in form of SQL statements and source code
• Convert these statements and source code into the destination format and load into PostgreSQL database

Obviously, the procedure of migrating database from MySQL to Postgres requires many efforts when doing it manually. In addition, there is high risk of data loss or corruption due to the human factor. It would be wise to use special software to automate the conversion process.

PostgreSQL Converters

One of such conversion tools is MySQL to PostgreSQL developed by Intelligent Converters. Intelligent Converters is a software vendor specializing in solutions for database conversion, migration and synchronization since 2001. Their MySQL to Postgres converter has extremely high performance due to direct reading and writing data without any middleware libraries or components. It supports all versions of MySQL and PostgreSQL including cloud solutions. The product provides command line version in order to automate and to schedule database conversion. MySQL to PostgreSQL converter allows not only migrate MySQL data into new database but also merge or synchronize it with existing PostgreSQL database.

Do you need to migrate only certain records to the destination database? No problem, the tool allows to filter data via SELECT queries. With this option, it is possible to select particular columns and records or even transform the data before converting it into PostgreSQL format. Here is a few examples of using this feature for different purposes.

• Filtering records: SELECT * FROM TheTable WHERE IdColumn < 300
• Select and rename individual columns: SELECT col1 AS LastName, col2 as CellPhone FROM TheTable
• Skip NULL values: SELECT * FROM TheTable WHERE Details IS NOT NULL

Sometimes it is necessary to change column type in the resulting database. For this purpose MySQL to PostgreSQL converter provides feature called “custom column mapping”. It is a dialog window that allows to change name, type, default value and NULL-attribute for any column of table as well as exclude certain columns from conversion.

If the target PostgreSQL server does not allow remote connection, the program provides option to export the data into SQL script. In this approach source database is exported into local file containing SQL-statements to create tables with all indexes and constraints and fill them with the data. Then database administrator can import the script file to PostgreSQL server the standard tools like psql or phpPgAdmin.

Visit the official product page for more information:

MySQL to PostgreSQL Converter

The post Database migration from MySQL to PostgreSQL appeared first on Information Technology Blog.

from Information Technology Blog http://ift.tt/2g0eWMb