Database migration is a common task in software development when switching between different database management systems (DBMS).

In this article, we'll explore how to migrate a MySQL database to PostgreSQL using a powerful tool called pgloader. We'll cover the concepts involved, and the steps required, and provide detailed examples with outputs to guide you through the process.

What is pgloader?

pgloader is a robust and flexible tool designed specifically for loading data into PostgreSQL databases from various sources, including other SQL databases like MySQL, SQLite, and SQL Server. It automates the process of schema discovery, data conversion, and data loading, making database migrations efficient and straightforward.

Why Migrate from MySQL to PostgreSQL?

There are several reasons why developers may choose to migrate from MySQL to PostgreSQL:

• Advanced Features and SQL Compliance: PostgreSQL offers more advanced features and better SQL compliance than MySQL.
  
• Robustness and Scalability: PostgreSQL is known for its robustness, scalability, and support for complex queries, making it suitable for large-scale applications.
  
• Support for Advanced Data Types: PostgreSQL provides better support for JSON, arrays, and other advanced data types, offering more flexibility in data modeling.
  
• Suitability for Enterprise-level Applications: PostgreSQL's ACID compliance and transactional integrity make it more suitable for enterprise-level applications.

Prerequisites

Before proceeding with the migration, ensure you have the following prerequisites installed:

• pgloader: Download and install pgloader from the official website or using package managers like apt or brew.
  
• MySQL client: Ensure you have MySQL installed and accessible from the command line.

Steps to Migrate MySQL Database to PostgreSQL

Migrating a database from MySQL to PostgreSQL requires a structured approach to ensure data integrity and minimize disruptions. In this comprehensive guide, we'll walk you through each step of the migration process, leveraging the power of pgloader for seamless data transfer.

Step 1: Create a Sample Database in MySQL

Begin by setting up a sample MySQL database with test data. This serves as the source database for our migration process. Using your MySQL client, execute the following SQL commands:

CREATE DATABASE sample_mysql_db;
USE sample_mysql_db;

CREATE TABLE users (
    id INT PRIMARY KEY,
    name VARCHAR(255),
    email VARCHAR(255)
);

INSERT INTO users VALUES
(1, 'John Doe', 'john@example.com'),
(2, 'Jane Doe', 'jane@example.com');

This establishes a MySQL database named sample_mysql_db with a users table populated with sample data.

Step 2: Install pgloader On Your System

pgLoader is a tool that can load data into a PostgreSQL database from various sources. To take advantage of pgLoader’s useSSL option for migrations from MySQL over an SSL connection, you need version 3.5.1 or newer. Since the default Ubuntu repositories currently provide an older version, we'll install pgLoader from the source.

First, update your package index and install the necessary dependencies:

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

Next, download the latest pgLoader release from its GitHub repository. Replace the URL in the command below with the latest version:

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

Extract the downloaded tarball:

tar xvf v3.6.2.tar.gz
cd pgloader-3.6.2/

Compile the pgLoader binary:

make pgloader

Move the binary to the /usr/local/bin directory:

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

Verify the installation by checking the pgLoader version:

pgloader --version

Step 3: Creating a PostgreSQL Role and Database

To facilitate the migration process using pgloader, you'll need to create a PostgreSQL role and a target database. The role will manage database access, and the database will be the destination for your migrated data.

Creating a PostgreSQL Role

PostgreSQL uses roles to manage database access. Roles can function as users or groups. For our migration, we'll create a new role that authenticates with a password rather than the default ident method, which uses the client's Ubuntu username as the PostgreSQL username.

Steps to Create a New Role:

1. Run the createuser Script: Execute the following command on your PostgreSQL server to create a new role. The -P flag prompts you to set a password for the new role:

   sudo -u postgres createuser --interactive -P

2. Enter Role Details: You will be prompted to provide the name and password for the new role. For this guide, we'll use pgloader_pg as the role name.

   Enter name of role to add: pgloader_pg
   Enter password for new role:
   Enter it again:

3. Assign Superuser Privileges: pgloader requires broad privileges to access and load data into tables. Grant superuser privileges to the new role by typing y when prompted:

   Shall the new role be a superuser? (y/n) y

Creating a PostgreSQL Database

Next, create a new PostgreSQL database where the data from the MySQL database will be loaded.

Steps to Create a New Database:

1. Run the createdb Script: Use the following command to create a new database named new_db (feel free to name it differently if you prefer):

   sudo -u postgres createdb new_db

   If the command executes successfully, it will complete without any output.

Step 4: Create a Dedicated User in MySQL and Manage Certificates

For security purposes, it's recommended to create a dedicated user in MySQL specifically for the migration process. Additionally, if SSL/TLS certificates are required for secure communication between MySQL and pgloader, ensure they are properly configured. You can create a dedicated user in MySQL using the following command:

CREATE USER 'migration_user'@'localhost' IDENTIFIED BY 'migration_password';
GRANT ALL PRIVILEGES ON sample_mysql_db.* TO 'migration_user'@'localhost';
FLUSH PRIVILEGES;

Managing SSL Certificates

pgLoader requires SSL certificates to connect securely to MySQL. You need to add these certificates to Ubuntu’s trusted certificate store.

1. Copy and Rename Certificates: Rename and copy the ca.pem and client-cert.pem files to /usr/local/share/ca-certificates/:

sudo cp ~/client-ssl/ca.pem /usr/local/share/ca-certificates/ca.pem.crt
sudo cp ~/client-ssl/client-cert.pem /usr/local/share/ca-certificates/client-cert.pem.crt

Update Certificate Store:

Update the system’s list of trusted certificates:

sudo update-ca-certificates

Output:

Updating certificates in /etc/ssl/certs...
2 added, 0 removed; done.
Running hooks in /etc/ca-certificates/update.d...
done.

Step 5: Migration of Data

With configurations in place, you can now migrate your MySQL data to PostgreSQL using pgLoader.

Running the Migration

1. Backup Data (Optional): While pgLoader doesn’t delete or transform data, you may choose to back up your database using mysqldump. Refer to MySQL documentation for details.
   
2. Run pgLoader Command: Use pgLoader to migrate the database. Replace the placeholders with your actual database details:

   pgloader mysql://pgloader_my:password@mysql_server_ip/sample_mysql_db?useSSL=true postgresql://pgloader_pg:password@localhost/new_db
   

   This command includes useSSL=true to ensure the connection is secure.
   
3. Check Migration Output: Successful migration will display an output table summarizing the process.

Verifying the Migration

1. Open PostgreSQL Prompt:

   sudo -i -u postgres psql

2. Connect to the New Database:

   \c new_db

3. Verify Data: Run a query to check the migrated data:

   SELECT * FROM sample_mysql_db.users;

   Expected output:

    id |    name    |        email        
   ----+------------+---------------------
     1 | John Doe   | john@example.com
     2 | Jane Doe   | jane@example.com
   (2 rows)

4. Exit PostgreSQL Prompt:

   \q

Conclusion

In this article, we explored the process of migrating a MySQL database to PostgreSQL using pgloader. We covered the necessary steps, including exporting the MySQL schema and data, installing pgloader, creating a PostgreSQL database, writing a pgloader script, and executing the migration.

By following these steps and examples, you can efficiently migrate MySQL databases to PostgreSQL while preserving data integrity and schema structure. Database migration is a crucial task in software development, and using tools like pgloader simplifies the process, allowing you to leverage the advanced features and capabilities of PostgreSQL.