pgloader用于将mysql数据库迁移到pg数据库,之前用DTS进行迁移,自增主键没有正常迁移,所以改为使用pgloader。pgloader部署在linux环境,支持apt-get 方式、源码部署和docker部署。
pgloader官网:https://pgloader.io/
服务器系统版本
#lsb_release -a
LSB Version: :core-4.1-amd64:core-4.1-noarch
Distributor ID: CentOS
Description: CentOS Linux release 7.5.1804 (Core)
Release: 7.5.1804
Codename: Core
在centos系统中使用源码方式迁移失败。
# 源码下载地址
https://github.com/dimitri/pgloader.git
# 上传源码文件至/opt
cd /opt
# 解压pgloader源码
unzip pgloader-v3.6.1.zip
# 进入pgloader目录
cd pgloader
# 给bootstrap-centos7.sh赋执行权限
chmod +x bootstrap-centos7.sh
# 执行chmod +x bootstrap-centos7.sh
./bootstrap-centos7.sh
# 编译
make pgloader
# 编译过程出现以下提示时,输入0继续:
# 但不知道是不是这里sbcl安装出现问题,源码方式部署迁移均未成功
# 无论装哪个pgloader,sbcl版本一直是1.4.0-1.el7,
# sbcl地址:http://www.sbcl.org/platform-table.html
# 这个版本太低了,迁移时会报一个控制栈耗尽的错误 Control stack exhausted (no more space for function call frames).
This is probably due to heavily nested or infinitely recursive function
calls, or a tail call that SBCL cannot or has not optimized away.
# 这个问题和服务器资源没有关系,服务器资源还很充足。
Type HELP for debugger help, or (SB-EXT:EXIT) to exit from SBCL.
restarts (invokable by number or by possibly-abbreviated name):
0: [CONTINUE ] Use the new definition of SCHEMA,
invalidating already-loaded code and
instances.
1: [RECKLESSLY-CONTINUE ] Use the new definition of SCHEMA as if it
were compatible, allowing old accessors to
use new instances and allowing new
accessors to use old instances.
2: [TRY-RECOMPILING ] Recompile catalog and try loading it again
3: [RETRY ] Retry
loading FASL for #<CL-SOURCE-FILE "pgloader" "src" "utils" "catalog">.
4: [ACCEPT ] Continue, treating
loading FASL for #<CL-SOURCE-FILE "pgloader" "src" "utils" "catalog">
as having been successful.
5: Retry ASDF operation.
6: [CLEAR-CONFIGURATION-AND-RETRY] Retry ASDF operation after resetting the
configuration.
7: [ABORT ] Give up on "pgloader"
8: Ignore runtime option --eval "(ql:quickload \"pgloader\")".
9: Skip rest of --eval and --load options.
10: Skip to toplevel READ/EVAL/PRINT loop.
11: [EXIT ] Exit SBCL (calling #'EXIT, killing the process).
(SB-KERNEL::%REDEFINE-DEFSTRUCT #<SB-KERNEL:STRUCTURE-CLASSOID SCHEMA> #<SB-KERNEL:LAYOUT for SCHEMA {20557883}> #<SB-KERNEL:LAYOUT for SCHEMA, INVALID=:UNINITIALIZED {205B0783}>)
0]
# 查看pgloader版本
./pgloader --version
------------------------
pgloader version "3.6.1"
compiled with SBCL 1.4.0-1.el7
# 迁移mysql数据到pg库 -d:debug级别日志,迁移时报控制栈耗尽的错误
./pgloader -d mysql://user@localhost/mysql postgresql://user@localhost/pg
# pgloader日志及迁移表缓存文件
/tmp/pgloader
该方式只能使用默认配置进行迁移,如果需要个性化配置,使用下方docker配置文件方式迁移。
# docker版本
docker --version
Docker version 1.13.1, build 0be3e21/1.13.1
# 拉取镜像
docker pull dimitri/pgloader:ccl.latest
# 查看pgloader版本 --rm:容器执行完自动删除
docker run --rm --name pgloader.tmp dimitri/pgloader:ccl.latest pgloader --version
# pgloader版本
pgloader version "3.6.3~devel"
compiled with Clozure Common Lisp Version 1.11.5/v1.11.5 (LinuxX8664)
# mysql数据库迁移pg 通过命令使用默认配置迁移
#docker run --rm --name pgloader.tmp dimitri/pgloader:ccl.latest pgloader -d mysql://user@localhost/mysql postgresql://user@localhost/pg
通过配置文件方式迁移可以修改默认迁移配置,比如规定类型迁移的标准、排除需要迁移的表等。
pgloader默认迁移转换规则:https://pgloader.readthedocs.io/en/latest/ref/mysql.html#default-mysql-casting-rules
pg.loader文件内容
# CAST type tinyint to smallint drop typemod:迁移时将mysql中的tinyint字段指定为int2并忽略长度。
# 其他为默认配置
------------------------------------
LOAD DATABASE
FROM mysql://user@localhost/mysql
INTO postgresql://user@localhost/pg
WITH include drop, create tables, create indexes, reset sequences,
workers = 8, concurrency = 1,
multiple readers per thread, rows per range = 50000
CAST type tinyint to smallint drop typemod
;
------------------------------------
# 运行容器
docker run -tid --name pgloder.ccl dimitri/pgloader:ccl.latest
# 复制配置文件到容器
docker cp /root/pgloader/pg.loader pgloder.ccl:/
# 进入容器
docker exec -it pgloder.ccl /bin/bash
# 使用文件方式执行迁移
pgloader pg.loader