关于mysql:mysql数据迁移到pgsql之我见

应用mysql 将数据迁徙到pgsql

1. 间接应用在线的脚本转化器 sqllines

online 地址

    长处就是疾速麻利,在线编辑;毛病在于,有的转换可能不准,只有学习的意义.且在线工具如果粘贴的文本过大会很卡 且翻译不进去(能够下载它的客户端工具进行操作)

2. 应用navicate在线迁徙

这种搞法就是适宜有源库,且表的个数超过了100(比拟多),间接点开客户端工具中的数据迁徙

这里的操作因人而异

值得要留神的一点就是navicate的同步过程中会有一些问题,我遇到的:
1.全副的索引间接失败,报错找不到对应的表
2.转换的对象的大小写须要设置一下(懂的都懂)
3.转换的建表语句中的默认值间接没了?
4.外键束缚中的delete cascade 间接变成了 delete no action

所以我倡议如果遇到这种状况,间接先只同步构造和数据

实现当前再独自同步索引和(外键)束缚 和自增序列

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

3 应用工具pgload进行在线同步

这里就不赘述pgload的装置过程,须要筹备的先决条件即是:两个须要数据迁徙的库的账号信息(ip 端口 username passowrd 最好是管理员账户 否则在创立一些对象的时候可能会失败)

筹备好pgload.load配置文件(这里有个坑明码不能有特殊字符如@等 否则则会始终解析失败 不晓得有没有遇到这类状况的大佬教教我咋解决)

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, 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 varchar when(= 1 precision) to "boolean" drop typemod keep default keep not null

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

一些其余的语法配置可参考[mysql迁徙到Pg 的配置相干]

(https://pgloader.readthedocs.io/en/latest/ref/mysql.html#usin…)

参考如下

• 应用PGLoader将MySQL数据库迁徙到PostgreSQL
• pg中文文档