For years MySQL and PostgreSQL compete to each other as very popular open source RDBMS providing a lot of management and programming tools and frameworks. However, when looking closer PostgreSQL appears to have multiple important benefits comparing to MySQL:
- 100% compliance with ANSI SQL standard
- multiple models of indexing, partitioning
- synchronous and asynchronous replication
- sophisticated options of backup and restore
- asynchronous commit
Those are far enough reasons for companies and organizations to migrate from MySQL to PostgreSQL even so the procedure requires a lot of resources. This whitepaper covers basic principles of database migration and some tools to simplify and automate this resinous process.
Basically MySQL to PostgreSQL database migration can be presented as sequence of the following steps. First, the table definitions are extracted from the source database in the form of DDL SQL statements. These statements are then converted into the destination format and loaded into the target database. Next, the data is exported from the source database into an intermediate storage, such as CSV files. The data is then transformed according to the destination format and loaded into the PostgreSQL database. Finally, views, stored procedures, and triggers are extracted from the MySQL database and converted into SQL statements and source code, which are then loaded into the PostgreSQL database.
Manual database migration is a complex process that requires a lot of effort and carries a high risk of data loss or corruption due to human error. It is recommended to use a specialized software to automate the conversion process. One such tool is the MySQL to PostgreSQL converter developed by Intelligent Converters, a software vendor specializing in solutions for database conversion, migration, and synchronization since 2001.
The MySQL to PostgreSQL converter has extremely high performance due to direct reading and writing of data without the need for middleware libraries or components. It supports all versions of MySQL and PostgreSQL, including cloud solutions, and provides a command line version to automate and schedule database conversion. The tool allows not only for the migration of MySQL data into a new database but also for the merging or synchronization of MySQL data with an existing PostgreSQL database
The converter also allows for the filtering of data via SELECT queries, enabling users to select particular columns and records or transform the data before converting it into the PostgreSQL format. It also provides a feature called “custom column mapping” that allows users to change the name, type, default value, and NULL-attribute for any column of a table, as well as exclude certain columns from conversion.
If the target PostgreSQL server does not allow remote connection, the converter provides an option to export the data into an SQL script. In this approach, the source database is exported into a local file containing SQL statements to create tables with all indexes and constraints and fill them with the data. The script file can then be imported to the PostgreSQL server using standard tools like psql or phpPgAdmin.
In summary, the process of migrating a database from MySQL to PostgreSQL is complex and carries a high risk of data loss or corruption. It is recommended to use specialized software to automate the conversion process. The MySQL to PostgreSQL converter developed by Intelligent Converters is a high-performance tool that supports all versions of MySQL and PostgreSQL, provides command line support, and offers features such as filtering, custom column mapping, and exporting to SQL script.
