Migrate database from MySQL to PostgreSQL using pgLoader








Are you trying to migrate a database from MySQL to PostgreSQL using pgLoader?

This guide will help you.

pgLoader is an open-source database migration tool developed to simplify the process of migrating an existing database from one database engine to PostgreSQL.
The tool supports migrations from several file types and database engines like MySQL, MS SQL and SQLite.
Here at Ibmi Media, as part of our Server Management Services, we regularly help our Customers to perform database migration related tasks.
In this context, we shall look into the process of migrating database from MySQL to PostgreSQL.

How to migrate database from MySQL to PostgreSQL ?

Recently, one of our customers approached us with the request to migrate his database from MySQL to PostgreSQL.
Here are the steps that we followed to do the same.

1. Set up pgLoader

i. First, we connect to the compute instance using SSH.
ii. Next, we update the apt packet cache and upgrade the software already installed on the instance.
For that, we run the below command:

$ apt update && apt upgrade -y

Then, we install pgLoader using the apt packet manager.
For that, here is the command that we run:

$ apt install pgloader

In order to check if the installation of pgLoader was successful, we run the following command:

$ pgloader --version

As a result, the below output displays.

pgloader version "3.4.1"
compiled with SBCL 1.3.3.debian

Thus, now the pgLoader is installed.
So, now we can start with the migration process.

2. Migrating the Database

Now, it’s time to migrate the database.

Using pgLoader, we can conveniently migrate the database with a single command:

pgloader mysql://mysqluser:password@<mysql-server>:<mysql-port>/<source-database> postgresql://<pgsql-role>:password@<pgsql_server>:<postgresql-port>/<target-database>

This command will automatically migrate the current database to the managed database instance.
However, the migration time may vary depending on the size of the database.

[Still, not able to migrate the database from MySQL to PostgreSQL? – We'd be happy to help you. ]



Conclusion

This article covers how to migrate a database using pgLoader with a single #command.
PostgreSQL, also known as “Postgres,” is an open-source relational database management system (RDBMS).
It has seen a drastic growth in popularity in recent years, with many developers and companies migrating their data to Postgres from other database solutions.
The prospect of migrating a database can be intimidating, especially when migrating from one database management system to another.

Facts about pgLoader:
1. pgLoader is an open-source #database migration tool that aims to simplify the process of migrating to #PostgreSQL.
2. It supports #migrations from several file types and RBDMSs — including #MySQL and SQLite — to PostgreSQL.
3. #pgLoader is a flexible tool that can perform a database migration in a single command.
4. With a few configuration tweaks, it can migrate an entire database from one physical machine to another using a secure SSL/TLS connection.


For Linux Tutorials

We create Linux HowTos and Tutorials for Sys Admins. Visit us on LinuxAPT.com

Also for Tech related tips, Visit forum.outsourcepath.com or General Technical tips on www.outsourcepath.com