How to Migrate PostgreSQL to MYSQL

PostgreSQL offers numerous advantages, including compatibility with various data types and a reliable environment with features like Multi-Version Concurrency Control (MVCC). However, if your use case requires capabilities such as long-term data retention and advanced data analysis, migrating to MySQL can be beneficial. 

This article provides a detailed guide on four easy methods to seamlessly migrate your data from PostgreSQL to MySQL, ensuring a smooth transition for your organization.

How to Migrate PostgreSQL to MySQL: 3 Methods

Method 1: Using Hevo

Hevo is a fully-managed, no-code ELT platform that makes data movement simple, reliable, and transparent. Hevo does away with the manual effort of writing scripts or managing complex ETL processes so teams can focus on data analysis instead of infrastructure maintenance. 

For a PostgreSQL to MySQL migration, it’s ideal to use Hevo since it handles the structural differences between these two relational databases automatically. Its fault-tolerant architecture ensures high availability and data integrity. This is great for businesses that need consistent, real-time data synchronization without additional technical costs. 

Step-by-step process

The following steps can be implemented to connect PostgreSQL to MySQL using Hevo:

Step 1: Configure PostgreSQL as your Source

• Click on ‘Create a Pipeline’
• Select your Source and Destination
• Configure your PostgreSQLSource by providing the following details:
  • Database Host and Port
  • User and Password
• Select other required parameters (Ingestion Modes, SSH/SSL, and advanced settings options).

Step 2: Configure MySQL as your Destination

• Similar to configuring Source, configure MySQL as your destination

We have now successfully moved PostgreSQL to MySQL Migration using Hevo. 

Key advantages of using Hevo to migrate from PostgreSQL to MYSQL

• Automated schema mapping: Hevo automatically detects your PostgreSQL data schema and maps it to the appropriate MySQL format. It’s adept at handling data type conversions.
• Real-time incremental data loading: Using Change Data Capture (CDC), Hevo identifies and transfers only the modified data, ensuring your MySQL destination is always up-to-date while reducing bandwidth usage.
• Python-based data transformation: It provides a flexible interface to clean, enrich, or modify your data in transit using drag-and-drop blocks or Python scripts.
• Live monitoring: Hevo gives full visibility into your Data Pipeline with real-time alerts and a centralized dashboard that tracks ingestion rates and latency.

Limitations

• Subscription costs: Unlike native open-source tools, Hevo is a paid service that can be a consideration for smaller projects or limited budgets.
• Internet dependency: As a SaaS-based platform, the migration speed and stability depend on your network connection and the platform’s cloud availability.
• Configuration limits: Some niche PostgreSQL extensions or custom data types can require manual transformation logic within the platform.

Method 2: Manually Connect PostgreSQL to MySQL using the pg2mysql PHP Script

Overview

The pg2mysql method is a developer-centric approach where you use a specialized PHP command-line script to translate PostgreSQL-specific SQL syntax into MySQL-compatible code. This method is different from automated platforms since it relies on generating an SQL dump and running a transformation engine over the file to bridge the dialect gap between the two database systems. 

This method is ideal for developers who require a lightweight migration path without any third-party integrations. It’s especially ideal for one-time migrations or legacy system shifts where a developer needs granular control over the SQL file before importing it into a destination MySQL server. 

Step-by-step

Below are the steps for PostgreSQL to MySQL migration manually Using the pg2mysql PHP Script:

Step 1: Downloading the Script and PHP

• Downloading the script and unpacking the archive is the first step to connecting PostgreSQL to MySQL using the pg2mysql php script.

wget http://www.lightbox.ca/pg2mysql/pg2mysql-1.9.tar.bz2  (contains original script from the author)

wget https://www.easyredmine.com/ER/media/knowledge-base/pg2mysql-1.9.zip (contains our modified script for php version 7.4)

tar -xvf pg2mysql-1.9.tar.bz2(in case of original script from the author)

unzip pg2mysql-1.9.zip (in case of our modified script for php version 7.4)

• You can install PHP using the command given below:

sudo apt install php7.4-cli (in case of our modified script)

Step 2: Creating Dump Database for PostgreSQL and running it

• Make a .sql dump of your PostgreSQL database, being sure to use the “—format p —inserts” option.

sudo -u postgres pg_dump –format p –inserts DBNAME_HERE > /path/to/file.sql

• Navigate to the pg2mysql-1.9 folder.

cd pg2mysql-1.9/

• Run this file, which we have created.

php pg2mysql_cli.php /path/to/pd/dump/file.sql /path/where/to/save/mysql/file.sql

There will be some lines like this.

Completed!     30820 lines        5539 sql chunks

Notes:

 – No its not perfect

 – Yes it discards ALL stored procedures

 – Yes it discards ALL queries except for CREATE TABLE and INSERT INTO 

 – Yes you can email us suggestions: info[AT]lightbox.org

    – In emails, please include the PostgreSQL code, and the expected MySQL code

 – If you’re having problems creating your PostgreSQL dump, make sure you use “–format p –inserts”

 – Default output engine if not specified is MyISAM”

• The corrected SQL dump file will now be created. It’s recommended that you read it and replace MyISAM with InnoDB wherever possible.
• At this point, you can restore the dump to a clean MySQL database.
• Since the script does not save indexes (mentioned by the creator), you must manually add them to each table (it will work without indexes, but it may cause serious performance issues). You must manually add them or use your own custom script.

Learn More About: 3 Best Methods to Export MySQL Database

 Key advantages

• Full syntax control: Since the output is a standard SQL file, you can manually inspect and modify queries, constraints, or engine types before the data is ever loaded.
• No-cost utility: This is a completely free, open-source solution that doesn’t require monthly subscriptions or enterprise licenses.
• Local execution: Data never leaves your infrastructure, so you get a high level of privacy and security for sensitive internal databases.
• Lightweight environment: It requires nothing more than a PHP runtime and basic terminal access, making it compatible with most Linux-based development environments.

Limitations

• Loss of logic and indexes: The script discards stored procedures and indexes; you must manually recreate these in MySQL to avoid performance degradation.
• One-way, manual workflow: It is a one-way PostgreSQL to MySQL data transfer process that does not support real-time syncing or automated incremental updates.
• Engineering overhead: It requires substantial manual intervention to handle data type mismatches and engine optimizations (e.g., MyISAM to InnoDB).
• No transformation: During data transfer, there is no room for transformation. As a firm, if you need to update your data before exporting it from PostgreSQL to MySQL, this might be a considerable drawback.

Method 3: Migrate Postgres to MySQL using the MySQL Workbench Migration Wizard

Overview

The MySQL Workbench Migration Wizard is a built-in, visual tool that helps in transitioning from various database systems to MySQL. It provides a structured, graphical interface that automates the complex tasks of object mapping, data type conversion, and data transfer. 

If you’re a database administrator already using the MySQL ecosystem, it’s an accessible option. Additionally, this method is also ideal if you want a guided experience without writing manual scripts or paying for third-party SaaS platforms. You can use ODBC (Open Database Connectivity) drivers to create a direct bridge between PostgreSQL and MySQL and ensure a smooth migration of tables, data, and basic schema structures.

Step-by-step

Follow these steps to migrate PostgreSQL to MySQL using the MySQL Workbench Migration Wizard:

Step 1: Download and install the PostgreSQL ODBC driver

Once we install PostgreSQL, we need to install an ODBC driver wherever we have installed MySQL Workbench.

Step 2: Open MySQL Workbench and start the migration wizard

• On the MySQL Workbench screen, click on the Database > Migrate on the main screen to start the Migration wizard. 
• We can see an overview page for the Migration wizard below.

Step 3: Set up the parameters required for connecting to the source database

• Click on the Start Migration button to connect to the Source Selection page.

Source

• Specify all the necessary information and click the Test Connection button to check the connection with PostgreSQL. 
• Click on the Next button to move to the destination selection page.

Step 4: Set up the parameters required for connecting to the destination database

• Specify the required parameters on the destination setup page to connect to the MySQL Server instance.
• Click on the Test Connection button to verify the connection, and click on the Next button to move to the Schema setup page.

Step 5: Select the schema for migration

The migration wizard communicates with the PostgreSQL database to fetch the schema data into the source database.

• Verify the tasks and click on the Next button to finish the schema setup for migration.
• Verify the tasks and click on the Next button to finish the schema setup for migration.

Step 6: Select the objects for migration

• Now, the retrieved schema from the source will be reverse-engineered to determine its structure. 
• In the source objects page, we can see the list of objects retrieved for migration.

• Click the Show Selection button to see the table objects in the source database. Click on the Next button to review the proposed migration.

Step 7: Review the proposed migration

In this step, the Migration wizard converts the objects into equivalent objects in MySQL and creates a MySQL code for the destination server.

• On the Manual Editing tab on the left side navigation panel. We can double-click on the Pagila row and rename the resultant database. It will also show the table columns, default values, and other attributes.

Step 8: Run the MySQL code to create the database objects

• Click on the Target Creation Options tab on the left side navigation pane. We will be given the option to run the generated code in the target RDBMS. Click on the Next button and move to the transfer data setup page.

• Transfer data setup page.
• The progress of the migration can be seen on the Create Schemata tab on the left-side navigation pane.
• Once the migration is done, click on the Next button and move to the transfer setup page.

Step 9: Transfer the data to the MySQL database

The data transfer setup page allows you to configure the transfer process, as given below.

• Configure the transfer process
• Select the first option of live transfer of data and click on the Next button. Once it is done, we can see the summary of the transfer process on the report page and click on the Finish button to close the Migration wizard.

Key advantages

• User-friendly graphical interface: The wizard-driven approach doesn’t need command-line expertise, opening it up to a wider range of users.
• Integrated object mapping: It automatically handles the conversion of data types and table structures between PostgreSQL and MySQL dialects.
• Built-in data validation: The tool provides real-time feedback and reports during the migration, so that you can catch and resolve errors during the schema creation phase.
• Customizable transformation: Users can manually edit the generated SQL scripts within the wizard to fine-tune the migration before applying changes to the destination.

Limitations

• ODBC dependency: The success of the migration heavily depends on the correct installation and configuration of external ODBC drivers.
• Resource-intensive: For very large datasets, the Workbench GUI may consume more local memory and CPU, potentially slowing down transfer speeds compared to CLI-based tools.

Complex logic limitations: While it’s great for tables and data, complex PostgreSQL-specific logic like triggers and advanced stored procedures may still need manual adjustment after migration.

Factors to Consider for Postgres to MySQL Migration

A few differences between Postgres and MySQL should be considered before moving from Postgres to MySQL. A few of these are mentioned below.

• Complexity brought on by certain data models: Numerous data types are supported by both Postgres and MySQL. This covers a wide range of data formats, including JSON, XML, and TEXT, as well as conventional SQL types like String, Boolean, Integer, and Timestamp. It’s important to remember, though, that MySQL does not handle all Postgres data types.
• Differences between SQL and database capabilities: There are several operations that are performed differently in Postgres and MySQL databases. Additionally, some features could be supported in one but not in the other. Understanding this might help you steer clear of certain typical traps.
• Stored procedures: The process must be written in the regular SQL syntax according to MySQL. In comparison, the procedures in Postgres are function-based and may be written in a variety of languages, including Ruby, Perl, SQL, Python, JavaScript, and others.
• Extensions for Postgres: It can complicate a migration if you use Postgres extensions; thus, before you migrate, be sure to audit each one separately.
• Case sensitivity and support for IF/IFNULL: If a Postgres table or column name is enclosed in double quotations, it is case-sensitive. The names of MySQL tables and columns, however, are not case-sensitive. For this reason, bear this in mind while doing a migration. Additionally, Postgres does not support IF and IFNULL statements for condition evaluation, but MySQL does.

Related: If you are looking to streamline your PostgreSQL workflow, do read our blog on PostgreSQL import CSV, saving you time and effort. And if you’re interested in optimizing performance and achieving high availability, don’t miss our guide on setting up PostgreSQL clusters [+clustering options].

Postgres vs MySQL Schema- Key Differences

• VARCHAR in Postgres vs MySQL
  • For the varchar type, you need to set up a max value in MySQL while this is not required for Postgres. Instead of this, you can use the text type in MySQL, or have the same varchar type. But, you should specify a max length for the same. If you go for the varchar option,keep in mind the max value so that there won’t be any troubles while migrating your data. 
• SERIAL in Postgres vs MySQL
  • When you set the serial type in MySQL, the underlying data type by default will be bigint unsigned auto_increment. Overall, this wouldn’t have an impact on how you store your data. But, its good to know this as the data type will be different based on the records you have.
• Spatial data in Postgres vs MySQL: POINT
  • Using spatial data is straightforward in PostgreSQL. When you define the location column as a type of Point, it is possible to insert spatial data by providing the required coordinates. In MySQL, spatial data types have different behavior. Based on the version of your MySQL, you might need to have a different approach to store spatial data.

Use Cases for PostgreSQL to MySQL Integration

• MySQL enables complex data analysis and insights beyond Postgres’ capabilities alone through powerful querying and processing.
• Centralizing data from Postgres and other sources in MySQL provides a unified view across systems. This consolidated data can enable change data capture to prevent future discrepancies.
• MySQL’s capabilities for long-term data retention facilitate historical trend analysis over time, without Postgres’ limits.
• Syncing Postgres data to MySQL leverages MySQL’s robust security features for advanced data governance and compliance.
• MySQL scales to handle large, growing data volumes from Postgres without performance impacts.
• With Postgres data in MySQL, machine learning for predictive analytics, customer segmentation and more is enabled.
• Visualization tools like Tableau, Power BI and Looker that connect to MySQL enhance business intelligence, augmenting Postgres’ reporting.

Additional Resources for PostgreSQL Integrations and Migrations

• Connect PostgreSQL to MongoDB
• Connect PostgreSQL to Redshift
• Integrate PostgreSQL to Databricks
• Replicate Postgres to Snowflake
• Export a PostgreSQL Table to a CSV File

Conclusion

In this article, we got a glimpse of how to connect PostgreSQL to MySQL after a brief introduction to the salient features and use cases. The manual methods talked about in this article are using CSV files, pg2mysql php script, and MySQL Workbench. The process can be a bit difficult for beginners. Moreover, you will have to update the data each and every time it is updated and this is where Hevo saves the day!

Hevo Data provides its users with a simpler platform for integrating data from 150+ data sources for Analysis. It is a No-code Data Pipeline that can help you combine data from multiple sources like PostgreSQL. You can use it to transfer data from multiple data sources into your Data Warehouses, Database, or a destination of your choice, such as MySQL.

It provides you with a consistent and reliable solution to managing data in real-time, ensuring that you always have Analysis-ready data in your desired destination. Sign up for a 14-day free trial and experience the feature-rich Hevo suite firsthand.

FAQ on Postgres to MySQL