pgloader的简单数据迁移教程
pgloader是一个用于其他数据库数据迁移到postgres数据库的工具,目前支持了多种数据库。
官方说明文档:https://pgloader.readthedocs.io/en/latest/index.html
项目地址:https://github.com/dimitri/pgloader/
文章目录
- 一、安装运行环境
- 一、数据迁移测试
- 迁移方法一
- 迁移方法二
一、安装运行环境
操作系统:ubuntu22.04
mysql版本:8.0.36
poetgres版本:17.0
pgloader版本:3.6.9
注意:
1.pgloader目前还是不支持mysql的9.x版本,因为pgloader目前不支持caching_sha2_password身份验证插件,只支持mysql_native_password。但是mysql从9开始已经弃用了mysql_native_password,所以pgloader会连不上mysql 9.x。
2.mysql8开始会默认使用caching_sha2_password身份验证插件,但mysql_native_password还没有弃用,因此需要手动选择使用mysql_native_password。
操作方法可以参考:https://github.com/dimitri/pgloader/issues/782
https://blog.csdn.net/jimn2000/article/details/142237825
3.pgloader建议使用使用源码编译安装,版本尽量选择最新的。
cd /path/to/pgloader
make pgloader
./build/bin/pgloader --help
否则可能会出现下面的问题
https://github.com/dimitri/pgloader/issues/1211
一、数据迁移测试
我们以sakila这个经典的数据库作为例子
下载地址:https://dev.mysql.com/doc/index-other.html
下载tgz包解压后导入mysql数据库
mysql -p < sakila-schema.sql
mysql -p < sakila-data.sql
在postgres上新建一个sakila数据库
create database sakila;
迁移方法一
进入pgloader安装目录,使用下面的命令将sakila数据从mysql迁移到mysql
./build/bin/pgloader mysql://root:123456@localhost/sakila postgresql://postgres:123456@localhost/sakila
如果运行后出现问题,可以加参数-d,这是以debug模式运行,便于查找问题,更多的参数可以用poloader -help查看。例如我们pgloader3.6.1版本运行后打印该错误
再加-d参数运行发现在调用address时找不到astext函数了,查了一下原来是astext函数在mysql8已经被移除了,改为使用ST_AsText函数。pgloader后面在新的版本中修改了这个问题。详情可以查看:https://github.com/dimitri/pgloader/issues/1029
迁移方法二
我们也可以用一个配置文件的形式来使用pgloader,官方文档中也举了这个例子,我就直接copy下来了。创建一个my.load文件,内容为:
LOAD DATABASEFROM mysql://root@localhost/sakilaINTO postgresql://localhost:54393/sakilaWITH include drop, create tables, create indexes, reset sequences,workers = 8, concurrency = 1,multiple readers per thread, rows per range = 50000SET PostgreSQL PARAMETERSmaintenance_work_mem to '128MB',work_mem to '12MB',search_path to 'sakila, public, "$user"'SET MySQL PARAMETERSnet_read_timeout = '120',net_write_timeout = '120'CAST type bigint when (= precision 20) to bigserial drop typemod,type date drop not null drop default using zero-dates-to-null,-- type tinyint to boolean using tinyint-to-boolean,type year to integerMATERIALIZE VIEWS film_list, staff_list-- INCLUDING ONLY TABLE NAMES MATCHING ~/film/, 'actor'-- EXCLUDING TABLE NAMES MATCHING ~<ory>-- DECODING TABLE NAMES MATCHING ~/messed/, ~/encoding/ AS utf8-- ALTER TABLE NAMES MATCHING 'film' RENAME TO 'films'-- ALTER TABLE NAMES MATCHING ~/_list$/ SET SCHEMA 'mv'ALTER TABLE NAMES MATCHING ~/_list$/, 'sales_by_store', ~/sales_by/SET SCHEMA 'mv'ALTER TABLE NAMES MATCHING 'film' RENAME TO 'films'ALTER TABLE NAMES MATCHING ~/./ SET (fillfactor='40')ALTER SCHEMA 'sakila' RENAME TO 'pagila'BEFORE LOAD DO$$ create schema if not exists pagila; $$,$$ create schema if not exists mv; $$,$$ alter database sakila set search_path to pagila, mv, public; $$;
使用如下命令进行数据迁移
./build/bin/pgloader my.load
但直接运行这个配置文件还是会报错
问题说创建file_list表的时候没有pagila.film_list_rating这个类型。解决这个问题需要知道的是pgloader目前是不支持视图迁移的,但是支持将源数据库的视图转为实体表然后再迁移,而file_list就是sakila的一个视图。另外,postgres多了一层模式(Schema)这个概念,一个数据库下面会有多个模式,模式下面才是表、函数、视图。
my.load这个配置文件会把源数据库的视图创建成实体表,再保存到目标数据库的MV这个模式下,而MV这个模式没有pagila.film_list_rating这个类型,那pagila.film_list_rating这个类型是什么呢?我们查看源数据的my_list这个视图,再找到这个视图所用的表,发现file_list_rating是一个枚举类型
具体问题找到了,解决办法就是我们先到postgres数据库的mv模式下面手动创建file_list_rating这个类型,然后再进行迁移,这样就没有报错了。
# 连接sakila数据库
\c sakila
# 使用mv模式
set search_path to mv;
# 创建pagila.film_list_rating类型
CREATE TYPE pagila.film_list_rating AS ENUM ('G','PG','PG-13','R','NC-17');