mysql数据迁移至postgres数据库工具之pgloader

1 、概述

本章的主要内容是mysql至postgresql数据库的迁移记录。

2、创建迁移账户

1.mysql数据库安装就位，创建dba权限的账户
CREATE USER 'dba_user'@'%' IDENTIFIED BY '123456!';
-- 授予所有数据库的所有权限（类似root）
GRANT ALL PRIVILEGES ON *.* TO 'dba_user'@'%' WITH GRANT OPTION;

2、postgres数据库安装就位，创建dba权限的账户
CREATE USER app_user WITH PASSWORD '123456';
授权：
ALTER USER app_user WITH SUPERUSER;

3、创建目标库

创建目标库
CREATE DATABASE testdb WITH ENCODING 'UTF8' LC_COLLATE 'C' LC_CTYPE 'C' TEMPLATE template0;

4、整理迁移工具镜像

1、Arm镜像：

注意：下载系统镜像，在镜像里下载pgloader即可

Docker pull arm64v8/ubuntu:24.04
apt update
apt install pgloader

2、X86镜像：
docker pull dimitri/pgloader:latest

5、整理迁移文件

mkdir /root/pgloader
[root@-ser-125 pgloader]# cat migrate.load 
LOAD DATABASE
  FROM mysql://dba_user:123456@10.0.0.121:3306/testdb1
  INTO pgsql://app_user:123456@10.0.0.121:5432/dbtest

WITH include drop, create tables, create indexes, reset sequences,
     workers = 8, concurrency = 1,batch rows = 1000,batch size = 50 MB,
     prefetch rows = 1000,workers = 2,concurrency = 1,max parallel create index = 2

SET PostgreSQL PARAMETERS
    maintenance_work_mem to '512MB',
    work_mem to '128MB'

SET MySQL PARAMETERS
    net_read_timeout = '120',
    net_write_timeout = '120'

ALTER SCHEMA 'testdb1' RENAME TO 'test_db1'
;
-----------------------------------------------
注释：
    batch rows = 1000,      -- 减小批处理大小
    batch size = 50 MB,     -- 减小批处理尺寸
    prefetch rows = 1000,   -- 减小预取行数
    workers = 2,            -- 减少工作线程
    concurrency = 1,        -- 降低并发度
    multiple readers = false, -- 禁用多读取器
    max parallel create index = 2  -- 限制并行创建索引

ALTER SCHEMA 'testdb1' RENAME TO 'test_db1' -- 将mysql中testdb1库迁移至dbtest库下的test_db1模式中，数据在这个模式内，不在默认的public中。即将库迁移到库下的模式中，

这类的旨在将mysql中多个库以模式的形式存在于postgres库中，则一个库下有多个模式。

或者：ALTER SCHEMA 'testdb1' RENAME TO 'public' -- 即默认迁移到库下的默认模式内，这类的旨在保持两个数据库的库保持一致。

5、数据同步脚本

[root@-ser-125 pgloader]# cat sync.sh 
docker run --rm -it --memory="4g" --memory-swap="4g"  -v $(pwd):/data  dimitri/pgloader:latest  pgloader -v /data/migrate.load

6、查看同步结果

 

7、后续处理

创建用户
create user test_db1 with password '123456';
--更新模式所有者
ALTER SCHEMA test_db1 OWNER TO postgres ;
--授权
GRANT CONNECT on DATABASE dbtest TO test_db1;
grant create,usage on schema test_db1  to test_db1 ;

--更新表所有者，在对应的库下的模式下操作

DO $$
DECLARE
    r record;
    i int;
    v_schema text[] := '{test_db1}';
    v_new_owner varchar := 'test_db1';
BEGIN
    FOR r IN
        SELECT 'ALTER TABLE "' || table_schema || '"."' || table_name || '" OWNER TO ' || v_new_owner || ';' AS a FROM information_schema.tables WHERE table_schema = ANY (v_schema)
        UNION ALL
        SELECT 'ALTER TABLE "' || sequence_schema || '"."' || sequence_name || '" OWNER TO ' || v_new_owner || ';' AS a FROM information_schema.sequences WHERE sequence_schema = ANY (v_schema)
        UNION ALL
        SELECT 'ALTER TABLE "' || table_schema || '"."' || table_name || '" OWNER TO ' || v_new_owner || ';' AS a FROM information_schema.views WHERE table_schema = ANY (v_schema)
        UNION ALL
        SELECT 'ALTER FUNCTION "' || nsp.nspname || '"."' || p.proname || '"(' || pg_get_function_identity_arguments(p.oid) || ') OWNER TO ' || v_new_owner || ';' AS a FROM pg_proc p JOIN pg_namespace nsp ON p.pronamespace = nsp.oid WHERE p.prokind='f' and nsp.nspname = ANY (v_schema)
         UNION ALL
        SELECT 'ALTER PROCEDURE "' || nsp.nspname || '"."' || p.proname || '"(' || pg_get_function_identity_arguments(p.oid) || ') OWNER TO ' || v_new_owner || ';' AS a FROM pg_proc p JOIN pg_namespace nsp ON p.pronamespace = nsp.oid WHERE p.prokind='p' and  nsp.nspname = ANY (v_schema)
        LOOP
        EXECUTE r.a;
    END LOOP;
END
$$;

8、 检查特殊对象

除了信息注册编号业务外其他序列替换成uuid；

将视图删除，已完成适配调整；

其他数据类型的检查，比如时间类型、默认值等。

9、 检查数据一致性

关键数据库表检查数据量大小;