{"id":2775,"date":"2025-01-13T16:24:06","date_gmt":"2025-01-13T16:24:06","guid":{"rendered":"https:\/\/bynatree.com\/?p=2775"},"modified":"2025-01-13T16:24:06","modified_gmt":"2025-01-13T16:24:06","slug":"effortless-database-migration-transition-from-mysql-8-0-40-to-postgresql-16-1","status":"publish","type":"post","link":"https:\/\/divaind.com\/ie1\/2025\/01\/13\/effortless-database-migration-transition-from-mysql-8-0-40-to-postgresql-16-1\/","title":{"rendered":"Effortless Database Migration: Transition from MySQL 8.0.40 to PostgreSQL 16.1 Using pgloader on CentOS 8"},"content":{"rendered":"

Introduction<\/b><\/h2>\n

 <\/p>\n

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.<\/span><\/p><\/blockquote>\n

 <\/p>\n

Basic Differences<\/b><\/h2>\n

MySQL<\/span><\/h3>\n
    \n
  • Performance-Oriented<\/b>: MySQL is optimized for high performance, especially in read-heavy applications, making it a popular choice for web applications.<\/span><\/li>\n
  • Cost-Effective<\/b>: Often associated with lower licensing fees and operational costs.<\/span><\/li>\n
  • Extensive Ecosystem<\/b>: Supported by a large community and numerous plugins for enhanced functionality.<\/span><\/li>\n<\/ul>\n

    PostgreSQL<\/span><\/h3>\n
      \n
    • Advanced Feature Set<\/b>: PostgreSQL is known for its rich feature set, including support for complex data types, advanced indexing, and strong compliance with SQL standards.<\/span><\/li>\n
    • Robust Concurrency<\/b>: Utilizes MVCC (Multi-Version Concurrency Control) to manage high concurrency with minimal locking.<\/span><\/li>\n
    • Extensible<\/b>: Highly extensible with support for custom datatypes, indexes, and procedural languages.<\/span><\/li>\n<\/ul>\n

      Pgloader Tool:<\/span><\/h2>\n

      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.<\/span><\/p>\n

       <\/p>\n

      In this blog we are going to discuss the pgloader tool. It’s an open source Database migration tool.<\/b><\/p><\/blockquote>\n

      I created a sample database and orders table in mysql and inserted some records for migration testing in mysql.
      \n<\/span><\/p>\n

      [root@mysql-01 centos]# mysql -u root -p\nEnter password:\nWelcome to the MySQL monitor.  Commands end with ; or \\g.\nYour MySQL connection id is 21\nServer version: 8.0.40 MySQL Community Server - GPL\nCopyright (c) 2000, 2024, Oracle and\/or its affiliates.\nOracle is a registered trademark of Oracle Corporation and\/or its\naffiliates. Other names may be trademarks of their respective\nowners.\nType 'help;' or '\\h' for help. Type '\\c' to clear the current input statement.\nmysql> show databases;\n+-------------------------------+\n| Database                      |\n+-------------------------------+\n| Audit_Storage                 |\n| dummy                         |\n| employees                     |\n| information_schema            |\n| mysql                         |\n| mysql_innodb_cluster_metadata |\n| performance_schema            |\n| public                        |\n+-------------------------------+\n15 rows in set (0.33 sec)\n\nmysql> create database sample;\nQuery OK, 1 row affected (0.08 sec)\nmysql> \\u sample\nDatabase changed\nmysql> CREATE TABLE orders (\n    ->     order_id INT AUTO_INCREMENT PRIMARY KEY,\n    ->     customer_id INT,\n    ->     order_date DATE,\n    ->     amount DECIMAL(10, 2),\n    ->     status ENUM('Pending', 'Shipped', 'Delivered', 'Cancelled')\n    -> );\nQuery OK, 0 rows affected (0.22 sec)\nmysql> INSERT INTO orders (customer_id, order_date, amount, status) VALUES\n    -> (1, '2023-06-12', 250.00, 'Shipped'),\n    -> (2, '2023-06-14', 300.50, 'Pending'),\n    -> (3, '2023-06-16', 150.75, 'Delivered'),\n    -> (4, '2023-06-18', 400.00, 'Cancelled'),\n    -> (5, '2023-06-20', 500.25, 'Pending');\nQuery OK, 5 rows affected (0.31 sec)\nRecords: 5  Duplicates: 0  Warnings: 0\n\n<\/pre>\n
      Installing pgloader from git repository in centos 08 <\/span>https:\/\/github.com\/dimitri\/pgloader<\/span><\/p>\n
      [root@mysql-01 centos]# su - postgres\nLast login: Tue Dec 31 15:45:27 EST 2024 on pts\/0\n[postgres@mysql-01 ~]$ wget https:\/\/github.com\/dimitri\/pgloader\/archive\/refs\/heads\/master.zip\n--2025-01-04 08:30:19--  https:\/\/github.com\/dimitri\/pgloader\/archive\/refs\/heads\/master.zip\nResolving github.com (github.com)... 20.207.73.82\nConnecting to github.com (github.com)|20.207.73.82|:443... connected.\nHTTP request sent, awaiting response... 302 Found\nLocation: https:\/\/codeload.github.com\/dimitri\/pgloader\/zip\/refs\/heads\/master [following]\n--2025-01-04 08:30:20--  https:\/\/codeload.github.com\/dimitri\/pgloader\/zip\/refs\/heads\/master\nResolving codeload.github.com (codeload.github.com)... 20.207.73.88\nConnecting to codeload.github.com (codeload.github.com)|20.207.73.88|:443... connected.\nHTTP request sent, awaiting response... 200 OK\nLength: unspecified [application\/zip]\nSaving to: 'master.zip'\n\nmaster.zip                             [       <=>                                                      ]   3.68M  2.01MB\/s    in 1.8s\n\n2025-01-04 08:30:22 (2.01 MB\/s) - 'master.zip' saved [3862018]\n\n[postgres@mysql-01 ~]$ unzip master.zip\nArchive:  master.zip\n70f355767018cc1d2770948563100b328fcc3f26\n   creating: pgloader-master\/\n  \n[postgres@mysql-01 ~]$ cd pgloader-master\n\n[postgres@mysql-01 pgloader-master]$ sudo sh bootstrap-centos.sh\nCentOS Stream 8 - AppStream                                                                                 12 kB\/s | 4.4 kB     00:00\nCentOS Stream 8 - BaseOS                                                                                    10 kB\/s | 3.9 kB     00:00\nCentOS Stream 8 - Extras                                                                                    13 kB\/s | 2.9 kB     00:00\nSafe Remi's RPM repository for Enterprise Linux 8 - x86_64                                                 1.1 kB\/s | 3.0 kB     00:02\nSafe Remi's RPM repository for Enterprise Linux 8 - x86_64                                                 545 kB\/s | 2.1 MB     00:04\nPackage yum-utils-4.0.21-25.el8.noarch is already installed.\nPackage zlib-devel-1.2.11-25.el8.x86_64 is already installed.\nDependencies resolved.\n===========================================================================================================================================\n Package                            Architecture                 Version                             Repository                       Size\n===========================================================================================================================================\nInstalling:\n rpmdevtools                        noarch                       8.10-8.el8                          appstream                        87 k\n sqlite-devel                       x86_64                       3.26.0-19.el8                       baseos                          165 k\nInstalling dependencies:\n sqlite                             x86_64                       3.26.0-19.el8                       baseos                          669 k\nInstalling Groups:\n Development Tools\n\nTransaction Summary\n===========================================================================================================================================\nInstall  3 Packages\n\nTotal download size: 921 k\nInstalled size: 2.0 M\nDownloading Packages:\n(1\/3): rpmdevtools-8.10-8.el8.noarch.rpm                                                                   125 kB\/s |  87 kB     00:00\n(2\/3): sqlite-3.26.0-19.el8.x86_64.rpm                                                                     580 kB\/s | 669 kB     00:01\n(3\/3): sqlite-devel-3.26.0-19.el8.x86_64.rpm                                                                89 kB\/s | 165 kB     00:01\n-------------------------------------------------------------------------------------------------------------------------------------------\nTotal                                                                                                      489 kB\/s | 921 kB     00:01\nRunning transaction check\nTransaction check succeeded.\nRunning transaction test\nTransaction test succeeded.\nRunning transaction\n  Preparing        :                                                                                                                   1\/1\n  Installing       : sqlite-3.26.0-19.el8.x86_64                                                                                       1\/3\n  Installing       : sqlite-devel-3.26.0-19.el8.x86_64                                                                                 2\/3\n  Installing       : rpmdevtools-8.10-8.el8.noarch                                                                                     3\/3\n  Running scriptlet: rpmdevtools-8.10-8.el8.noarch                                                                                     3\/3\n  Verifying        : rpmdevtools-8.10-8.el8.noarch                                                                                     1\/3\n  Verifying        : sqlite-3.26.0-19.el8.x86_64                                                                                       2\/3\n  Verifying        : sqlite-devel-3.26.0-19.el8.x86_64                                                                                 3\/3\n\nInstalled:\n  rpmdevtools-8.10-8.el8.noarch                sqlite-3.26.0-19.el8.x86_64                sqlite-devel-3.26.0-19.el8.x86_64\n\nComplete!\nLast metadata expiration check: 0:00:40 ago on Sat 04 Jan 2025 08:39:20 AM EST.\nPackage epel-release-8-19.el8.noarch is already installed.\nDependencies resolved.\n===========================================================================================================================================\n Package                              Architecture                   Version                            Repository                    Size\n===========================================================================================================================================\nUpgrading:\n epel-release                         noarch                         8-21.el8                           epel                          24 k\n     replacing  epel-next-release.noarch 8-19.el8\n\nTransaction Summary\n===========================================================================================================================================\nUpgrade  1 Package\n\nTotal download size: 24 k\nDownloading Packages:\nepel-release-8-21.el8.noarch.rpm                                                                            82 kB\/s |  24 kB     00:00\n-------------------------------------------------------------------------------------------------------------------------------------------\nTotal                                                                                                       34 kB\/s |  24 kB     00:00\nRunning transaction check\nTransaction check succeeded.\nRunning transaction test\nTransaction test succeeded.\nRunning transaction\n  Preparing        :                                                                                                                   1\/1\n  Running scriptlet: epel-release-8-21.el8.noarch                                                                                      1\/1\n  Upgrading        : epel-release-8-21.el8.noarch                                                                                      1\/3\n  Running scriptlet: epel-release-8-21.el8.noarch                                                                                      1\/3\n  Cleanup          : epel-release-8-19.el8.noarch                                                                                      2\/3\n  Obsoleting       : epel-next-release-8-19.el8.noarch                                                                                 3\/3\n  Running scriptlet: epel-next-release-8-19.el8.noarch                                                                                 3\/3\n  Verifying        : epel-release-8-21.el8.noarch                                                                                      1\/3\n  Verifying        : epel-release-8-19.el8.noarch                                                                                      2\/3\n  Verifying        : epel-next-release-8-19.el8.noarch                                                                                 3\/3\n\nUpgraded:\n  epel-release-8-21.el8.noarch\n\nComplete!\nLast metadata expiration check: 0:00:56 ago on Sat 04 Jan 2025 08:39:20 AM EST.\nNo match for argument: sbcl.x86_64\nError: Unable to find a match: sbcl.x86_64\n--2025-01-04 08:40:21--  http:\/\/downloads.sourceforge.net\/project\/sbcl\/sbcl\/2.2.5\/sbcl-2.2.5-source.tar.bz2\nResolving downloads.sourceforge.net (downloads.sourceforge.net)... 104.18.13.149, 104.18.12.149, 2606:4700:9ad1:498:f9e:0:8280:21e3\nConnecting to downloads.sourceforge.net (downloads.sourceforge.net)|104.18.13.149|:80... connected.\nHTTP request sent, awaiting response... 302 Found\nLocation: http:\/\/cyfuture.dl.sourceforge.net\/project\/sbcl\/sbcl\/2.2.5\/sbcl-2.2.5-source.tar.bz2?viasf=1 [following]\n--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\nResolving cyfuture.dl.sourceforge.net (cyfuture.dl.sourceforge.net)... 49.50.119.27\nConnecting to cyfuture.dl.sourceforge.net (cyfuture.dl.sourceforge.net)|49.50.119.27|:80... connected.\nHTTP request sent, awaiting response... 200 OK\nLength: 7029912 (6.7M) [application\/octet-stream]\nSaving to: 'sbcl-2.2.5-source.tar.bz2'\n\nsbcl-2.2.5-source.tar.bz2          100%[===============================================================>]   6.70M  1.97MB\/s    in 3.4s\n\n2025-01-04 08:40:28 (1.97 MB\/s) - 'sbcl-2.2.5-source.tar.bz2' saved [7029912\/7029912]\n\nsrc\/runtime\/sbcl not found, aborting installation.\nSee .\/INSTALL, the \"SOURCE DISTRIBUTION\" section\nLast metadata expiration check: 0:01:16 ago on Sat 04 Jan 2025 08:39:20 AM EST.\nDependencies resolved.\n===========================================================================================================================================\n Package                              Architecture                  Version                              Repository                   Size\n===========================================================================================================================================\nInstalling:\n freetds-devel                        x86_64                        1.4.23-1.el8                         epel                         57 k\nInstalling dependencies:\n freetds                              x86_64                        1.4.23-1.el8                         epel                        310 k\n freetds-libs                         x86_64                        1.4.23-1.el8                         epel                        392 k\n\nTransaction Summary\n===========================================================================================================================================\nInstall  3 Packages\n\nTotal download size: 759 k\nInstalled size: 2.5 M\nDownloading Packages:\n(1\/3): freetds-devel-1.4.23-1.el8.x86_64.rpm                                                               139 kB\/s |  57 kB     00:00\n(2\/3): freetds-1.4.23-1.el8.x86_64.rpm                                                                     494 kB\/s | 310 kB     00:00\n(3\/3): freetds-libs-1.4.23-1.el8.x86_64.rpm                                                                485 kB\/s | 392 kB     00:00\n-------------------------------------------------------------------------------------------------------------------------------------------\nTotal                                                                                                      723 kB\/s | 759 kB     00:01\nRunning transaction check\nTransaction check succeeded.\nRunning transaction test\nTransaction test succeeded.\nRunning transaction\n  Preparing        :                                                                                                                   1\/1\n  Installing       : freetds-libs-1.4.23-1.el8.x86_64                                                                                  1\/3\n  Running scriptlet: freetds-libs-1.4.23-1.el8.x86_64                                                                                  1\/3\n  Installing       : freetds-1.4.23-1.el8.x86_64                                                                                       2\/3\n  Installing       : freetds-devel-1.4.23-1.el8.x86_64                                                                                 3\/3\n  Running scriptlet: freetds-devel-1.4.23-1.el8.x86_64                                                                                 3\/3\n  Verifying        : freetds-1.4.23-1.el8.x86_64                                                                                       1\/3\n  Verifying        : freetds-devel-1.4.23-1.el8.x86_64                                                                                 2\/3\n  Verifying        : freetds-libs-1.4.23-1.el8.x86_64                                                                                  3\/3\n\nInstalled:\n  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\n\nComplete!\n\n<\/pre>\n
      [postgres@mysql-01 pgloader-master]$ wget http:\/\/prdownloads.sourceforge.net\/sbcl\/sbcl-2.2.9-x86-64-linux-binary.tar.bz2\n--2025-01-04 11:10:55--  \n--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\nConnecting to master.dl.sourceforge.net (master.dl.sourceforge.net)|216.105.38.12|:80... connected.\nHTTP request sent, awaiting response... 200 OK\nLength: 11641067 (11M) [application\/octet-stream]\nSaving to: 'sbcl-2.2.9-x86-64-linux-binary.tar.bz2.1'\n\nsbcl-2.2.9-x86-64-linux-binary.tar 100%[===============================================================>]  11.10M   482KB\/s    in 88s\n\n2025-01-04 11:22:35 (130 KB\/s) - 'sbcl-2.2.9-x86-64-linux-binary.tar.bz2.1' saved [11641067\/11641067]\n\n[postgres@mysql-01 pgloader-master]$ bzip2 -tvv sbcl-2.2.9-x86-64-linux-binary.tar.bz2.1\n  sbcl-2.2.9-x86-64-linux-binary.tar.bz2.1:\n    [1: huff+mtf rt+rld]\n    [2: huff+mtf rt+rld]\n \u2014----------------\n\u2014----------------- \n[postgres@mysql-01 pgloader-master]$ cd sbcl-2.2.9-x86-64-linux\n[postgres@mysql-01 sbcl-2.2.9-x86-64-linux]$ sudo sh install.sh\n\/usr\/local\n\u2014---------------------------------\n\u2014---------------------------------\nmake: Entering directory \nSBCL has been installed:\n binary \/usr\/local\/bin\/sbcl\n core and contribs in \/usr\/local\/lib\/sbcl\/\n\nDocumentation:\n man \/usr\/local\/share\/man\/man1\/sbcl.1\n\n\n\n\n[postgres@mysql-01 pgloader-master]$ sudo yum -y install yum-utils rpmdevtools @\"Development Tools\"\nakopytov_sysbench                                                                                          437  B\/s | 1.0 kB     00:02\nakopytov_sysbench-source                                                                                   437  B\/s | 1.0 kB     00:02\nPackage yum-utils-4.0.21-25.el8.noarch is already installed.\nPackage rpmdevtools-8.10-8.el8.noarch is already installed.\nDependencies resolved.\n===========================================================================================================================================\n Package                          Architecture                    Version                           Repository                        Size\n===========================================================================================================================================\nInstalling Groups:\n Development Tools\n\nTransaction Summary\n===========================================================================================================================================\n\nComplete!\n\n[postgres@mysql-01 SOURCES]$ wget https:\/\/github.com\/dimitri\/pgloader\/archive\/v3.6.10.tar.gz\n--2025-01-04 11:35:42--  https:\/\/github.com\/dimitri\/pgloader\/archive\/v3.6.10.tar.gz\nResolving github.com (github.com)... 20.207.73.82\nConnecting to github.com (github.com)|20.207.73.82|:443... connected.\nHTTP request sent, awaiting response... 302 Found\nLocation: https:\/\/codeload.github.com\/dimitri\/pgloader\/tar.gz\/refs\/tags\/v3.6.10 [following]\n--2025-01-04 11:35:43--  https:\/\/codeload.github.com\/dimitri\/pgloader\/tar.gz\/refs\/tags\/v3.6.10\nResolving codeload.github.com (codeload.github.com)... 20.207.73.88\nConnecting to codeload.github.com (codeload.github.com)|20.207.73.88|:443... connected.\nHTTP request sent, awaiting response... 200 OK\nLength: unspecified [application\/x-gzip]\nSaving to: 'v3.6.10.tar.gz'\n\nv3.6.10.tar.gz                         [        <=>                                                     ]   3.55M  2.22MB\/s    in 1.6s\n\n2025-01-04 11:35:45 (2.22 MB\/s) - 'v3.6.10.tar.gz' saved [3722933]\n[postgres@mysql-01 pgloader-master]$ rpmbuild -ba pgloader.spec\nExecuting(%prep): \/bin\/sh -e \/var\/tmp\/rpm-tmp.sDpiO2\n+ umask 022\n+ cd \/var\/lib\/pgsql\/rpmbuild\/BUILD\n+ cd \/var\/lib\/pgsql\/rpmbuild\/BUILD\n+ rm -rf pgloader-3.6.10\n+ \/usr\/bin\/gzip -dc \/var\/lib\/pgsql\/rpmbuild\/SOURCES\/v3.6.10.tar.gz\n+ \/usr\/bin\/tar -xof -\n+ STATUS=0\n+ '[' 0 -ne 0 ']'\n+ cd pgloader-3.6.10\n+ \/usr\/bin\/chmod -Rf a+rX,u+w,g-w,o-w .\n+ exit 0\nExecuting(%build): \/bin\/sh -e \/var\/tmp\/rpm-tmp.dQV5Tk\n+ umask 022\n+ cd \/var\/lib\/pgsql\/rpmbuild\/BUILD\n+ cd pgloader-3.6.10\n+ make pgloader\nmkdir -p build\ncurl -o build\/quicklisp.lisp http:\/\/beta.quicklisp.org\/quicklisp.lisp\n  % Total    % Received % Xferd  Average Speed   Time    Time     Time  Current\n                                 Dload  Upload   Total   Spent    Left  Speed\n100 57144  100 57144    0     0  28373      0  0:00:02  0:00:02 --:--:-- 28387\n\u2026\u2026\u2026\u2026\u2026\u2026\u2026\u2026\u2026\u2026\u2026\u2026\u2026\u2026\u2026\u2026\u2026\u2026\u2026\u2026\u2026\u2026\u2026\u2026\u2026\u2026\u2026\u2026\u2026\u2026\u2026\u2026\u2026\u2026\u2026\u2026\u2026\u2026\u2026\u2026\u2026..\n\u2026\u2026\u2026\u2026\u2026\u2026\u2026\u2026\u2026\u2026\u2026\u2026\u2026\u2026\u2026\u2026\u2026\u2026\u2026\u2026\u2026\u2026\u2026\u2026\u2026\u2026\u2026\u2026\u2026\u2026\u2026\u2026\u2026\u2026\u2026\u2026\u2026\u2026\u2026\u2026\u2026\u2026..\nWrote: \/var\/lib\/pgsql\/rpmbuild\/RPMS\/x86_64\/pgloader-3.6.10-22.el8.x86_64.rpm\nExecuting(%clean): \/bin\/sh -e \/var\/tmp\/rpm-tmp.h3bwev\n+ umask 022\n+ cd \/var\/lib\/pgsql\/rpmbuild\/BUILD\n+ cd pgloader-3.6.10\n+ \/usr\/bin\/rm -rf \/var\/lib\/pgsql\/rpmbuild\/BUILDROOT\/pgloader-3.6.10-22.el8.x86_64\n+ exit 0\n[postgres@mysql-01 pgloader-master]$ ls -lh \/var\/lib\/pgsql\/rpmbuild\/RPMS\/x86_64\/\ntotal 18M\n-rw-r--r-- 1 postgres postgres 18M Jan  4 11:47 pgloader-3.6.10-22.el8.x86_64.rpm\n\n[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\nVerifying...                          ################################# [100%]\nPreparing...                          ################################# [100%]\nUpdating \/ installing...\n   1:pgloader-3.6.10-22.el8           ################################# [100%]\n[postgres@mysql-01 pgloader-master]$ vi sbcl-dummy.spec\n[postgres@mysql-01 pgloader-master]$ rpmbuild -bb sbcl-dummy.spec\nProcessing files: sbcl-1.0-1.x86_64\nChecking for unpackaged file(s): \/usr\/lib\/rpm\/check-files \/var\/lib\/pgsql\/rpmbuild\/BUILDROOT\/sbcl-1.0-1.x86_64\nWrote: \/var\/lib\/pgsql\/rpmbuild\/RPMS\/x86_64\/sbcl-1.0-1.x86_64.rpm\nExecuting(%clean): \/bin\/sh -e \/var\/tmp\/rpm-tmp.GZbEFg\n+ umask 022\n+ cd \/var\/lib\/pgsql\/rpmbuild\/BUILD\n+ \/usr\/bin\/rm -rf \/var\/lib\/pgsql\/rpmbuild\/BUILDROOT\/sbcl-1.0-1.x86_64\n+ exit 0\n[postgres@mysql-01 pgloader-master]$ sudo rpm -ivh \/var\/lib\/pgsql\/rpmbuild\/RPMS\/x86_64\/sbcl-1.0-1.x86_64.rpm\nVerifying...                          ################################# [100%]\nPreparing...                          ################################# [100%]\nUpdating \/ installing...\n   1:sbcl-1.0-1                       ################################# [100%]\n\n[postgres@mysql-01 pgloader-master]$ pgloader --version\npgloader version \"3.6.7~devel\"\ncompiled with SBCL 2.2.9\n\n<\/pre>\n
      To restore databases, ensure that users have the necessary grants on MySQL and PostgreSQL. Create a database in PostgreSQL for the restoration process.<\/span><\/p>\n
      postgres=# create database sample2;\nCREATE DATABASE<\/pre>\n
      Restoring database using pgloader with mysql and postgres credentials.<\/span><\/h3>\n
      Pgloader<\/span>
      \n<\/span>mysql:\/\/<username>:<password>@<hostname>\/<DB_name> <\/span>
      \n<\/span>postgres:\/\/<username>:<password>@<hostname>\/<DB_name><\/span><\/p>\n
      [postgres@mysql-01 pgloader-master]$ pgloader mysql:\/\/raj:Mysql%123@localhost\/sample pgsql:\/\/admin:psql123@localhost\/sample2\n2025-01-04T15:24:52.226998-05:00 LOG pgloader version \"3.6.7~devel\"\n2025-01-04T15:24:53.459988-05:00 LOG Migrating from #<MYSQL-CONNECTION mysql:\/\/raj@localhost:3306\/sample {1005929A33}>\n2025-01-04T15:24:53.460988-05:00 LOG Migrating into #<PGSQL-CONNECTION pgsql:\/\/admin@localhost:5432\/sample2 {1005AEF0B3}>\n2025-01-04T15:24:57.356956-05:00 LOG report summary reset\n             table name     errors       rows      bytes      total time\n-----------------------  ---------  ---------  ---------  --------------\n        fetch meta data          0          2                     1.513s\n         Create Schemas          0          0                     0.076s\n       Create SQL Types          0          1                     0.118s\n          Create tables          0          2                     0.113s\n         Set Table OIDs          0          1                     0.025s\n-----------------------  ---------  ---------  ---------  --------------\n          sample.orders          0          5     0.2 kB          0.236s\n-----------------------  ---------  ---------  ---------  --------------\nCOPY Threads Completion          0          4                     0.251s\n Index Build Completion          0          1                     0.184s\n         Create Indexes          0          1                     0.039s\n        Reset Sequences          0          1                     0.316s\n           Primary Keys          0          1                     0.019s\n    Create Foreign Keys          0          0                     0.000s\n        Create Triggers          0          0                     0.002s\n        Set Search Path          0          1                     0.006s\n       Install Comments          0          0                     0.000s\n-----------------------  ---------  ---------  ---------  --------------\n      Total import time          \u2713          5     0.2 kB          0.817s\n\n<\/pre>\n
      Verification steps for Database Migration:<\/span><\/p>\n
      postgres=# \\c sample2\nYou are now connected to database \"sample2\" as user \"postgres\".\nsample2=# \\d\n                List of relations\n Schema |        Name         |   Type   | Owner\n--------+---------------------+----------+-------\n sample | orders              | table    | admin\n sample | orders_order_id_seq | sequence | admin\n(2 rows)\n\nsample2=# \\dt\n        List of relations\n Schema |  Name  | Type  | Owner\n--------+--------+-------+-------\n sample | orders | table | admin\n(1 row)<\/pre>\n
      \n
      Credit: For pgloader tool : <\/span>https:\/\/github.com\/dimitri\/pgloader<\/span><\/a><\/p>\n<\/blockquote>\n
      \u00a0<\/span><\/p>\n","protected":false},"excerpt":{"rendered":"
      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…<\/p>\n","protected":false},"author":1,"featured_media":2776,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[24],"tags":[90,104,113,121,149,206,209,215,216,252,253,278,279,284,311],"class_list":["post-2775","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-mysql","tag-centos-database-migration","tag-data-transfer-tools","tag-database-migration","tag-database-upgrade","tag-efficient-database-transition","tag-mysql-8-0-40-to-postgresql-16-1","tag-mysql-migration-guide","tag-mysql-to-postgresql","tag-mysql-to-postgresql-conversion","tag-pgloader-on-centos-8","tag-pgloader-tutorial","tag-postgresql-migration-steps","tag-postgresql-performance","tag-postgresql-tips-and-tricks","tag-seamless-database-migration","category-24","description-off"],"aioseo_notices":[],"_links":{"self":[{"href":"https:\/\/divaind.com\/ie1\/wp-json\/wp\/v2\/posts\/2775","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/divaind.com\/ie1\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/divaind.com\/ie1\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/divaind.com\/ie1\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/divaind.com\/ie1\/wp-json\/wp\/v2\/comments?post=2775"}],"version-history":[{"count":0,"href":"https:\/\/divaind.com\/ie1\/wp-json\/wp\/v2\/posts\/2775\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/divaind.com\/ie1\/wp-json\/wp\/v2\/media\/2776"}],"wp:attachment":[{"href":"https:\/\/divaind.com\/ie1\/wp-json\/wp\/v2\/media?parent=2775"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/divaind.com\/ie1\/wp-json\/wp\/v2\/categories?post=2775"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/divaind.com\/ie1\/wp-json\/wp\/v2\/tags?post=2775"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}