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

关键要点

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

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

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

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

解决方案概述

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

1. 配置目标 PostgreSQL 数据库。
2. 从 SQL Server 数据库导入架构。
3. 根据源表定义创建目标表。
4. 解决由于区域设置差异导致的日期相关列问题。
5. 执行数据验证。
6. 清理用于迁移的中间对象。

前提条件

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

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

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


我们使用 的架构来演示迁移过程。

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

配置目标 PostgreSQL 数据库

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

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

bash psql --host=pgsql-target.cluster-xx.<region>.rds.amazonaws.com --port=5432 --dbname=fdwdemo --username=postgres

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

sql CREATE SCHEMA fdw; CREATE SCHEMA dms_sample;

输出应如下所示：

sql fdwdemo=> CREATE SCHEMA fdw; CREATE SCHEMA fdwdemo=> CREATE SCHEMAdms_sample; CREATE SCHEMA

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

sql CREATE USER dms_user WITH PASSWORD 'your_complex_password'; GRANT ALLPRIVILEGES ON SCHEMA fdw TO dms_user; GRANT ALL PRIVILEGES ON SCHEMAdms_sample TO dms_user;

输出应如下所示：

sql fdwdemo=> CREATE USER dms_user WITH PASSWORD 'your_complex_password'; CREATE ROLE fdwdemo=> GRANT ALL PRIVILEGES ON SCHEMA fdw TO dms_user; GRANTfdwdemo=> GRANT ALL PRIVILEGES ON SCHEMA dms_sample TO dms_user; GRANT

4. 运行以下命令以安装 tds_fdw 扩展：

sql CREATE EXTENSION tds_fdw;

输出应如下所示：

sql fdwdemo=> CREATE EXTENSION tds_fdw; CREATE EXTENSION

5. 在目标数据库（PostgreSQL）中创建一个外部服务器（SQL Server），并授予 dms_user 使用权限。外部服务器表示您希望从 PostgreSQL 连接的远程数据库。在这种情况下，它是 sql-source.xx.<region>.rds.amazonaws.com 实例中的 dms_sample 数据库。有关查询规划时使用的 TDS 版本和优化器相关参数等高级选项，请参见 。

sql CREATE SERVER mssql_dms_sample FOREIGN DATA WRAPPER tds_fdw OPTIONS (servername 'sql-source.xx.<region>.rds.amazonaws.com', port '1433', database 'dms_sample'); GRANT USAGE ON FOREIGN SERVER mssql_dms_sample TO dms_user;

输出应如下所示：

sql fdwdemo=> CREATE SERVER mssql_dms_sample FOREIGN DATA WRAPPER tds_fdwfdwdemo-> OPTIONS (servername 'sql-source.xx.<region>.rds.amazonaws.com', port '1433', database 'dms_sample'); CREATE SERVER fdwdemo=> GRANT USAGE ON FOREIGNSERVER mssql_dms_sample TO dms_user; GRANT

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

sql CREATE USER MAPPING FOR dms_user SERVER mssql_dms_sample OPTIONS (username 'dms_user', password 'your_complex_password');

输出应如下所示：

sql fdwdemo=> CREATE USER MAPPING FOR dms_user fdwdemo-> SERVERmssql_dms_sample fdwdemo-> OPTIONS (username 'dms_user', password 'your_complex_password'); CREATE USER MAPPING

从 SQL Server 数据库导入架构

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

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

bash psql --host=pgsql-target.cluster-xx.<region>.rds.amazonaws.com --port=5432 --dbname=fdwdemo --username=dms_user

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

sql IMPORT FOREIGN SCHEMA dbo EXCEPT ("getNewID") FROM SERVERmssql_dms_sample INTO fdw OPTIONS (import_default 'true');

输出应如下所示：

sql fdwdemo=> IMPORT FOREIGN SCHEMA dbo fdwdemo-> EXCEPT ("getNewID") fdwdemo-> FROM SERVER mssql_dms_sample fdwdemo-> INTO fdw fdwdemo-> OPTIONS (import_default 'true'); IMPORT FOREIGN SCHEMA

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

sql fdwdemo=> \dE fdw.*

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

sql SELECT 'SELECT COUNT(*) as ' || foreign_table_name||'_CNT FROM fdw.' || foreign_table_name|| ';' FROM information_schema.foreign_tables WHEREforeign_table_schema ='fdw' AND foreign_server_name = 'mssql_dms_sample'; \gexec

根据源表定义创建目标表

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

如果您已经使用其他方法（如 或 ）创建了目标表，则请改用 INSERT INTO..SELECT.. FROM.. 语法生成脚本。例如：

sql INSERT INTO dms_sample.ticket_purchase_hist AS SELECT * FROMfdw.ticket_purchase_hist;

sql SELECT 'CREATE TABLE dms_sample.' || foreign_table_name || ' AS SELECT * FROM fdw.' || foreign_table_name || ';' FROM information_schema.foreign_tablesWHERE foreign_table_schema ='fdw' AND foreign_server_name = 'mssql_dms_sample'; \gexec

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

sql fdwdemo=> SELECT 'CREATE TABLE dms_sample.' || foreign_table_name || ' ASSELECT * FROM fdw.' || foreign_table_name || ';' fdwdemo-> FROMinformation_schema.foreign_tables fdwdemo-> WHERE foreign_table_schema ='fdw' fdwdemo-> AND foreign_server_name = 'mssql_dms_sample';

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

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

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

sql fdwdemo=> \d fdw.sporting_event

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

sql CREATE TABLE NOTNULL, NOT NULL, [home_team_id] [int] NOT NULL, [away_team_id] [int] NOT NULL, [location_id] [int] NOT NULL, [start_date_time] [datetime] NOT NULL, [start_date] AS (CONVERT([date],[start_date_time])), [sold_out] [int] NOT NULL, CONSTRAINT [sporting_event_pk] PRIMARY KEYCLUSTERED ( [id] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] GO

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

sql fdwdemo=> select start_date from fdw.sporting_event limit 1;

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

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

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

sql create view dbo.sporting_event_v as select id, sport_type_name, home_team_id, away_team_id, location_id, start_date_time, CONVERT(datetime,start_date) start_date_wrap, sold_out fromdbo.sporting_event;

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

sql IMPORT FOREIGN SCHEMA dbo LIMIT TO (sporting_event_v) FROM SERVERmssql_dms_sample INTO fdw OPTIONS (import_default 'true');

输出应如下所示：

sql fdwdemo=> IMPORT FOREIGN SCHEMA dbo fdwdemo-> LIMIT TO (sporting_event_v) fdwdemo-> FROM SERVER mssql_dms_sample fdwdemo-> INTO fdw fdwdemo-> OPTIONS (import_default 'true'); IMPORT FOREIGN SCHEMA

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

sql CREATE TABLE dms_sample.sporting_event AS SELECT * FROMfdw.sporting_event WHERE 1=2;

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