如何将PostgreSQL数据库迁移到SQLServer？

Question

我有一个PostgreSQL数据库，想要将其迁移到SQL Server（包括架构和数据）。由于我比较穷，所以不想花钱。又因为我比较懒，不想做太多的工作。目前我是逐个表格地进行操作，而且有大约100个表格需要处理，这非常乏味。

有没有什么技巧可以满足我的需求？

- Hut8

---

我想问一下，如果你没有钱，为什么要转移到SQL Server呢？虽然Express版本是免费的，但部署所需的基础设施则不是... - Matthew Wood

这些踩票很愚蠢; 这是一个完全合法的问题，而且在这里，MS SQL -> MySQL迁移问题已经得到了赞同。 我正在迁移到SQL Server，因为我已经有一个MS SQL数据库，但我只能在PostgreSQL数据库中找到很多有价值的数据。 大多数服务器的托管价格大致相同，我喜欢没有第三方附加组件的ASP.NET MVC 3。 LINQ to SQL在PostgreSQL上不稳定。 - Hut8

使用pg_dump SQL方法和在SQL Server上导入DDL/DML时，你遇到了哪些问题？当你说“我是逐个表格操作”的时候，这是什么意思？ - Grzegorz Szpetkowski

Answer 1

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

如果您可以将模式转换而不包括数据，则可以使用此命令缩短数据步骤：

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

这个加载速度会比较慢，但是--column-inserts选项会为每行数据生成最通用的INSERT语句，应该是兼容的。

编辑：下面是转换模式的建议：

我会从导出模式开始，但删除与所有权或权限有关的任何内容。 这应该足够了：

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

编辑这个文件，在开头添加一行BEGIN TRANSACTION;，在结尾添加一行ROLLBACK TRANSACTION;。现在你可以将其加载并在 SQL Server 中的查询窗口运行它。如果出现任何错误，请确保到文件底部，选中 ROLLBACK 语句并运行它（通过高亮显示语句并按 F5 键）。

基本上，你需要解决每个错误，直到脚本顺利运行。然后，你可以将 ROLLBACK TRANSACTION 更改为 COMMIT TRANSACTION 并进行最后一次运行。

不幸的是，我无法确定你可能会遇到哪些错误，因为我从未从 PostgreSQL 到 SQL Server 进行过迁移，只有相反的情况。但是，以下是一些可能会出现问题的事项（显然，这不是详尽无遗的列表）：

• PostgreSQL 通过将 NOT NULL INTEGER 字段与 SEQUENCE 链接并使用 DEFAULT 来自动增加字段。在 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。我曾经因为 UTF-16 输出从 SQL Server 到 PostgreSQL 的问题而遇到一些问题，所以这值得研究。
• PostgreSQL 数据类型 TEXT 只是没有最大长度的 VARCHAR。在 SQL Server 中，TEXT 是...复杂的（并且已弃用）。需要审查原始架构中声明为 TEXT 的每个字段，以确定适当的 SQL Server 数据类型。
• SQL Server 有额外的数据类型用于 UNICODE 数据。我对此不太熟悉，无法提出建议。我只是指出这可能是一个问题。

- Matthew Wood

---

非常棒的信息。非常感谢。有没有关于如何在没有商业产品的情况下转换模式的提示？我也被卡住了。 - Hut8

添加了更多细节。请注意，我还修复了pg_dump命令的数据版本，以添加关键选项：--data-only。 - Matthew Wood

Answer 2

我找到了一种更快、更简单的方法来完成这个任务。

首先，将您的表格（或查询）复制到一个制表符分隔的文件中，如下所示：

COPY (SELECT siteid, searchdist, listtype, list, sitename, county, street, 
   city, state, zip, georesult, elevation, lat, lng, wkt, unlocated_bool, 
   id, status, standard_status, date_opened_or_reported, date_closed, 
   notes, list_type_description FROM mlocal) TO 'c:\SQLAzureImportFiles\data_script_mlocal.tsv' NULL E''

接下来，您需要在SQL中创建表格，但是这不会为您处理任何模式。该模式必须与导出的tsv文件的字段顺序和数据类型匹配。

最后，您可以运行SQL的bcp实用程序来导入tsv文件，操作如下：

bcp MyDb.dbo.mlocal in "\\NEWDBSERVER\SQLAzureImportFiles\data_script_mlocal.tsv" -S tcp:YourDBServer.database.windows.net -U YourUserName -P YourPassword -c

我遇到了一些需要注意的事情。Postgres和SQL Server处理布尔字段的方式不同。在SQL Server架构中，您的布尔字段需要设置为varchar(1)，生成的数据将是'f'、't'或null。然后，您需要将此字段转换为位(bit)。可以使用以下方法：

ALTER TABLE mlocal ADD unlocated bit;
UPDATE mlocal SET unlocated=1 WHERE unlocated_bool='t';
UPDATE mlocal SET unlocated=0 WHERE unlocated_bool='f';
ALTER TABLE mlocal DROP COLUMN unlocated_bool;

另一个问题是两个平台之间的地理/几何字段非常不同。使用ST_AsText(geo)将几何字段导出为WKT格式，并在SQL Server端进行适当转换。

可能还需要进行更多调整以解决这种不兼容性。

编辑。虽然这种技术从技术上讲确实可行，但我正在尝试将100多个表中的数百万条记录传输到SQL Azure，而通过bcp到SQL Azure却非常不稳定。我一直收到间歇性的无法打开BCP主机数据文件错误，服务器间歇性超时，出现某些记录没有被传输的情况，而没有任何错误或问题的迹象。因此，这种技术不适用于向Azure SQL传输大量数据。

- Brad Mathews

Answer 3

您可以使用Navicate这个强大的GUI工具来处理各种数据库，包括Postgres和SQL Server。您可以按照以下步骤轻松传输模式和数据：

1. 为源数据库和目标数据库创建两个连接

2. 转到工具->数据传输

选择源数据库和目标数据库及其IP、数据库名称和模式

如选项所示，如果目标表不存在，则会创建

Tada，仅需10分钟即可将我63个表及其数据从Postgres传输到SQL Server。

享受它吧！

- Simin Ghasemi