Pgloader vs MySQL-to-PostgreSQL

By Brenda Newman 4 years ago

 

Migrating databases between different DBMS is a complicated task that requires a lot of time and efforts. This article explores several ways of database migration from MySQL to PostgreSQL.

Old versions of MySQL allow to dump database into script file having PostgreSQL compatible syntax. This trick could be used for the most straight forward two-steps approach to database migration from MySQL to PostgreSQL:

  1. Export the source database into script file

mysqldump –compatible=postgresql > script.sql

  1. Import the resulting script to PostgreSQL via the statement

psql -f script.psql

However, real tests of this approach may prove it cannot be used for migration of medium and large databases.

Second option of migration uses a well-known open-source tool pgloader that migratesMySQL databases to PostgreSQL using the COPY command, loads data, indexes, and foreign keys, and converts the data. This tool has advantages as follows:

  1. Works with various data sources like SQLite, MySQL and CSV
  2. It is open source and free to use
  3. It is a cross-platform software

Pgloader is a right solution for users who feel comfortable with command line tools and does not need to essentially customize migration procedure.

Third option to migrate a database from MySQL to PostgreSQL is to use special commercial tools. Obviously, it is the most powerful and flexible option since those tools provide dedicated features to customize database migration and automate it via intuitive user-friendly interfaces.

One of those tools is MySQL to PostgreSQL converter developed by Intelligent Converters, one of the leading software companies in the database migration and synchronization field. If has the following advantages over Pgloader:

  • MySQL-to-PostgreSQLis extremely customizable and allow to migrate the database viaeasy-to-usewizard style interface.
  • Each step of the migration is well explained and utterly documented.
  • The tool can save conversion settings into profile to simplify the next runs.
  • There are multiple options to process existing PostgreSQL database: overwrite all, overwrite existing tables only, merge and synchronize.
  • MySQL data can be pre-process via SELECT queries before migration.
  • Option to customize name, type and other attributes of any column in MySQL tables. Also, it allows to exclude any columns from migration.

If you need to synchronize large and complicated MySQL database with PostgreSQL, incremental synchronization would be the best choice. One of approaches to synchronize databases incrementally is trigger-based synchronization. It is based on building insert, update and delete triggers for each table being synchronized in the source database. Every trigger store sall 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. Intelligent Converters offer MySQL-PostgreSQL Sync tool that implements incremental trigger-based synchronization.

You can learn more about MySQL to PostgreSQL migration and synchronization tools on the official site of Intelligent Converters.