Nowadays there are many advanced database management systems optimized for different tasks and purposes. If talking about open-source RDBMS, MySQL and PostgreSQL both of which are, rich in programming APIs and administration tools. While both systems are quite competitive in providing advanced features of data management, there are many reasons a lot of businesses will choose PostgreSQL over its contemporary. The main reasons of such choice are compatibility with the standards of ANSI SQL, support for various indexing models, asynchronous commit, support for both synchronous and asynchronous replication.

Generally, there are steps involved in database migration:

1. Table definitions are extracted with corresponding indexes and constraints in form of SQL CREATE-statements from MySQL database source.
2. Before loading into the target database, the statements are converted into destination format.
3. Using an intermediate storage such as CSV files, export data from MySQL database.
4. The exported data is transformed to the format of the destination and loaded into Postgres database.
5. Views, triggers, and stored procedures should be extracted from MySQL database in form of SQL statements and source code.
6. Before loading into the PostgreSQL database, both extraction formats from the previous step should be converted into destination format.
7. Lastly, ensure that proper conversion has been made to the database objects.

Manual approach to MySQL to PostgreSQL database migration requires loads of efforts and could take too much time. More than this, human factor has a tendency to expose data to high risk of corruption or loss. To avoid those risks, the migration must be automated with a special software.

Fortunately, some Intelligent Converters has developed such automation tools. Since 2001, this software vendor has catered to database migration, synchronization, and conversion solutions. Most average database converters cannot boast performance of about 10,000 records per second. But this is not applied the MySQL to Postgres converter due to these capabilities:

• Does not depend on 3rd party libraries or components when reading or writing the data
• Supports modern versions of MySQL and PostgreSQL including such forks as MariaDB, Percona and SAAS variations
• Database conversion can be scripted and scheduled through the command line support
• Merge and synchronize options of database migration are supported

When some project requires partial or pre-processed migration, the tool provides filtering data via SELECT queries for those purposes. This feature allows not only select records and columns of MySQL tables but also join multiple tables into single one and apply various transformations on the source data before importing it into PostgreSQL database.

Another useful feature is implemented via the dialogue window called ‘custom column mapping’, it helps to change column name, type and other attributes in the resulting database. Also, certain columns can be excluded from migration using this option.

When the target PostgreSQL prevents remote connection, MySQL to Postgres converter can export the data into SQL script. Later the person responsible for database migration will complete the procedure using the following steps:

• • Export of database into ‘SQL-statement’ files
  • Creation of tables containing indexes and constraints
  • Filling the tables with data

• Load the script file to PostgreSQL server

Francisco Butler