Migrating databases between different DBMS is a complicated task that requires a lot of time and efforts especially when both source and destination systems are quite powerful and complicated. The whitepaper exposes approaches to database migration from MySQL to PostgreSQL.
Some old versions of MySQL can export database into script file with syntax compatible to PostgreSQL. This feature obviously can be part of the most straight forward two-steps technique of database migration from MySQL to PostgreSQL:
- Export the source database into script file
mysqldump –compatible=postgresql>script.sql
- Import the resulting script to PostgreSQL via the statement
psql -f script.psql
The bad news is that low performance of this method makes it not suitable for migration of medium and large databases.
Second option of migration uses a well-known open-source tool pg loader that migrates MySQL databases to PostgreSQL using the COPY command, loads data, indexes, and foreign keys, and converts the data. This tool has advantages as follows:
- Works with various data sources like SQLite, MySQL and CSV
- It is distributed as open-sources oftware under free license
- Being a set of LISP scripts, it is a cross-platform project
Does Pgloaderfit for all MySQL to PostgreSQL migration projects? It would be correct to say it is a good choice for experienced users and database professionals who feel comfortable with command line tools and do not need to customize migration.
Database migration from MySQL to PostgreSQL can also be handled through the dedicated commercial software. It is the most powerful and flexible yet easy to use option because those tools usually can customize and automate database migration via intuitive user-friendly interfaces.
MySQL to PostgreSQL converter is one of those commercial tools developed by Intelligent Converters, a leading software vendor specializing in the database migration and synchronization field. If has the benefits compared to Pgloader as follows:
- MySQL-to-PostgreSQL is extremely customizable and allow to migrate the database via easy-to-use wizard style interface.
- Every single step of the migration procedure is easy to understand and utterly documented.
- The converter can serialize all settings into profile to simplify the next runs.
- Existing PostgreSQL database can be processed using multiple options: overwrite all, overwrite existing tables only, merge and synchronize.
- The source data can be pre-processed via technique of SELECT queries.
- The tool support customization of name, type and other attributes of any MySQL column as well as excluding columns from migration.
If you need to synchronize MySQL and PostgreSQL databases having huge row sets, incremental synchronization would be the best choice. One of approaches to synchronize databases incrementally is trigger-based synchronization. It includes building insert, update and delete triggers for each MySQL table being synchronized. Every trigger stores all modifications of the corresponding table into the change log. Then synchronization tool walks through all entries of that change log and replicates all changes into the destination database.The described trigger-based method of incremental synchronization is implemented in MySQL-PostgreSQL Sync tool offered by Intelligent Converters.
More information about migration and synchronization tools for MySQL and PostgreSQL is available on the Intelligent Converters official site.
