How to migrate from MySQL to Postgres

In this PostgreSQL tutorial, we will cover “How to migrate from MySQL to Postgres” which means we are going to moving the database from MySQL to Postgresql.

When we are working on a different database like MySQL. sometimes we need to migrate from one database to another like Postgres due to flexibility, stability, and reliability.

Maybe our company wants us to move all databases from MySQL to Postgres. In that case, “What will we do?.“.

We are going to need these methods to move the database from one environment to another.

Actually, in this tutorial, we will migrate from MySQL to Postgresql over a TCP/IP connection.

How to migrate from MySQL to Postgres

There are some prerequisites that we need to follow before migrating the database:

1. We are going to need two different servers Postgres and MySQL, For that, we have used two different operating systems Ubuntu and kali linux on virtual machine like vmware.
2. Install MySQL on kali and Postgres on ubuntu.

To start MySQL on Kali :

It comes with preinstalled Postgres database, To start the MySQL type the below command

systemctl start mysql

Check the status of the MySQL server whether it is running or not, type the command.

systemctl staus mysql

To install Postgres on Ubuntu Linux:

sudo apt install postgresql-contrib

Check the status of the Postgresql whether it is running or not, type the command.

systemctl status postgresql

3. Create a new database named “mysql” in the MySQL server and insert some data into it.

If already exist “mysql” database, then drop that and create a new one of the same name.

CREATE DATABASE mysql;

USE mysql; --switch to newly created database

CREATE TABLE data(id int,name varchar(20)); -- new table named data

Insert the following data into the table named data.

INSERT INTOdata(id,name)VALUES(1,'Sam'),(2,'Jhon');

4. To migrate from one database to another, we need to install a tool called pgLoader that can load data into Postgres from different sources. It works like the copy command of PostgreSQL.

Before installing pgLoader, install the following packages on your Ubuntu machine:

• sbcl: It is a Common Lisp compiler.
• unzip: It is a de-archiver for .zip files.
• libsqlite3-dev: It is a collection of development files for SQLite 3.
• gawk: This is a pattern scanning and processing language.
• curl: This is a command line tool for transferring data from a URL.
• make: It is a utility for managing package compilation.
• freetds-dev: It is a client library for MS SQL and Sybase databases.
• libzip-dev: It is a library for reading, creating, and modifying zip archives.

sbcl,unzip, libsqlite3-dev, gawk, curl, make, freetds-dev, libzip-dev


--run the below command to install all of these libraries

sudo apt sbcl unzip libsqlite3-dev gawk curl make freetds-dev libzip-dev

5. let’s download the latest version of pgLoader from Github to install it.

• Type the following command to download pgLoader from github:

curl -fsSLO https://github.com/dimitri/pgloader/archive/v3.6.2.tar.gz

• Then, extract the file in current directory using :

tar xvf v3.6.2.tar.gz

       or

tar xf v3.6.2.tar.gz

• After that, move to the pgLoader directory:

cd pgloader-3.6.2/

• Now compile the pgLoader binary using make utility and it takes time:

make pgloader

6. Once the above step completes, then move the binary file into the “/usr/local/bin ” directory of your system, the location where Ubuntu searches for executable files.

And then check the version of pgLoader, If it is installed correctly using the following command.

sudo mv ./build/bin/pgloader /usr/local/bin/

                and

pgloader --version

7. Before we can begin our migration we’ll need to make some configuration changes to both your PostgreSQL and MySQL instances.

Creating Postgres user with database:

sudo -u postgres createuser --interactive -P

• The above command will ask for a new role, enter the name of the role and for the password, enter the password and note down the password somewhere.

• Then create database

sudo -u postgres createdb new_dbname

• Now you have created a user with an empty database where you will load the MySQL database.

Creating Mysql user with access to the source database:

Open MySQL prompt by typing the following command:

mysql -u root -p

• From the MySQL prompt, use the CREATE USER command to create a new MySQL user, name it pglod_mysql.
• Because this user will only access MySQL from your PostgreSQL server, be sure to replace “postgres_server_ip” with the public IP address of your PostgreSQL server. Additionally, replace password with a secure passphrase:

CREATE USER 'pglod_my'@'postgres_server_ip' IDENTIFIED BY 'password';

Next, grant the pglod_mysql user access to the target database and all of its tables. Here, we’ll specify the database that you’d like to migrate, use its name in place of source_db:

GRANT ALL ON source_db.* TO 'pglod_mysql'@'postgresql_server_ip';

And then run the following command to reload the grant tables, enable the privilege changes, and exit from MySQL prompt:

FLUSH PRIVILEGES;

Then exit from MySQL prompt.

exit

Now find the file “mariadb.cnf” by going to the /etc/mysq/ folder and open the file using the below command.

sudo nano mariadb.cnf

Add the following line.

[mysqld]
bind-address = 0.0.0.0

Here bind-address defines the incoming connection from IP addresses and 0.0.0.0 represents that it can accept connection from any IP address.

Save the file by pressing CTRL+W, hit Enter, and CTRL+X.

Restart the MySQL server using the below command.

systemctl restart mysql

Now go return to your Postgres server and log in to the MySQL server as the pglod_my user.

mysql -u pglod_my -p -h 192.168.253.129

As we can see in the above output, we have successfully logged into MySQL prompt as pglod_my user.

Migrating the data from MySQL to Postgres.

Now that we’ve configured remote access from your PostgreSQL server to our MySQL server.

Syntax:

pgloader mysql://mysql_username:password@mysql_server_ip/source_db_name?option_1=value&option_n=value postgresql://postgres_role_name:password@postgres_server_ip/destination_db_name?option_1=value&option_n=value

The above syntax contains the pgloader command with two connection strings, the first for the source database and the second for the destination database.

Both of these connection strings start with declaring what kind of DBMS the connection string points to, followed by the username and password that have access to the database. The host IP address of the server where the database is installed, the name of the database pgLoader should target.

Now go to the PostgreSQL server and open the terminal type the below command to migrate data.

pgloader mysql://pglod_my:12345@192.168.253.12/mysql?useSSL=false postgresql://pgloader_pg:12345@localhost/sam

Let’s verify the migrated data from MySQL.

Log into Postgresql command prompt using the below command.

sudo -u postgres psql

Connect to the database and list all tables using the below command.

\c sam  --connect to database named sam

\d    --list all the tables in the sam database

Well, we have successfully migrated data from MySQL database to Postgresql, as we can see in the above output.

You may also like to read the following PostgreSQL tutorials.

• PostgreSQL installation on Linux step by step
• How to create a table in PostgreSQL
• Postgresql create user with password
• How to connect to PostgreSQL database
• How to Uninstall PostgreSQL
• Postgresql Rank function
• Postgresql ilike case insensitive
• How to Restart PostgreSQL
• Postgresql group_concat
• PostgreSQL WHERE
• PostgreSQL CASE

So in this Postgresql, we have learned about “How to migrate from MySQL to Postgres”.