将数据从 MS SQL 迁移到 PostgreSQL？
我环顾四周，似乎找不到任何可以回答这个具体问题的东西。
将数据从 MS SQL Server 2005 DB 移动到 Postgres 安装 (8.x) 的最简单方法是什么？
我研究了几个实用程序，例如“Full Convert Enterprise”等，它们都因某种原因而失败，从导致其崩溃的奇怪错误到插入空值而不是实际数据（什么？）。
我正在查看一个数据库，其中包含除单个视图之外的所有表，没有存储过程、函数等。
此时我即将编写一个小实用程序来为我完成此操作，我只是不敢相信这是必要的。肯定有什么东西可以做到这一点吗？我什至不太担心成本，尽管免费更好:)
I've looked around and can't seem to find anything that answers this specific question.
What is the simplest way to move data from an MS SQL Server 2005 DB to a Postgres install (8.x)?
I've looked into several utilities like "Full Convert Enterprise", etc, and they all fail for one reason or another, ranging from strange errors that make it blow up to inserting nulls rather than actual data (wth?).
I'm looking at a DB with all table except for a single view, no stored procs, functions, etc.
At this point I'm about to write a small utility to do it for me, I just can't believe that's necessary. Surely there's something somewhere that can do this? I'm not even too worried about cost, although free is preferable :)
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（6）
我不知道为什么没有人提到使用强大的 MS SQL Server Management Studio 最简单、最简单的方法。
只需使用内置的 SSIS 导入/导出功能即可。您可以按照以下步骤操作：
首先，您需要安装PostgreSQL ODBC 驱动程序< /a> 对于 Windows。根据CPU架构（x86/x64）安装正确的版本非常重要。
在 Management Studio 中，右键单击您的数据库：任务 -> 导出数据
选择SQL Server Native Client作为数据源。
选择用于 ODBC 的 .Net Framework 数据提供程序作为目标驱动程序。
按以下形式将连接字符串设置为数据库：
Driver={PostgreSQL ODBC Driver(UNICODE)};Server=;Port=;Database=;UID=;PWD=
在下一页中，您只需选择要导出的表即可。 SQL Server 将生成默认映射，您可以自由编辑它。您可能会遇到一些类型不匹配的问题，需要一些时间才能解决。例如，如果 SQL Server 中有一个布尔列，您应该将其导出为 int4。
Microsoft Docs 包含通过 ODBC 连接到 PostgreSQL 的详细说明。
PS：如果您想查看已安装的 ODBC 驱动程序，您需要通过 ODBC 数据源管理员。
I don't know why nobody has mentioned the simplest and easiest way using robust MS SQL Server Management Studio.
Simply you just need to use the built-in SSIS Import/export feature. You can follow these steps:
Firstly, you need to install the PostgreSQL ODBC Driver for Windows. It's very important to install the correct version in terms of CPU arch (x86/x64).
Inside Management Studio, Right click on your database: Tasks -> Export Data
Choose SQL Server Native Client as the data source.
Choose .Net Framework Data Provider for ODBC as the destination driver.
Set the Connection String to your database in the following form:
Driver={PostgreSQL ODBC Driver(UNICODE)};Server=;Port=;Database=;UID=;PWD=
In the next page, you just need to select which tables you want to export. SQL Server will generate a default mapping and you are free to edit it. Probably you`ll encounter some Type Mismatch problems which take some time to solve. For example, if you have a boolean column in SQL Server you should export it as int4.
Microsoft Docs hosts a detailed description of connecting to PostgreSQL through ODBC.
PS: if you want to see your installed ODBC Driver, you need to check it via ODBC Data Source Administrator.
查看软件目录。在管理/开发工具下我看到DBConvert for MS SQL & PostgreSQL。可能还列出了其他类似的工具。
Take a look at the Software Catalogue. Under Administration/development tools I see DBConvert for MS SQL & PostgreSQL. Probably there are other similar tools listed.
您可以使用MS DTS功能（我认为在最新版本中重命名为SSIS）。 DTS 的一个问题是，在将数据加载到 pg 中时，我无法使其在每一行之后进行提交。如果您只有几行 10 万行左右，这很好，但它确实非常慢。
我通常最终会编写一个小脚本，以 CSV 格式从 SQLServer 转储数据，然后在 PostgreSQL 端使用 COPY WITH CSV。
不过，这两者都只处理数据。处理模式有点困难，因为数据类型不一定直接映射。但它可以轻松地与模式的静态加载一起编写脚本。如果架构很简单（例如只是 varchar/int 数据类型），则该部分也可以轻松地根据 INFORMATION_SCHEMA 中的数据编写脚本。
You can use the MS DTS functionality (renamed to SSIS in the latest version I think). One issue with the DTS is that I've been unable to make it do a commit after each row when loading the data into pg. Which is fine if you only have a couple of 100k rows or so, but it's really very slow.
I usually end up writing a small script that dumps the data out of SQLServer in CSV format, and then use COPY WITH CSV on the PostgreSQL side.
Both those only take care of the data though. Taking care of the schema is a bit harder, since datatypes don't necessarily map straight over. But it can easily be scripted together with a static load of the schema. If the schema is simple (just varchar/int datatypes for example), that part can also easily be scripted off the data in INFORMATION_SCHEMA.
显然，有针对 MS SQL Server 2005 和 PostgreSQL 的 .NET 绑定。因此，只需要几行代码就可以编写一个可以安全地将数据从一个设备传输到另一个设备的程序。该视图可能必须手动完成，因为 Postgres 不使用与 SQL Server 相同的视图语言。
Well there are .NET bindings for MS SQL Server 2005 (obviously) and also for PostgreSQL. So it would only take a few lines of code to code up a program that could transfer data safely from one to the other. The view would probably have to be done manually as Postgres doesn't use the same language for views as SQL Server.
这个答案是为了帮助总结当前的连接字符串，因为有人可能忽略了评论。
ODBC 连接字符串的当前版本是：
对于 32 位系统
对于 64 位系统
您可以通过在 Windows 搜索中键入 ODBC 来检查驱动程序名称。
并打开ODBC数据源管理器
This answer is to help summarize current connection string because someone may overlooked the comment.
Current version of ODBC connection string is:
For 32-bit system
For 64-bit system
You can check the driver name by typing ODBC in windows search.
And open ODBC Data Source Administrator
我遇到了 SSMS 和
bit值的问题（SqlServer 中为 1/0，但在 PostgreSql 中为 true/false），所以我在 DBeaver 上搜索并停止，它是免费的，并且几乎都是他自己映射表和列，所以非常简单。对于相对较小的值（约 100 个表，约 2000 万行），它表现良好，不到 30 分钟。但是，在具有大量列的大型表（约 1000 万行以上）上，它可能会遇到困难。我最终使用 DBeaver 处理除通过 SSIS 以 CSV 格式导出并通过 COPY 命令导入 PostgreSql 的大表之外的所有内容。另外，删除大表上的 PK、约束、索引并在迁移后重新添加它们也有很大帮助。
如果您在 Linux 上运行 PostgreSql，您可以考虑使用 FDW ，它可以导入架构和数据无需太多努力。
I had a problem with SSMS and
bitvalues (1/0 in SqlServer, but in PostgreSql it is true/false) so I searched and stopped on DBeaver, it's free and pretty much maps the tables and columns all by himself so it's very easy. On a relatively small values (~100 tables, ~20mil rows) it performs fine, under 30min.However, on large tables (~10mil+ rows) with significant amount of columns it may struggle. I ended up using DBeaver for everything except the big table which I exported via SSIS in CSV format and imported in PostgreSql via
COPYcommand. Also dropping PK, Contraints, indexes on the large table and re-adding them after the migration helped a lot.If you're running PostgreSql on linux, you may consider using FDW which can import schema and data without much effort.