从postgresql转储文件填充MySQL数据库 [英] Populate MySQL database from postgresql dump file

问题描述

实际上，我需要从由postgresql生成为

Actually I need to populate MySQL database from a SQL file which was generated by postgresql as

pg_dump dbname > myfile.sql

所以，如果我尝试喜欢的话

So, if I try to do like

mysql> source myfile.sql

这显然行不通.尽管它确实填充了70％的表，但是我想知道有没有一种方法可以实现它?

This obviously does not work. Although it did populate 70% tables but I want to know is there a way to achieve it ?

我可以从postgresql的源文件中获取并填充MySql数据库.

From the source file of postgresql, can I source and populate my database of MySql.

推荐答案

如果要将数据和结构从postgres表迁移到mysql等效表，最简单的方法是使用数据库转换工具，例如: openDBCopy .

If you want to migrate the data and structure from postgres table to mysql equivalent, the easiest way is using a database conversion tool like : ESF Data Migration Toolkit or the opensource counterpart openDBCopy .

如果您不想或者不能使用迁移工具，而只需要迁移数据，另一种简单的方法可以是从PostgreSQL导出CSV格式的数据，然后将其导入MySQL，因此您可以执行它与一些命令，如:

If you don't want or you can't use a migration tool, and you need to only migrate data, another simple way could be export data in CSV format from PostgreSQL and then import it in MySQL, so you can do it with some commands like :

在Postgres(导出)上:

COPY (SELECT query) TO '/path to csv file -*.csv'; 

打开Mysql(导入):

load data infile 'path to csv file-*.csv' into table tablename fields terminated by ',' lines terminated by '\n' . 

如果您仍然想继续使用转储工具(pg_dump)，则可以添加此选项以生成MySQL可以更好理解的转储文件:

If you anyway want to proceed with the dump tool(pg_dump) you can add this options to produce a dump file that MySQL can understand better :

-d --data-only --no-owner --no-acl --attribute-inserts --disable-dollar-quoting --no-tablespaces

请记住，根据源数据库的结构，您可能必须操纵转储文件以使MysQL能够理解生成的转储文件...

Keep in mind that, depending on the structure of the source database, you could have to manipulate the dump file to allow MysQL to understand the generated dump file ...

这篇关于从postgresql转储文件填充MySQL数据库的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！