How to migrate data from MySQL to PostgreSQL

You will transfer an existing MySQL database schema and data to a fresh PostgreSQL installation. These steps target Ubuntu 24.04 with MariaDB 11.4 as the source and PostgreSQL 16.4 as the destination.

Prerequisites

• Ubuntu 24.04 LTS (Jammy Jellyfish) or Debian 12 (Bookworm).
• Root or sudo access to the source server running MySQL 8.0 or MariaDB 10.11.
• Root or sudo access to the destination server running PostgreSQL 16.x.
• The pgloader package installed on the destination server.
• A valid MySQL root password and a PostgreSQL superuser role.

Step 1: Install pgloader on the destination server

Download the latest stable release of pgloader and install it using the official installer script. This tool handles the conversion of data types, character sets, and SQL dialects automatically.

curl -L https://github.com/pgloader/pgloader/releases/download/v0.8.1/pgloader_0.8.1_amd64.deb -o pgloader.deb
sudo dpkg -i pgloader.deb
sudo apt-get install -f

Verify the installation by checking the version:

pgloader --version

Expected output:

pgloader 0.8.1

Step 2: Prepare the source MySQL server

Ensure the MySQL server is running and accessible from the destination host. Create a dedicated user for the migration process to avoid locking the root account during the transfer.

mysql -u root -p -e "CREATE USER 'pgloader'@'%' IDENTIFIED BY 'SecurePassword123!';"
mysql -u root -p -e "GRANT SELECT, LOCK TABLES ON *.* TO 'pgloader'@'%';"
mysql -u root -p -e "FLUSH PRIVILEGES;"

Restart the MySQL service to apply any necessary configuration changes if you modified the my.cnf file for performance tuning.

sudo systemctl restart mysql

Step 3: Configure the migration script

Create a configuration file named mysql2pg.conf on the destination server. This file defines the source and destination connection details and specifies which database to migrate.

cat > mysql2pg.conf