Mysql Postgresql Db

从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安装

1
2

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：直接一条命令执行

1
2
3

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挂载（官方文档或网上其它资料没说这个步骤，但我试了一直说找不到文件，最后还是用这个方法解决）

1
2

mkdir /root/tmpdir
cd /root/tmpdir

编辑load文件vi my.load

1
2
3
4
5
6
7

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 执行其他操作。

执行迁移

1
2

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

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

 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

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

Licensed under HiOFD