使用 tdsfdw 扩展将数据从 SQL Server 迁移到 PostgreSQL 数据库博客

使用 tdsfdw 扩展将数据从 SQL Server 迁移至 PostgreSQL

关键要点

在本文中，我们将介绍如何使用 tdsfdw 扩展将 SQL Server 中的数据迁移到 PostgreSQL。借助 PostgreSQL 的灵活性和 tdsfdw 扩展的强大功能，用户可以顺利完成数据迁移，并处理与 SQL Server 中 DATE 数据类型相关的问题。该方法适用于简单的迁移需求，无需使用 AWS DMS 即可直接通过网络传输数据。

PostgreSQL 已成为许多企业和初创公司首选的开源关系数据库，支持领先的商业和移动应用程序。许多客户在从 Oracle 和 Microsoft SQL Server 等商业数据库引擎迁移时选择 PostgreSQL 作为目标数据库。AWS 提供了 Amazon Relational Database Service (Amazon RDS) for PostgreSQL 和 Amazon Aurora PostgreSQLCompatible Edition，作为完全托管的 PostgreSQL 数据库服务。

您可以通过 AWS Schema Conversion Tool (AWS SCT) 和 AWS Database Migration Service (AWS DMS) 将数据从 SQL Server 迁移到 PostgreSQL。AWS SCT 可将源数据库架构和大部分代码对象转换为目标数据库的兼容格式。借助 AWS DMS，您可以实现源数据库与目标数据库之间的数据迁移，而不需大幅停机。另一种迁移数据库的方法是使用 Babelfish for Aurora PostgreSQL， Babelfish 支持常用的 TSQL 语言和语义，减轻与应用程序数据库调用相关的代码更改，从而缩小需要重写的应用程序代码的范围，降低新增应用程序错误的风险。

对于一些简单的迁移需求，如少量表的单次迁移，客户会寻找快速迁移 SQL Server 到 PostgreSQL 的方法。本文展示了一种替代的数据迁移方法，使用 PostgreSQL 扩展 tdsfdw。PostgreSQL 的设计旨在轻松扩展，扩展是增强数据库功能的附加模块。tdsfdw 扩展允许 PostgreSQL 通过名为外部数据包装器的特性访问通过支持的表格数据流 (TDS) 协议的数据库由 Sybase 和 SQL Server 数据库使用。外部数据包装器负责从远程数据源获取数据并将其返回给 PostgreSQL 引擎。有关使用 PostgreSQL 外部数据包装器支持的联邦查询的更多信息，请参见 Federated query support for Amazon Aurora PostgreSQL and Amazon RDS for PostgreSQL。

解决方案概述

实现此解决方案的高级步骤如下：

前提条件

本文假定您已创建并运行 SQL Server 实例。在这里，我们有一个配置好的 RDS for SQL Server 数据库，包含以下信息。该解决方案也适用于在非 Amazon RDS 环境中设置的 SQL Server。

我们也假设您已创建并运行 PostgreSQL 数据库。在这里，我们有以下目标数据库。虽然我们使用 AWS Aurora PostgreSQL，但该解决方案在支持所需 tdsfdw 扩展的 Amazon RDS for PostgreSQL 上也有效。

以下图示说明我们的数据库配置：

我们使用 Amazon DMS Sample Database for SQL Server 的架构来演示迁移过程。

该解决方案涉及创建和使用 AWS 资源，因此会在您的账户中产生费用，包括但不限于 RDS for SQL Server 实例和存储成本以及 Aurora 实例和存储成本。有关更多信息，请参考 AWS 定价。

配置目标 PostgreSQL 数据库

执行以下步骤以设置 tdsfdw 扩展并创建用于存储外部表和迁移目标表的空架构：

连接到目标数据库 fdwdemo，使用具有 rdssuperuser 权限的账户。您可以使用在 RDS 实例创建时创建的 admin 用户 postgres，该用户具有此类权限，并在提示时输入密码。

bashpsql host=pgsqltargetclusterxxltregiongtrdsamazonawscom port=5432 dbname=fdwdemo username=postgres

创建一个空架构。在这里，我们使用 fdw 架构来存储直接引用 SQL Server 数据库的外部表。我们使用 dmssample 架构来存储从 SQL Server 迁移的对象。尽管空架构并不是必须的，但与其他内部已有对象的现有架构相比，它使清理和故障排除更加容易。

sqlCREATE SCHEMA fdwCREATE SCHEMA dmssample

输出应如下所示：

sqlfdwdemo=gt CREATE SCHEMA fdwCREATE SCHEMAfdwdemo=gt CREATE SCHEMA dmssampleCREATE SCHEMA

运行以下命令以创建用户 dmsuser，用于以最小权限进行数据迁移：

sqlCREATE USER dmsuser WITH PASSWORD yourcomplexpasswordGRANT ALL PRIVILEGES ON SCHEMA fdw TO dmsuserGRANT ALL PRIVILEGES ON SCHEMA dmssample TO dmsuser

输出应如下所示：

sqlfdwdemo=gt CREATE USER dmsuser WITH PASSWORD yourcomplexpasswordCREATE ROLEfdwdemo=gt GRANT ALL PRIVILEGES ON SCHEMA fdw TO dmsuserGRANTfdwdemo=gt GRANT ALL PRIVILEGES ON SCHEMA dmssample TO dmsuserGRANT

运行以下命令以安装 tdsfdw 扩展：

sqlCREATE EXTENSION tdsfdw

输出应如下所示：

sqlfdwdemo=gt CREATE EXTENSION tdsfdwCREATE EXTENSION

在目标数据库PostgreSQL中创建一个外部服务器SQL Server，并授予 dmsuser 使用权限。外部服务器表示您希望从 PostgreSQL 连接的远程数据库。在这种情况下，它是 sqlsourcexxltregiongtrdsamazonawscom 实例中的 dmssample 数据库。有关查询规划时使用的 TDS 版本和优化器相关参数等高级选项，请参见 TDS 外部数据包装器。

sqlCREATE SERVER mssqldmssample FOREIGN DATA WRAPPER tdsfdwOPTIONS (servername sqlsourcexxltregiongtrdsamazonawscom port 1433 database dmssample)GRANT USAGE ON FOREIGN SERVER mssqldmssample TO dmsuser

输出应如下所示：

sqlfdwdemo=gt CREATE SERVER mssqldmssample FOREIGN DATA WRAPPER tdsfdwfdwdemogt OPTIONS (servername sqlsourcexxltregiongtrdsamazonawscom port 1433 database dmssample)CREATE SERVERfdwdemo=gt GRANT USAGE ON FOREIGN SERVER mssqldmssample TO dmsuser GRANT

指定用户 dmsuser 登入外部服务器时使用的用户名和密码。OPTIONS 子句中的 dmsuser 指的是在源 SQL Server 上创建的 SQL 登录名。

sqlCREATE USER MAPPING FOR dmsuserSERVER mssqldmssampleOPTIONS (username dmsuser password yourcomplexpassword)

输出应如下所示：

sqlfdwdemo=gt CREATE USER MAPPING FOR dmsuserfdwdemogt SERVER mssqldmssamplefdwdemogt OPTIONS (username dmsuser password yourcomplexpassword)CREATE USER MAPPING

从 SQL Server 数据库导入架构

完成以下步骤以导入架构：

使用之前创建的用户 dmsuser 连接到目标数据库 fdwdemo。在提示时输入密码。

bashpsql host=pgsqltargetclusterxxltregiongtrdsamazonawscom port=5432 dbname=fdwdemo username=dmsuser

为了批量创建所需的外部表，您可以使用 IMPORT FOREIGN SCHEMA 命令，而不是逐个使用 CREATE FOREIGN TABLE。在以下 SQL 命令中，我们在创建外部表时排除了视图 getNewID 并在外部表定义中包含列的默认表达式。

sqlIMPORT FOREIGN SCHEMA dbo EXCEPT (getNewID) FROM SERVER mssqldmssample INTO fdw OPTIONS (importdefault true)

输出应如下所示：

sqlfdwdemo=gt IMPORT FOREIGN SCHEMA dbofdwdemogt EXCEPT (getNewID)fdwdemogt FROM SERVER mssqldmssamplefdwdemogt INTO fdwfdwdemogt OPTIONS (importdefault true)IMPORT FOREIGN SCHEMA

导入外部表后，运行以下命令列出目标数据库 fdw 中的表：

sqlfdwdemo=gt dE fdw

可选地，运行以下查询来统计源数据库中外部数据包装器的行数。SELECT 语句为架构 fdw 中的每个外部表生成 SELECT COUNT() 语句，gexec 命令逐一运行生成的语句：

sqlSELECT SELECT COUNT() as foreigntablenameCNT FROM fdw foreigntablename FROM informationschemaforeigntables WHERE foreigntableschema =fdwAND foreignservername = mssqldmssamplegexec

根据源表定义创建目标表

运行以下脚本，根据前面导入的外部表定义创建目标表，并在表创建后使用 CREATE TABLE AS SELECT (CTAS) SQL 语法填充数据。根据源和目标的大小，这可能需要几分钟来创建表并传输数据。

如果您已经使用其他方法如 AWS SCT 或 Liquibase创建了目标表，则请改用 INSERT INTOSELECT FROM 语法生成脚本。例如：

sqlINSERT INTO dmssampleticketpurchasehist AS SELECT FROM fdwticketpurchasehist

sqlSELECT CREATE TABLE dmssample foreigntablename AS SELECT FROM fdw foreigntablename FROM informationschemaforeigntablesWHERE foreigntableschema =fdwAND foreignservername = mssqldmssamplegexec

生成的脚本和执行输出如下，部分输出已被省略以提高可读性：

sqlfdwdemo=gt SELECT CREATE TABLE dmssample foreigntablename AS SELECT FROM fdw foreigntablename fdwdemogt FROM informationschemaforeigntablesfdwdemogt WHERE foreigntableschema =fdwfdwdemogt AND foreignservername = mssqldmssample

处理由于地区设置差异导致的日期相关列

作为故障排除的一部分，让我们检查 PostgreSQL 中的外部表结构和 SQL Server 中的源表结构，以sportingevent 表为例。

要显示外部表的列定义，请在 psql 终端中运行 d fdwsportingevent：

sqlfdwdemo=gt d fdwsportingevent

要检查源表定义，在连接到源 SQL Server 数据库时使用 SQL Server Management Studio (SSMS) 脚本化 sportingevent 表的定义。有关说明，请参见 脚本表。

sqlCREATE TABLE [dbo][sportingevent]( [id] [bigint] IDENTITY(11) NOT NULL [sporttypename] [varchar](15) NOT NULL [hometeamid] [int] NOT NULL [awayteamid] [int] NOT NULL [locationid] [int] NOT NULL [startdatetime] [datetime] NOT NULL [startdate] AS (CONVERT([date][startdatetime])) [soldout] [int] NOT NULL CONSTRAINT [sportingeventpk] PRIMARY KEY CLUSTERED ( [id] ASC)WITH (PADINDEX = OFF STATISTICSNORECOMPUTE = OFF IGNOREDUPKEY = OFF ALLOWROWLOCKS = ON ALLOWPAGELOCKS = ON) ON [PRIMARY]) ON [PRIMARY]GO

要确认之前出现的 ERROR invalid input 中的根本原因，单独查询 startdate。同样的错误应如预期出现：

sqlfdwdemo=gt select startdate from fdwsportingevent limit 1

由于访问外部表时，SQL Server 中的 DATE 数据类型数据转换为 TEXT 格式，然后再转换为 DATE 数据类型，因此 PostgreSQL 默认设置的 YYYYMMDD 格式与通过外部服务器得到的 MM DD YYYY HH12MISSAM 格式不兼容。如果 SQL Server 中使用了 DATETIME2 数据类型列，也会出现类似问题。

解决方案 1：创建视图将源表数据类型从 DATE 转换为 DATETIME

在此解决方案中，我们将 DATE 数据类型转换为 DATETIME，后者在 PostgreSQL 中映射为 TIMESTAMP。为减少对源数据库的影响，我们创建一个视图而不是直接在源 SQL Server 数据库中进行修改。使用 SSMS 连接到源 SQL Server 数据库 dmssample，使用 SQL 登录 dmsuser 运行以下 SQL 以创建视图：

sqlcreate view dbosportingeventvasselect id sporttypename hometeamid awayteamid locationid startdatetime CONVERT(datetimestartdate) startdatewrap soldoutfrom dbosportingevent

在目标 PostgreSQL 数据库中，将视图 sportingeventv 作为新外部表导入。此处的 LIMIT TO 子句用于限制仅导入此表。

sqlIMPORT FOREIGN SCHEMA dbo LIMIT TO (sportingeventv) FROM SERVER mssqldmssample INTO fdw OPTIONS (importdefault true)

输出应如下所示：

sqlfdwdemo=gt IMPORT FOREIGN SCHEMA dbofdwdemogt LIMIT TO (sportingeventv)fdwdemogt FROM SERVER mssqldmssamplefdwdemogt INTO fdwfdwdemogt OPTIONS (importdefault true)IMPORT FOREIGN SCHEMA

通过 fdwsportingeventv 创建一个不会涉及数据的目标表。此过程中不会涉及实际的数据转换，因此成功完成：

sqlCREATE TABLE dmssamplesportingevent AS SELECT FROM fdwsportingevent WHERE 1=2

接下来，通过 sportingeventv 视图将数据插入，并