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.