乐趣区

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

jiezi

应用 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 中文文档
退出移动版