最后更新于

从MySQL到PostgreSQL的迁移工具

分类： mysql postgresql 数据库

以前项目多是基于MySQL数据库进行开发，最近有需求需要兼容更多类型的数据库，比如PostgreSQL。那么如何实现快速的数据库表结构迁移，甚至是包括数据迁移呢？这里极力推荐使用开源工具PGLoader，它可以完美实现从MySQL到PostgreSQL的表结构，包括主键、索引、自增序列迁移，同时支持数据迁移。

🌐 PGLoader官网

PGLoader官方源码： https://github.com/dimitri/pgloader

PGLoader官方文档： https://pgloader.readthedocs.io/en/latest/pgloader.html

🐳 PGLoader的安装

这里我们使用Docker安装：

docker run --rm -it ghcr.io/dimitri/pgloader:3.6.9 pgloader --version
docker run --rm -it ghcr.io/dimitri/pgloader:3.6.9 pgloader --help

当前最新版本是3.6.9，可以根据需要自行修改。

🚀 使用PGLoader迁移数据

方法1：直接一条命令执行

docker run --rm -it ghcr.io/dimitri/pgloader:3.6.9 \
pgloader  mysql://user1:xxxxxx@10.10.0.22:3306/db_test1 \
pgsql://user1:xxxxxx@10.10.0.23:5432/db_test1

这种方法直接明了，但是缺乏定制操作，当数据量大时就会报错而中断。

方法2：使用load文件

先建个临时目录用于Docker挂载（官方文档或网上其它资料没说这个步骤，但我试了一直说找不到文件，最后还是用这个方法解决）：

mkdir /root/tmpdir
cd /root/tmpdir

编辑load文件：

vi my.load

配置内容：

LOAD DATABASE
         FROM      mysql://user1:xxxxxx@10.10.0.22:3306/db_test1
         INTO      pgsql://user1:xxxxxx@10.10.0.23:5432/db_test1
with prefetch rows = 10000
WITH include drop, create tables, create indexes, workers = 8, concurrency = 1
ALTER SCHEMA 'db_test1' RENAME TO 'public'
;

配置说明：

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

执行迁移：

docker run -v /root/tmpdir:/tmp --rm -it ghcr.io/dimitri/pgloader:3.6.9 \
pgloader  /tmp/my.load

📋 高级配置示例

更多load文件配置可参考以下例子：

LOAD DATABASE
     FROM      mysql://root@localhost/sakila
     INTO postgresql://localhost:54393/sakila

 WITH include drop, create tables, create indexes, reset sequences,
      workers = 8, concurrency = 1,
      multiple readers per thread, rows per range = 50000

  SET PostgreSQL PARAMETERS
      maintenance_work_mem to '128MB',
      work_mem to '12MB',
      search_path to 'sakila, public, "$user"'

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

 CAST type bigint when (= precision 20) to bigserial drop typemod,
      type date drop not null drop default using zero-dates-to-null,
      -- type tinyint to boolean using tinyint-to-boolean,
      type year to integer

 MATERIALIZE VIEWS film_list, staff_list

 -- INCLUDING ONLY TABLE NAMES MATCHING ~/film/, 'actor'
 -- EXCLUDING TABLE NAMES MATCHING ~<ory>
 -- DECODING TABLE NAMES MATCHING ~/messed/, ~/encoding/ AS utf8
 -- ALTER TABLE NAMES MATCHING 'film' RENAME TO 'films'
 -- ALTER TABLE NAMES MATCHING ~/_list$/ SET SCHEMA 'mv'

 ALTER TABLE NAMES MATCHING ~/_list$/, 'sales_by_store', ~/sales_by/
  SET SCHEMA 'mv'

 ALTER TABLE NAMES MATCHING 'film' RENAME TO 'films'
 ALTER TABLE NAMES MATCHING ~/./ SET (fillfactor='40')

 ALTER SCHEMA 'sakila' RENAME TO 'pagila'

 BEFORE LOAD DO
   $$ create schema if not exists pagila; $$,
   $$ create schema if not exists mv;     $$,
   $$ alter database sakila set search_path to pagila, mv, public; $$;

该配置来自官方文档： https://pgloader.readthedocs.io/en/latest/ref/mysql.html#using-advanced-options-and-a-load-command-file

相关文章