为 GPT-Load、New-API 更换数据库：我是如何顺利将 MySQL 迁移到 PostgreSQL 的

发表于 更新于 分类于 阅读次数：

自部署了 T 佬的 GPT-Load 和 New API，一开始使用的是 MySQL，想迁移到 PostgreSQL，因此轻微折腾了一下，过程极其顺利，稍微写一篇记录一下。

相关版本：

  • MySQL：8.3.0
  • PostgreSQL 16.9.0
  • pgloader：3.6.7~devel

该方案理论适合多数项目服务的迁移，细节方面需要根据实际情况做调整，只在 T 佬的 GPT-Load 和 New API 测试操作成功，运行了一段时间也没有问题。

1.1 前置条件

  • 执行迁移操作的机器，需要能访问 MySQL 和 PostgreSQL
  • 提前准备好迁移时使用的用户，这里 MySQL 用的是 migration，仅授予读权限，PostgreSQL 则临时用了超管 postgres
  • 执行迁移前，记得先将相关服务停掉，例如迁移 New API，就将 New API 服务停一下，迁移后，修改相关配置信息，连接到新的数据库即可。

1.2 迁移执行命令脚本

pgloader 工具可用二进制文件，也可以使用 docker，我这里采用的是 docker，过程可能需要多次执行，所以命令写成了脚本 migrate.sh：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
#!/bin/bash

docker run --rm \
  -e MYSQL_USER=$MYSQL_USER \
  -e MYSQL_PASSWORD=$MYSQL_PASSWORD \
  -e MYSQL_HOST=$MYSQL_HOST \
  -e MYSQL_PORT=$MYSQL_PORT \
  -e MYSQL_DATABASE=$MYSQL_DATABASE \
  -e PGSQL_USER=$PGSQL_USER \
  -e PGSQL_PASSWORD=$PGSQL_PASSWORD \
  -e PGSQL_HOST=$PGSQL_HOST \
  -e PGSQL_PORT=$PGSQL_PORT \
  -e PGSQL_DATABASE=$PGSQL_DATABASE \
  -v ./migrate.load:/app/migrate.load \
  dimitri/pgloader pgloader /app/migrate.load

1.3 迁移使用的 load 配置文件

再准备需要映射到容器内部的 load 文件：migrate.load

通过 AI 生成 + 自己修改了一下

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
-- ====================================================================
-- pgloader Command File for MySQL to PostgreSQL Migration
-- Version: 2.0 (Robust & Well-Commented)
-- ====================================================================

LOAD DATABASE

    -- ------------------------------------------------
    -- Section 1: 数据库连接信息 (必须修改)，运行前需要先设置好环境变量。
    -- ------------------------------------------------
    -- 源数据库: MySQL
    FROM mysql://{{MYSQL_USER}}:{{MYSQL_PASSWORD}}@{{MYSQL_HOST}}:{{MYSQL_PORT}}/{{MYSQL_DATABASE}}

    -- 目标数据库: PostgreSQL
    INTO postgresql://{{PGSQL_USER}}:{{PGSQL_PASSWORD}}@{{PGSQL_HOST}}:{{PGSQL_PORT}}/{{PGSQL_DATABASE}}

    -- 正确的位置：此指令必须在 LOAD DATABASE 块内部，作为对目标（INTO）的修饰
    -- 它告诉 pgloader 在加载完成后，将它创建的 {{MYSQL_DATABASE}} schema 重命名为 'public'
    ALTER SCHEMA '{{MYSQL_DATABASE}}' RENAME TO 'public'

WITH
    -- ---------------------------------------------------------------------
    -- Section 2: 核心迁移选项 (通用配置，可根据需求微调)
    -- ---------------------------------------------------------------------

    -- 清理目标库：在创建新表前，先删除 PostgreSQL 中已存在的同名表。
    -- 警告：这是一个破坏性操作，请确保目标数据库是空的或可以被覆盖！
    include drop,

    -- 结构迁移：控制要创建的数据库对象
    create tables,           -- 创建表结构
    create indexes,          -- 在数据加载后创建索引 (推荐)
    reset sequences,         -- 将序列 (自增ID) 的值重置为当前表的最大值
    foreign keys,            -- 创建外键约束

    -- 性能调优：通过并发来加速迁移
    workers = 4,             -- 并发的工作进程数，建议设置为 CPU 核心数的 1-2 倍
    concurrency = 4,         -- 每个工作进程中并发处理的表数量
    batch size = 25MB       -- 单个事务中处理的数据量大小

-- ---------------------------------------------------------------------
-- Section 3: (可选) 自定义类型转换规则 (CAST)
-- 如果 pgloader 的默认转换不满足需求，可以在这里自定义规则。
-- 默认注释掉，有需要时再开启并修改。
-- ---------------------------------------------------------------------
CAST
    -- 将 MySQL 的 tinyint(1) 转换为 PostgreSQL 的 boolean 类型
    -- type tinyint when (= 1 precision) to boolean,

    -- 将所有名为 'status' 且类型为 ENUM 的列转换为 PostgreSQL 的 TEXT 类型
    -- column "status" type enum to text

    -- 将包含 JSON 字符串的 text/varchar 列直接转换为 jsonb 类型，可预先通过以下 SQL 语句查询哪些字段需要
    -- SELECT TABLE_NAME, COLUMN_NAME, DATA_TYPE, COLUMN_TYPE FROM information_schema.COLUMNS
    --   WHERE TABLE_SCHEMA = '数据库名'
    --   AND DATA_TYPE IN ('json','geometry','enum','set','bit');
    column channels.channel_info to jsonb,
    column tasks.data to jsonb,
    column tasks.properties to jsonb

-- 排除不需要迁移的表
-- EXCLUDING TABLE NAMES MATCHING 'logs', 'temp_table'

-- ---------------------------------------------------------------------
-- Section 4: (可选) 迁移后执行的命令 (AFTER LOAD)
-- 在数据加载成功后，在 PostgreSQL 数据库上自动执行一些收尾命令。
-- ---------------------------------------------------------------------
AFTER LOAD DO
    -- 对所有迁移过来的表进行 ANALYZE，更新统计信息。
    -- 这是保证迁移后查询性能的关键步骤，强烈建议执行！
    $$ ANALYZE; $$;

注意该部分：

1
2
3
4
5
6
7
-- 将包含 JSON 字符串的 text/varchar 列直接转换为 jsonb 类型，可预先通过以下 SQL 语句查询哪些字段需要
-- SELECT TABLE_NAME, COLUMN_NAME, DATA_TYPE, COLUMN_TYPE FROM information_schema.COLUMNS
--   WHERE TABLE_SCHEMA = '数据库名'  -- 此处是 new-api
--   AND DATA_TYPE IN ('json','geometry','enum','set','bit');
column channels.channel_info to jsonb,
column tasks.data to jsonb,
column tasks.properties to jsonb

针对不同的 MySQL 执行一次命令，获取 MySQL 数据库中，哪张表的哪个字段的类型使用了 json、enum 等类型，这样可以在使用 pgloader 迁移时，通过 CAST 字段配置转换，上面的 load，是针对 New API 的。

1.4 配置环境变量

这么搞主要是方便发文，不用担心敏感信息暴露。

在执行的操作系统上，配置好需要的环境变量：

  • MYSQL_USER
  • MYSQL_PASSWORD
  • MYSQL_HOST
  • MYSQL_PORT
  • MYSQL_DATABASE
  • PGSQL_USER
  • PGSQL_PASSWORD
  • PGSQL_HOST
  • PGSQL_PORT
  • PGSQL_DATABASE

1.5 大概率会遇到的问题：无法正常连接到 MySQL

报错日志大致如下：

1
2
3
4
5
6
7
8
2025-07-25T15:00:15.112999Z ERROR mysql: Failed to connect to mysql at "192.168.1.185" (port 3306) as user "migration": Condition QMYND:MYSQL-UNSUPPORTED-AUTHENTICATION was signalled.
2025-07-25T15:00:15.112999Z LOG report summary reset
       table name     errors       rows      bytes      total time
-----------------  ---------  ---------  ---------  --------------
  fetch meta data          0          0                     0.000s
-----------------  ---------  ---------  ---------  --------------
-----------------  ---------  ---------  ---------  --------------

报错信息很明显了，主要是因为 MySQL 8.3 默认认证方式为 caching_sha2_password，pgloader 不支持该认证方式。

尝试将 migration 用户的认证方式改为 mysql_native_password 插件，依旧报错误

最后将 MySQL 的默认身份认证插件修改为：mysql_native_password，重启 MySQL，正常连接。