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

本文涉及的产品
云原生数据库 PolarDB MySQL 版,Serverless 5000PCU 100GB
云数据库 RDS MySQL Serverless,0.5-2RCU 50GB
简介: postgresql|数据库|MySQL数据库向postgresql数据库迁移的工具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,有输出表示已经安装,

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

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

三,

部署

已经部署过了

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

第一次部署的,make pgloader的输出是这样的:

;; loading system "cffi"
;; loading system "cl+ssl"
;; loading system "mssql"
;; loading file #P"/root/pgloader_soft/pgloader-bundle-3.6.2/local-projects/pgloader-3.6.2/src/hooks.lisp"
;; loading system "pgloader"
compressed 0 bytes into 8 at level -1
compressed 32768 bytes into 617 at level -1
compressed 3047424 bytes into 820965 at level -1
compressed 23691264 bytes into 6785671 at level -1
compressed 91127808 bytes into 16513612 at level -1
# that's ugly, but necessary when building on Windows :(
mv bin/pgloader.tmp bin/pgloader

环境变量设置:

vi /etc/profile
alias pgloader=/root/pgloader-bundle-3.6.9/bin/pgloader
:wq
source /etc/profile

验证:

[root@centos5 ~]# pgloader --version
pgloader version "3.6.9"
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表:

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

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

vi pg.load.test1
load database
from mysql://root:密码@192.168.123.16:3306/test
into pgsql://postgres:密码@192.168.123.15:5432/postgres;

开始迁移:

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

[postgres@centos5 ~]$ pgloader --verbose pg.load.test1>> pg.load.test2 2>&1
[postgres@centos5 ~]$ cat pg.load.test2 
-bash: pgloader: command not found
[postgres@centos5 ~]$ logout
[root@centos5 ~]# mv /home/postgres/pg.load.test1 ./
[root@centos5 ~]# pgloader --verbose pg.load.test1>> pg.load.test2 2>&1
[root@centos5 ~]# cat pg.load.test
pg.load.test1  pg.load.test2  
[root@centos5 ~]# cat pg.load.test2
2023-08-04T08:03:56.023000+08:00 NOTICE Starting pgloader, log system is ready.
2023-08-04T08:03:56.049000+08:00 LOG pgloader version "3.6.9"
2023-08-04T08:03:56.115000+08:00 LOG Migrating from #<MYSQL-CONNECTION mysql://root@192.168.123.16:3306/test {1008D4C553}>
2023-08-04T08:03:56.115000+08:00 LOG Migrating into #<PGSQL-CONNECTION pgsql://postgres@192.168.123.15:5432/postgres {1008D4D933}>
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)"
2023-08-04T08:03:56.404000+08:00 LOG report summary reset
       table name     errors       read   imported      bytes      total time       read      write
-----------------  ---------  ---------  ---------  ---------  --------------  ---------  ---------
  fetch meta data          0          0          0                     0.000s    
-----------------  ---------  ---------  ---------  ---------  --------------  ---------  ---------
-----------------  ---------  ---------  ---------  ---------  --------------  ---------  ---------
[root@centos5 ~]# vim pg.load.test1
[root@centos5 ~]# pgloader --verbose pg.load.test1>> pg.load.test2 2>&1
[root@centos5 ~]# cat pg.load.test2
2023-08-04T08:03:56.023000+08:00 NOTICE Starting pgloader, log system is ready.
2023-08-04T08:03:56.049000+08:00 LOG pgloader version "3.6.9"
2023-08-04T08:03:56.115000+08:00 LOG Migrating from #<MYSQL-CONNECTION mysql://root@192.168.123.16:3306/test {1008D4C553}>
2023-08-04T08:03:56.115000+08:00 LOG Migrating into #<PGSQL-CONNECTION pgsql://postgres@192.168.123.15:5432/postgres {1008D4D933}>
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)"
2023-08-04T08:03:56.404000+08:00 LOG report summary reset
       table name     errors       read   imported      bytes      total time       read      write
-----------------  ---------  ---------  ---------  ---------  --------------  ---------  ---------
  fetch meta data          0          0          0                     0.000s    
-----------------  ---------  ---------  ---------  ---------  --------------  ---------  ---------
-----------------  ---------  ---------  ---------  ---------  --------------  ---------  ---------
2023-08-04T08:05:15.005000+08:00 NOTICE Starting pgloader, log system is ready.
2023-08-04T08:05:15.015000+08:00 LOG pgloader version "3.6.9"
2023-08-04T08:05:15.048000+08:00 LOG Migrating from #<MYSQL-CONNECTION mysql://root@192.168.123.16:3306/test {1008DBCD63}>
2023-08-04T08:05:15.048000+08:00 LOG Migrating into #<PGSQL-CONNECTION pgsql://postgres@192.168.123.15:5432/postgres {1008DBE1E3}>
2023-08-04T08:05:15.138000+08:00 NOTICE Prepare PostgreSQL database.
2023-08-04T08:05:15.194000+08:00 NOTICE Processing tables in this order: test.emp: 14 rows, test.dept: 4 rows
2023-08-04T08:05:15.208000+08:00 NOTICE COPY test.emp with 14 rows estimated [2/4]
2023-08-04T08:05:15.227000+08:00 NOTICE COPY test.dept with 4 rows estimated [1/4]
2023-08-04T08:05:15.241000+08:00 NOTICE DONE copying test.dept in 0.000s
2023-08-04T08:05:15.241000+08:00 NOTICE DONE copying test.emp in 0.000s
2023-08-04T08:05:15.253000+08:00 NOTICE CREATE UNIQUE INDEX idx_16388_primary ON test.emp (empno);
2023-08-04T08:05:15.268000+08:00 NOTICE Completing PostgreSQL database.
2023-08-04T08:05:15.268000+08:00 NOTICE Reset sequences
2023-08-04T08:05:15.282000+08:00 NOTICE ALTER TABLE test.emp ADD PRIMARY KEY USING INDEX idx_16388_primary;
2023-08-04T08:05:15.284000+08:00 NOTICE ALTER DATABASE "postgres" SET search_path TO public, test;
2023-08-04T08:05:15.286000+08:00 LOG report summary reset
             table name     errors       read   imported      bytes      total time       read      write
-----------------------  ---------  ---------  ---------  ---------  --------------  ---------  ---------
        fetch meta data          0          3          3                     0.045s    
         Create Schemas          0          0          0                     0.001s    
       Create SQL Types          0          0          0                     0.002s    
          Create tables          0          4          4                     0.017s    
         Set Table OIDs          0          2          2                     0.006s    
-----------------------  ---------  ---------  ---------  ---------  --------------  ---------  ---------
               test.emp          0         14         14     0.6 kB          0.033s     0.023s  
              test.dept          0          4          4     0.1 kB          0.009s     0.005s  
-----------------------  ---------  ---------  ---------  ---------  --------------  ---------  ---------
COPY Threads Completion          0          4          4                     0.022s    
 Index Build Completion          0          1          1                     0.006s    
         Create Indexes          0          1          1                     0.002s    
        Reset Sequences          0          0          0                     0.010s    
           Primary Keys          0          1          1                     0.001s    
    Create Foreign Keys          0          0          0                     0.000s    
        Create Triggers          0          0          0                     0.000s    
        Set Search Path          0          1          1                     0.000s    
       Install Comments          0          0          0                     0.000s    
-----------------------  ---------  ---------  ---------  ---------  --------------  ---------  ---------
      Total import time          ✓         18         18     0.7 kB          0.041s    

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

可以看到,完美迁移了

相关实践学习
基于CentOS快速搭建LAMP环境
本教程介绍如何搭建LAMP环境,其中LAMP分别代表Linux、Apache、MySQL和PHP。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心,利用清洁计算为万物互联的新世界提供源源不断的能源动力,目前开服的区域包括中国(华北、华东、华南、香港)、新加坡、美国(美东、美西)、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程,来了解阿里云能够为你的业务带来哪些帮助 &nbsp; &nbsp; 相关的阿里云产品:云服务器ECS 云服务器 ECS(Elastic Compute Service)是一种弹性可伸缩的计算服务,助您降低 IT 成本,提升运维效率,使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
目录
相关文章
|
13天前
|
SQL 数据可视化 关系型数据库
轻松入门MySQL:深入探究MySQL的ER模型,数据库设计的利器与挑战(22)
轻松入门MySQL:深入探究MySQL的ER模型,数据库设计的利器与挑战(22)
|
13天前
|
存储 关系型数据库 MySQL
轻松入门MySQL:数据库设计之范式规范,优化企业管理系统效率(21)
轻松入门MySQL:数据库设计之范式规范,优化企业管理系统效率(21)
|
9天前
|
存储 关系型数据库 MySQL
MySQL基础入门:数据库操作全攻略
MySQL基础入门:数据库操作全攻略
43 0
|
7天前
|
Docker 容器 关系型数据库
【PolarDB-X从入门到精通】 第四讲:PolarDB分布式版安装部署(源码编译部署)
本期课程将于4月11日19:00开始直播,内容包括源码编译基础知识和实践操作,课程目标是使学员掌握源码编译部署技能,为未来发展奠定基础,期待大家在课程中取得丰富的学习成果!
【PolarDB-X从入门到精通】 第四讲:PolarDB分布式版安装部署(源码编译部署)
|
9天前
|
关系型数据库 MySQL 数据库
卸载云服务器上的 MySQL 数据库
卸载云服务器上的 MySQL 数据库
29 0
|
1天前
|
关系型数据库 MySQL 数据库
数据库基础(mysql)
数据库基础(mysql)
|
2天前
|
存储 关系型数据库 MySQL
【后端面经】【数据库与MySQL】为什么MySQL用B+树而不用B树?-02
【4月更文挑战第11天】数据库索引使用规则:`AND`用`OR`不用,正用反不用,范围中断。索引带来空间和内存代价,包括额外磁盘空间、内存占用和数据修改时的维护成本。面试中可能涉及B+树、聚簇索引、覆盖索引等知识点。MySQL采用B+树,因其利于范围查询和内存效率。数据库不使用索引可能因`!=`、`LIKE`、字段区分度低、特殊表达式或全表扫描更快。索引与NULL值处理在不同数据库中有差异,MySQL允许NULL在索引中的使用。
9 3
|
4天前
|
关系型数据库 MySQL 数据库连接
Django(四):Django项目部署数据库及服务器配置详解(MySQL)
Django(四):Django项目部署数据库及服务器配置详解(MySQL)
27 11
|
8天前
|
关系型数据库 MySQL 数据库
mysql卸载、下载、安装(window版本)
mysql卸载、下载、安装(window版本)
|
1月前
|
关系型数据库 MySQL 数据库连接
关于MySQL-ODBC的zip包安装方法
关于MySQL-ODBC的zip包安装方法