Every database specialist recognizes MySQL and PostgreSQL as extremely popular open source RDBMS supplied with wide range of administration and development tools. At the same time PostgreSQL has multiple advantages towards MySQL when comparing these database management systems:
- Full compatibility with famous ANSI standard of SQL
- Multiple models of indexing are supported
- Asynchronous commit is available
- DBMS supports both synchronous and asynchronous replication options
This may explain why many companies migrate from MySQL to PostgreSQL.
When considering manual approach to database migration procedure, it may be organized into sequence of the following steps:
- Extract MySQL table definitions, indexes and constraints in form of SQL CREATE-statements
- Translate these statements into PostgreSQL format and load into the database
- Export MySQL data into comma separated values files an intermediate storage
- Transform this data so it complies to PostgreSQL format and import into the target database
- Extract MySQL views, stored procedures and triggers as SQL statements and source code
- Translate all statements and source code so it complies to PostgreSQL format and import into the target database
- Verify that all objects have been converted and imported into PostgreSQL correctly
From this introduction into basic migration steps it is obvious that manual converting MySQL database to PostgreSQL takes a lot of efforts and it becomes even worse for large and complicated corporate databases. Doing such complicated task manually implies high risk of data loss or corruption due to the human factor.
It is reasonable to use special software to automate migration from MySQL to PostgreSQL. There are multiple software companies provide solutions for this purpose. One them is Intelligent Converters, a software vendor working in field of database migration, conversion and synchronization since 2001. Their migration tool offers high performance due to extremely efficient algorithms of reading and writing data. The program supports all versions of MySQL and PostgreSQL including such variations as MariaDB and Percona and SAAS solutions. The installation pack of the converter includes command line version to automate and to schedule the migration procedure. MySQL to PostgreSQL converter also allows merge and synchronize an existing PostgreSQL database with MySQL data.
Sometimes migration projects require to process partial data. For this purpose, MySQL to PostgreSQL converter provides flexible and powerful feature to filter MySQL data via SELECT queries. This option allows to select particular columns and rows or even transform the source data before migrating to PostgreSQL.
When particular migration project requires more customization, for example changing column name, type or attributes in the resulting database, MySQL to PostgreSQL converter provides feature called “custom column mapping” for this purpose. It is implemented as a dialog to change name, type, default value and NULL-attribute for any column of table. This option can also be used to exclude certain columns from migration.
For those situations when PostgreSQL server does not allow direct connection, the program provides option to convert database into SQL script. In this approach MySQL database is converted into local file containing SQL-statements in PostgreSQL format to create tables, indexes and constraints and fill them with the data. Later the person responsible for database migration will be able to import that script into PostgreSQL database using the standard tools like phpPgAdmin.
