Migrating a database from MySQL to PostgreSQL is a common task for organizations aiming to improve performance, scalability, and reliability. PostgreSQL offers advanced features, strong data integrity, and better compliance with modern application requirements. In this guide, you will learn a complete, step-by-step process to migrate a MySQL database to PostgreSQL using PgLoader and Docker.
The blog covers everything from installing and configuring MySQL, creating a sample database and inserting data, to executing the migration and verifying the results in PostgreSQL using real, working commands. This approach is quick, secure, and highly reliable, making it suitable for both developers and database administrators.
1. Install MySQL Server on Ubuntu
First, update your system and install MySQL:
sudo apt update
sudo apt install mysql-server -y
Verify the installation:
mysql --version
You get a result like this
cybrosys@cybrosys:~$ mysql --version
mysql Ver 8.0.44-0ubuntu0.22.04.1 for Linux on x86_64 ((Ubuntu))
Enter into mysql like this
sudo mysql
Explore more about the mysql features by using the –help flag
mysql --help
2. Configure MySQL Root Password
Set a password for the MySQL root user and enable native password authentication:
ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'rootpass';
FLUSH PRIVILEGES;
EXIT;
Now login using the password:
mysql -u root -p
It will show the prompt for entering the password.Enter the password ‘rootpass’ for log in the mysql
3. Restart MySQL Service
If you modified any MySQL configuration file:
sudo nano /etc/mysql/mysql.conf.d/mysqld.cnf
Add these lines below the [mysqld]
default-authentication-plugin = mysql_native_password
character-set-server = utf8
collation-server = utf8_general_ci
Purpose of adding these 3 lines in mysql.cnf file
- mysql_native_password: Ensures PgLoader can authenticate with MySQL because it does not fully support the default MySQL 8 authentication plugin.
- utf8 character set: Prevents data corruption by keeping all text data in UTF-8 format, which is fully compatible with PostgreSQL.
- utf8_general_ci collation: Maintains consistent, case-insensitive text comparison during and after migration.
After adding these lines, restart mysql
sudo systemctl restart mysql
4. Create a Sample MySQL Database and User
Login again:
mysql -u root -prootpass
Create a database:
CREATE DATABASE demo_mysql;
Create a PgLoader user:
CREATE USER 'pgloader'@'localhost' IDENTIFIED WITH mysql_native_password BY 'pgloader';
GRANT ALL ON demo_mysql.* TO 'pgloader'@'localhost';
FLUSH PRIVILEGES;
5. Create a Table and Insert Data in MySQL
Select database:
USE demo_mysql;
Create table:
CREATE TABLE users (
id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(50),
email VARCHAR(100),
age INT
);
Insert sample records:
INSERT INTO users (name, email, age) VALUES
('Rahul', 'rahul@gmail.com', 25),
('Anu', 'anu@gmail.com', 22),
('Vijay', 'vijay@gmail.com', 30);
Verify data:
SELECT * FROM users;
6. Verify MySQL Databases
SHOW DATABASES;
You should see:
- demo_mysql
- mysql
- information_schema
- performance_schema
- sys
7. Create PostgreSQL Database
Switch to PostgreSQL user and create the destination database:
sudo su postgres
psql -p 5433
CREATE DATABASE demo_pg;
8. Install PgLoader Using Docker
Make sure Docker is installed:
sudo apt install docker.io -y
sudo systemctl start docker
sudo systemctl enable docker
9. Migrate MySQL to PostgreSQL Using PgLoader
Run PgLoader using Docker:
docker run --rm -it --network host dimitri/pgloader:latest \
pgloader mysql://pgloader:pgloader@localhost/demo_mysql \
postgresql://postgres:postgres@localhost:5433/demo_pg
Migration Output Summary
PgLoader will:
- Fetch table metadata
- Create PostgreSQL schemas
- Create tables
- Copy data
- Reset sequences
- Create primary keys
- Build indexes
Final status will show success with all rows migrated.
You get a result like this, after the completion of migration from mysql to postgres
cybrosys@cybrosys:~$ docker run --rm -it --network host dimitri/pgloader:latest \
pgloader mysql://pgloader:pgloader@localhost/demo_mysql \
postgresql://postgres:postgres@localhost:5433/demo_pg
2025-12-07T12:27:32.048999Z LOG pgloader version "3.6.7~devel"
2025-12-07T12:27:32.204997Z LOG Migrating from #<MYSQL-CONNECTION mysql://pgloader@localhost:3306/demo_mysql {10073374F3}>
2025-12-07T12:27:32.205997Z LOG Migrating into #<PGSQL-CONNECTION pgsql://postgres@localhost:5433/demo_pg {100748CB63}>
2025-12-07T12:27:33.088983Z LOG report summary reset
table name errors rows bytes total time
----------------------- --------- --------- --------- --------------
fetch meta data 0 2 0.273s
Create Schemas 0 0 0.004s
Create SQL Types 0 0 0.015s
Create tables 0 2 0.045s
Set Table OIDs 0 1 0.016s
----------------------- --------- --------- --------- --------------
demo_mysql.users 0 3 0.1 kB 0.071s
----------------------- --------- --------- --------- --------------
COPY Threads Completion 0 4 0.077s
Index Build Completion 0 1 0.089s
Create Indexes 0 1 0.006s
Reset Sequences 0 0 0.077s
Primary Keys 0 1 0.004s
Create Foreign Keys 0 0 0.001s
Create Triggers 0 0 0.001s
Set Search Path 0 1 0.003s
Install Comments 0 0 0.000s
----------------------- --------- --------- --------- --------------
Total import time ? 3 0.1 kB 0.258s
10. Verify the Migrated Data in PostgreSQL
Connect to PostgreSQL:
sudo su postgres
psql -p 5433
Switch database:
\c demo_pg;
List tables:
\d
You will see:
Schema | Name | Type | Owner
demo_mysql | users | table | postgres
Fetch migrated records:
SELECT * FROM demo_mysql.users;
Output:
id | name | email | age
----+-------+-----------------+-----
1 | Rahul | rahul@gmail.com | 25
2 | Anu | anu@gmail.com | 22
3 | Vijay | vijay@gmail.com | 30
This confirms that MySQL data has been successfully migrated into PostgreSQL.
Why Use PgLoader for MySQL to PostgreSQL Migration
- Automatic schema creation
- Fast bulk data transfer
- Data type conversion handled internally
- Works perfectly with Docker
- Supports large databases
- Minimal manual effort
Conclusion
This migration approach is extremely useful for database administrators who handle cross-database platform transitions in real-world environments. It simplifies the complex task of moving data from MySQL to PostgreSQL by automating schema creation, data transfer, and validation through PgLoader and Docker. By following these structured steps, administrators can reduce manual errors, minimize downtime, ensure data integrity, and perform reliable migrations even for production systems.
This method also allows quick testing, rollback flexibility, and consistent results across different environments, making it a practical and efficient solution for modern database management operations.