当前位置：首页 > news >正文

pgloader的简单数据迁移教程

news 2024/10/28 20:50:24

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');