Database migration between different DBMS is requires a lot of time and efforts especially when they are such powerful and complicated as MySQL and PostgreSQL. This article explores approaches to database migration from MySQL to PostgreSQL.
Some forks of MySQL (for example MariaDB v10.2) can export database into PostgreSQL compatible script. This is the most straight forward method of MySQL to PostgreSQL database migration:
- 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
This method has very low performance that makes it not useful for migration of corporate scale databases and large data warehouses.
Second option of migration uses a well-known open-source tool pgloader 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-source software under free license
- Being a set of LISP scripts, it is a cross-platform project
So, is this true that Pgloader fits for all MySQL to PostgreSQL migration projects? The answer is that’s a good choice for database professionals who feel comfortable with command line tools and do not need to customize migration.
The easiest option of database migration from MySQL to PostgreSQL is using special commercial tools that can customize and automate the entire process through intuitive user-friendly interface.
One of those tools is MySQL to PostgreSQL converter offered by Intelligent Converters, software company specializing in database migration for more than 20 years. MySQL to PostgreSQL converter has the following benefits compared to Pgloader:
- It is extremely flexible and customizable
- Provides very easy-to-use wizard style interface
- Every single step of the migration is well documented
- It can store all settings into profile to simplify the next runs
- The source data can be filtered using SELECT queries
- Options to process existing PostgreSQL database: overwrite, merge or synchronize
If you need not only migrate but also synchronize large MySQL and PostgreSQL databases, incremental synchronization technique is the right choice. One of incremental synchronization methods is known as trigger-based database synchronization. This approach is based on building triggers for insert, update and delete operations for each MySQL table being synchronized. Each of those triggers tracks all modifications into the special changelog table. Then synchronization tool scans all rows of that service table and writes the corresponding changes into the target database. The database synchronization method specified above is implemented in MySQL-PostgreSQL Sync tool that is also offered by Intelligent Converters.
Learn more about database migration and synchronization tools for MySQL and PostgreSQL on the official site of Intelligent Converters.
