Introduction
In today’s dynamic business environment, data migration across database 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.
| 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 | [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
| 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 | [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! |
| 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 | [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.
| 1 2 | 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>
| 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 | [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:
| 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 | 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
