本次mysql到postgresql数据库迁移工具使用pgloader迁移，做常规迁移。如果遇到特殊字段，比如mysql中的地理坐标字段gemotry,需要采用其他方法。该方法在具体说明。
安装条件准备
pgloader版本 | pgloader-bundle-3.6.2.tgz |
Pgloader 部署的服务器ip地址 | 192.168.56.112（请根据实际环境部署该软件，保证该地址可以同时连接pg和mysql数据库） |
源端mysql数据库地址 | 192.168.56.113 |
目标端postgres 数据库地址 | 192.168.56.114 |
Pgloader 安装部署 | 略 |
源端mysql数据库参数要求 | Innodb_large_prefix=on net_write_timeout=3600; net_read_timeout=3600; 其中net_write_timeout/net_read_timeout在迁移期间建议修改，迁移后恢复成默认值 |
目标端Postgres 数据库要求 | 在mysql rds中是通过geometory字段类型存储经纬度等地理位置信息，在通过pgloader 转换后，默认会转换成point类型，如果需要改变为geometory字段类型，需要安装gis插件。 |
数据库迁移账号 | 数据库迁移账号请避免带有特殊字符，比如@#￥%^&等，如有特殊字符@，可以先进行密码修改，迁移完成后，在修改回去或者需要多加一个@，即密码如果遇到@，则再需要再后面添加一个@ |
PGLoader工具安装
安装依赖包
以下的安装操作都是以root用户执行。
yum -y install yum-utils rpmdevtools @"Development Tools" sqlite-devel zlib-devel yum -y install epel-release yum -y install sbcl yum install freetds freetds-devel -y yum -y install unixODBC yum -y install libtool-ltdl* yum -y install cl-asdf-20101028-8.el7.noarch.rpm yum -y install common-lisp-controller-7.4-8.el7.noarch.rpm yum -y install sbcl-1.4.0-1.el7.x86_64.rpm rpm -ivh freetds-libs-1.1.20-1.el7.x86_64.rpm freetds-1.1.20-1.el7.x86_64.rpm yum -y install freetds-devel-1.1.20-1.el7.x86_64.rpm ln -s /usr/lib64/libsybdb.so.5 /usr/lib64/libsybdb.so |
Pgloader解压安装
软件安装包目录为/root/pgloader_soft,现场可以根据实际环境自行决定上传目录。
tar -xvf pgloader-bundle-3.6.2.tgz cd pgloader-bundle-3.6.2/ make pgloader --出现下面的信息表示安装成功
|
设置alias
vi /etc/profile alias pgloader=/root/pgloader_soft/pgloader-bundle-3.6.2/bin/pgloader :wq source /etc/profile |
验证安装
[root@orcl bin]# pgloader --version; pgloader version "3.6.2" compiled with SBCL 1.4.0-1.el7 |
源端数据库对象收集
在迁移前需要对源数据库的对象和数据进行一次整理，以便后续的数据对象和数据验证
对象验证
Mysql对象验证包括：表，视图，存储过程，触发器，函数等。
查看表数量
本脚统计的是电网资源实例下的所有表对象的数量。如果是其他实例，请输入对应的schema
SELECT count(*), table_schema FROM information_schema.`TABLES` d WHERE d.table_schema IN ( 'xxxxx') AND d.table_type = 'BASE TABLE' group by table_schema order by table_schema |
查看视图数量
本脚统计的是电网资源实例下的所有视图对象的数量。如果是其他实例，请输入对应的schema
select count(*), table_schema from information_schema.VIEWS d WHERE d.table_schema IN ( 'xxxxxx', ) group by table_schema order by table_schema |
查看存储过程，函数，序列数量
本脚统计的是电网资源实例下的所有存储过程，函数对象的数量。如果是其他实例，请输入对应的schema
SELECT count(*), routine_type, routine_schema FROM information_schema.ROUTINES d WHERE d.routine_schema IN ( 'xxxxxx' ) GROUP BY routine_type, routine_schema ORDER BY routine_schema, routine_type |
查看触发器数量
本脚统计的是电网资源实例下的所有触发器对象的数量。如果是其他实例，请输入对应的schema
SELECT count(*), trigger_schema FROM information_schema.`TRIGGERS` d WHERE d.trigger_schema IN ( 'xxxxxx' ) GROUP BY trigger_schema ORDER BY trigger_schema |
特性验证
不同数据库之间的迁移，可能伴随着特性的转变。Mysql中需要关注自增和自动更新时间两个特性，其中自增特性在postgresql数据库中是通过序列实现，自动更新时间特性在postgresql数据库中是通过触发器实现。
查看自增特性和自动更新特性
SELECT count(*), table_schema, extra FROM information_schema.`COLUMNS` d WHERE ( d.extra LIKE 'auto%' OR d.extra LIKE 'on%' ) GROUP BY table_schema, extra order by table_schema,extra |
数据验证
验证每个表的数据量
参考脚本：<mysql获得指定schema下表的精确行数.sql>
目标端创建数据库
略
数据迁移配置
常规数据迁移
常规数据迁移
test1迁移配置和迁移
请注意新建pg.load.test1 配置文件。以下配置文件都使用相同方法。
配置文件请修改为实际的数据库链接地址。
该配置文件为：把mysql中test1数据库，迁移到pg中的tdb数据库下，
vi pg.load.test1 load database from mysql://admin_user:admin_user@192.168.56.113:3306/test1 into pgsql://admin_user:admin_user@192.168.56.114:5432/tdb; |
pgloader --verbose pg.load.test1>> pg.load.test1 2>&1 |
特殊字段迁移geometry
在迁移过程中需要注意到特殊字段，在阿里云mysql rds中是通过geometory字段类型存储经纬度等地理位置信息，在通过pgloader 转换后，默认会转换成point类型，如果需要改变为geometory字段类型，该字段类型需要手动修改
以下脚本都在postgresql数据库tdb中执行
select 'ALTER TABLE test1.'||a.table_name||' ALTER COLUMN geometry TYPE public.geometry USING geometry::public.geometry;' from information_schema.columns a where a.table_schema='test1' and a.column_name='geometry' and a.data_type='point' |
3: 特殊字段数据迁移
由于geo字段类型是非标准常见的字段类型，需要专门的开发工具进行迁移。迁移工具为
nrxt-gis-geotransfer，参考附件工具。该工具需要jdk8环境
配置数据源
#服务端口 server: port: 18009 #context-path: /psr-platform-transfer spring: application: name: psr-platform-transfer datasource: druid: target: ##目标端数据库 type: com.alibaba.druid.pool.DruidDataSource driverClassName: org.postgresql.Driver url: jdbc:postgresql://192.168.56.114:5432/tdb?currentSchema=test1 username: admin_user password: admin_user initialSize: 10 maxActive: 100 source: ##源端数据库 type: com.alibaba.druid.pool.DruidDataSource driverClassName: com.mysql.cj.jdbc.Driver url: jdbc:mysql://192.168.56.113:3306/test?autoReconnect=true&useSSL=false&allowPublicKeyRetrieval=true&serverTimezone=GMT%2B8&allowMultiQueries=true username: admin_user password: admin_user initialSize: 10 maxActive: 100
#----------- 日志配置 ----------- logging: level: root: INFO #设置root日志的级别，一般为INFO （日志级别：TRACE < DEBUG < INFO < WARN < ERROR < FATAL），建议为INFO com.sgcc.nrxt.pms.repositories: INFO |
开始执行
建议window下执行
windows双击start.bat启动 Linux 命令行启动 nohup ./start.sh start |
触发器对象处理
在迁移的过程中出现大量的触发器，该触发器是有mysql中的ON UPDATE CURRENT_TIMESTAMP 功能，在经过pgloader 迁移改造成postgres后，会变成触发器。
由于实际开发环境中要求不能有触发器，所有的触发器功能由应用程序端进行实现，因此需要删除该类触发器。具体操作如下：
单个触发器删除示例脚本：
drop trigger on_update_current_timestamp on xxx.t_xxx_xxx; |
批量触发器删除示例脚本：
生成批量删除触发器对象脚本:
select 'drop trigger '||t.trigger_name||' on '||t.event_object_schema||'.'||t.event_object_table||';' from information_schema.triggers t ; |
生成批量删除触发器函数脚本：
SELECT FORMAT('DROP FUNCTION %s(%s);' ,p.oid::regproc ,pg_get_function_identity_arguments(p.oid)) FROM pg_proc AS p INNER JOIN pg_namespace AS n ON p.pronamespace = n.oid WHERE n.nspname NOT IN ('pg_catalog', 'information_schema', 'public'); |
时区时间字段处理
在mysql中 timestamp字段会自动映射成timestamptz，该字段值带有时区，需要改成非时区的字段类型。
请注意修改字段期间会产生大量的归档日志，请及时清理，表的数据量越大，修改字段越慢，可以同时开启多个sql窗口运行
SELECT 'alter table '||d.table_schema||'.'||table_name||' alter column '||d.column_name||' TYPE timestamp(6) USING ' ||d.column_name||'::timestamp(6);' FROM information_schema.COLUMNS d WHERE d.data_type LIKE'timestamp with time zone' AND d.table_schema NOT IN ( 'public', 'information_schema', 'pg_catalog' ) Order by table_schema ,table_name |
date时间字段处理
在mysql中 date字段会自动映射成date，从业务中了解到该字段类型date不适配引用代码，需要修改为timestamp(6)。
请注意修改字段期间会产生大量的归档日志，请及时清理，表的数据量越大，修改字段越慢，可以同时开启多个sql窗口运行
SELECT 'alter table '||d.table_schema||'.'||table_name||' alter column '||d.column_name||' TYPE timestamp(6) USING ' ||d.column_name||'::timestamp(6);' FROM information_schema.COLUMNS d WHERE d.data_type LIKE 'date' AND d.table_schema NOT IN ( 'public', 'information_schema', 'pg_catalog' ) Order by table_schema ,table_name |
迁移错误处理
非空约束错误处理
在迁移过程中出现表 t_xxx 迁移错误，错误原因是由于非空约束问题。
ERROR Database error 23502: null value in column "lock_time" violates not-null constraint DETAIL: Failing row contains (1305343082518937601, jgZ, dcdb9e4d-a85f-4013-80df-4e2fa43f5a3c, null, 1304434587946971138, 7dae530a4640288285747d757c01747dae50f80422, eeee, 273786, null, null, null, null, null, null, null). CONTEXT: COPY t_xxx , line 1: "1305343082518937601 jgZ dcdb9e4d-a85f-4013-80df-4e2fa43f5a3c \N 1304434587946971138 7dae530a46402882..." |
需要手工处理，针对此问题，在进行表数据导出。
单表迁移配置
vi pg.load.txx load database from mysql://admin_user:admin_user@192.168.56.113:3306/test1 into pgsql://admin_user:admin_user@192.168.56.114:5432/tdb; INCLUDING ONLY TABLE NAMES MATCHING ' t_xx'; |
pgloader pg.load.txx |
默认值错误处理
在mysql 迁移postgres 过程中，由于默认值格式错误导致表，无法迁移成功。需要手动修改后在进行迁移。
QUERY: CREATE TABLE txxx ( ...... a varchar(100) default '01'', b varchar(20), ...... ); |
修改方法：
ALTER TABLE `txxx` MODIFY COLUMN `a` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT '01' |
修改完成后，针对该单表进行迁移
单表迁移配置
vi pg.load.txx load database from mysql://admin_user:admin_user@192.168.56.113:3306/test1 into pgsql://admin_user:admin_user@192.168.56.114:5432/tdb; INCLUDING ONLY TABLE NAMES MATCHING ' txxx'; |
pgloader pg.load.txx |
Varchar(0)字段处理
在迁移过程中发现某个表的字段定义为varchar(0)，导致pgloader 迁移失败。
QUERY: CREATE TABLE t1 ( ...... a varchar(2), b varchar(0), ...... ); |
修改字段长度后，进行单表迁移
单表迁移配置
vi pg.load.t1 load database from mysql://admin_user:admin_user@192.168.56.113:3306/test1 into pgsql://admin_user:admin_user@192.168.56.114:5432/tdb; INCLUDING ONLY TABLE NAMES MATCHING ' t1'; |
pgloader pg.load.t1 |
自增问题处理
在mysql中某些主键会使用自增功能AUTO_INCREMENT，但是在postgres中自增特性的字段类型是serial,因此如果使用postgres中的自增功能需要修改表字段类型。在不改变字段类型的情况下来实现postgres中的自增，需要引入序列。因此需要注意序列的权限。
Mysql 表
CREATE TABLE `undo_log` ( `id` bigint(20) NOT NULL AUTO_INCREMENT, `branch_id` bigint(20) NOT NULL, `xid` varchar(100) COLLATE utf8mb4_bin DEFAULT NULL, `context` varchar(128) COLLATE utf8mb4_bin DEFAULT NULL, `rollback_info` longblob NOT NULL, `log_status` int(11) NOT NULL, `log_created` datetime DEFAULT NULL, `log_modified` datetime DEFAULT NULL, `LAST_UPDATE_TIME` timestamp NULL , PRIMARY KEY (`id`) USING BTREE, UNIQUE KEY `ux_undo_log` (`xid`,`branch_id`) USING BTREE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin; |
Postgres 表
CREATE TABLE undo_log ( id bigint NOT NULL, branch_id bigint NOT NULL, xid character varying(100), context character varying(128), rollback_info bytea NOT NULL, log_status bigint NOT NULL, log_created timestamp(6) without time zone, log_modified timestamp(6) without time zone, last_update_time timestamp(6) without time zone ); CREATE SEQUENCE undo_log_id_seq START WITH 1 INCREMENT BY 1 NO MINVALUE NO MAXVALUE CACHE 1; ALTER TABLE undo_log_id_seq OWNER TO pms3; ALTER SEQUENCE undo_log_id_seq OWNED BY undo_log.id; ALTER TABLE ONLY undo_log ALTER COLUMN id SET DEFAULT nextval('undo_log_id_seq'::regclass); ALTER TABLE ONLY undo_log ADD CONSTRAINT idx_23386_primary PRIMARY KEY (id); CREATE UNIQUE INDEX idx_23386_ux_undo_log ON undo_log USING btree (xid, branch_id); CREATE INDEX ux_undo_log ON undo_log USING btree (xid, branch_id); GRANT SELECT ON TABLE undo_log TO app_fxtb; GRANT SELECT,INSERT,DELETE,UPDATE ON TABLE undo_log TO app_psr; GRANT SELECT,INSERT,DELETE,UPDATE ON TABLE undo_log TO mid_psr; GRANT SELECT,UPDATE ON SEQUENCE undo_log_id_seq TO mid_psr; GRANT SELECT,UPDATE ON SEQUENCE undo_log_id_seq TO app_psr; GRANT SELECT,UPDATE ON SEQUENCE undo_log_id_seq TO app_fxtb; |
视图对象处理
权限脚本
性能问题处理
在迁移过程中针对大表数据迁移，会出现内存耗尽现象，该现象常常发生在迁移存在大字段表的过程中。由于每个线程默认读取一定行数的数据，如果行中存在大字段，会导致内存消耗很多，严重得情况下内存耗尽，比如在迁移test1数据时会出现Heap exhausted （no more space for allocation）等报错。因此需要针对大表进行专门性能调优
1：在内存比较充足的服务器上部署pgloader；2：修改每次读取的行数。具体如下：
vi pg.load.a load database from mysql://admin_user:admin_user@192.168.56.113:3306/test1 into pgsql://admin_user:admin_user@192.168.56.114:5432/tdb; WITH include drop, create tables, create indexes, reset sequences, batch rows=3000,batch concurrency=1,prefetch rows=10000; |
pgloader --verbose pg.load.a >> pg.load.a.log 2>&1 |
对象验证
查看表数量
本脚统计的是tdb数据库下所有表对象的数量。如果是其他实例，请输入对应的database
SELECT table_schema, COUNT ( * ) FROM information_schema.tables d WHERE d.table_catalog = 'tdb' and d.table_schema not in('information_schema','pg_catalog','public') GROUP BY table_schema order by table_schema |
查看视图数量
本脚统计的是tdb数据库下所有视图对象的数量。如果是其他实例，请输入对应的database
SELECT table_schema, COUNT ( * ) FROM information_schema.views d WHERE d.table_catalog = 'tdb' and d.table_schema not in('information_schema','pg_catalog','public') GROUP BY table_schema order by table_schema |
查看存储过程，函数数量
SELECT count(*),routine_schema,routine_type FROM information_schema.routines WHERE ROUTINE_SCHEMA NOT IN ( 'pg_catalog', 'public', 'information_schema' ) group by routine_schema,routine_type order by routine_schema,routine_type |
查看序列数量
SELECT COUNT ( * ), sequence_schema FROM information_schema.sequences d WHERE d.sequence_catalog = 'tdb' GROUP BY sequence_schema ORDER BY sequence_schema |
查看触发器数量
SELECT COUNT ( * ), TRIGGER_SCHEMA FROM information_schema.triggers WHERE TRIGGER_CATALOG = 'tdb' GROUP BY TRIGGER_SCHEMA order by TRIGGER_SCHEMA |
数据验证
验证每个表的数据量
参考脚本：<postgresql获得指定schema下表的精确行数.sql>
