Microsoft SQL Server 到 PostgreSQL 迁移，作者 Ian Harding

来自 PostgreSQL 维基
跳转到导航跳转到搜索

最初由 Ian A. Harding <[email protected]> 创建，版本 1.00，2001 年 9 月 17 日，更多信息请查看历史记录。

如何将数据库从流行的专有数据库迁移到世界上最强大的开源数据库。

免责声明

以下文档真诚地提供，并仅包含安全的编程和流程。对于因遵循这些说明而直接或间接导致的任何个人或设备的任何损失或损坏，作者不承担任何责任。

简介

Microsoft SQL Server 是一款非常流行的关系型数据库管理系统 (RDBMS)，其许可证限制性很强，如果数据库规模较大或被大量客户端使用，则拥有成本很高。但是，它确实提供了一个非常用户友好的界面，易于学习和使用，并且入门级配置成本低廉。这导致了非常庞大的用户群。

PostgreSQL 现在在基本功能集、可靠性和性能方面挑战 MS SQL Server，其许可证限制性远低于 MS SQL Server，并且是开源的。当然，随着拥有成本成为问题，以及用户对关系型数据库系统的了解不断加深，用户正在从 MS SQL Server 迁移到 PostgreSQL。

本 HOW-TO 旨在帮助那些现在准备将数据库迁移到 PostgreSQL 的 MS SQL Server 用户。

注意事项

RDBMS 功能的实现方式以及程度各不相同。一些应用程序严重依赖所谓的中间件，或者依赖客户端应用程序来处理业务逻辑。另一些应用程序则试图在数据库中放入尽可能多的逻辑。如果您的应用程序属于后者，那么您的迁移将更加困难。虽然将逻辑放在数据库服务器中是一个明智的设计选择，但它将需要使用特定供应商的结构化查询语言 (SQL) 扩展进行编程，例如 Microsoft 的 Transact SQL (T-SQL)。PostgreSQL 也是如此。没有简单的方法来迁移存储过程、触发器或规则。从好的方面来说，PostgreSQL 提供了多种语言选项，这些选项都比 T-SQL 更优雅。

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

最后，程序员选择的 SQL 语法也会影响此过程。大多数 RDBMS 都在接近不断发展的 SQL 标准。也就是说，它们正逐渐摆脱特定供应商的语法，例如用于左外连接的 '*=' 语法。在 MS SQL Server 7.0 版本中仍然支持此语法，但在 PostgreSQL 中从未支持过。

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

我几乎在所有情况下都使用工具命令语言 (TCL)，因此我也在这里使用它。您可以使用任何您喜欢的语言。

表

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

简要查看此文件将向您展示我们要面对的挑战。MS 在所有标识符周围使用方括号，以保护您免受设计不良的选择，例如使用保留关键字，从而导致如下情况：

CREATE TABLE [dbo].[Select] ([Union] [int])

是可能的。PostgreSQL 使用双引号代替。MS 在所有对象中使用对象所有者限定符，在本例中为“dbo”。PostgreSQL 在对象名称中没有这样的限定符。

另一个需要注意的是，MS SQL 标识符区分大小写，但在实践中，大多数安装都是区分大小写。PostgreSQL 在 SQL 关键字和未加引号的标识符中区分大小写，强制所有查询转换为小写。它与区分大小写不同，因为您可以使用上面提到的双引号保护创建表，以便只能使用相同的双引号方法访问它们。我发现，在迁移到 PostgreSQL 时，最好放弃对象标识符中的大小写。此外，为了避免将来出现问题，最好避免使用需要引号的任何标识符。

值得注意的是，对于数据比较，PostgreSQL 区分大小写，并且没有选项可以更改此行为。如果大小写对操作不重要，并且存在不同的可能性，则必须在文本比较的两侧强制数据转换为大写或小写。此转换可能是强制用于联接和比较的数据全部转换为大写或小写的最佳时机。您还需要查看应用程序，以查找利用 MS SQL Server 的典型区分大小写功能的代码，这些代码比较用户输入的信息。

索引是一个亮点，大部分都是。PostgreSQL 中的 CLUSTER 关键字与 MS SQL Server 索引创建中的 CLUSTERED 关键字不同。PostgreSQL 允许您“聚类”表，即以该字段的顺序重新排列表中的元组。这听起来不错，但聚类不会为更新和插入操作维护，而且每次生成聚类时都会破坏您所有的其他索引。

话虽如此，这里列出了一些需要更正的内容：

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

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

数据

数据就是数据。它以文本形式导入，并根据您创建表时使用的数据类型，由数据库转换为适当的格式。如果您有二进制数据，我建议您咨询其他专家。

当然，这里也有几个需要注意的地方。由于我们使用 COPY 命令，并且它将换行符解释为元组的结尾，因此您需要清除 MS SQL Server 中所有文本字段中潜伏的换行符。这很容易做到。此外，MS SQL Server 中的数据转储将使用标准的 cr/lf 行终止符，需要将其更改为 lf，否则会在字符串比较中造成混乱，以及其他问题。我采取了简单的方法，通过 ftp 将转储文件下载到我的运行我最喜欢的类 Unix 操作系统的机器上，该机器会为您完成此转换。

从 MS SQL Server 中转储数据的第一个步骤是在 Win32 机器上将所有字段的名称输入到一个文本文件中。您可以作弊并发出

select name from sysobjects where type = 'U'

在查询分析器 (ISQL-W) 中获取列表，然后将结果保存到文件。然后，编写一个方便的小脚本以调用 bcp，即批量复制程序。我的脚本如下所示：

set file [open "C:\\inetpub\\ftproot\\tablelist.txt" r] while {![eof $file]} {
    set table [gets $file]
    exec bcp ..$table out $table -c -k -S192.168.100.1 -Usa -Ppassword -r ~
}
close $file

这会将所有列出的表转储到当前目录中同名文件中。-c 标志表示使用纯字符格式。-k 标志告诉 bcp “保留空值”。这在稍后导入数据时很重要。-r 是“行终止符”。为了更轻松地清除回车符，我使用它来标记行的结束。我将此脚本放在 C:\InetPub\ftproot 目录中，这样我就可以直接进入下一步。

从类 Unix 机器上启动 ftp 并获取您之前创建的文件列表。将其放到工作目录中。更改到新的工作目录并获取文件

ftp> lcd /home/homer/workdir Local directory now /home/homer/workdir
ftp> fget tablelist.txt

这应该将所有数据文件下载到工作目录中，并神奇地将行终止符转换为类 Unix 格式。如果您不能使用 FTP，则可以使用其他方法将文件从这里传输到那里。请注意，您可能需要使用一小段 sed 脚本来解决 cr/lf 问题。

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

#!/usr/pkg/bin/tclsh
set file [open tblnames r]
set flist [read -nonewline $file]
close $file
set flist [split $flist \n]
foreach f $flist {
    set file [open $f r]
    set data [read -nonewline $file]
    close $file
    regsub -all {\000} $data {} data
    regsub -all {\n} $data \\\n data
    regsub -all {~} $data \n data
    set file [open $f w]
    puts -nonewline $file $data
    close $file
}

regsub 行是完成工作的部分。它们将所有空值 (\000) 替换为空字符串，将所有换行符替换为文字“\n”，这将告诉 COPY 在我们导入文件时该怎么做，然后我的行终止符被替换为换行符，这是 COPY 所期望的。有更干净、更容易的方法来做到这一点，但你明白意思。

现在，回到您之前编辑的用于创建数据库对象的 sql 文件。我假设它此时位于类 Unix 机器上。它应该包含一系列 CREATE TABLE 语句，然后是 ALTER TABLE 和 CREATE INDEX 等语句。现在我们需要做的就是告诉它，我们希望在创建表后、但在其他任何操作之前加载数据。

对于每个 CREATE TABLE 语句，请在后面添加一个 COPY 语句。例如

COPY tablename FROM '/home/homer/workdir/tablename' with null as ;

完成此操作后，将其针对您的 PostgreSQL 数据库执行，例如

$ psql newdb < modifiedscript.sql &> outfile

应该可以工作。输出文件对于查找问题很有帮助。它会变得很乱，因此

$ grep ERROR outfile

可以告诉您事情进展得如何。我保证您需要进行一些故障排除。

视图

视图非常简单，只要您没有在其中使用太多函数。我比较喜欢 isnull()。与大多数函数一样，它也有 PostgreSQL 等效函数 coalesce()。令人惊讶的是，许多函数可以正常工作。例如，round() 完全相同。datepart() 变为 date_part()，但参数相同，尽管 PostgreSQL 对格式字符串可能更加严格。例如，SQL Server 接受 datepart(yyyy, mydatefield) 以及 datepart(year, mydatefield)。PostgreSQL 希望看到 date_part('year', mydatefield)（注意单引号）。

生成视图的 sql 与生成表的 sql 非常相似。在企业管理器中，右键单击您的数据库并选择“所有任务”，然后从上下文菜单中选择“生成 SQL 脚本”。取消选中“脚本所有对象”，然后选择“所有视图”。在“格式”选项卡中，取消选中“生成 DROP...”。在“选项”选项卡中，选择“MS-DOS”文件格式，并确保选中“创建一个文件”。单击“确定”，为其命名，并将其放到可以找到的位置。

将此文件运行通过您创建的用于清理表的 sql 的相同脚本，看看它是否可以在 PostgreSQL 上运行。如果不行，您将需要对函数进行一些修复。

总结

将数据库从 MS SQL Server 迁移并非总是容易。但是，这样做总是值得的。你会发现 PostgreSQL 是一款极其强大且灵活的产品，拥有全球最好的技术支持，即产品的实际开发人员和用户。如果你花了好几天时间试图让 xp_sendmail 在 SQL Server 7.0 版本上运行，或者想知道那些庞大的“服务包”中到底有什么，那么你一定会喜欢上它。

扩展链接

  • Ispirer 工具包 自动迁移整个数据库架构（表、视图、存储过程、函数、触发器等），并将数据从 Microsoft SQL Server 迁移到 PostgreSQL。
  • SQLWays 工具包 是一款易于使用的数据库迁移工具，用于在最流行的数据库之间迁移整个数据库架构、SQL 对象、表和数据。该工具提供免费的在线转换器，并提供免费的演示许可证。
  • Convertum.ru 是一款强大的工具，可以自动迁移 25 多个数据库，包括从 SQL Server 迁移到 PostgreSQL。提供 30 天免费试用。
  • Full Convert 从 SQL Server 到 PostgreSQL 的即时数据库转换。事实上，它原生支持 30 多个数据库引擎，并且可以在两台机器上同时运行数据库，实现每秒数十万条记录的转换速度。
  • DBConvert 数据库转换/同步软件 在 PostgreSQL/Amazon 和 Microsoft SQL Server、SQL Azure 之间迁移和同步数据。
  • ESF 数据库迁移工具包 使您能够在 3 个简单的步骤中将数据从 SQL Server 迁移到 PostgreSQL，无需编写任何脚本。支持 PostgreSQL、MySQL、Oracle、SQL Server、IBM DB2、Informix、Microsoft Access、Microsoft Excel、dBase、Foxpro、Firbird、SQLite 等。
  • DB 迁移助手 开源数据库迁移应用程序，用 php 编写。目前支持从 mssqlnative 迁移到 postgres9。
  • MSSQL-to-PostgreSQL 是一款用于在 SQL Server 或 SQL Azure 与 PostgreSQL 之间迁移和同步数据的程序。高性能得益于直接连接到源数据库和目标数据库。命令行支持允许脚本化、自动化和调度转换过程。
  • Babelfish for PostgreSQL 为 PostgreSQL 添加了一个端点，该端点可以理解 SQL Server 线程协议表格数据流 (TDS)，以及常用的 T-SQL 结构，包括 SQL 方言、游标、目录视图、数据类型、触发器、存储过程和函数等元素。
摘自 "https://wiki.postgresql.ac.cn/index.php?title=Microsoft_SQL_Server_to_PostgreSQL_Migration_by_Ian_Harding&oldid=39595"