Skip to main content

Pgloader vs. DBConvert - Migrate from MySQL to PostgreSQL

  • Updated

Migrating data between different types of databases is not a trivial task. In this article, we will compare several ways of converting from MySQL to PostgreSQL.

 

Essentially, both MySQL and PostgreSQL are relational databases. But why is there a problem at all?

 

You might be thinking the path is as easy as the three quick steps below.

  1. mysqldump --compatible=postgresql > data.sql 
  2. some_script_that_transforms_mysql_to_postgres_syntax.sh data.sql data.psql
  3. psql -f data.psql 

It was possible to set the value of "--compatible" option to postgresql in old MySQL 5.7. This option was used for the generation of output that is more compatible with PostgreSQL servers.

Since MySQL 8, according to documentation, that won't work. The postgresql value is not supported anymore.  

Actually, it was impossible to get a PostgreSQL compatible dump file even in the old MySQL 5.7 mysqldump. Several Github Repos contain scripts that perform the second step of converting a MySQL dump to PostgreSQL format. But most of them are now dead.
 
 
So, this path is dead-end and does not deserve further consideration.
 

 

pgloader looks more promising, and indeed it is.

toy-loader.320.jpg

pgloader is a well-known open-source tool that imports data from MySQL into PostgreSQL using the COPY command, loads data, indexes, and foreign keys, and converts data to PostgreSQL as intended.

 

pgloader pros: 

  1. pgloader loads data from various sources like SQLite, MySQL, CSV into PostgreSQL. 
  2. It is licensed under The PostgreSQL Licence and free to use. 
  3. pgloader is a cross-platform software. 
  4. Docker image is available. 

 

 

 DBConvert database migration software

dbconvert-mysql-postgresql.png

DBConvert & DBSync for MySQL and PostgreSQL are popular software tools that minimize the challenges of migrating and syncing data between MySQL and PostgreSQL databases.

 

DBConvert/ DBSync for MySQL and PostgreSQL Pros.:

  1. In any combination, using our MySQL to PostgreSQL converter, data migrations between the following databases are possible:
    • MySQL,
    • MariaDB,
    • Percona,
    • PostgreSQL,
    • Heroku Postgres,
    • AWS RDS/ Aurora,
    • Google Cloud SQL for MySQL, PostgreSQL,
    • Azure Database for MySQL, PostgreSQL
  2. DBConvert tools are highly customizable and allow you to quickly and easily export data from MySQL to Postgres using a comprehensive graphical interface. DBConvert Applications guide you through several steps, from connecting to source and destination databases to scheduling the next runs.
  3. In DBConvert products, the target database is presented in a tree, which significantly simplifies settings' configuration. The proven advantage of DBConvert software is that non-professional users can effectively use it.
  4. After making a copy of your source database to target DB, keep your databases in Sync with Update, Insert, and Drop synchronization features. 
  5. The DBSync application performs bidirectional replication between MySQL and PostgreSQL (where two different databases simultaneously replicate changes from each other).

 

Conclusion:

In this article, we've covered two possible ways of migrating data from MySQL to PostgreSQL.  

Pgloader is an excellent solution for Linux users and those who feel comfortable in the terminal. It does its job when migrating from many databases to PostgreSQL. But conversion is possible in ONE direction only.

 

DBConvert tools support migration and synchronization in both directions between MySQL and PostgreSQL database types. Many more on-premises and cloud databases conversion is supported. DBSync is a perfect solution to keep databases in sync after initial migration continuously. 

 

Related articles

Comments

0 comments

Please sign in to leave a comment.