从 postgresql 转储文件填充 MySQL 数据库
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/10462044/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Populate MySQL database from postgresql dump file
提问by Nikhil Sharma
Actually I need to populate MySQL database from a SQL file which was generated by postgresql as
实际上我需要从由 postgresql 生成的 SQL 文件中填充 MySQL 数据库作为
pg_dump dbname > myfile.sql
So, if I try to do like
所以，如果我尝试这样做
mysql> source myfile.sql
This obviously does not work. Although it did populate 70% tables but I want to know is there a way to achieve it ?
这显然行不通。虽然它确实填充了 70% 的表，但我想知道有没有办法实现它？
From the source file of postgresql, can I source and populate my database of MySql.
我可以从 postgresql 的源文件中获取并填充我的 MySql 数据库吗？
回答by aleroot
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 Toolkitor the opensource counterpart openDBCopy.
如果您想将数据和结构从 postgres 表迁移到 mysql 等价物，最简单的方法是使用数据库转换工具，如：ESF 数据迁移工具包或开源对应物openDBCopy。
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 :
如果你不想或者你不能使用迁移工具，你只需要迁移数据，另一种简单的方法是从PostgreSQL导出CSV格式的数据，然后导入到MySQL中，这样你就可以用一些命令如：
ON Postgres (Export):
关于 Postgres（导出）：
COPY (SELECT query) TO '/path to csv file -*.csv';
ON Mysql (Import):
关于 Mysql（导入）：
load data infile 'path to csv file-*.csv' into table tablename fields terminated by ',' lines terminated by '\n' .
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 :
如果您无论如何想继续使用转储工具（pg_dump），您可以添加此选项以生成一个 MySQL 可以更好地理解的转储文件：
-d --data-only --no-owner --no-acl --attribute-inserts --disable-dollar-quoting --no-tablespaces
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 ...
请记住，根据源数据库的结构，您可能必须操作转储文件以允许 MySQL 理解生成的转储文件......
回答by javed
The best way to migrate without loosing data is to use Mysql Workbench migrations. This tutorialexplains how to do the migrations. Few things not mentioned in the tutorial that I found to be important are below:
在不丢失数据的情况下进行迁移的最佳方法是使用 Mysql Workbench 迁移。本教程解释了如何进行迁移。教程中没有提到但我发现很重要的几件事如下：
- Install latest postgres drivers to connect source postgres database, how to install this driver can be found in this tutoria:
- Download and install the postgres odbc drivers from here
- After installation you will see the drivers in "Open ODBC Administrator"
1.
2. - Use "PostgreSQL Unicode(x64)" as the driver while setting up the source postgres database
- 安装最新的 postgres 驱动程序以连接源 postgres 数据库，如何安装此驱动程序可以在本教程中找到：
- 从这里下载并安装 postgres odbc 驱动程序
- 安装后，您将在“打开 ODBC 管理器”中看到驱动程序 1. 2.
- 在设置源 postgres 数据库时使用“PostgreSQL Unicode(x64)”作为驱动程序
Note: using "PostgreSQL ANSI(x64) " as driver gives error while migrations . May be because my data contains unicode characters.
注意：使用“PostgreSQL ANSI(x64)”作为驱动程序会在迁移时出错。可能是因为我的数据包含 unicode 字符。
