从 Postgres 迁移到 SQL Server 2008
我需要将数据库从 Postgres 7 迁移到 SQL Server 2008。我熟悉 SSIS 导入和导出向导,但我对如何定义数据源或定义数据提供程序感到困惑。
将 Postgres 迁移到 SQL Server 的最佳方法是什么?如何为 Postgres 定义数据源/驱动程序?
如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱,如果其他用户或者作者回复了您的评论,将不能在第一时间通知您!
发布评论
评论(4)
我在使用 SQL Server 2008 R2 中的导入向导从 PostgreSQL 导入表时遇到问题。我安装了 PostgreSQL ODBC 驱动程序,因此对于导入向导中的数据源,我选择了“.Net Framework Data Provider for Odbc”并为我的 PostgreSQL 数据库提供了 DSN 名称。向导发现表没问题,但是当我执行导入时出现错误
我在 Microsoft 博客文章 此处检索数据。显然,问题在于各种 ODBC 驱动程序在报告列元数据时使用不同的属性名称。为了使导入工作,我必须编辑“ProviderDescriptors.xml”文件,该文件位于
C:\Program Files\Microsoft SQL Server\100\DTS\ProviderDescriptors\ProviderDescriptors.xml
中
在 ... ... 元素 我必须将属性从 ...
... 更改为 ...
也就是说,我必须调整
MaximumLengthColumnName
、NumericPrecisionColumnName
和NumericScaleColumnName
code> 属性值分别为“LENGTH”
、“PRECISION”
和“SCALE”
。进行更改后,从 PostgreSQL 到 SQL Server 的导入成功运行。
I was having problems using the Import Wizard in SQL Server 2008 R2 to import tables from PostgreSQL. I had the PostgreSQL ODBC driver installed, so for the Data Source in the Import Wizard I chose ".Net Framework Data Provider for Odbc" and supplied the DSN name for my PostgreSQL database. The wizard found the tables okay, but when I went to perform the import I got the error
I found the solution in the Microsoft blog post here. Apparently the problem is that various ODBC drivers use different attribute names when reporting column metadata. To get the import to work I had to edit the "ProviderDescriptors.xml" file, which was located at
C:\Program Files\Microsoft SQL Server\100\DTS\ProviderDescriptors\ProviderDescriptors.xml
In the ...
... element I had to change the attributes from ...
... to ...
That is, I had to tweak the
MaximumLengthColumnName
,NumericPrecisionColumnName
, andNumericScaleColumnName
attribute values to"LENGTH"
,"PRECISION"
, and"SCALE"
, respectively.Once that change was made the import from PostgreSQL to SQL Server ran successfully.
我祝您在尝试使用 SQL Server 导入和导出向导从 PostgreSQL 导入 SQL Server 时一切顺利。然而,我读过许多留言板帖子,其中有人无法使其正常工作。例如:
这是我在该主题上找到的最有帮助的线程:
帮助可能试图实现类似目标的人矿。不要在 SQL Server 导入和导出向导的数据源下拉菜单中选择“PostgreSQL OLE DB Provider”,而是选择“.Net Framework Data Provider for Odbc”
然后你必须创建一个 DSN 并提供一个连接字符串。以下 ConnectionString 对我有用
Driver={PostgreSQL};Server=localhost;Port=5432;Database=TestMasterMap;Uid=postgres;Pwd=;
要创建一个 DSN,你有进入管理工具à数据源(ODBC)并创建用户DSN。完成此操作后,您可以在 SQL Server 导入和导出向导的 DSN 文本框中提供 DSN 名称。
一位评论者声称它有效,但他在大表上遇到“读取元组时内存不足”错误。因此,对于超过 300 万行的表,他必须将导入分成 300 万行块。
此外,该线程中还有一个指向 PostgreSQL 的本机 .NET 提供程序的链接。
就我个人而言,如果这是我只需要做一次的事情,并且如果我很好地理解架构和数据,我会尝试:
执行上述操作可能比花几天时间搞乱 SSIS 导入/导出向导和 PostgreSQL 花费的时间更少(但如果这些工具能发挥作用那就太好了!)
I wish you the best of luck in trying to import from PostgreSQL into SQL Server using SQL Server Import and Export Wizard. However, I have read numerous message board threads with people having trouble getting it to work. For example:
Here is the most helpful thread that I have found on the topic:
To help someone who might be trying to achieve similar goal as mine. Instead of selecting the “PostgreSQL OLE DB Provider” in the data source drop down menu of SQL Server Import and Export Wizard, select “.Net Framework Data Provider for Odbc”
Then you have to make a DSN and provide a ConnectionString. Following ConnectionString worked for me
Driver={PostgreSQL};Server=localhost;Port=5432;Database=TestMasterMap;Uid=postgres;Pwd=;
To make a DSN you have to go into Administrative Toolsà Data Sources (ODBC) and create a user DSN. Once this is done you can supply the DSN name in the DSN text box of SQL Server Import and Export Wizard.
One commenter claimed that it worked, but that he got "Out of memory while reading tuples" errors on big tables. So for tables with more than 3 million rows, he had to break the import up into 3 million row chunks.
Also, there's a link to the native .NET provider for PostgreSQL in that thread.
Personally, if this is something that I only had to do once, and if I understood the schema and the data fairly well, I would try:
It might take you less time to do the above than messing with SSIS Import/Export Wizard and PostgreSQL for days (but it would be nice if those tools worked!)
当我评论完上面的答案时,我想到尝试 SQL WorkbenchJ;它有一个数据泵功能,对我来说效果很好。我设法将数据从 PostgreSQL 数据库导出到 SQL Server 实例。
对于那些想要以批处理模式(通过 shell)运行此程序的人,操作方法如下:Google 网上论坛帖子。讨论中提到的 WbCopy 命令在我能找到的任何地方都没有真正记录,但您可以通过数据泵接口生成一个命令,然后更改您需要的任何内容。
As I finished commenting the answer above, I thought of trying SQL WorkbenchJ; it has a datapump feature that worked pretty well for me. I managed to export data from my PostgreSQL database to an SQL server instance.
Those who'd like to run this in batch mode (via shell), here's how to do it: Google Groups Thread. The WbCopy command mentioned on the discussion isn't really documented anywhere I could find, but you can generate one through the datapump interface and then change whatever you need.
给出一个更实际的例子,说明如何实现标记答案中描述的内容;您可以从 PostgresQL 导出到平面文件,然后使用 bcp 实用程序,用于在 SQL Server 中导入。
例如,在 .bat 文件中,对于单个表(并且您需要已在目标 SQL DB 中创建该表):
To give a little more practical example of how you can achieve what's described in marked answer; you can export from PostgresQL to flat files then use bcp Utility to import in SQL Server.
e.g. in a .bat file, for a single table (and you need to have the table already created in the destination SQL DB):