mysql8迁移到postgre16

1. 环境

• ubuntu24.04
• mysql8.3.0
• postgre 16.3

2. pgloader迁移

2.1. 安装工具

# ubuntu 此 系统易出错, 建议使用源码编译安装
sudo apt install -y pgloader

# rocky/centos/rhel等
sudo dnf install -y pgloader

# 其它安装方式请自行查询

# 查看版本号
pgloader -V

# 源码编译安装(本人未安装完成)
apt remove -y pgloader 
apt install -y sbcl unzip libsqlite3-dev make curl gawk freetds-dev libzip-dev

git clone https://github.com/dimitri/pgloader.git
cd pgloader
make pgloader

# 全局安装 , 可以不安装直接使用路径使用
make install 

# 直接路径使用命令
/app/test/pgloader/build/bin/pgloader -V
pgloader --debug mysql://root:'123'@127.0.0.1/nextcloud_storage postgresql://postgres:pass@127.0.0.1:5432/nextcloud_storage

# 使用docker
podman pull ghcr.io/dimitri/pgloader:latest

2.2. 创建迁移脚本

非必须步骤, 可以直接使用命令

2.2.1. 最简配置

# postgre中创建数据库
CREATE DATABASE zongheng;

# 注: 这里linux下一键建立的脚本, 如不需要请去掉首尾行
cat > mysql_2_postgre.load << "EOF"
load database
from mysql://ccz:password@localhost:3306/zongheng
into pgsql://postgres:password@localhost:5432/zongheng;
EOF

2.2.2. 设定转换规则

load database
from mysql://ccz:password@172.168.10.247:3306/zongheng
into pgsql://postgres:password@172.168.10.247:5432/zongheng

WITH workers = 8, prefetch rows = 5000, batch rows = 2000, concurrency = 4, batch concurrency = 4, batch size=3MB, multiple readers per thread

SET maintenance_work_mem to '6000MB', work_mem to '256MB'
;

在迁移过程中，可能需要根据实际情况调整迁移脚本中的选项。例如，如果需要迁移的数据表中包含存储过程、触发器等，可能需要添加相应的选项来处理这些对象。具体可以参考pgloader文档。

2.3. 执行迁移

2.4. 使用配置执行

# 命令执行
pgloader mysql_2_postgre.load

# docker执行
podman run --rm -it -v /app/test/:/app/test/ --memory=8G ghcr.io/dimitri/pgloader:latest pgloader --dynamic-space-size 8000 --debug --verbose /app/test/mysql_2_postgre.load

2.4.1. 直接执行命令

# docker 执行
podman run --rm -it ghcr.io/dimitri/pgloader:latest pgloader --debug --verbose mysql://ccz:password@172.168.10.247:3306/zongheng postgresql://postgres:password@172.168.10.247:5432/zongheng

# 本地命令执行
pgloader --debug --verbose mysql://ccz:password@localhost:3306/zongheng postgresql://postgres:password@localhost:5432/zongheng

完成后请检查数据

3. 可能问题

#Condition QMYND:MYSQL-UNSUPPORTED-AUTHENTICATION was signalled
pgloader不支持caching_sha2_password
在/etc/my.cnf 中增加 default-authentication-plugin=mysql_native_password
重启mysql

# ERROR mysql: 76 fell through ECASE expression
编辑 ostgresql.conf, find the line password_encryption = scram-sha-256, change it to password_encryption = md5.
Edit pg_hba.conf, change all the scram-sha-256 to md5 for the listening address.
Restart the postgresql server.
Launch psql, change the password for the user I used for import \password import_user.

# 执行内存异常, 
命令后面加参数 --dynamic-space-size 8000