How to Migrate Data from MySQL to PostgreSQL in 2026: 3 Proven Methods

Moving data from MySQL to PostgreSQL does not have to be complicated. Whether you are migrating a small dataset or a large production database, the right method depends on your team’s technical expertise, data volume, and how much control you need over the process.

Here is why teams make the switch:

• Advanced features and capabilities: PostgreSQL supports complex queries, full-text search, JSONB data types, and better handling of concurrent transactions through Multi-Version Concurrency Control (MVCC)
• Enhanced performance and scalability: PostgreSQL performs better for complex queries and large datasets due to its sophisticated query planner and optimizer. It scales well for both read-heavy and write-heavy workloads
• Compliance and extensibility: PostgreSQL is highly standards-compliant with ANSI SQL and offers extensive support for custom data types, operators, and functions, making it a strong fit for businesses that need custom solutions

This guide walks you through three proven methods to move your MySQL data to PostgreSQL, from a no-code automated approach to technical options like pgloader and Foreign Data Wrapper. By the end, you will know exactly which method fits your use case and have the step-by-step instructions to get started.

Why Do We Need to Migrate MySQL Data to PostgreSQL? 

MySQL to PostgreSQL: Pre-Migration Considerations

A successful migration requires careful planning. Key considerations include:

Consideration	What to Do
Data Type Compatibility	Assess and address differences in data types between MySQL and PostgreSQL. Define and implement data type transformations where necessary before migration begins
Migration Configuration	Create a configuration file to control the migration process, including data mapping, transformations, and schema modifications
Application Compatibility	Evaluate the impact of migration on existing applications. Thoroughly test applications after migration to ensure compatibility
Backup and Recovery	Develop a disaster recovery plan, create full backups, and test backup and restore procedures before starting the migration

Migrate MySQL to PostgreSQL Without the Complexity

Manual migration is time-consuming, error-prone, and requires significant engineering effort. Hevo eliminates all of that.

• No-code setup: Connect MySQL and PostgreSQL in minutes. No scripts, no configuration files, no engineering overhead
• Automatic schema mapping: Detects and maps MySQL schemas to PostgreSQL automatically, handling data type differences without manual intervention
• Real-time replication: Keeps PostgreSQL in sync with MySQL during migration, ensuring zero data loss and minimal downtime
• Built-in fault tolerance: Auto-retries and self-healing pipelines ensure your migration completes reliably even when sources fail

Full visibility: Monitor migration progress, lineage, and pipeline health from a single dashboard in real time

Pre-Migration Considerations

A successful migration requires careful planning. Key considerations include:

1. Data Type Compatibility:

• Analyze: Assess and address differences in data types between MySQL and PostgreSQL.
• Transform: Define and implement data type transformations where necessary.

2. Migration Configuration:

• Create a configuration file: This allows for granular control over the migration process, including data mapping, transformations, and schema modifications.

3. Application Compatibility:

• Analyze: Evaluate the impact of the migration on existing applications.
• Test: Thoroughly test applications after migration to ensure compatibility.

4. Backup and Recovery:

• Develop a disaster recovery plan.
• Create full backups.
• Test backup and restore procedures.

What are the Methods To Migrate Data From MySQL To PostgreSQL?

Before diving into each method, here is a quick overview to help you choose the right one for your use case:

Method	Best For	Technical Skill Required	Setup Time	Real-Time Sync
Hevo Data	Teams that want a fast, no-code migration with automated schema mapping and real-time replication	Low	Minutes	Yes
pgloader	Technical teams that need full control over the migration using a single command-line tool	High	Hours	No
Foreign Data Wrapper (FDW)	Teams that want to query MySQL data directly from PostgreSQL without a full data transfer	High	Hours	No

Before diving into each method, here is a quick overview of the best PostgreSQL database migration tools to help you choose the right one for your use case.

Method 1: Using Hevo Data (Recommended)

Hevo Data is the fastest and simplest way to move data from MySQL to PostgreSQL. Its no-code interface handles schema mapping, data type conversions, and real-time replication automatically, without writing a single line of code.

Step 1: Connect MySQL as Your Source

• Log in to your Hevo account and navigate to Pipelines in the navigation bar
• Click + Create Pipeline and select MySQL as your source
• Enter the following details:
  • Pipeline Name: A unique name for your pipeline
  • Database Host: The IP address or hostname of your MySQL server
  • Database Port: Default is 3306
  • Database User: A user with read privileges on the MySQL database
  • Database Password: The corresponding password
  • Database Name: The name of the MySQL database to migrate
• Select your preferred ingestion mode. Enable binlog-based incremental loading to capture only new or changed records and avoid full reloads
• Click Test Connection to verify access
• Once connected, Hevo automatically detects your tables and schemas. Select the tables you want to replicate

Step 2: Configure PostgreSQL as Your Destination

• Click Destinations in the navigation bar
• Click + Create Standard Destination and select PostgreSQL
• Enter the following details:
  • Destination Name: A unique name for your PostgreSQL destination
  • Database Host: The IP address or DNS of your PostgreSQL server
  • Database Port: Default is 5432
  • Database User: A user with the necessary privileges on the PostgreSQL database
  • Database Password: The corresponding password
  • Database Name: The name of the destination database
  • Schema Name (Optional): Default is public
• Click Test Connection to verify

Step 3: Configure Schema Mapping and Transformations

• Review Hevo’s automatic schema and data type mappings from MySQL to PostgreSQL
• Make adjustments where required, for example tweaking column types or renaming fields
• Use Hevo’s visual transformations for simple cleaning and standardization
• Use Python transformations for complex logic such as derived columns, conditional mapping, or data masking
• Validate transformed data with a sample load before running the full migration

Step 4: Activate and Monitor Your Pipeline

Set up alerts to be notified of any pipeline failures or anomalies

Click Activate Pipeline to begin extraction, transformation, and loading

Monitor migration progress in real time from Hevo’s dashboard

View logs, latency, and errors as data moves from MySQL to PostgreSQL

Method 2: Using pgloader

pgloader is an open-source database migration tool that enables you to move data from different RDBMSs like MySQL and SQLite to PostgreSQL. It performs real-time transformations on the data it retrieves, executes raw SQL commands before and after the loading process, and uses the COPY PostgreSQL protocol to stream data efficiently into the server.

Prerequisites:

1. Access to servers with a firewall
2. Both servers must have a non-root user with sudo privileges
3. Use your PostgreSQL server as the MySQL client machine to migrate data using pgloader

Step 1: Create a Sample Database in MySQL

1. Open the MySQL prompt on your MySQL server:

mysql -u root -p

2. Create a new database and switch to it:

CREATE DATABASE sample_db;

USE sample_db;

3. Create a table in the database:

CREATE TABLE source_table (

    employee_id INT PRIMARY KEY,

    first_name VARCHAR(50),

    last_name VARCHAR(50),

    start_date DATE,

    salary VARCHAR(50)

);

4. Insert sample entries into the table:

INSERT INTO source_table (employee_id, first_name, last_name, start_date, salary) 

VALUES 

(1, ‘Elizabeth’, ‘Cotten’, ‘2007-11-11’, ‘$105433.18’),

(2, ‘Yanka’, ‘Dyagileva’, ‘2017-10-30’, ‘$107540.67’),

(3, ‘Lee’, ‘Dorsey’, ‘2013-06-04’, ‘$118024.04’),

(4, ‘Kasey’, ‘Chambers’, ‘2010-08-18’, ‘$116456.98’),

(5, ‘Bram’, ‘Tchaikovsky’, ‘2018-09-16’, ‘$61989.50’);

Step 2: Install pgloader on Your System

1. Update the package index on your PostgreSQL server:

postgres-server$ sudo apt update

2. Install pgloader dependencies:

postgres-server$ sudo apt install sbcl unzip libsqlite3-dev gawk curl make freetds-dev libzip-dev

3. Download the latest pgloader release from GitHub:

postgres-server$ curl -fsSLO https://github.com/dimitri/pgloader/releases/download/v3.6.9/pgloader-bundle-3.6.9.tgz

4. Extract the tarball:

postgres-server$ tar xvf v3.6.9.tar.gz

5. Navigate to the pgloader directory and compile the binary:

postgres-server$ cd pgloader-3.6.9/

postgres-server$ make pgloader

6. Move the binary to the system path:

postgres-server$ sudo mv ./build/bin/pgloader /usr/local/bin/

7. Verify the installation:

postgres-server$ pgloader –version

Step 3: Build a PostgreSQL Database and Role

1. Create a new PostgreSQL role:

postgres-server$ sudo -u postgres createuser –interactive -P

2. When prompted, enter the following:
   • A name for the new role
   • A password and confirmation
   • Select superuser when asked:

Shall the new role be a superuser? (y/n) y

3. Create the target database:

postgres-server$ sudo -u postgres createdb xxx

Step 4: Create a Dedicated MySQL User and Manage Certificates

1. Open the MySQL prompt:

mysql -u root -p

2. Create a dedicated MySQL user for pgloader. Replace your_postgres_server_ip with your PostgreSQL server’s public IP and set a secure password:

mysql> CREATE USER ‘new_mysql_name’@’your_postgres_server_ip’ IDENTIFIED BY ‘password’ REQUIRE SSL;

3. Grant access to the target database and reload privileges:

GRANT ALL ON source_db.* TO ‘new_mysql_name’@’your_postgresql_server_ip’;

FLUSH PRIVILEGES;

4. Exit the MySQL prompt:

mysql> exit

5. Add SSL certificates to Ubuntu’s trusted certificate store:

postgres-server$ sudo cp ~/client-ssl/ca.pem /usr/local/share/ca-certificates/ca.pem.crt

postgres-server$ sudo cp ~/client-ssl/client-cert.pem /usr/local/share/ca-certificates/client-cert.pem.crt

postgres-server$ sudo update-ca-certificates

Step 5: Migrate Your Data

1. Run the following pgloader command to migrate data from MySQL to PostgreSQL. Replace the placeholders with your actual database credentials:

pgloader mysql://pgloader_my:mysql_password@mysql_server_ip/sample_db?useSSL=true postgresql://pgloader_pg:postgresql_password@localhost/new_db

2. A successful migration will produce output similar to this:

            table name     errors       rows      bytes      total time

———————–  ———  ———  ———  ————–

        fetch meta data          0          2                     0.111s

         Create Schemas          0          0                     0.001s

       Create SQL Types          0          0                     0.005s

          Create tables          0          2                     0.017s

         Set Table OIDs          0          1                     0.010s

———————–  ———  ———  ———  ————–

 source_db.sample_table          0          5     0.2 kB          0.048s

———————–  ———  ———  ———  ————–

      Total import time          ✓          5     0.2 kB          0.084s

Step 6: Verify the Migration

1. Access the PostgreSQL prompt:

postgres-server$ sudo -i -u postgres psql

2. Connect to the target database:

\c new_db

3. Run a query to verify the migrated data:

SELECT * FROM sample_db.source_table;

4. Expected output:

employee_id | first_name |  last_name  | start_date |   salary   

————-+————+————-+————+————

           1 | Elizabeth  | Cotten      | 2007-11-11 | $105433.18

           2 | Yanka      | Dyagileva   | 2017-10-30 | $107540.67

           3 | Lee        | Dorsey      | 2013-06-04 | $118024.04

           4 | Kasey      | Chambers    | 2010-08-18 | $116456.98

           5 | Bram       | Tchaikovsky | 2018-09-16 | $61989.50

(5 rows)

5. Close the PostgreSQL prompt:

\q

Other pgloader Migration Options

1. Using a pgloader Load File

• Create a load file named pgload_test.load
• Define your MySQL source and PostgreSQL destination within the LOAD DATABASE command:

LOAD DATABASE

    FROM mysql://<user>:<password>@<mysql_host>/<mysql_db>?useSSL=true

    INTO pgsql://<user>:<password>@<postgres_host>/<postgres_db>

WITH

    include drop,

    create tables

ALTER SCHEMA ‘<source_schema>’ RENAME TO ‘public’;

• Run the load file:

pgloader pgload_test.load

2. Local MySQL to PostgreSQL Migration

• To migrate a MySQL database to a PostgreSQL database on the same machine, run:

pgloader mysql://root@localhost/sample_db pgsql://sammy:postgresql_password@localhost/target_db

3. Migration from CSV Files

• To migrate MySQL data from a CSV file to PostgreSQL:

pgloader load.csv pgsql://sammy:password@localhost/target_db

Method 3: Using Foreign Data Wrapper (FDW)

Foreign Data Wrapper (FDW) allows you to query MySQL data directly from PostgreSQL without a full data transfer. Instead of moving data, it creates foreign tables in PostgreSQL that map to tables on your MySQL server, enabling seamless cross-database querying.

Note: mysql_fdw currently supports PostgreSQL 14, 15, 16, 17, and 18.

To connect MySQL and PostgreSQL through Foreign Data Wrapper, you will need the following commands:

• CREATE EXTENSION: Creates a MySQL Foreign Data Wrapper extension on the PostgreSQL host
• CREATE SERVER: Defines a connection to the MySQL server
• CREATE USER MAPPING: Associates a PostgreSQL role with the foreign server
• CREATE FOREIGN TABLE: Defines a single table in PostgreSQL that maps to a table on the MySQL server
• IMPORT FOREIGN SCHEMA: Imports multiple remote tables into the local schema

Step 1: Create an Extension

1. Use the CREATE EXTENSION command to load the mysql_fdw extension into your PostgreSQL database:

CREATE EXTENSION mysql_fdw;

2. The command uses the following parameters:

• IF NOT EXISTS: Issues a notification instead of an error if the extension already exists
• extension_name: The name of the extension to install. Use mysql_fdw to connect to MySQL
• schema_name: The schema in which to install the extension. The named schema must already exist

Step 2: Create a Server

1. Use the CREATE SERVER command to define a connection to your MySQL server:

CREATE SERVER mysql_server 

FOREIGN DATA WRAPPER mysql_fdw 

OPTIONS (host ‘127.0.0.1’, port ‘3306’);

2. The command uses the following parameters:

• IF NOT EXISTS: Issues a notification instead of an error if a server with the same name already exists
• server_name: A unique name for the foreign server, for example mysql_server
• FOREIGN DATA WRAPPER fdw_name: The name of the Foreign Data Wrapper managing the server. Use mysql_fdw
• OPTIONS: Connection details for the server, including:
  • host: The address or hostname of the MySQL server. Default is 127.0.0.1
  • port: The port number of the MySQL server. Default is 3306
  • secure_auth: Enables or disables secure authentication. Default is true
  • ssl_key: The pathname of the client’s private key file
  • ssl_cert: The name of the client public key certificate file
  • ssl_ca: The name of the Certificate Authority (CA) certificate file
  • use_remote_estimate: Instructs the server to use EXPLAIN commands for cost estimation. Default is false
  • reconnect: Enables or disables automatic reconnection to the MySQL server if the connection is lost. Default is false
  • fetch_size: Specifies the number of rows mysql_fdw retrieves in each fetch operation. Default is 100

Step 3: Create User Mapping

1. Use the CREATE USER MAPPING command to associate a PostgreSQL role with the MySQL server:

CREATE USER MAPPING FOR public 

SERVER mysql_server 

OPTIONS (username ‘test’, password ‘secret’);

2. The command uses the following parameters:

• IF NOT EXISTS: Issues a notification instead of an error if the mapping already exists
• user_name: The existing PostgreSQL user to map to the foreign server. Use PUBLIC for a mapping with no user-specific restrictions
• server_name: The name of the server for which the user mapping is created
• OPTIONS: Connection credentials for the mapping:
  • username: The username on the MySQL server
  • password: The password associated with the username

Step 4: Create a Foreign Table

1. First, create the source table on your MySQL server:

CREATE TABLE warehouse (

    warehouse_id      INT PRIMARY KEY,

    warehouse_name    TEXT,

    warehouse_created TIMESTAMP

);

2. On your PostgreSQL server, create a corresponding foreign table that maps to the MySQL table:

CREATE FOREIGN TABLE warehouse (

    warehouse_id      INT,

    warehouse_name    TEXT,

    warehouse_created TIMESTAMP

)

SERVER mysql_server

OPTIONS (dbname ‘db’, table_name ‘warehouse’);

3. The command uses the following parameters:

• IF NOT EXISTS: Issues a notification instead of an error if a relation with the same name already exists
• table_name: The name of the foreign table, including the schema name if required
• column_name: The name of each column, corresponding to a column on the MySQL server
• data_type: The data type of each column. Use the same data type on both servers for a smooth connection
• server_name: The name of the existing foreign server the table will use
• OPTIONS:
  • dbname: The name of the database on the MySQL server
  • table_name: The name of the table on the MySQL server. Default is the foreign table name
  • max_blob_size: The maximum blob size to read without truncation

Step 5: Import Foreign Schema

1. Use the IMPORT FOREIGN SCHEMA command to import table definitions from your MySQL server into PostgreSQL. First, create the source tables on MySQL:

CREATE TABLE color (cid INT PRIMARY KEY, cname TEXT);

INSERT INTO color VALUES (1, ‘Red’);

INSERT INTO color VALUES (2, ‘Green’);

INSERT INTO color VALUES (3, ‘Orange’);

CREATE TABLE fruit (fid INT PRIMARY KEY, fname TEXT);

INSERT INTO fruit VALUES (1, ‘Orange’);

INSERT INTO fruit VALUES (2, ‘Mango’);

2. On your PostgreSQL server, run the following command to import the schema:

IMPORT FOREIGN SCHEMA edb FROM SERVER mysql_server INTO public;

3. Verify the imported data:

SELECT * FROM color;

Expected output:

cid | cname

—–+——–

   1 | Red

   2 | Green

   3 | Orange

(3 rows)

SELECT * FROM fruit;

Expected output:

fid | fname

—–+——–

   1 | Orange

   2 | Mango

(2 rows)

4. The IMPORT FOREIGN SCHEMA command uses the following parameters:

• remote_schema: The remote schema to import from, which is the MySQL database
• LIMIT TO (table_name): Limits the import to a specified subset of tables
• EXCEPT (table_name): Excludes specified tables from the import
• SERVER server_name: The server from which the foreign tables will be imported
• local_schema: The name of the local schema where the imported foreign tables will be created
• OPTIONS:
  • import_default: Controls whether column default expressions are included in imported table definitions. Default is false
  • import_not_null: Controls whether NOT NULL constraints are included in imported table definitions. Default is true

Key PostgreSQL Performance Optimization Techniques

Indexing

In PostgreSQL, indexing enhances query performance by allowing rapid data retrieval. They work by creating a sorted data structure that allows the database engine to locate the rows that satisfy a WHERE clause quickly. Some common index types include B-tree for equality and range queries, GIN for full-text search, and GiST for complex data types. 

Query Optimization

Query optimization in PostgreSQL involves simplifying queries, using appropriate joins, and ensuring efficient use of indexes. Tools like ‘EXPLAIN’ help understand query execution plans and identify areas for improvement.

For example, Using SELECT * FROM EMP WHERE ID=8; would retrieve the entire row where ID is found to be 8.

However, if we want to retrieve only certain information like name and address, then it is advisable to use 

SELECT NAME, ADDRESS FROM EMP WHERE ID=8;

Normalization and Denormalization

Normalization in PostgreSQL involves organizing data into tables and reducing redundancy by splitting large tables into smaller ones to maintain data integrity and minimize storage space. It aims to eliminate data anomalies and ensure each table serves a single purpose. 

On the other hand, denormalization involves combining tables to optimize query performance by reducing joins and improving data retrieval speed. It trades off some redundancy for faster read operations and is often used in read-heavy applications or for analytical purposes.

Partitioning

Partitioning in PostgreSQL involves dividing large tables into smaller, more manageable chunks called partitions based on specific criteria such as ranges or lists of values. It enables operations to target only relevant partitions, which enhances query performance and maintenance.

Caching

Additionally, PostgreSQL supports query-level caching with techniques like prepared statements. Function result caching can be achieved through custom implementations or third-party extensions to enhance performance for repetitive queries.

You can also learn more about:

• PostgreSQL on Google Cloud SQL to MySQL
• Amazon Aurora to MySQL
• Convert SQL Server to MySQL
• MySQL to SQL Server

Conclusion

Moving data from MySQL to PostgreSQL does not have to be a complex, high-risk project. With the right method and tooling, your team can complete the migration reliably and get back to focusing on analytics and product development.

If you need full control over the process, pgloader and Foreign Data Wrapper give you the technical flexibility to handle schema conversion, incremental transfers, and cross-database querying. But if your priority is speed, reliability, and zero maintenance overhead, Hevo Data is the most practical choice.

Hevo automates the entire process from schema mapping and data type conversion to real-time replication and pipeline monitoring, so your team does not have to write a single script or babysit a migration job. With 150+ connectors, automatic schema handling, and 24/7 expert support, Hevo gets your PostgreSQL database up and running with accurate, fresh data in minutes.

Over 2,000 data teams trust Hevo for production-ready data pipelines. Try it free for 14 days and see how fast your migration can be. Get Started with Hevo for Free! No credit card required. Up and running in minutes.

Additional Resources

• Load Data from Postgresql to MYSQL
• Migrate from PostgreSQL to Snowflake
• PostgreSQL reporting tools 
• MySQL analytics tools 

FAQs