最简单的方法是将PostgreSQL数据库迁移到SQL Server中 [英] Easiest way to migrate a PostgreSQL database into an SQL Server one

问题描述

我有一个PostgreSQL数据库，我想移动到SQL Server - 模式和数据。我很穷，所以我不想支付任何钱。我也很懒，所以我不想做很多工作。目前我正在做这个表，有大约100个表要做。这是非常繁琐的。

I have a PostgreSQL database that I want to move to SQL Server -- both schema and data. I am poor so I don't want to pay any money. I am also lazy, so I don't want to do very much work. Currently I'm doing this table by table, and there are about 100 tables to do. This is extremely tedious.

有什么窍门，我想做什么。

Is there some sort of trick that does what I want?

推荐答案

我相信你可能已经得到了投票，因为从PostgreSQL生成一个简单的SQL脚本，可以（理论上）可以再次运行只是关于任何DBMS容易。如果一个是一个普通的PostgreSQL用户，它听起来像一个愚蠢的问题。

I believe that you may have gotten down-votes due to amazing ease of generating a simple SQL script from PostgreSQL that can (theoretically) be run again just about any DBMS. If one is a regular PostgreSQL user, it sounds like a dumb question.

这不公平，因为事实证明这实际上是一个适度硬的问题（虽然更多是由于SQL Server的奇怪的语法和接口比任何失败的PostgreSQL）。

That's not fair since it turns out this is actually a moderately hard problem (although more due to SQL Server's odd syntax and interface than any failing of PostgreSQL).

您应该能够在此Serverfault页面中接受的答案中找到一些有用的信息： http://serverfault.com/questions/65407/best-tool-to-migrate-a-postgresql-database-to-ms- sql-2005 。

You should be able to find some useful information in the accepted answer in this Serverfault page: http://serverfault.com/questions/65407/best-tool-to-migrate-a-postgresql-database-to-ms-sql-2005.

如果您可以在没有数据的情况下转换模式，则可以使用以下命令缩短数据的步骤：

If you can get the schema converted without the data, you may be able to shorten the steps for the data by using this command:

pg_dump --data-only --column-inserts your_db_name > data_load_script.sql

这个加载会很慢， / em>选项生成每个数据行可能的最通用的INSERT语句，并且应该兼容。

This load will be quite slow, but the --column-inserts option generates the most generic INSERT statements possible for each row of data and should be compatible.

编辑：关于转换模式的建议如下：

Suggestions on converting the schema follows:

我将首先转储模式，但删除与所有权或权限有关的任何内容。这应该就够了：

I would start by dumping the schema, but removing anything that has to do with ownership or permissions. This should be enough:

pg_dump --schema-only --no-owner --no-privileges your_db_name > schema_create_script.sql

编辑此文件以添加行BEGIN TRANSACTION;到开始和ROLLBACK TRANSACTION;到最后。现在您可以加载它并在SQL Server中的查询窗口中运行它。如果你得到任何错误，确保你去文件的底部，突出显示ROLLBACK语句并运行它（通过击中F5，而语句突出显示）。

Edit this file to add the line "BEGIN TRANSACTION;" to the beginning and "ROLLBACK TRANSACTION;" to the end. Now you can load it and run it in a query window in SQL Server. If you get any errors, make sure you go to the bottom of the file, highlight the ROLLBACK statement and run it (by hitting F5 while the statement is highlighted).

基本上，你必须解决每个错误，直到脚本干净地运行。然后，您可以将ROLLBACK TRANSACTION更改为COMMIT TRANSACTION并运行最后一次。

Basically, you have to resolve each error until the script runs through cleanly. Then you can change the "ROLLBACK TRANSACTION" to "COMMIT TRANSACTION" and run one final time.

不幸的是，我无法帮助您看到哪些错误从PostgreSQL到SQL Server，只有相反的方式。但是，有些事情我认为会是一个问题（显然不是一个详尽的列表）：

Unfortunately, I cannot help with which errors you may see as I have never gone from PostgreSQL to SQL Server, only the other way around. Some things that I would expect to be an issue, however (obviously, NOT an exhaustive list):

• PostgreSQL自动递增字段使用DEFAULT将NOT NULL INTEGER字段链接到SEQUENCE。在SQL Server中，这是一个IDENTITY列，但它们不完全相同。我不知道如果他们是等价的，但如果你原来的模式充满了id字段，你可能会在一些麻烦。我不知道SQL Server是否有CREATE SEQUENCE，因此您可能必须删除这些。


• 数据库函数/存储过程不会在RDBMS平台之间转换。您需要删除任何CREATE FUNCTION语句，并手动翻译算法。


• 小心数据文件的编码。我是一个Linux人，所以我不知道如何验证Windows中的编码，但你需要确保SQL Server期望与从PostgreSQL导入的文件相同。 _pg_dump_有一个选项 - encoding = ，可让您设置特定的编码。我似乎记得Windows往往使用双字节，UTF-16编码的Unicode，其中PostgreSQL使用UTF-8。


• PostgreSQL数据类型TEXT只是一个没有最大长度的VARCHAR。在SQL Server中，TEXT是...复杂（并已弃用）。


• SQL Server为UNICODE数据提供了额外的数据类型。我不熟悉它提出建议。我只是指出这可能是一个问题。

• PostgreSQL does auto-increment fields by linking a NOT NULL INTEGER field to a SEQUENCE using a DEFAULT. In SQL Server, this is an IDENTITY column, but they're not exactly the same thing. I'm not sure if they are equivalent, but if your original schema is full of "id" fields, you may be in for some trouble. I don't know if SQL Server has CREATE SEQUENCE, so you may have to remove those.
• Database functions / Stored Procedures do not translate between RDBMS platforms. You'll need to remove any CREATE FUNCTION statements and translate the algorithms manually.
• Be careful about encoding of the data file. I'm a Linux person, so I have no idea how to verify encoding in Windows, but you need to make sure that what SQL Server expects is the same as the file you are importing from PostgreSQL. _pg_dump_ has an option --encoding= that will let you set a specific encoding. I seem to recall that Windows tends to use two-byte, UTF-16 encoding for Unicode where PostgreSQL uses UTF-8. I had some issue going from SQL Server to PostgreSQL due to UTF-16 output so it would be worth researching.
• The PostgreSQL datatype TEXT is simply a VARCHAR without a max length. In SQL Server, TEXT is... complicated (and deprecated). Each field in your original schema that are declared as TEXT will need to be reviewed for an appropriate SQL Server data type.
• SQL Server has extra data types for UNICODE data. I'm not familiar enough with it to make suggestions. I'm just pointing out that it may be an issue.

这篇关于最简单的方法是将PostgreSQL数据库迁移到SQL Server中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！