How to use Pgloader to Migrate MySQL Database to Postgres

MySQL is faster and is used for small organizations but with MySQL, you can’t perform complex and advanced queries. But with PostgreSQL, you can perform such kinds of queries, moreover, it provides analytics or data mining environment.

PostgreSQL has abilities for querying optimization, giving better query responses compared to MySQL.

There is a tool called pgloader which migrates the database of different database servers such as SQLite, and MySQL to a PostgreSQL server. The pgloader is an open-source tool that migrates the databases to PostgreSQL.

Follow the below steps to migrate the MySQL database to PostgreSQL:

I have two systems Windows and Ubuntu, MySQL is installed on Windows and PostgreSQL on Ubuntu.

First, you need to install the pgloader so install the pgloader on the Ubuntu system using the below command.

sudo apt-get update
sudo apt-get install pgloader

Now run the below command on the PostgreSQL server to create the new user. If you don’t know how to create the user in PostgreSQL, you can refer to this How to create user in PostgreSQL

sudo -u postgres createuser --interactive -P

After executing the above command, first, it asks for the password for the ‘postgres’ user and it prompts the steps to create the new user with different permission.

The above command asks for three things, the name of the role which is ‘postgres_pgloader’ as you can see in the output, the password for the role, and third is the new role is going to be superuser or not.

Now after creating the role ‘postgres_pgloader’, create an empty database where the MySQL database data will be copied.

Use the below command to create the empty database.

sudo -u postgres createdb pgdb

The above command executes without showing anything as output in the terminal, which means the database was created successfully.

Now you have a PostgreSQL role with an empty database where you can copy the data from the MySQL database.

Login into MySQL using as root using the below command.

mysql -u root -p

Or you can log in by opening the MySQL Command Line Client on Windows by searching through the search bar.

After login, create a user using the below syntax.

CREATE USER 'user_name'@'server_ip' IDENTIFIED BY 'password';

• CREATE USER: The command to create the new user on MySQL.
• user_name: The name of the new user.
• server_ip: It is the IP address of the server where PostgreSQL is installed.
• password: Password for the new user.

The next step is to grant access to the user for the database whose data you want to copy to the database at the PostgreSQL server. So use the below command.

GRANT ALL ON mysql_db.* TO 'mysql_pgloader'@'postgres_server_ip';

• mysql_db: This is a database on the MySQL server whose data will be copied to the database on the PostgreSQL server.
• mysql_pgloader: This is the user that you have created in the above step.
• postgres_server_ip: This is the IP address of the PostgreSQL server.

Run the below command to again load the grant table and apply the privileges changes.

FLUSH PRIVILEGES;

Then exit from the MySQL prompt using the below command.

exit