Migrate a MySQL database to PostgreSQL using pgLoader

I received a dump of a MySQL database, but my current main database is PostgreSQL. I started thinking about how to migrate data from MySQL to PostgreSQL and found a tool called PgLoader.

What tools we need

1. Mysql
2. Postgresql
3. PgLoader

*I will demonstrate the entire process on AlmaLinux 8.

Let’s get started

1. Install Mysql 8

dnf install mysql-server -y
systemctl start mysqld
systemctl enable mysqld

Now, let’s create a test_db with a table and add some data.

mysql -u root -p

# create database test_db
CREATE DATABASE test_db;

# switch to this database test_db
USE test_db;

# create table sample_table
CREATE TABLE sample_table (
  user_id INT PRIMARY KEY,
  first_name VARCHAR(50),
  last_name VARCHAR(50),
  birthday DATE
);

# populate this table with some data
INSERT INTO sample_table (user_id, first_name, last_name, birthday) 
VALUES (1, 'Alexei', 'Cosetov', '1986-05-16'),
(2, 'Elon', 'Musk', '1971-06-28'),
(3, 'Bill', 'Gates', '1955-10-28'),
(4, 'Jeff', 'Bezos', '1964-01-12'),
(5, 'Mark', 'Zuckerberg', '1984-05-14');

# can close the MySQL prompt
exit

2. Install Postgresql 15

Let’s install PostgreSQL now. I will use the official PostgreSQL package for CentOS/AlmaLinux.

# Install the repository RPM:
dnf install -y https://download.postgresql.org/pub/repos/yum/reporpms/EL-8-x86_64/pgdg-redhat-repo-latest.noarch.rpm

# Disable the built-in PostgreSQL module:
dnf -qy module disable postgresql

# Install PostgreSQL:
dnf install -y postgresql15-server

# Optionally initialize the database and enable automatic start:
/usr/pgsql-15/bin/postgresql-15-setup initdb
systemctl enable postgresql-15
systemctl start postgresql-15

Now, let’s create a main_db using Postgresql.

su postgres
psql

# add default password for user postgres
ALTER USER postgres PASSWORD 'myPassword';
# create database main_db
CREATE DATABASE main_db;
\q

3. Install PgLoader

After installing PostgreSQL from the official repository, you will also find PgLoader available in the official repository. Let’s start.

dnf install pgloader -y

Installation of PgLoader is complete.

Migrate data from MySQL to Postgresql

We are ready to go.

pgloader mysql://root@localhost/test_db pgsql://postgres:myPassword@localhost/main_db

This is the result of running the command.

Now let have a look what we have in Postgresql.

sudo -u postgres psql

# connect to our main_db
\c main_db

# select data
select * from sample_table;

We have completed the data migration from MySQL to PostgreSQL.