Mysql导入PostgreSQL
异构数据库导入可以用FDW,但测试后发现FDW部分显示乱码,所以决定采用COPY文本文件的方式,具体步骤如下:
1、转化DDL
2、MySQL端生成文本文件,并做相应转化,把换行符替换掉
mysql > SELECT CONCAT('select * from ',
table_name,
' into outfile '/tmp/pv_split/',
table_name,
'.dat' fields terminated by '|' ;')
FROM
information_schema.tables
WHERE
table_schema='who_ebi';
shell>
sed -i 's/r//g' /tmp/pv_split/*
如果不替换这一步会报错误
ERROR: literal carriage return found in data
HINT: Use "r" to represent carriage return.
CONTEXT: COPY acl_resources, line 6
3、导入PostgreSQL数据库
select
'copy '||table_name||' from
''/tmp/pv_split/'||table_name||'.dat''
with
DELIMITER ''|