如何将MySQL数据库迁移到PostgreSQL？

PGLoader

Pgloader 是一个支持任意数据库迁移至 pgsql 的命令行工具。使用 COPY 流式传输协议将数据加载到 PostgreSQL 中，并使用单独的线程来读取和写入数据。

安装

安装有多种方式，Docker 安装、安装包安装、源码安装等。

这里我推荐使用源码安装，因为其他方式都可能会遇到各式各样的问题，但是源码安装我一遍就成功了。

步骤：（本人系统为 Centos）

1. clone 项目。

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

1. 进入项目目录，执行 bootstrap-centos7.sh（脚本会自动帮你安装各种依赖）

cd pgloader

chmod +x ./bootstrap-centos7.sh

./bootstrap-centos7.sh

1. 编译

make pgloader

1. 检查是否安装成功

./build/bin/pgloader --version

./build/bin/pgloader --help

数据处理

本人情况：MySQL 和 PostgreSQL 都在一台机器上。（如果是其他情况的可以看看下面其他博主的一些文章）

1. 编写脚本命令。

vim pgload.load

LOAD DATABASE
 FROM mysql://root:password@localhost:3306/temp_db
 INTO pgsql://postgres:password@localhost:5432/target_db

 WITH include drop, create tables, create indexes, workers = 8, concurrency = 1

ALTER SCHEMA 'temp_db' RENAME TO 'public'
;

复制

1. 执行脚本。

./build/bin/pgloader pgload.load

1. 检查是否成功。

脚本命令解释

• LOAD
  DATABASE 指定从数据库加载
• FROM
  源数据库
• INTO
  目标数据库
• WITH
  指定 PGLoader
  行为
• include drop
  ，迁移过程中，PGLoader 会删除目标 PostgreSQL 数据库中在源数据库中同名的任何表。注意备份。
• create tables
  , 配置 PGLoader 根据源数据库数据在目标数据库中创建新表，如果使用 create no - - tables，则需要手动在目标数据库中创建好对应的表。
• ALTER SCHEMA
  , 在 WITH 语句之后，配置特定 SQL 来告诉 PGLoader 执行其他操作。

详情见官方文档...

遇到的问题

MySQL的datetime转换为PostgreSQL无时区的timestamp

时间类型：MySQL 中是 datetime
，转换到 PostgreSQL 为 timestamp with time zone
，它变成带时区的时间了。

需求是不需要带上时区，因为转换会默认使用当前本机的时区，也可以指定时区。

这个问题在 github 找到了相关反馈。

https://github.com/dimitri/pgloader/issues/317

在脚本中加上指定的类型转换即可。

type datetime to timestamp drop default drop not null using zero-dates-to-null

完整脚本

LOAD DATABASE
 FROM mysql://root:MC+2020!@localhost:3306/taas_core
 INTO pgsql://postgres:jetlinks@localhost:5433/taas_core

 WITH include drop, create tables, create indexes, workers = 8, concurrency = 1
      
    CAST type datetime to timestamp drop default drop not null using zero-dates-to-null

ALTER SCHEMA 'taas_core' RENAME TO 'public'
;

复制

参考

官方文档：Migrating a MySQL Database to PostgreSQL

https://pgloader.readthedocs.io/en/latest/ref/mysql.html

空想具現化：pgloader 安装 & 使用

https://blog.homurax.com/2019/10/16/install-and-use-pgloader/

Ein Verne ：从 MySQL 迁移到 PostgreSQL 方案调研

https://einverne.github.io/post/2019/09/migrate-from-mysql-to-postgresql.html