Introduction
In today’s dynamic business environment, data migration across database migration systems is a purposeful action that corresponds to changing organizational demands. In this blog we are going to discuss transitioning from MySQL to PostgreSQL database migration. However, this transition demands rigorous planning to address potential concerns and ensure seamless integration with existing business practices.
Basic Differences
MySQL
- Performance-Oriented: MySQL is optimized for high performance, especially in read-heavy applications, making it a popular choice for web applications.
- Cost-Effective: Often associated with lower licensing fees and operational costs.
- Extensive Ecosystem: Supported by a large community and numerous plugins for enhanced functionality.
PostgreSQL
- Advanced Feature Set: PostgreSQL is known for its rich feature set, including support for complex data types, advanced indexing, and strong compliance with SQL standards.
- Robust Concurrency: Utilizes MVCC (Multi-Version Concurrency Control) to manage high concurrency with minimal locking.
- Extensible: Highly extensible with support for custom datatypes, indexes, and procedural languages.
Pgloader Tool:
Pgloader is an open-source migration program that simplifies the process of moving data from MySQL to PostgreSQL. It works with a broad variety of data formats and structures, automating most of the data conversion process to ensure a seamless and fast transition. With its broad features, pgloader can handle enormous datasets, making it a must-have solution for enterprises wishing to move their databases. However, although pgloader handles many conversions easily, other complicated schemas may require manual changes for best performance.
In this blog we are going to discuss the pgloader tool. It’s an open source Database migration tool.
I created a sample database and orders table in mysql and inserted some records for migration testing in mysql.
[root@mysql-01 centos]# mysql -u root -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 21
Server version: 8.0.40 MySQL Community Server - GPL
Copyright (c) 2000, 2024, Oracle and/or its affiliates.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> show databases;
+-------------------------------+
| Database |
+-------------------------------+
| Audit_Storage |
| dummy |
| employees |
| information_schema |
| mysql |
| mysql_innodb_cluster_metadata |
| performance_schema |
| public |
+-------------------------------+
15 rows in set (0.33 sec)
mysql> create database sample;
Query OK, 1 row affected (0.08 sec)
mysql> \u sample
Database changed
mysql> CREATE TABLE orders (
-> order_id INT AUTO_INCREMENT PRIMARY KEY,
-> customer_id INT,
-> order_date DATE,
-> amount DECIMAL(10, 2),
-> status ENUM('Pending', 'Shipped', 'Delivered', 'Cancelled')
-> );
Query OK, 0 rows affected (0.22 sec)
mysql> INSERT INTO orders (customer_id, order_date, amount, status) VALUES
-> (1, '2023-06-12', 250.00, 'Shipped'),
-> (2, '2023-06-14', 300.50, 'Pending'),
-> (3, '2023-06-16', 150.75, 'Delivered'),
-> (4, '2023-06-18', 400.00, 'Cancelled'),
-> (5, '2023-06-20', 500.25, 'Pending');
Query OK, 5 rows affected (0.31 sec)
Records: 5 Duplicates: 0 Warnings: 0
Installing pgloader from git repository in centos 08 https://github.com/dimitri/pgloader
[root@mysql-01 centos]# su - postgres
Last login: Tue Dec 31 15:45:27 EST 2024 on pts/0
[postgres@mysql-01 ~]$ wget https://github.com/dimitri/pgloader/archive/refs/heads/master.zip
--2025-01-04 08:30:19-- https://github.com/dimitri/pgloader/archive/refs/heads/master.zip
Resolving github.com (github.com)... 20.207.73.82
Connecting to github.com (github.com)|20.207.73.82|:443... connected.
HTTP request sent, awaiting response... 302 Found
Location: https://codeload.github.com/dimitri/pgloader/zip/refs/heads/master [following]
--2025-01-04 08:30:20-- https://codeload.github.com/dimitri/pgloader/zip/refs/heads/master
Resolving codeload.github.com (codeload.github.com)... 20.207.73.88
Connecting to codeload.github.com (codeload.github.com)|20.207.73.88|:443... connected.
HTTP request sent, awaiting response... 200 OK
Length: unspecified [application/zip]
Saving to: 'master.zip'
master.zip [ <=> ] 3.68M 2.01MB/s in 1.8s
2025-01-04 08:30:22 (2.01 MB/s) - 'master.zip' saved [3862018]
[postgres@mysql-01 ~]$ unzip master.zip
Archive: master.zip
70f355767018cc1d2770948563100b328fcc3f26
creating: pgloader-master/
[postgres@mysql-01 ~]$ cd pgloader-master
[postgres@mysql-01 pgloader-master]$ sudo sh bootstrap-centos.sh
CentOS Stream 8 - AppStream 12 kB/s | 4.4 kB 00:00
CentOS Stream 8 - BaseOS 10 kB/s | 3.9 kB 00:00
CentOS Stream 8 - Extras 13 kB/s | 2.9 kB 00:00
Safe Remi's RPM repository for Enterprise Linux 8 - x86_64 1.1 kB/s | 3.0 kB 00:02
Safe Remi's RPM repository for Enterprise Linux 8 - x86_64 545 kB/s | 2.1 MB 00:04
Package yum-utils-4.0.21-25.el8.noarch is already installed.
Package zlib-devel-1.2.11-25.el8.x86_64 is already installed.
Dependencies resolved.
===========================================================================================================================================
Package Architecture Version Repository Size
===========================================================================================================================================
Installing:
rpmdevtools noarch 8.10-8.el8 appstream 87 k
sqlite-devel x86_64 3.26.0-19.el8 baseos 165 k
Installing dependencies:
sqlite x86_64 3.26.0-19.el8 baseos 669 k
Installing Groups:
Development Tools
Transaction Summary
===========================================================================================================================================
Install 3 Packages
Total download size: 921 k
Installed size: 2.0 M
Downloading Packages:
(1/3): rpmdevtools-8.10-8.el8.noarch.rpm 125 kB/s | 87 kB 00:00
(2/3): sqlite-3.26.0-19.el8.x86_64.rpm 580 kB/s | 669 kB 00:01
(3/3): sqlite-devel-3.26.0-19.el8.x86_64.rpm 89 kB/s | 165 kB 00:01
-------------------------------------------------------------------------------------------------------------------------------------------
Total 489 kB/s | 921 kB 00:01
Running transaction check
Transaction check succeeded.
Running transaction test
Transaction test succeeded.
Running transaction
Preparing : 1/1
Installing : sqlite-3.26.0-19.el8.x86_64 1/3
Installing : sqlite-devel-3.26.0-19.el8.x86_64 2/3
Installing : rpmdevtools-8.10-8.el8.noarch 3/3
Running scriptlet: rpmdevtools-8.10-8.el8.noarch 3/3
Verifying : rpmdevtools-8.10-8.el8.noarch 1/3
Verifying : sqlite-3.26.0-19.el8.x86_64 2/3
Verifying : sqlite-devel-3.26.0-19.el8.x86_64 3/3
Installed:
rpmdevtools-8.10-8.el8.noarch sqlite-3.26.0-19.el8.x86_64 sqlite-devel-3.26.0-19.el8.x86_64
Complete!
Last metadata expiration check: 0:00:40 ago on Sat 04 Jan 2025 08:39:20 AM EST.
Package epel-release-8-19.el8.noarch is already installed.
Dependencies resolved.
===========================================================================================================================================
Package Architecture Version Repository Size
===========================================================================================================================================
Upgrading:
epel-release noarch 8-21.el8 epel 24 k
replacing epel-next-release.noarch 8-19.el8
Transaction Summary
===========================================================================================================================================
Upgrade 1 Package
Total download size: 24 k
Downloading Packages:
epel-release-8-21.el8.noarch.rpm 82 kB/s | 24 kB 00:00
-------------------------------------------------------------------------------------------------------------------------------------------
Total 34 kB/s | 24 kB 00:00
Running transaction check
Transaction check succeeded.
Running transaction test
Transaction test succeeded.
Running transaction
Preparing : 1/1
Running scriptlet: epel-release-8-21.el8.noarch 1/1
Upgrading : epel-release-8-21.el8.noarch 1/3
Running scriptlet: epel-release-8-21.el8.noarch 1/3
Cleanup : epel-release-8-19.el8.noarch 2/3
Obsoleting : epel-next-release-8-19.el8.noarch 3/3
Running scriptlet: epel-next-release-8-19.el8.noarch 3/3
Verifying : epel-release-8-21.el8.noarch 1/3
Verifying : epel-release-8-19.el8.noarch 2/3
Verifying : epel-next-release-8-19.el8.noarch 3/3
Upgraded:
epel-release-8-21.el8.noarch
Complete!
Last metadata expiration check: 0:00:56 ago on Sat 04 Jan 2025 08:39:20 AM EST.
No match for argument: sbcl.x86_64
Error: Unable to find a match: sbcl.x86_64
--2025-01-04 08:40:21-- http://downloads.sourceforge.net/project/sbcl/sbcl/2.2.5/sbcl-2.2.5-source.tar.bz2
Resolving downloads.sourceforge.net (downloads.sourceforge.net)... 104.18.13.149, 104.18.12.149, 2606:4700:9ad1:498:f9e:0:8280:21e3
Connecting to downloads.sourceforge.net (downloads.sourceforge.net)|104.18.13.149|:80... connected.
HTTP request sent, awaiting response... 302 Found
Location: http://cyfuture.dl.sourceforge.net/project/sbcl/sbcl/2.2.5/sbcl-2.2.5-source.tar.bz2?viasf=1 [following]
--2025-01-04 08:40:22-- http://cyfuture.dl.sourceforge.net/project/sbcl/sbcl/2.2.5/sbcl-2.2.5-source.tar.bz2?viasf=1
Resolving cyfuture.dl.sourceforge.net (cyfuture.dl.sourceforge.net)... 49.50.119.27
Connecting to cyfuture.dl.sourceforge.net (cyfuture.dl.sourceforge.net)|49.50.119.27|:80... connected.
HTTP request sent, awaiting response... 200 OK
Length: 7029912 (6.7M) [application/octet-stream]
Saving to: 'sbcl-2.2.5-source.tar.bz2'
sbcl-2.2.5-source.tar.bz2 100%[===============================================================>] 6.70M 1.97MB/s in 3.4s
2025-01-04 08:40:28 (1.97 MB/s) - 'sbcl-2.2.5-source.tar.bz2' saved [7029912/7029912]
src/runtime/sbcl not found, aborting installation.
See ./INSTALL, the "SOURCE DISTRIBUTION" section
Last metadata expiration check: 0:01:16 ago on Sat 04 Jan 2025 08:39:20 AM EST.
Dependencies resolved.
===========================================================================================================================================
Package Architecture Version Repository Size
===========================================================================================================================================
Installing:
freetds-devel x86_64 1.4.23-1.el8 epel 57 k
Installing dependencies:
freetds x86_64 1.4.23-1.el8 epel 310 k
freetds-libs x86_64 1.4.23-1.el8 epel 392 k
Transaction Summary
===========================================================================================================================================
Install 3 Packages
Total download size: 759 k
Installed size: 2.5 M
Downloading Packages:
(1/3): freetds-devel-1.4.23-1.el8.x86_64.rpm 139 kB/s | 57 kB 00:00
(2/3): freetds-1.4.23-1.el8.x86_64.rpm 494 kB/s | 310 kB 00:00
(3/3): freetds-libs-1.4.23-1.el8.x86_64.rpm 485 kB/s | 392 kB 00:00
-------------------------------------------------------------------------------------------------------------------------------------------
Total 723 kB/s | 759 kB 00:01
Running transaction check
Transaction check succeeded.
Running transaction test
Transaction test succeeded.
Running transaction
Preparing : 1/1
Installing : freetds-libs-1.4.23-1.el8.x86_64 1/3
Running scriptlet: freetds-libs-1.4.23-1.el8.x86_64 1/3
Installing : freetds-1.4.23-1.el8.x86_64 2/3
Installing : freetds-devel-1.4.23-1.el8.x86_64 3/3
Running scriptlet: freetds-devel-1.4.23-1.el8.x86_64 3/3
Verifying : freetds-1.4.23-1.el8.x86_64 1/3
Verifying : freetds-devel-1.4.23-1.el8.x86_64 2/3
Verifying : freetds-libs-1.4.23-1.el8.x86_64 3/3
Installed:
freetds-1.4.23-1.el8.x86_64 freetds-devel-1.4.23-1.el8.x86_64 freetds-libs-1.4.23-1.el8.x86_64
Complete!
[postgres@mysql-01 pgloader-master]$ wget http://prdownloads.sourceforge.net/sbcl/sbcl-2.2.9-x86-64-linux-binary.tar.bz2
--2025-01-04 11:10:55--
--2025-01-04 11:21:07-- (try: 3) http://master.dl.sourceforge.net/project/sbcl/sbcl/2.2.9/sbcl-2.2.9-x86-64-linux-binary.tar.bz2?viasf=1
Connecting to master.dl.sourceforge.net (master.dl.sourceforge.net)|216.105.38.12|:80... connected.
HTTP request sent, awaiting response... 200 OK
Length: 11641067 (11M) [application/octet-stream]
Saving to: 'sbcl-2.2.9-x86-64-linux-binary.tar.bz2.1'
sbcl-2.2.9-x86-64-linux-binary.tar 100%[===============================================================>] 11.10M 482KB/s in 88s
2025-01-04 11:22:35 (130 KB/s) - 'sbcl-2.2.9-x86-64-linux-binary.tar.bz2.1' saved [11641067/11641067]
[postgres@mysql-01 pgloader-master]$ bzip2 -tvv sbcl-2.2.9-x86-64-linux-binary.tar.bz2.1
sbcl-2.2.9-x86-64-linux-binary.tar.bz2.1:
[1: huff+mtf rt+rld]
[2: huff+mtf rt+rld]
—----------------
—-----------------
[postgres@mysql-01 pgloader-master]$ cd sbcl-2.2.9-x86-64-linux
[postgres@mysql-01 sbcl-2.2.9-x86-64-linux]$ sudo sh install.sh
/usr/local
—---------------------------------
—---------------------------------
make: Entering directory
SBCL has been installed:
binary /usr/local/bin/sbcl
core and contribs in /usr/local/lib/sbcl/
Documentation:
man /usr/local/share/man/man1/sbcl.1
[postgres@mysql-01 pgloader-master]$ sudo yum -y install yum-utils rpmdevtools @"Development Tools"
akopytov_sysbench 437 B/s | 1.0 kB 00:02
akopytov_sysbench-source 437 B/s | 1.0 kB 00:02
Package yum-utils-4.0.21-25.el8.noarch is already installed.
Package rpmdevtools-8.10-8.el8.noarch is already installed.
Dependencies resolved.
===========================================================================================================================================
Package Architecture Version Repository Size
===========================================================================================================================================
Installing Groups:
Development Tools
Transaction Summary
===========================================================================================================================================
Complete!
[postgres@mysql-01 SOURCES]$ wget https://github.com/dimitri/pgloader/archive/v3.6.10.tar.gz
--2025-01-04 11:35:42-- https://github.com/dimitri/pgloader/archive/v3.6.10.tar.gz
Resolving github.com (github.com)... 20.207.73.82
Connecting to github.com (github.com)|20.207.73.82|:443... connected.
HTTP request sent, awaiting response... 302 Found
Location: https://codeload.github.com/dimitri/pgloader/tar.gz/refs/tags/v3.6.10 [following]
--2025-01-04 11:35:43-- https://codeload.github.com/dimitri/pgloader/tar.gz/refs/tags/v3.6.10
Resolving codeload.github.com (codeload.github.com)... 20.207.73.88
Connecting to codeload.github.com (codeload.github.com)|20.207.73.88|:443... connected.
HTTP request sent, awaiting response... 200 OK
Length: unspecified [application/x-gzip]
Saving to: 'v3.6.10.tar.gz'
v3.6.10.tar.gz [ <=> ] 3.55M 2.22MB/s in 1.6s
2025-01-04 11:35:45 (2.22 MB/s) - 'v3.6.10.tar.gz' saved [3722933]
[postgres@mysql-01 pgloader-master]$ rpmbuild -ba pgloader.spec
Executing(%prep): /bin/sh -e /var/tmp/rpm-tmp.sDpiO2
+ umask 022
+ cd /var/lib/pgsql/rpmbuild/BUILD
+ cd /var/lib/pgsql/rpmbuild/BUILD
+ rm -rf pgloader-3.6.10
+ /usr/bin/gzip -dc /var/lib/pgsql/rpmbuild/SOURCES/v3.6.10.tar.gz
+ /usr/bin/tar -xof -
+ STATUS=0
+ '[' 0 -ne 0 ']'
+ cd pgloader-3.6.10
+ /usr/bin/chmod -Rf a+rX,u+w,g-w,o-w .
+ exit 0
Executing(%build): /bin/sh -e /var/tmp/rpm-tmp.dQV5Tk
+ umask 022
+ cd /var/lib/pgsql/rpmbuild/BUILD
+ cd pgloader-3.6.10
+ make pgloader
mkdir -p build
curl -o build/quicklisp.lisp http://beta.quicklisp.org/quicklisp.lisp
% Total % Received % Xferd Average Speed Time Time Time Current
Dload Upload Total Spent Left Speed
100 57144 100 57144 0 0 28373 0 0:00:02 0:00:02 --:--:-- 28387
……………………………………………………………………………………………………………..
………………………………………………………………………………………………………………..
Wrote: /var/lib/pgsql/rpmbuild/RPMS/x86_64/pgloader-3.6.10-22.el8.x86_64.rpm
Executing(%clean): /bin/sh -e /var/tmp/rpm-tmp.h3bwev
+ umask 022
+ cd /var/lib/pgsql/rpmbuild/BUILD
+ cd pgloader-3.6.10
+ /usr/bin/rm -rf /var/lib/pgsql/rpmbuild/BUILDROOT/pgloader-3.6.10-22.el8.x86_64
+ exit 0
[postgres@mysql-01 pgloader-master]$ ls -lh /var/lib/pgsql/rpmbuild/RPMS/x86_64/
total 18M
-rw-r--r-- 1 postgres postgres 18M Jan 4 11:47 pgloader-3.6.10-22.el8.x86_64.rpm
[postgres@mysql-01 pgloader-master]$ sudo rpm -ivh --nodeps /var/lib/pgsql/rpmbuild/RPMS/x86_64/pgloader-3.6.10-22.el8.x86_64.rpm
Verifying... ################################# [100%]
Preparing... ################################# [100%]
Updating / installing...
1:pgloader-3.6.10-22.el8 ################################# [100%]
[postgres@mysql-01 pgloader-master]$ vi sbcl-dummy.spec
[postgres@mysql-01 pgloader-master]$ rpmbuild -bb sbcl-dummy.spec
Processing files: sbcl-1.0-1.x86_64
Checking for unpackaged file(s): /usr/lib/rpm/check-files /var/lib/pgsql/rpmbuild/BUILDROOT/sbcl-1.0-1.x86_64
Wrote: /var/lib/pgsql/rpmbuild/RPMS/x86_64/sbcl-1.0-1.x86_64.rpm
Executing(%clean): /bin/sh -e /var/tmp/rpm-tmp.GZbEFg
+ umask 022
+ cd /var/lib/pgsql/rpmbuild/BUILD
+ /usr/bin/rm -rf /var/lib/pgsql/rpmbuild/BUILDROOT/sbcl-1.0-1.x86_64
+ exit 0
[postgres@mysql-01 pgloader-master]$ sudo rpm -ivh /var/lib/pgsql/rpmbuild/RPMS/x86_64/sbcl-1.0-1.x86_64.rpm
Verifying... ################################# [100%]
Preparing... ################################# [100%]
Updating / installing...
1:sbcl-1.0-1 ################################# [100%]
[postgres@mysql-01 pgloader-master]$ pgloader --version
pgloader version "3.6.7~devel"
compiled with SBCL 2.2.9
To restore databases, ensure that users have the necessary grants on MySQL and PostgreSQL. Create a database in PostgreSQL for the restoration process.
postgres=# create database sample2; CREATE DATABASE
Restoring database using pgloader with mysql and postgres credentials.
Pgloader
mysql://<username>:<password>@<hostname>/<DB_name>
postgres://<username>:<password>@<hostname>/<DB_name>
[postgres@mysql-01 pgloader-master]$ pgloader mysql://raj:Mysql%123@localhost/sample pgsql://admin:psql123@localhost/sample2
2025-01-04T15:24:52.226998-05:00 LOG pgloader version "3.6.7~devel"
2025-01-04T15:24:53.459988-05:00 LOG Migrating from #<MYSQL-CONNECTION mysql://raj@localhost:3306/sample {1005929A33}>
2025-01-04T15:24:53.460988-05:00 LOG Migrating into #<PGSQL-CONNECTION pgsql://admin@localhost:5432/sample2 {1005AEF0B3}>
2025-01-04T15:24:57.356956-05:00 LOG report summary reset
table name errors rows bytes total time
----------------------- --------- --------- --------- --------------
fetch meta data 0 2 1.513s
Create Schemas 0 0 0.076s
Create SQL Types 0 1 0.118s
Create tables 0 2 0.113s
Set Table OIDs 0 1 0.025s
----------------------- --------- --------- --------- --------------
sample.orders 0 5 0.2 kB 0.236s
----------------------- --------- --------- --------- --------------
COPY Threads Completion 0 4 0.251s
Index Build Completion 0 1 0.184s
Create Indexes 0 1 0.039s
Reset Sequences 0 1 0.316s
Primary Keys 0 1 0.019s
Create Foreign Keys 0 0 0.000s
Create Triggers 0 0 0.002s
Set Search Path 0 1 0.006s
Install Comments 0 0 0.000s
----------------------- --------- --------- --------- --------------
Total import time ✓ 5 0.2 kB 0.817s
Verification steps for Database Migration:
postgres=# \c sample2
You are now connected to database "sample2" as user "postgres".
sample2=# \d
List of relations
Schema | Name | Type | Owner
--------+---------------------+----------+-------
sample | orders | table | admin
sample | orders_order_id_seq | sequence | admin
(2 rows)
sample2=# \dt
List of relations
Schema | Name | Type | Owner
--------+--------+-------+-------
sample | orders | table | admin
(1 row)
Credit: For pgloader tool : https://github.com/dimitri/pgloader
