将 Microsoft SQL Server 迁移到 PostgreSQL 由 Ian Harding

来自 PostgreSQL wiki
跳转到导航跳转到搜索

作者 Ian A. Harding <[email protected]> v1.00，最后更新于 2001 年 9 月 17 日

如何将数据库从最受欢迎的专有数据库管理系统之一迁移到世界上最强大的开源管理系统。

摘要

以下文件出于善意提供，仅供安全编程和过程理解。作者不对因直接或间接遵循这些说明而导致的人身或设备损失或损坏承担任何责任。

介绍

Microsoft SQL Server 是一个非常流行的数据库管理系统，它具有高度限制性的许可证，如果数据库规模很大，或被大量客户端使用，则拥有成本很高。然而，它提供了相当友好的用户界面，非常易于使用和学习，并且入门级的配置也相对便宜。这导致了一个庞大的用户群。

PostgreSQL 现在在基本功能、可用性和性能方面与 MS SQL Server 相匹敌，它拥有更宽松的许可证，并且是开源的。作为迁移过程的缓解措施，从 MS SQL Server 迁移到 PostgreSQL 的用户既要考虑拥有成本，又要扩展他们在关系数据库系统方面的知识。

本 HOW-TO 为准备立即将数据库迁移到 PostgreSQL 的用户而编写。

注意事项

RDBMS 的特性在程序员中以不同的方式和不同的级别实现。一些应用程序高度依赖于所谓的中间件，或者业务逻辑由客户端应用程序处理。另一些则试图将尽可能多的逻辑放到数据库中。如果应用程序属于最后一类，则迁移将更加困难。虽然将逻辑放在数据库服务器中是一个合理的架构选择，但它将需要在特定供应商的结构化查询语言（SQL）中进行编程，例如 Microsoft SQL 的 Transact 扩展 (T-SQL)。PostgreSQL 也是如此。没有简单的方法来迁移存储过程、触发器或规则。从好的方面来说，PostgreSQL 提供了多种语言选项，它们都比 T-SQL 更令人愉悦。

所有 RDBMS 都提供内置函数。但是，与 SQL 的过程扩展一样，它们不可移植。幸运的是，它们之间存在一定程度的重叠，简单的语法使迁移相对容易。

最后，程序员选择的 SQL 语法可能会影响此过程。大多数 RDBMS 正在逐渐接近批准的 SQL 标准。也就是说，它们正在远离特定供应商的语法，例如用于 LEFT OUTER JOIN 的 “*=” 语法。这种语法在 MS SQL Server 7.0 版本中仍然受支持，但从未在 PostgreSQL 中受支持。

此过程将需要大量手动编辑脚本和数据文件，或者使用脚本语言以编程方式修改这些文件，然后进行大量的少量编辑。我还没有聪明到能够识别出所有可能的迁移选项，或者将它们放入一个脚本中。我已经在一个合理的时限内完成了对一个相对复杂的数据库应用程序的迁移。您的目标应该是这个，而不是一个技术上完美的脚本。


我将使用命令语言 (TCL) 工具来执行几乎所有操作，因此我在这里也使用它。您可以使用您想要的任何语言。

表格

使用 MS SQL Server 脚本工具导出表格定义。在企业管理器中，右键单击数据库并选择“生成所有任务”，然后在上下文菜单中选择“SQL 脚本”。取消选中“所有对象的脚本”选项，然后选择“所有表格”。在“格式”选项卡中，取消选中“生成...”。在“选项”选项卡中，选择“脚本索引和脚本主键...”。选择 MS-DOS 文件格式，并确保“创建文件”已选中。单击确定，给它一个名字，并将其放在你可以找到它的任何地方。

简要查看此文件将向您展示我们所面临的问题。MS 为所有标识符使用方括号，以防范像使用保留字作为疯狂的东西这样的糟糕的设计选择，例如

 CREATE TABLE [dbo]. [Seleccionar] ([Unión Europea] [int])

是可能的。PostgreSQL 反而使用双引号。MS 使用对象所有者的限定符为所有对象，在本例中为 'dbo'。PostgreSQL 没有这种对对象名称的限制。

需要注意的另一件事是，MS SQL Server 中的 SQL 标识符不区分大小写，但实际上大多数安装都将其设置为区分大小写。PostgreSQL 对 SQL 关键字和未引用的标识符不区分大小写，强制所有查询小写。它与不区分大小写不同，因为可以通过前面提到的双引号来保护创建表格，因此只能使用相同的双引号方法访问它们。我认为在迁移到 PostgreSQL 时最好放弃对对象标识符的大小写。此外，为了避免在路上出现问题，最好避免不必要的引号。

需要注意的是，对于数据比较，PostgreSQL 区分大小写，并且没有选项可以更改此行为。如果大小写对操作并不重要，并且它们之间存在差异，您将被迫在文本比较的两边强制将数据转换为大写或小写。此转换可能是一个强制数据用于比较和连接的好时机，使其始终为大写或小写。您还必须检查使用户输入的信息进行比较的请求代码，这些代码利用了 MS SQL Server 的典型不区分大小写特性。

另一个并非立即显而易见的问题是，MS SQL Server 支持包含逗号分隔列表的 ALTER TABLE 语句。PostgreSQL 目前不支持此功能。这一点很重要，因为 MS SQL Server 脚本工具会为所有约束创建 ALTER TABLE 语句。如果表格具有多个约束，则必须将其单独拆分为自己的 ALTER TABLE 语句，或者将其移到 CREATE TABLE 语句中。

索引是一个亮点，大多数情况下都是如此。PostgreSQL 中的 cluster 关键字与 MS SQL Server 创建索引中的 clustered 关键字不同。PostgreSQL 允许您对表格进行“cluster”，也就是说，重新排列表格中的元组，以便对该字段进行排序。这听起来不错，除了集群不会针对更新和插入操作保持，并且每次您创建集群时都会破坏所有其他索引之外。

综上所述，以下是一些需要纠正的部分列表：

  1. 强制小写。
  2. 删除所有方括号。
  3. 删除所有对象所有者前缀（即“dbo”）。
  4. 删除所有对文件组的引用（即“ON PRIMARY”）。
  5. 删除所有可选支持关键字（即“WITH NOCHECK”、“CLUSTERED”）。
  6. 更新所有不受支持的数据类型（即“DATETIME”转换为“timestamp”）。此外，这是一个摆脱 MONEY 的好时机。它在 PostgreSQL 中受支持，但已经过时了。使用数值（19,4）。
  7. 将 T-SQL 批处理终止符 “GO” 替换为 PostgreSQL 批处理终止符 “;”。

将此文件放在安全的地方，现在我们将获取数据。

数据

数据就是数据。它以文本形式导入，并根据您在创建表格时使用的 数据类型 将其塑造成数据库中的正确形式。如果您有二进制数据，那就找错人了。

当然，这里也有一些错误。由于我们使用 COPY 命令，并且它将行尾解释为元组的结束，因此必须清除 MS SQL Server 中文本字段中潜伏的所有换行符。这很容易做到。此外，MS SQL Server 的数据转储使用标准 CR/LF 行终止符，它必须更改为 LF，否则会导致字符串比较等问题。我走了一条捷径，通过 FTP 将转储下载到我的机器上，我的机器使用我最喜欢的类 Unix 操作系统，它会为您进行转换。

从 MS SQL Server 转储数据的第一个步骤是在 Win32 机器上的文本文件中写入所有字段名称。你可以作弊，在查询分析器 (ISQL-W) 中输入

 select name from sysobjects WHERE type = 'U'

以获取列表，然后将结果保存到文件中。然后，编写一个非常有用的简短脚本，用于调用 BCP，即批量复制程序。我的脚本看起来像这样：

 conjunto de archivos [Open "C: \ \ inetpub \ \ ftproot \ \ tablelist.txt" r], mientras que ([eof $ file]) (
     cuadro [gets $ file]
     Exec bcp .. mesa $ out $ table-c-k-S192.168.100.1-Usa-Ppassword-R ~
 )
 cerca de $ archivo

这将所有列出的表格转储到当前目录中同名文件中。选项 -c 表示使用纯文本格式。标志 -k 告诉 bcp “保留空值”。这在以后导入数据时很重要。-R 是“行终止符”。为了便于传输时的换行符清理，我使用它来指示行的结束。我将此脚本放在 C:\Inetpub\ftproot 目录中，这样您就可以进行下一步。

从 Unix 机器上，启动 ftp 并获取您之前创建的列表文件。将其放在一个工作目录中。切换到新的工作目录并获取文件

 ftp> lcd / home / Homer / local workdir ahora / home / Homer / workdir
 ftp> fget tablelist.txt

这应该将所有数据文件下载到工作目录中，并神奇地将行终止符转换为与 Unix 兼容的格式。如果您无法使用 FTP，则还有其他方法可以将文件从这里转移到那里。只需要记住，您可能需要使用 sed 脚本来解决 CR/LF 问题。

现在，让我们解决嵌入式换行符问题。

 #! / usr / pkg / bin / tclsh conjunto de archivos [tblnames abierto r]
 flist conjunto [lectura nonewline $ file]
 cerca de $ archivo
 flist conjunto [$ split flist \ n]
 flist foreach f $ (
     conjunto de archivos [Open $ f r]
     conjunto de datos [read-nonewline $ file]
     cerca de $ archivo
     regsub-all (\ 000) () $ data de datos
     regsub-all (\ n) $ data \ \ \ n datos
     regsub todo ~) ($ datos \ n datos
     conjunto de archivos [Open $ f w]
     pone-nonewline $ file $ datos
     cerca de $ archivo
 )

使用 regsub 处理换行符。用空字符串替换所有空值（\000），然后将所有换行符替换为文字 "\n"，这将在导入文件时告诉复制程序执行什么操作，然后我的行结束符将替换为换行符，这是 COPY 期待的。有更干净、更容易的方法来做这件事，但你明白了。

现在，重新打开修改过的 SQL 文件以创建您的对象数据库。我假设您现在在 Unix 环境中。您应该有一系列 CREATE TABLE 命令，后跟 ALTER TABLE 和 CREATE INDEX 等语句。现在我们要做的是告诉它在创建完表后，但在任何其他操作之前，我们要加载数据。

对于每个 CREATE TABLE 命令，请接着使用一个 COPY 语句。类似于

 COPY FROM TableName "/ home / Homer / workdir / NombreDeTabla con nula como;

完成这些操作后，您可以在您的 PostgreSQL 数据库中执行以下命令，

 Newdb $ psql <modifiedscript.sql &> OUTFILE

应该可以正常运行。输出文件有助于查找问题。它变得如此混乱，

 OUTFILE $ cat | grep ERROR

可以提供有关事情进展方式的线索。我向您保证，您将需要进行一些调整。

视图 == ==

视图相当容易，只要您没有在其中使用太多函数。我最喜欢的函数之一是 IsNull()。像大多数函数一样，它有一个 PostgreSQL 对应函数，即 coalesce()。令人惊讶的是，许多函数都能正常工作。例如，round() 与 SQL Server 中完全相同。DatePart() 变成了 date_part()，但参数相同，尽管 PostgreSQL 对格式字符串可能更加严格。例如，SQL Server 会接受 DatePart(yyyy, myDateField) 和 DatePart(year, myDateField)。PostgreSQL 想要看到 date_part('year', myDateField)（请注意单引号）。

用于生成视图的 SQL 与用于表的 SQL 大致相同。在企业管理器中，右键单击数据库并选择“生成所有任务”，然后在上下文菜单中选择“生成 SQL 脚本”。取消选中“脚本化所有对象”选项，然后选择“所有视图”。在“格式”选项卡中，取消选中“生成...”选项。在“选项”选项卡中，选中“选择 MS-DOS 文件格式”，并确保选中“创建文件”。单击“确定”，为其命名，并将其保存在您能找到的位置。

通过用于清理表的 SQL 的相同脚本运行此文件，并查看它是否在 PostgreSQL 中正常工作。如果不是，您将需要修复其中一些函数。

摘要

从 MS SQL Server 转换数据库并不总是容易。但它总是值得的。您会发现 PostgreSQL 是一个非常强大且灵活的产品，拥有世界上最好的技术支持、开发人员和产品实际用户。如果您在 SQL Server 7.0 中花费了几天时间试图使 xp_sendmail 工作，或者想知道那些庞大的“服务包”中的内容，那么您会喜欢它。

摘自 "https://wiki.postgresql.ac.cn/index.php?title=Migración_de_Microsoft_SQL_Server_a_PostgreSQL_por_Ian_Harding&oldid=17243"