devbox
我家小花儿
这个屌丝很懒，什么也没留下！
关注作者
热门标签
热门文章
当前位置:   article > 正文

postgresql|数据库|MySQL数据库向postgresql数据库迁移的工具pgloader的部署和初步使用

作者：我家小花儿 | 2024-04-10 22:18:46

赞

踩

pgloader

前言：

MySQL数据库和postgresql数据库之间的差异并不多，这里的差异指的是对SQL语言的支持两者并不大，但底层的东西差异是非常多的，例如，MySQL的innodb引擎概念，数据库用户管理，这些和postgresql相比是完全不同的（MySQL用户就是用户，没有角色，postgresql有用户，有角色，但差异不大），但，索引例如btree这些两者又是相同的或者说相通的。

那么，这些差异化的地方导致MySQL在向postgresql数据库迁移数据的时候会造成一些困扰，虽然两者数据库使用的SQL语言百分之九十是相同的，但就是这剩下的百分之十也是一个巨大的鸿沟。

普通的方案，MySQLdump或者navicat直接备份MySQL，然后将备份文件导入postgresql需要修改很多地方，最基本的是需要提取insert语句，光这些工作量就非常的大了，如果要迁移的数据很多的话，因此，该方案基本上是不能考虑的。

OK，那么，有没有MySQL数据库平滑的向postgresql数据库迁移的方案呢？

答案是有的，版本答案就是pgloader工具，但丑话先说前面，此工具只支持全量迁移并不支持增量迁移，这应该是一个比较遗憾的地方，其它的方面，比如迁移效率（迁移用时），迁移质量（也就是迁移到postgresql的数据是否完整，准确），迁移方式（例如，库对库迁移，读取csv文件迁移，编写配置文件迁移）这些方面是让人挑不出理的。

一，

pgloader的部署安装

下载

pgloader通常的部署方式是三种，第一种是编译部署，第二种是docker镜像部署，第三种是二进制部署，本文选择的是二进制部署，其它部署方式本文不予讨论。

pgloader的官方下载地址：

https://github.com/dimitri/pgloader/releases

离线安装的地址：

链接：https://pan.baidu.com/s/18aN0CvyoXy_o8iP-aqVwGg?pwd=kkey 
提取码：kkey 
 

建议选择最新版本3.6.9

 二，

环境依赖安装

上述的离线安装包 ，解压后，进入文件夹，rpm -ivh * 即可。

如果是centos的，确认是否有安装libtool-ltdl，有输出表示已经安装，将那个libtool-ltdl-2.4.2-22.h1.eulerosv2r7.x86_64.rpm移除即可。

确认是否有安装libtool-ltdl，有输出表示已经安装，

  1. [root@centos5 ~]# rpm -qa |grep libtool-ltdl
  2. libtool-ltdl-2.4.2-22.el7_3.x86_64

这里说明一下，两个关键依赖，分别是freetds-devel和sbcl-1.4.0，其它rpm包是这两个关键依赖的依赖。

三，

部署

已经部署过了

  1. [root@centos5 pgloader-bundle-3.6.9]# pwd
  2. /root/pgloader-bundle-3.6.9
  3. [root@centos5 pgloader-bundle-3.6.9]# make pgloader
  4. make: `pgloader' is up to date.

第一次部署的，make pgloader的输出是这样的：

  1. ;; loading system "cffi"
  2.  
  3. ;; loading system "cl+ssl"
  4.  
  5. ;; loading system "mssql"
  6.  
  7. ;; loading file #P"/root/pgloader_soft/pgloader-bundle-3.6.2/local-projects/pgloader-3.6.2/src/hooks.lisp"
  8.  
  9. ;; loading system "pgloader"
  10.  
  11. compressed 0 bytes into 8 at level -1
  12.  
  13. compressed 32768 bytes into 617 at level -1
  14.  
  15. compressed 3047424 bytes into 820965 at level -1
  16.  
  17. compressed 23691264 bytes into 6785671 at level -1
  18.  
  19. compressed 91127808 bytes into 16513612 at level -1
  20.  
  21. # that's ugly, but necessary when building on Windows :(
  22.  
  23. mv bin/pgloader.tmp bin/pgloader

环境变量设置：

  1. vi /etc/profile
  2.  
  3.  
  4.  
  5. alias pgloader=/root/pgloader-bundle-3.6.9/bin/pgloader
  6.  
  7. :wq
  8.  
  9.  
  10.  
  11. source /etc/profile

验证：

  1. [root@centos5 ~]# pgloader --version
  2. pgloader version "3.6.9"
  3. compiled with SBCL 1.4.0-1.el7

四，

pgloader的初步使用

既然是MySQL迁移数据到postgresql，那么，自然需要两个服务器，服务器1  IP是192.168.123.15，该服务器部署postgresql-12.5，服务器2 IP是192.168.123.16，该服务器部署mariadb

如何部署这两个数据库就不在这里废话了。

MySQL数据库有test数据库，该库里有erp表：

  1. MariaDB [(none)]> use test;
  2. Reading table information for completion of table and column names
  3. You can turn off this feature to get a quicker startup with -A
  4.  
  5. Database changed
  6. MariaDB [test]> show tables;
  7. +----------------+
  8. | Tables_in_test |
  9. +----------------+
  10. | DEPT           |
  11. | EMP            |
  12. +----------------+
  13. 2 rows in set (0.00 sec)
  14.  
  15. MariaDB [test]> select * from EMP;
  16. +-------+--------+-----------+------+------------+------+------+--------+
  17. | EMPNO | ENAME  | JOB       | MGR  | HIREDATE   | SAL  | COMM | DEPTNO |
  18. +-------+--------+-----------+------+------------+------+------+--------+
  19. |  7369 | SMITH  | CLERK     | 7902 | 1980-12-17 |  800 | NULL |     20 |
  20. |  7499 | ALLEN  | SALESMAN  | 7698 | 1981-02-20 | 1600 |  300 |     30 |
  21. |  7521 | WARD   | SALESMAN  | 7698 | 1981-02-22 | 1250 |  500 |     30 |
  22. |  7566 | JONES  | MANAGER   | 7839 | 1981-04-02 | 2975 | NULL |     20 |
  23. |  7654 | MARTIN | SALESMAN  | 7698 | 1981-09-28 | 1250 | 1400 |     30 |
  24. |  7698 | BLAKE  | MANAGER   | 7839 | 1981-05-01 | 2850 | NULL |     30 |
  25. |  7782 | CLARK  | MANAGER   | 7839 | 1981-06-09 | 2450 | NULL |     10 |
  26. |  7788 | SCOTT  | ANALYST   | 7566 | 1987-04-19 | 3000 | NULL |     20 |
  27. |  7839 | KING   | PRESIDENT | NULL | 1981-11-17 | 5000 | NULL |     10 |
  28. |  7844 | TURNER | SALESMAN  | 7698 | 1981-09-08 | 1500 |    0 |     30 |
  29. |  7876 | ADAMS  | CLERK     | 7788 | 1987-05-23 | 1100 | NULL |     20 |
  30. |  7900 | JAMES  | CLERK     | 7698 | 1981-12-03 |  950 | NULL |     30 |
  31. |  7902 | FORD   | ANALYST   | 7566 | 1981-12-03 | 3000 | NULL |     20 |
  32. |  7934 | MILLER | CLERK     | 7782 | 1982-01-23 | 1300 | NULL |     10 |
  33. +-------+--------+-----------+------+------------+------+------+--------+
  34. 14 rows in set (0.00 sec)

编辑迁移配置文件，在postgresql所在的服务器上，文件内容如下：

  1. vi pg.load.test1
  2.  
  3.  
  4.  
  5. load database
  6.  
  7. from mysql://root:密码@192.168.123.16:3306/test
  8.  
  9. into pgsql://postgres:密码@192.168.123.15:5432/postgres;

开始迁移：

迁移命令和日志文件的内容：

  1. [postgres@centos5 ~]$ pgloader --verbose pg.load.test1>> pg.load.test2 2>&1
  2. [postgres@centos5 ~]$ cat pg.load.test2 
  3. -bash: pgloader: command not found
  4. [postgres@centos5 ~]$ logout
  5. [root@centos5 ~]# mv /home/postgres/pg.load.test1 ./
  6. [root@centos5 ~]# pgloader --verbose pg.load.test1>> pg.load.test2 2>&1
  7. [root@centos5 ~]# cat pg.load.test
  8. pg.load.test1  pg.load.test2  
  9. [root@centos5 ~]# cat pg.load.test2
  10. 2023-08-04T08:03:56.023000+08:00 NOTICE Starting pgloader, log system is ready.
  11. 2023-08-04T08:03:56.049000+08:00 LOG pgloader version "3.6.9"
  12. 2023-08-04T08:03:56.115000+08:00 LOG Migrating from #<MYSQL-CONNECTION mysql://root@192.168.123.16:3306/test {1008D4C553}>
  13. 2023-08-04T08:03:56.115000+08:00 LOG Migrating into #<PGSQL-CONNECTION pgsql://postgres@192.168.123.15:5432/postgres {1008D4D933}>
  14. 2023-08-04T08:03:56.404000+08:00 ERROR mysql: Failed to connect to mysql at "192.168.123.16" (port 3306) as user "root": MySQL Error [1045]: "Access denied for user 'root'@'192.168.123.15' (using password: YES)"
  15. 2023-08-04T08:03:56.404000+08:00 LOG report summary reset
  16.        table name     errors       read   imported      bytes      total time       read      write
  17. -----------------  ---------  ---------  ---------  ---------  --------------  ---------  ---------
  18.   fetch meta data          0          0          0                     0.000s    
  19. -----------------  ---------  ---------  ---------  ---------  --------------  ---------  ---------
  20. -----------------  ---------  ---------  ---------  ---------  --------------  ---------  ---------
  21. [root@centos5 ~]# vim pg.load.test1
  22. [root@centos5 ~]# pgloader --verbose pg.load.test1>> pg.load.test2 2>&1
  23. [root@centos5 ~]# cat pg.load.test2
  24. 2023-08-04T08:03:56.023000+08:00 NOTICE Starting pgloader, log system is ready.
  25. 2023-08-04T08:03:56.049000+08:00 LOG pgloader version "3.6.9"
  26. 2023-08-04T08:03:56.115000+08:00 LOG Migrating from #<MYSQL-CONNECTION mysql://root@192.168.123.16:3306/test {1008D4C553}>
  27. 2023-08-04T08:03:56.115000+08:00 LOG Migrating into #<PGSQL-CONNECTION pgsql://postgres@192.168.123.15:5432/postgres {1008D4D933}>
  28. 2023-08-04T08:03:56.404000+08:00 ERROR mysql: Failed to connect to mysql at "192.168.123.16" (port 3306) as user "root": MySQL Error [1045]: "Access denied for user 'root'@'192.168.123.15' (using password: YES)"
  29. 2023-08-04T08:03:56.404000+08:00 LOG report summary reset
  30.        table name     errors       read   imported      bytes      total time       read      write
  31. -----------------  ---------  ---------  ---------  ---------  --------------  ---------  ---------
  32.   fetch meta data          0          0          0                     0.000s    
  33. -----------------  ---------  ---------  ---------  ---------  --------------  ---------  ---------
  34. -----------------  ---------  ---------  ---------  ---------  --------------  ---------  ---------
  35. 2023-08-04T08:05:15.005000+08:00 NOTICE Starting pgloader, log system is ready.
  36. 2023-08-04T08:05:15.015000+08:00 LOG pgloader version "3.6.9"
  37. 2023-08-04T08:05:15.048000+08:00 LOG Migrating from #<MYSQL-CONNECTION mysql://root@192.168.123.16:3306/test {1008DBCD63}>
  38. 2023-08-04T08:05:15.048000+08:00 LOG Migrating into #<PGSQL-CONNECTION pgsql://postgres@192.168.123.15:5432/postgres {1008DBE1E3}>
  39. 2023-08-04T08:05:15.138000+08:00 NOTICE Prepare PostgreSQL database.
  40. 2023-08-04T08:05:15.194000+08:00 NOTICE Processing tables in this order: test.emp: 14 rows, test.dept: 4 rows
  41. 2023-08-04T08:05:15.208000+08:00 NOTICE COPY test.emp with 14 rows estimated [2/4]
  42. 2023-08-04T08:05:15.227000+08:00 NOTICE COPY test.dept with 4 rows estimated [1/4]
  43. 2023-08-04T08:05:15.241000+08:00 NOTICE DONE copying test.dept in 0.000s
  44. 2023-08-04T08:05:15.241000+08:00 NOTICE DONE copying test.emp in 0.000s
  45. 2023-08-04T08:05:15.253000+08:00 NOTICE CREATE UNIQUE INDEX idx_16388_primary ON test.emp (empno);
  46. 2023-08-04T08:05:15.268000+08:00 NOTICE Completing PostgreSQL database.
  47. 2023-08-04T08:05:15.268000+08:00 NOTICE Reset sequences
  48. 2023-08-04T08:05:15.282000+08:00 NOTICE ALTER TABLE test.emp ADD PRIMARY KEY USING INDEX idx_16388_primary;
  49. 2023-08-04T08:05:15.284000+08:00 NOTICE ALTER DATABASE "postgres" SET search_path TO public, test;
  50. 2023-08-04T08:05:15.286000+08:00 LOG report summary reset
  51.              table name     errors       read   imported      bytes      total time       read      write
  52. -----------------------  ---------  ---------  ---------  ---------  --------------  ---------  ---------
  53.         fetch meta data          0          3          3                     0.045s    
  54.          Create Schemas          0          0          0                     0.001s    
  55.        Create SQL Types          0          0          0                     0.002s    
  56.           Create tables          0          4          4                     0.017s    
  57.          Set Table OIDs          0          2          2                     0.006s    
  58. -----------------------  ---------  ---------  ---------  ---------  --------------  ---------  ---------
  59.                test.emp          0         14         14     0.6 kB          0.033s     0.023s  
  60.               test.dept          0          4          4     0.1 kB          0.009s     0.005s  
  61. -----------------------  ---------  ---------  ---------  ---------  --------------  ---------  ---------
  62. COPY Threads Completion          0          4          4                     0.022s    
  63.  Index Build Completion          0          1          1                     0.006s    
  64.          Create Indexes          0          1          1                     0.002s    
  65.         Reset Sequences          0          0          0                     0.010s    
  66.            Primary Keys          0          1          1                     0.001s    
  67.     Create Foreign Keys          0          0          0                     0.000s    
  68.         Create Triggers          0          0          0                     0.000s    
  69.         Set Search Path          0          1          1                     0.000s    
  70.        Install Comments          0          0          0                     0.000s    
  71. -----------------------  ---------  ---------  ---------  ---------  --------------  ---------  ---------
  72.       Total import time          ✓         18         18     0.7 kB          0.041s

在postgresql数据库上查看是否迁移成功：

可以看到，完美迁移了

 

声明：本文内容由网友自发贡献，不代表【wpsshop博客】立场，版权归原作者所有，本站不承担相应法律责任。如您发现有侵权的内容，请联系我们。转载请注明出处：https://www.wpsshop.cn/w/我家小花儿/article/detail/401302
推荐阅读
相关标签

Copyright © 2003-2013 www.wpsshop.cn 版权所有，并保留所有权利。

  

闽ICP备14008679号