使用Pgloader将MySQL迁移到PostgreSQL详细教程

0. 前言

pgloader 是一个专为 PostgreSQL 设计的数据加载工具，主要使用 COPY命令。其优越性主要体现在事务行为，pgloader 会保留一个包含被拒绝数据的独立文件，但同时会继续尝试在你的数据库中复制正常的数据。相比之下，PostgreSQL 的默认行为是事务性的，任何输入数据（文件或远程数据库）中的错误行都会导致整个表的批量加载中止。

pgloader 还能够实现数据的重格式化。例如，它能将 MySQL 的日期时间戳 0000-00-00 和 0000-00-00 00:00:00 转换为 PostgreSQL 的 NULL 值，因为我们的日历系统从未有过零年。

使用 pgloader，你可以提供一个命令文件，或者直接从命令行运行。更多的使用详情请查看 快速入门。

Pgloader 的错误处理机制使得任何遇到的错误不会打断迁移过程。它会继续迁移数据，并在最后提供一个详细的错误报告，帮助你了解并解决问题。 pgloader可以并行处理多个表的迁移，大大提高了迁移速度。同时，它还支持增量迁移，你可以在初次完整迁移后，再次运行 Pgloader，只迁移源数据库中的新增数据。这无疑大大提高了迁移的效率。

1. 安装和操作示例

Pgloader 还使用了批量处理技术，可以在不消耗过多内存的情况下迁移大量数据。

$ createdb newdb
$ pgloader ./test/sqlite/sqlite.db postgresql:///newdb

或者我们要从 MySQL 进行完全迁移，包括架构定义（表，索引，外键，注释）和修正数据的并行加载：

$ createdb pagila
$ pgloader mysql://user@localhost/sakila postgresql:///pagila

1.1 linux安装

如果你想安装 pgloader，它的完整安装文档可以在 https://pgloader.readthedocs.io/ 上查看。如果你正在使用 debian 系统，只需执行下面的命令即可安装：

$ apt-get install pgloader

1.2 docker安装

如果你正在使用 docker，你可以使用 CI 在每次提交到 master 分支时构建的最新版本：

$ docker pull ghcr.io/dimitri/pgloader:latest
$ docker run --rm -it ghcr.io/dimitri/pgloader:latest pgloader --version

2. 教程

Pgloader 的简单使用教程

2.1 MySQL迁移教程

1. 安装 Pgloader：你可以直接在你的 Linux 系统中使用包管理器来安装 Pgloader，例如在 Ubuntu 系统中，你可以使用以下命令来安装：

   sudo apt-get install pgloader
   

2. 准备你的迁移文件：你需要编写一个 .load 文件，来描述你的迁移需求。例如：cc.load

   load database
       from 'mysql://user:password@localhost/dbname'
       into 'postgres://user:password@localhost/dbname'
   
   CAST type datetime to timestamptz drop default drop not null using zero-dates-to-null,
        type date drop not null drop default using zero-dates-to-null,
   
   WITH create tables, create indexes, reset sequences,
        foreign keys
   
   SET maintenance_work_mem to '128MB', work_mem to '12MB'
   

在命令行中直接包含密码可能存在安全风险，因此通常建议使用其他方式来提供密码。在 pgloader 的情况下，你可以在命令行中省略密码，然后 pgloader 会提示你输入密码。

不过，如果你确实需要在命令行中提供密码，可以按照以下格式指定数据库 URL：

postgresql://username:password@localhost/database

或者对于 MySQL：

mysql://username:password@localhost/database

这样，你就可以在 pgloader 命令中包含密码了。例如：

$ pgloader mysql://user:password@localhost/sakila postgresql://user:password@localhost/pagila

但请注意，这种方式可能会让密码在命令历史或日志文件中留下记录，因此请确保你理解相关风险，并在必要时采取适当的安全措施。
3. 运行 Pgloader：你可以使用以下命令来运行 Pgloader：

 pgloader cc.load

完整的文档可以在线查看，包括所有 pgloader 子命令的手册页。请查看 https://pgloader.readthedocs.io/。

$ pgloader --help
pgloader [选项 ... ] 源 目标
  --help -h                       布尔值  显示使用方法并退出。
  --version -V                    布尔值  显示 pgloader 版本并退出。
  --quiet -q                      布尔值  安静模式
  --verbose -v                    布尔值  详细模式
  --debug -d                      布尔值  显示调试级别信息。
  --client-min-messages           字符串   控制台看到的日志过滤器（默认："warning"）
  --log-min-messages              字符串   日志文件中看到的日志过滤器（默认："notice"）
  --summary -S                    字符串   将摘要复制到的文件名
  --root-dir -D                   字符串   输出根目录。（默认：#P"/tmp/pgloader/"）
  --upgrade-config -U             布尔值  输出对应于 v2.x 的 .conf 文件的命令
  --list-encodings -E             布尔值  列出 pgloader 已知的编码并退出。
  --logfile -L                    字符串   发送日志的文件名。
  --load-lisp-file -l             字符串   从文件中读取用户代码
  --dry-run                       布尔值  只检查数据库连接，不加载任何东西。
  --on-error-stop                 布尔值  不处理错误。
  --no-ssl-cert-verification      布尔值  指示 OpenSSL 不验证证书。
  --context -C                    字符串   命令上下文变量
  --with                          字符串   加载选项
  --set                           字符串   PostgreSQL 选项
  --field                         字符串   源文件字段规范
  --cast                          字符串   特定的转换规则
  --type                          字符串   强制输入源类型
  --encoding                      字符串   预期的源编码
  --before                        字符串   加载数据前运行的 SQL 脚本
  --after                         字符串   加载数据后运行的 SQL 脚本
  --self-upgrade                  字符串   pgloader 更新源的路径
  --regress                       布尔值  驱动回归测试

pgloader 快速入门

2.2 SQLite 进行完全迁移：

$ createdb newdb
$ pgloader ./test/sqlite/sqlite.db postgresql:///newdb

或者从 MySQL 进行完全迁移，包括架构定义（表，索引，外键，注释）和修正数据的并行加载：

$ createdb pagila
$ pgloader mysql://user@localhost/sakila postgresql:///pagila