mysql2pgsql

使用pgloader进行迁移

pgloader是一个强大的数据迁移工具，专为将不同数据库之间的数据迁移到PostgreSQL而设计。它支持从MySQL到PostgreSQL的迁移，并提供了一种简单且灵活的方式来转移数据。

安装pgloader

使用pgloader迁移数据

1、命令行方式

1

2、脚本方式

pgloader load.script
1

以容器方式运行

#下载镜像
docker pull dimitri/pgloader
#后台启动一个容器
docker run -tid --name pgloader_test dimitri/pgloader 
# copy laoder 脚本
docker cp ./mysql2pgsql.load pgloader_test:/ 
#进入容器
docker exec -it pgloader_test /bin/bash
#执行loade 命令
pgloader mysql2pgsql.load
1
2
3
4
5
6
7
8
9
10

使用 py-mysql2pgsql

没有验证

附录

pgloader脚本

语法参考：https://pgloader.readthedocs.io/en/latest/command.html ， https://pgloader.readthedocs.io/en/latest/ref/mysql.html

LOAD <source-type>
FROM <source-url>
       [ HAVING FIELDS <source-level-options> ]
             INTO <postgresql-url>
       [ TARGET TABLE [ "<schema>" ]."<table name>" ]
       [ TARGET COLUMNS <columns-and-options> ]

    [ WITH <load-options> ]

    [ SET <postgresql-settings> ]

[ BEFORE LOAD [ DO <sql statements> | EXECUTE <sql file> ] ... ]
[  AFTER LOAD [ DO <sql statements> | EXECUTE <sql file> ] ... ]
    ;
1
2
3
4
5
6
7
8
9
10
11
12
13
14

LOAD子句

• DATABASE：从数据库加载
• CSV： 从CSV文件加载

FROM子句

指定数据来源，可以是CSV ，或者Mysql等

CSV支持 inline，stdin，filename 等方式。

mysql必须是MYSQL 连接串。

INTO子句

指定目标存储位置，可以指定数据库，表或字段。

WITH子句

指定参数，可以是以下格式：

• key = value
• use option
• do not use option

所有数据源都支持以下参数：

• on error stop, on error resume next
• batch rows = R
• batch size = … MB
• prefetch rows = …

并行控制参数：

• workers = W
• concurrency = C
• max parallel create index = I

一些参数：

include drop：先删除所有已存在的表。不光是在源数据库中的表，还会删除级联的表。

include no drop：不删除任何表。

truncate： 对每个表先进行 truncate 操作。

disable triggers：禁用触发器。

create tables：创建表

create no tables：不创建表。

create indexes：创建索引

schema only：仅表结构

data only：仅数据

SET子句

指定session参数。

BEFORE LOAD DO

加载CSV数据之前的操作，例如创建表。

BEFORE LOAD EXECUTE

AFTER LOAD DO

AFTER LOAD EXECUTE

AFTER CREATE SCHEMA DO

AFTER CREATE SCHEMA EXECUTE

Connection URIs

参考：https://www.postgresql.org/docs/9.3/libpq-connect.html#LIBPQ-CONNSTRING

ostgresql://[user[:password]@][netloc][:port][/dbname][?param1=value1&...]

postgresql://
postgresql://localhost
postgresql://localhost:5433
postgresql://localhost/mydb
postgresql://user@localhost
postgresql://user:secret@localhost
postgresql://other@localhost/otherdb?connect_timeout=10&application_name=myapp
1
2
3
4
5
6
7
8
9

user或者password中含有@，则用2个@代替。含有：，则用2个：代替。

pgloader示例

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; $$;
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

pgloader问题

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

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

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

type datetime to timestamp without time zone 
1