How to Migrate MySQL to PostgreSQL Using Free Tools

Image Source: unsplash

You can migrate MySQL PostgreSQL databases using several popular free tools:

• pgLoader

• mysql_fdw

• mysqldump

• Kettle (useful for large data ETL processes)

Choose tools that offer automation and reliability for smoother migrations. You will find step-by-step instructions for each tool in this guide.

Tip: Always test your data and application after migration to ensure everything works as expected.

Preparation

Image Source: unsplash

Backup MySQL

Start by backing up your MySQL database. You protect your data from accidental loss during migration. Use the mysqldump command to create a backup file. Store this file in a safe location.

Tip: Run migration tools on a separate server. This prevents performance issues on your production system.

Check Compatibility

Before you begin the MySQL PostgreSQL migration, check for compatibility issues. You need to review your database schema and data types. PostgreSQL uses stricter data types than MySQL.
Common compatibility issues include:

• Data type mismatches between MySQL and PostgreSQL

• Schema differences that require adjustments

• Application compatibility with PostgreSQL features

• Incompatible data type values, such as MySQL ‘zero dates’

• Object names longer than 63 characters, which PostgreSQL does not allow

• Duplicate index names, allowed in MySQL but not in PostgreSQL

• Foreign key constraint violations from missing or inconsistent data

Use MySQL Workbench to manually map types and set up connections. This tool helps you identify and fix schema differences before migration.

Set Up PostgreSQL

Prepare your PostgreSQL server for the migration. Make sure your hardware meets recommended requirements.
Here is a quick reference table:

Monitor your PostgreSQL server’s performance during migration. Adjust resources if you notice slowdowns or bottlenecks.

Note: Matching server size helps you avoid resource shortages and ensures a smooth migration.

MySQL PostgreSQL Migration Tools

You have several free tools to help you migrate from MySQL PostgreSQL databases. Each tool has its own strengths and limitations. The table below gives you a quick comparison:

You can also explore other free tools for MySQL PostgreSQL migration:

pgLoader Setup

pgLoader is a popular choice for MySQL PostgreSQL migrations. You can automate both schema and data transfer with a single command. This tool handles most data type conversions for you. You can use pgLoader for remote migrations over SSL. This means you can move your data securely, even if your servers are in different locations.

pgLoader enforces the use of trusted certificates for SSL connections. You must set up your MySQL server for encrypted connections and add the necessary CA and client certificates to your PostgreSQL server’s trusted store. This setup protects your data from eavesdropping and tampering. If you misconfigure certificates, your connection may fail, so double-check your SSL settings.

For large databases, pgLoader performs well. For example, if you migrate a 50GB database, the initial load may take about 2.3 hours. Index creation can take 45 minutes, and foreign key setup about 20 minutes. You can expect a total downtime of around 3.5 hours for this size.
If you need to migrate even larger databases, you may want to look at tools like Omni Loader, which can handle millions of records per second.

mysql_fdw Extension

You can use the mysql_fdw extension to connect your PostgreSQL server directly to your MySQL database. This tool lets you run queries on your MySQL data from PostgreSQL. You can use this method for phased migrations. You can validate your data live and move tables one at a time.
The setup for mysql_fdw is more complex than other tools. You need to install the extension, configure foreign data wrappers, and set up user mappings. This method works well if you want to minimize downtime or test your migration in stages.

mysqldump Compatible Export

mysqldump is a classic tool for exporting MySQL data. You can use the --compatible option to make your export more suitable for PostgreSQL. This reduces compatibility issues during import.

• The --compatible option helps align your MySQL data with PostgreSQL requirements.

• You still need to adjust SQL syntax and data types after export.

mysqldump --compatible=postgresql -h... -u... -p... dbname tablename > PostgresqlData.sql
Use this command to export your MySQL data in a PostgreSQL-friendly format.

Keep in mind that mysqldump does not handle all differences between MySQL and PostgreSQL. You may need to edit your exported SQL file before importing it into PostgreSQL.

Tip:
Migration times depend on your database size and the tool you choose. Small databases (under 1GB) can migrate in minutes. Large databases (over 100GB) may take several hours. Tools like DBConvert Streams can speed up the process with parallel processing.

You now have a clear overview of the main free tools for MySQL PostgreSQL migration. Choose the one that fits your needs and technical skills.

Data Transfer Steps

Image Source: unsplash

Configure Connections

You need to set up connections between your MySQL and PostgreSQL servers before starting the migration. Follow these steps for a smooth setup:

1. Export your MySQL data using mysqldump.

2. Run the following command to export table data to XML format:

1

echo "select * from mysql_table_name" | mysql [CONNECTION PARAMETERS] -X --default-character-set=utf8 > mysql_table_name.xml

3. Write a script to convert the XML data into PostgreSQL-compatible INSERT statements.

4. Use the command below to execute the SQL statements in PostgreSQL:

1

psql [CONNECTION PARAMETERS] -f FILENAME -1

Tip: Always use UTF-8 encoding in both databases to avoid character set problems.

Run Migration

After configuring connections, you can start the migration process. Each tool has its own steps:

• With pgLoader, install the tool and create a configuration file with your source and target details. Run the migration command to transfer schema and data.

• For mysql_fdw, install the extension on PostgreSQL, set up foreign data wrappers, and map users. This lets you query MySQL tables directly from PostgreSQL.

• Using mysqldump, export your data, adjust the SQL file for compatibility, and import it into PostgreSQL.

Common challenges include character set issues, stored procedure differences, and timezone mismatches. Set a standard timezone for your application to avoid problems.

MySQL PostgreSQL Data Transfer

Handling data type differences is important during migration. Use the table below to map types correctly:

You should also map users and permissions carefully:

1. Create a custom database and schema for your application.

2. Set up DDL and DML roles for data definition and manipulation.

3. Revoke unnecessary permissions from PUBLIC.

4. Grant specific privileges to each role.

5. Create users for migration and application operations.

Note: MySQL requires a maximum length for varchar, while PostgreSQL does not. Adjust your schema as needed.

You may encounter errors like data type incompatibility, view modification issues, and enum handling. Drop and recreate views when needed, and use custom ENUM types in PostgreSQL for better management.

Testing & Validation

Verify Data

You need to confirm that your data migrated correctly. Start by checking that all tables appear in your PostgreSQL schema. Review indexes to make sure they match your original MySQL setup. Validate sequences to keep your data consistent.

• Check every table for completeness.

• Review indexes for accuracy.

• Confirm that sequences work as expected.

• Run comprehensive checks on your data.

• Use spot checks to catch small errors.

• Examine the entire dataset for integrity.

MOLT Verify acts as a safety net during migration. It helps you find missing records, mismatched values, or schema inconsistencies before you switch your application to PostgreSQL.

You can also use these methods for deeper validation:

1. Compare each record in both databases.

2. Generate a hash value for collections to verify integrity.

3. Count records in both systems to ensure they match.

If you find issues, you can roll back changes in PostgreSQL. The system marks the transaction as ‘aborted’ in the commit log. This makes the changes invisible without altering your tables, so you can quickly restore your data.

Test Application

After you verify your data, test your application with the new PostgreSQL database. Run all major workflows and features. Check that your application connects to PostgreSQL and that users can log in, view data, and save changes.

• Test user authentication.

• Run reports and queries.

• Save and update records.

• Check for errors or missing data.

If you see problems, review your migration steps and fix any issues before going live.

Performance Check

You should measure your database performance after migration. Focus on key metrics to ensure your system runs smoothly.

Common performance issues include slower queries, changes in index use, and differences in join algorithms. You can address these by reviewing your migration plan, validating data, and comparing performance before and after migration.

Tip: Establish a performance baseline before migration. This helps you spot and fix issues quickly after switching to PostgreSQL.

You can migrate MySQL to PostgreSQL using free tools like pgLoader, mysqldump, and custom scripts. Follow these steps for a successful migration:

1. Install and configure your chosen tool.

2. Export and adjust your data.

3. Import into PostgreSQL.

4. Test and validate every part of your database.

Thorough testing ensures your data and application work as expected.
For troubleshooting and advanced tips, check the FAQ section.

After migration, optimize PostgreSQL by monitoring performance, refining schema design, and using tools like pgAdmin or Grafana for better reliability and speed.

FAQ

How do you handle MySQL AUTO_INCREMENT columns in PostgreSQL?

PostgreSQL uses the SERIAL or BIGSERIAL data type for auto-incrementing columns. You can map MySQL’s AUTO_INCREMENT to these types during migration.

Tip: Always check your schema after migration to confirm correct sequence setup.

What should you do if you see character set errors?

You should set both databases to use UTF-8 encoding. This prevents most character set issues.

1

SET client_encoding = 'UTF8';

Check your export and import commands for encoding options.

Can you migrate stored procedures and triggers automatically?

Most free tools do not convert stored procedures or triggers. You need to rewrite them in PostgreSQL’s PL/pgSQL language.

Note: Review your business logic to ensure it works after migration.

How do you minimize downtime during migration?

You can use tools like mysql_fdw for phased migration. This lets you move data in stages and validate results before switching your application.

• Test each phase.

• Plan a final cutover window.

• Notify users in advance.

Recommended reading

• SQL Optimization Course
• SQL optimization knowledge
• Database industry knowledge

What is SQLFlash?

SQLFlash is your AI-powered SQL Optimization Partner.

Based on AI models, we accurately identify SQL performance bottlenecks and optimize query performance, freeing you from the cumbersome SQL tuning process so you can fully focus on developing and implementing business logic.

How to use SQLFlash in a database?

• SQLFlash + MySQL

• SQLFlash + PostgreSQL