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.
mysqldump --compatible=postgresql > data.sql
some_script_that_transforms_mysql_to_postgres_syntax.sh data.sql data.psql
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.
pgloader looks more promising, and indeed it is.
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 loads data from various sources like SQLite, MySQL, CSV into PostgreSQL.
- It is licensed under The PostgreSQL Licence and free to use.
- pgloader is a cross-platform software.
- Docker image is available.
DBConvert database migration software
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.:
- In any combination, using our MySQL to PostgreSQL converter, data migrations between the following databases are possible:
- Heroku Postgres,
- AWS RDS/ Aurora,
- Google Cloud SQL for MySQL, PostgreSQL,
- Azure Database for MySQL, PostgreSQL
- 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.
- 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.
- After making a copy of your source database to target DB, keep your databases in Sync with Update, Insert, and Drop synchronization features.
- The DBSync application performs bidirectional replication between MySQL and PostgreSQL (where two different databases simultaneously replicate changes from each other).
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.