mysql数据迁移到pgsql之我见!

使用mysql 将数据迁移到pgsql

1. 直接使用在线的脚本转化器

sqllines

online 地址

    优点就是快速敏捷,在线编辑.缺点在于,部分sql转换存在明显的语法错误,且不能复制过大的文本(有兴趣可以下载它的客户端工具进行操作)

2. 使用navicate在线迁移(工具迁移)

使用场景: 数据库表数量>100

这里的操作因人而异

值得要注意的一点就是navicate的同步过程中会有一些问题,我遇到的:

1. 索引迁移直接失败,报错找不到对应的表
2. 转换的对象的大小写需要注意
3. 转换的建表语句中的默认值会丢失
4. 外键约束中的delete cascade 直接变成了 delete no action
5. 无法同步视图及触发器
6. 主键自增需要用序列来实现

建议如果遇到这种情况,直接先只同步结构和数据

完成以后再单独同步索引和约束 和序列

2.1 获取主键自增序列

SELECT 
concat('create sequence ',a.name,'_seq',' INCREMENT 1 START 1 NO MINVALUE NO MAXVALUE;')
FROM 
(SELECT table_name as name FROM 
information_schema.columns WHERE 
table_schema = 'your_schema_name' -- schema的名称
and COLUMN_NAME = 'id' -- 字段为id
)a;

2.2 获取索引的创建

SELECT 
CONCAT('CREATE ',
if(a.non_unique = 1,'INDEX ','UNIQUE INDEX '),
a.INDEX_NAME,
' ON ',
a.TABLE_NAME,
' using btree',
' (',
a.index_column,
');'
)
FROM 
(select TABLE_NAME, non_unique,INDEX_NAME, GROUP_CONCAT(COLUMN_NAME) as INDEX_COLUMN
from
information_schema.statistics
where
table_schema='your_schema_name' -- 数据库名
AND index_name <> 'PRIMARY' -- 将主键去掉
GROUP BY TABLE_NAME, INDEX_NAME,non_unique) a

2.3 外键约束

SELECT DISTINCT
  CONCAT('ALTER TABLE ', kcu.table_name, ' ADD CONSTRAINT ', tc.constraint_name,'_', kcu.column_name,  ' FOREIGN KEY (', kcu.column_name, ') REFERENCES ', kcu.referenced_table_name, '(', kcu.referenced_column_name, ');') AS create_statement
FROM
  information_schema.key_column_usage kcu
  JOIN information_schema.table_constraints tc ON kcu.constraint_name = tc.constraint_name
WHERE
  kcu.referenced_table_name IS NOT NULL
  AND tc.constraint_schema = 'your_schema_name'
  AND tc.constraint_type != 'PRIMARY KEY'
  AND tc.constraint_name NOT IN (
    SELECT
      constraint_name
    FROM
      information_schema.referential_constraints
    WHERE
      constraint_schema = 'your_schema_name'
    GROUP BY
      constraint_name
    HAVING
      COUNT(*) > 1
  );

2.4 视图创建sql生成

SELECT
    CONCAT(
        'DROP VIEW IF EXISTS ',
        table_name,
        ';',
        '\n',
        'CREATE OR REPLACE VIEW ',
        table_name,
        ' AS ',
        view_definition,
        ';'
    ) AS view_creation_query
FROM
    information_schema.views
WHERE
    table_schema = DATABASE ();

上面获取的是mysql的视图的创建过程 如果转成其他数据库 需要自己转换语法(如内置函数或者存储函数)

3 使用命令行工具工具pgloader进行在线同步

LOAD DATABASE
 FROM mysql://root:root@192.168.124.11:3306/bam
 INTO pgsql://bim6:Bamboocloud1234@10.0.0.200:8887/postgres
WITH include drop, create tables, create indexes,foreign keys, reset no sequences, 
workers = 8, concurrency = 1, 
multiple readers per thread, rows per range = 50000

CAST type timestamp to timestamp drop default drop not null using zero-dates-to-null,
type datetime to timestamp drop default drop not null using zero-dates-to-null,
type tinyint  when (= 1 precision) to boolean drop typemod using tinyint-to-boolean,
type bit when (= 1 precision) to boolean drop typemod using bits-to-boolean,
type varchar when(= 1 precision) to "boolean" drop typemod keep default keep not null;

值得要说的就是对于一些字段的迁移如mysql中的timestamp 迁移到pg 则会变成 timestamptz 可能会对程序造成一些影响,这里使用了CAST进行类型转换.

一些其他的语法配置可参考[mysql迁移到Pg 的配置相关]

• pg默认cast配置

---

3.1 docker 安装pgloader 及数据同步

建议使用管理员密码进行同步 pg的管理员创建语句如下
CREATE USER testuser2 SYSADMIN INHERIT LOGIN RESOURCE POOL 'default_pool' PASSWORD 'Bamboocloud1234';

1. 安装pgloader

   docker pull dimitri/pgloader
   docker run --rm --name pgloader dimitri/pgloader:latest pgloader --version
   
   mkdir pgload-docker && cd pgload-docker
   touch pgload.load
   

2. 配置pgload.load文件内容

   LOAD DATABASE
    FROM mysql://root:root@10.88.0.18:3306/bim-tjb
    INTO pgsql://testuser2:Bamboocloud1234@10.99.1.111:8887/postgres
   WITH include drop, create tables,disable triggers, create indexes,foreign keys, reset no sequences, 
   workers = 8, concurrency = 1, 
   multiple readers per thread, rows per range = 50000
   
   CAST  type timestamp to timestamp drop default drop not null using zero-dates-to-null,
   type datetime to timestamp drop default drop not null using zero-dates-to-null,
   type tinyint  when (= 1 precision) to boolean drop typemod using tinyint-to-boolean,
   type bit when (= 1 precision) to boolean drop typemod using bits-to-boolean,
   type varchar when(= 1 precision) to "boolean" drop typemod keep default keep not null;
   
   type bigint when (= precision 20) to bigserial drop typemod,
   type date drop not null drop default using zero-dates-to-null,
   type tinyint to smallint   drop typemod,
   type smallint to smallint  drop typemod,
   type mediumint to integer  drop typemod,
   type integer to integer    drop typemod,
   type float to float        drop typemod,
   type numeric to numeric keep typemod,
   type decimal to decimal keep typemod,
   type char       to char keep typemod using remove-null-characters,
   type varchar    to varchar keep typemod using remove-null-characters,
   type tinytext   to text using remove-null-characters,
   type text       to text using remove-null-characters,
   type mediumtext to text using remove-null-characters,
   type longtext   to text using remove-null-characters,
   type binary     to bytea using byte-vector-to-bytea,
   type varbinary  to bytea using byte-vector-to-bytea,
   type tinyblob   to bytea using byte-vector-to-bytea,
   type blob       to bytea using byte-vector-to-bytea,
   type mediumblob to bytea using byte-vector-to-bytea,
   type longblob   to bytea using byte-vector-to-bytea;

3. 启动容器

   docker run -tid --name pg -v $(pwd):/pgload dimitri/pgloader 

4. 执行同步命令

   docker exec pg pgloader /pgload/pgload.load

   

3.2同步csv数据源

# 创建表 默认public shcema
CREATE TABLE mytable (
    id INTEGER,
    name TEXT,
    age INTEGER
);
# sample.csv

id,name,age
1,Alice,25
2,Bob,30
3,Charlie,35

# 执行命令
./build/bin/pgloader --type csv \
--with "skip header = 1"  \
sample.csv \
pgsql://testuser3:Bamboocloud@@:1234@10.0.0.200:8887/postgres?tablename=mytable 

也可以直接加载配置文件中的配置

   INTO postgresql://testuser3:Bamboocloud@@:1234@10.0.0.200:8887/postgres
        TARGET TABLE oracle.mytable
        TARGET COLUMNS
        (
            id, name, age
        )
   WITH truncate,
        skip header = 1,
        fields optionally enclosed by '"',
        fields escaped by backslash-quote,
        fields terminated by ','

    SET work_mem to '32 MB', maintenance_work_mem to '64 MB';

执行命令pgloader samp.load

参考如下

• pg中文文档
• pgload
• 使用PGLoader将MySQL数据库迁移到PostgreSQL
• 如何用pgloader将Zabbix的MySQL数据库迁移到PostgreSQL
• pgload随笔
• pgloader-pg迁移神器