一、tds_fdw概述
1.1、tds_fdw 功能介绍
tds_fdw 是 PostgreSQL 中的一个外部数据包装器（Foreign Data Wrapper），它允许 PostgreSQL 连接到使用 Tabular Data Stream（TDS）协议的数据库，如 Sybase 和 Microsoft SQL Server。tds_fdw 的核心功能是通过 FreeTDS 库实现与 TDS 协议兼容数据库的通信，支持 PostgreSQL 9.2 及更高版本，并且已经在多种操作系统上进行了测试，包括但不限于 CentOS、Ubuntu、openSUSE 等。
1.2、tds_fdw 用途
主要是在 PostgreSQL 中集成和操作远程的 SQL Server 数据，而无需进行复杂的数据迁移或重建整个数据结构。它适用于需要在 PostgreSQL 环境中集成 SQL Server 或 Sybase 数据的场景，或者在进行数据迁移或实时同步任务时需要临时访问非 PostgreSQL 数据库的场景。
1.3、部署流程：
安装 tds_fdw 通常涉及以下几个步骤：
- 安装依赖：首先需要安装 freetds 及其开发库，这可以通过包管理器如 yum 或 apt 来完成。
- 下载和解压tds_fdw包：从 GitHub 仓库下载 tds_fdw 的压缩包，并在服务器上解压。
- 编译和安装：使用 make 命令编译 tds_fdw，并通过 make install 将编译后的文件安装到 PostgreSQL 的扩展目录中。
- 创建外部服务器和用户映射：在 PostgreSQL 中创建外部服务器和用户映射，以便 PostgreSQL 知道如何连接到远程数据库。
- 创建外部表：创建一个外部表来表示远程数据库中的表，这样就可以在 PostgreSQL 中查询远程数据了。
1.4、官网地址
更多信息，请查看官网地址：
https://github.com/tds-fdw/tds_fdw
二、环境准备
2.1、安装依赖
sudo yum install epel-release
sudo yum install freetds-devel
sudo yum install centos-release-scl
sudo yum install gcc make wget
2.2、下载安装包
[postgres16@Server install]$ wget https://github.com/tds-fdw/tds_fdw/archive/refs/heads/master.zip
--2024-07-25 13:32:12-- https://github.com/tds-fdw/tds_fdw/archive/refs/heads/master.zip
正在解析主机 github.com (github.com)... 20.205.243.166
正在连接 github.com (github.com)|20.205.243.166|:443... 已连接。
已发出 HTTP 请求，正在等待回应... 302 Found
位置：https://codeload.github.com/tds-fdw/tds_fdw/zip/refs/heads/master [跟随至新的 URL]
--2024-07-25 13:32:13-- https://codeload.github.com/tds-fdw/tds_fdw/zip/refs/heads/master
正在解析主机 codeload.github.com (codeload.github.com)... 20.205.243.165
正在连接 codeload.github.com (codeload.github.com)|20.205.243.165|:443... 已连接。
已发出 HTTP 请求，正在等待回应... 200 OK
长度：未指定 [application/zip]
正在保存至: “master.zip”
master.zip [ <=> ] 127.58K 153KB/s 用时 0.8s
2024-07-25 13:32:15 (153 KB/s) - “master.zip” 已保存 [130641]
[postgres16@Server install]$
2.3、解压安装包
[postgres16@Server install]$ unzip master.zip
Archive: master.zip
a694e823f3df2a3fd8f56744461ea4fe4338d8ea
creating: tds_fdw-master/
inflating: tds_fdw-master/.gitattributes
creating: tds_fdw-master/.github/
inflating: tds_fdw-master/.github/ISSUE_TEMPLATE.md
inflating: tds_fdw-master/.gitignore
inflating: tds_fdw-master/ForeignSchemaImporting.md
inflating: tds_fdw-master/ForeignServerCreation.md
inflating: tds_fdw-master/ForeignTableCreation.md
inflating: tds_fdw-master/InstallAlpine.md
inflating: tds_fdw-master/InstallDebian.md
inflating: tds_fdw-master/InstallOSX.md
inflating: tds_fdw-master/InstallRHELandClones.md
inflating: tds_fdw-master/InstallUbuntu.md
inflating: tds_fdw-master/InstallopenSUSE.md
inflating: tds_fdw-master/LICENSE
inflating: tds_fdw-master/META.json
inflating: tds_fdw-master/Makefile
inflating: tds_fdw-master/README.md
inflating: tds_fdw-master/UserMappingCreation.md
inflating: tds_fdw-master/Variables.md
creating: tds_fdw-master/include/
inflating: tds_fdw-master/include/deparse.h
inflating: tds_fdw-master/include/options.h
inflating: tds_fdw-master/include/tds_fdw.h
inflating: tds_fdw-master/include/visibility.h
creating: tds_fdw-master/logo/
inflating: tds_fdw-master/logo/tds_fdw.svg
creating: tds_fdw-master/sql/
inflating: tds_fdw-master/sql/tds_fdw.sql
creating: tds_fdw-master/src/
inflating: tds_fdw-master/src/deparse.c
inflating: tds_fdw-master/src/options.c
inflating: tds_fdw-master/src/tds_fdw.c
inflating: tds_fdw-master/tds_fdw--2.0.1--2.0.2.sql
inflating: tds_fdw-master/tds_fdw--2.0.2--2.0.3.sql
inflating: tds_fdw-master/tds_fdw.control
creating: tds_fdw-master/tests/
extracting: tds_fdw-master/tests/.gitignore
inflating: tds_fdw-master/tests/README.md
creating: tds_fdw-master/tests/lib/
extracting: tds_fdw-master/tests/lib/__init__.py
inflating: tds_fdw-master/tests/lib/messages.py
inflating: tds_fdw-master/tests/lib/tests.py
inflating: tds_fdw-master/tests/mssql-tests.py
inflating: tds_fdw-master/tests/postgresql-tests.py
creating: tds_fdw-master/tests/tests/
creating: tds_fdw-master/tests/tests/mssql/
inflating: tds_fdw-master/tests/tests/mssql/000_create_schema.json
inflating: tds_fdw-master/tests/tests/mssql/000_create_schema.sql
inflating: tds_fdw-master/tests/tests/mssql/001_create_tinyint_min_table.json
inflating: tds_fdw-master/tests/tests/mssql/001_create_tinyint_min_table.sql
inflating: tds_fdw-master/tests/tests/mssql/002_create_tinyint_max_table.json
inflating: tds_fdw-master/tests/tests/mssql/002_create_tinyint_max_table.sql
inflating: tds_fdw-master/tests/tests/mssql/003_create_smallint_min_table.json
inflating: tds_fdw-master/tests/tests/mssql/003_create_smallint_min_table.sql
inflating: tds_fdw-master/tests/tests/mssql/004_create_smallint_max_table.json
inflating: tds_fdw-master/tests/tests/mssql/004_create_smallint_max_table.sql
inflating: tds_fdw-master/tests/tests/mssql/005_create_int_min_table.json
inflating: tds_fdw-master/tests/tests/mssql/005_create_int_min_table.sql
inflating: tds_fdw-master/tests/tests/mssql/006_create_int_max_table.json
inflating: tds_fdw-master/tests/tests/mssql/006_create_int_max_table.sql
inflating: tds_fdw-master/tests/tests/mssql/007_create_bigint_min_table.json
inflating: tds_fdw-master/tests/tests/mssql/007_create_bigint_min_table.sql
inflating: tds_fdw-master/tests/tests/mssql/008_create_bigint_max_table.json
inflating: tds_fdw-master/tests/tests/mssql/008_create_bigint_max_table.sql
inflating: tds_fdw-master/tests/tests/mssql/009_create_decimal_table.json
inflating: tds_fdw-master/tests/tests/mssql/009_create_decimal_table.sql
inflating: tds_fdw-master/tests/tests/mssql/010_create_float4_table.json
inflating: tds_fdw-master/tests/tests/mssql/010_create_float4_table.sql
inflating: tds_fdw-master/tests/tests/mssql/011_create_float8_table.json
inflating: tds_fdw-master/tests/tests/mssql/011_create_float8_table.sql
inflating: tds_fdw-master/tests/tests/mssql/012_create_date_table.json
inflating: tds_fdw-master/tests/tests/mssql/012_create_date_table.sql
inflating: tds_fdw-master/tests/tests/mssql/013_create_time_table.json
inflating: tds_fdw-master/tests/tests/mssql/013_create_time_table.sql
inflating: tds_fdw-master/tests/tests/mssql/014_create_datetime_table.json
inflating: tds_fdw-master/tests/tests/mssql/014_create_datetime_table.sql
inflating: tds_fdw-master/tests/tests/mssql/015_create_datetime2_table.json
inflating: tds_fdw-master/tests/tests/mssql/015_create_datetime2_table.sql
inflating: tds_fdw-master/tests/tests/mssql/016_create_datetimeoffset_table.json
inflating: tds_fdw-master/tests/tests/mssql/016_create_datetimeoffset_table.sql
inflating: tds_fdw-master/tests/tests/mssql/017_create_char_table.json
inflating: tds_fdw-master/tests/tests/mssql/017_create_char_table.sql
inflating: tds_fdw-master/tests/tests/mssql/018_create_varchar_table.json
inflating: tds_fdw-master/tests/tests/mssql/018_create_varchar_table.sql
inflating: tds_fdw-master/tests/tests/mssql/019_create_varcharmax_table.json
inflating: tds_fdw-master/tests/tests/mssql/019_create_varcharmax_table.sql
inflating: tds_fdw-master/tests/tests/mssql/020_create_binary4_table.json
inflating: tds_fdw-master/tests/tests/mssql/020_create_binary4_table.sql
inflating: tds_fdw-master/tests/tests/mssql/021_create_varbinary4_table.json
inflating: tds_fdw-master/tests/tests/mssql/021_create_varbinary4_table.sql
inflating: tds_fdw-master/tests/tests/mssql/022_create_varbinarymax_table.json
inflating: tds_fdw-master/tests/tests/mssql/022_create_varbinarymax_table.sql
inflating: tds_fdw-master/tests/tests/mssql/023_create_null_datetime_table.json
inflating: tds_fdw-master/tests/tests/mssql/023_create_null_datetime_table.sql
inflating: tds_fdw-master/tests/tests/mssql/024_create_null_datetime2_table.json
inflating: tds_fdw-master/tests/tests/mssql/024_create_null_datetime2_table.sql
inflating: tds_fdw-master/tests/tests/mssql/025_create_match_column_table.json
inflating: tds_fdw-master/tests/tests/mssql/025_create_match_column_table.sql
inflating: tds_fdw-master/tests/tests/mssql/026_create_column_name_table.json
inflating: tds_fdw-master/tests/tests/mssql/026_create_column_name_table.sql
inflating: tds_fdw-master/tests/tests/mssql/027_create_query_option_table.json
inflating: tds_fdw-master/tests/tests/mssql/027_create_query_option_table.sql
inflating: tds_fdw-master/tests/tests/mssql/028_create_view_simple_prerequisites.json
inflating: tds_fdw-master/tests/tests/mssql/028_create_view_simple_prerequisites.sql
inflating: tds_fdw-master/tests/tests/mssql/029_create_view_simple.json
inflating: tds_fdw-master/tests/tests/mssql/029_create_view_simple.sql
creating: tds_fdw-master/tests/tests/postgresql/
inflating: tds_fdw-master/tests/tests/postgresql/000_create_schema.json
inflating: tds_fdw-master/tests/tests/postgresql/000_create_schema.sql
inflating: tds_fdw-master/tests/tests/postgresql/001_create_server.json
inflating: tds_fdw-master/tests/tests/postgresql/001_create_server.sql
inflating: tds_fdw-master/tests/tests/postgresql/002_create_user_mapping.json
inflating: tds_fdw-master/tests/tests/postgresql/002_create_user_mapping.sql
inflating: tds_fdw-master/tests/tests/postgresql/003_import_schema.json
inflating: tds_fdw-master/tests/tests/postgresql/003_import_schema.sql
inflating: tds_fdw-master/tests/tests/postgresql/004_tinyintmin.json
inflating: tds_fdw-master/tests/tests/postgresql/004_tinyintmin.sql
inflating: tds_fdw-master/tests/tests/postgresql/005_tinyintmax.json
inflating: tds_fdw-master/tests/tests/postgresql/005_tinyintmax.sql
inflating: tds_fdw-master/tests/tests/postgresql/006_smallintmin.json
inflating: tds_fdw-master/tests/tests/postgresql/006_smallintmin.sql
inflating: tds_fdw-master/tests/tests/postgresql/007_smallintmax.json
inflating: tds_fdw-master/tests/tests/postgresql/007_smallintmax.sql
inflating: tds_fdw-master/tests/tests/postgresql/008_intmin.json
inflating: tds_fdw-master/tests/tests/postgresql/008_intmin.sql
inflating: tds_fdw-master/tests/tests/postgresql/009_intmax.json
inflating: tds_fdw-master/tests/tests/postgresql/009_intmax.sql
inflating: tds_fdw-master/tests/tests/postgresql/010_bigintmin.json
inflating: tds_fdw-master/tests/tests/postgresql/010_bigintmin.sql
inflating: tds_fdw-master/tests/tests/postgresql/011_bigintmax.json
inflating: tds_fdw-master/tests/tests/postgresql/011_bigintmax.sql
inflating: tds_fdw-master/tests/tests/postgresql/012_decimal.json
inflating: tds_fdw-master/tests/tests/postgresql/012_decimal.sql
inflating: tds_fdw-master/tests/tests/postgresql/013_float4.json
inflating: tds_fdw-master/tests/tests/postgresql/013_float4.sql
inflating: tds_fdw-master/tests/tests/postgresql/014_float8.json
inflating: tds_fdw-master/tests/tests/postgresql/014_float8.sql
inflating: tds_fdw-master/tests/tests/postgresql/015_date.json
inflating: tds_fdw-master/tests/tests/postgresql/015_date.sql
inflating: tds_fdw-master/tests/tests/postgresql/016_time.json
inflating: tds_fdw-master/tests/tests/postgresql/016_time.sql
inflating: tds_fdw-master/tests/tests/postgresql/017_datetime.json
inflating: tds_fdw-master/tests/tests/postgresql/017_datetime.sql
inflating: tds_fdw-master/tests/tests/postgresql/018_datetime2.json
inflating: tds_fdw-master/tests/tests/postgresql/018_datetime2.sql
inflating: tds_fdw-master/tests/tests/postgresql/019_datetimeoffset.json
inflating: tds_fdw-master/tests/tests/postgresql/019_datetimeoffset.sql
inflating: tds_fdw-master/tests/tests/postgresql/020_char.json
inflating: tds_fdw-master/tests/tests/postgresql/020_char.sql
inflating: tds_fdw-master/tests/tests/postgresql/021_varchar.json
inflating: tds_fdw-master/tests/tests/postgresql/021_varchar.sql
inflating: tds_fdw-master/tests/tests/postgresql/022_varcharmax.json
inflating: tds_fdw-master/tests/tests/postgresql/022_varcharmax.sql
inflating: tds_fdw-master/tests/tests/postgresql/023_binary4.json
inflating: tds_fdw-master/tests/tests/postgresql/023_binary4.sql
inflating: tds_fdw-master/tests/tests/postgresql/024_varbinary4.json
inflating: tds_fdw-master/tests/tests/postgresql/024_varbinary4.sql
inflating: tds_fdw-master/tests/tests/postgresql/025_varbinarymax.json
inflating: tds_fdw-master/tests/tests/postgresql/025_varbinarymax.sql
inflating: tds_fdw-master/tests/tests/postgresql/026_null_datetime.json
inflating: tds_fdw-master/tests/tests/postgresql/026_null_datetime.sql
inflating: tds_fdw-master/tests/tests/postgresql/027_null_datetime2.json
inflating: tds_fdw-master/tests/tests/postgresql/027_null_datetime2.sql
inflating: tds_fdw-master/tests/tests/postgresql/028_column_match_enabled.json
inflating: tds_fdw-master/tests/tests/postgresql/028_column_match_enabled.sql
inflating: tds_fdw-master/tests/tests/postgresql/029_column_match_disabled.json
inflating: tds_fdw-master/tests/tests/postgresql/029_column_match_disabled.sql
inflating: tds_fdw-master/tests/tests/postgresql/030_column_name.json
inflating: tds_fdw-master/tests/tests/postgresql/030_column_name.sql
inflating: tds_fdw-master/tests/tests/postgresql/031_query_option_column_match_enabled.json
inflating: tds_fdw-master/tests/tests/postgresql/031_query_option_column_match_enabled.sql
inflating: tds_fdw-master/tests/tests/postgresql/032_query_option_column_match_disabled.json
inflating: tds_fdw-master/tests/tests/postgresql/032_query_option_column_match_disabled.sql
inflating: tds_fdw-master/tests/tests/postgresql/033_view_simple.json
extracting: tds_fdw-master/tests/tests/postgresql/033_view_simple.sql
inflating: tds_fdw-master/tests/tests/postgresql/034_explain.json
extracting: tds_fdw-master/tests/tests/postgresql/034_explain.sql
inflating: tds_fdw-master/tests/tests/postgresql/035_rescan.json
inflating: tds_fdw-master/tests/tests/postgresql/035_rescan.sql
inflating: tds_fdw-master/tests/validate-test-json
[postgres16@Server install]$
2.4、切换目录
[postgres16@Server install]$ cd tds_fdw-master/
[postgres16@Server tds_fdw-master]$
三、部署
3.1、编译安装
- 注意：如果您有多个 PostgreSQL 版本并且不想为默认版本构建，请首先找到 pg_config 的二进制文件的位置，记下完整路径，然后相应地调整 PG_CONFIG。
步骤1：
[postgres16@Server tds_fdw-master]$ make USE_PGXS=1 PG_CONFIG=/postgres16/server/bin/pg_config
gcc -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Werror=vla -Wendif-labels -Wmissing-format-attribute -Wimplicit-fallthrough=3 -Wcast-function-type -Wshadow=compatible-local -Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard -Wno-format-truncation -Wno-stringop-truncation -O2 -fPIC -fvisibility=hidden -I./include/ -fvisibility=hidden -I. -I./ -I/postgres16/server/include/server -I/postgres16/server/include/internal -D_GNU_SOURCE -c -o src/tds_fdw.o src/tds_fdw.c
src/tds_fdw.c: 在函数‘tdsIterateForeignScan’中:
src/tds_fdw.c:1875:7: 警告：‘ncol’的声明隐藏了先前的一个局部变量 [-Wshadow=compatible-local]
int ncol;
^~~~
src/tds_fdw.c:1686:6: 附注：被隐藏的声明在这里
int ncol;
^~~~
src/tds_fdw.c: 在函数‘tdsImportForeignSchema’中:
src/tds_fdw.c:3954:17: 警告：未使用的变量‘buf’ [-Wunused-variable]
StringInfoData buf;
^~~
src/tds_fdw.c:3953:8: 警告：未使用的变量‘is_sql_server’ [-Wunused-variable]
bool is_sql_server = true;
^~~~~~~~~~~~~
In file included from /postgres16/server/include/server/postgres.h:46,
from src/tds_fdw.c:32:
src/tds_fdw.c: 在函数‘tdsIsSqlServer’中:
/postgres16/server/include/server/utils/elog.h:142:5: 警告：this statement may fall through [-Wimplicit-fallthrough=]
do { \
^
/postgres16/server/include/server/utils/elog.h:164:2: 附注：in expansion of macro ‘ereport_domain’
ereport_domain(elevel, TEXTDOMAIN, __VA_ARGS__)
^~~~~~~~~~~~~~
src/tds_fdw.c:487:5: 附注：in expansion of macro ‘ereport’
ereport(ERROR,
^~~~~~~
src/tds_fdw.c:492:4: 附注：here
case FAIL:
^~~~
In file included from /postgres16/server/include/server/postgres.h:46,
from src/tds_fdw.c:32:
/postgres16/server/include/server/utils/elog.h:142:5: 警告：this statement may fall through [-Wimplicit-fallthrough=]
do { \
^
/postgres16/server/include/server/utils/elog.h:164:2: 附注：in expansion of macro ‘ereport_domain’
ereport_domain(elevel, TEXTDOMAIN, __VA_ARGS__)
^~~~~~~~~~~~~~
src/tds_fdw.c:493:5: 附注：in expansion of macro ‘ereport’
ereport(ERROR,
^~~~~~~
src/tds_fdw.c:498:4: 附注：here
default:
^~~~~~~
In file included from /postgres16/server/include/server/postgres.h:46,
from src/tds_fdw.c:32:
src/tds_fdw.c: 在函数‘tdsGetRowCountShowPlanAll’中:
/postgres16/server/include/server/utils/elog.h:142:5: 警告：this statement may fall through [-Wimplicit-fallthrough=]
do { \
^
/postgres16/server/include/server/utils/elog.h:164:2: 附注：in expansion of macro ‘ereport_domain’
ereport_domain(elevel, TEXTDOMAIN, __VA_ARGS__)
^~~~~~~~~~~~~~
src/tds_fdw.c:955:6: 附注：in expansion of macro ‘ereport’
ereport(ERROR,
^~~~~~~
src/tds_fdw.c:960:5: 附注：here
case FAIL:
^~~~
In file included from /postgres16/server/include/server/postgres.h:46,
from src/tds_fdw.c:32:
/postgres16/server/include/server/utils/elog.h:142:5: 警告：this statement may fall through [-Wimplicit-fallthrough=]
do { \
^
/postgres16/server/include/server/utils/elog.h:164:2: 附注：in expansion of macro ‘ereport_domain’
ereport_domain(elevel, TEXTDOMAIN, __VA_ARGS__)
^~~~~~~~~~~~~~
src/tds_fdw.c:961:6: 附注：in expansion of macro ‘ereport’
ereport(ERROR,
^~~~~~~
src/tds_fdw.c:966:5: 附注：here
default:
^~~~~~~
In file included from /postgres16/server/include/server/postgres.h:46,
from src/tds_fdw.c:32:
src/tds_fdw.c: 在函数‘tdsGetRowCountExecute’中:
/postgres16/server/include/server/utils/elog.h:142:5: 警告：this statement may fall through [-Wimplicit-fallthrough=]
do { \
^
/postgres16/server/include/server/utils/elog.h:164:2: 附注：in expansion of macro ‘ereport_domain’
ereport_domain(elevel, TEXTDOMAIN, __VA_ARGS__)
^~~~~~~~~~~~~~
src/tds_fdw.c:1120:6: 附注：in expansion of macro ‘ereport’
ereport(ERROR,
^~~~~~~
src/tds_fdw.c:1125:5: 附注：here
case FAIL:
^~~~
In file included from /postgres16/server/include/server/postgres.h:46,
from src/tds_fdw.c:32:
/postgres16/server/include/server/utils/elog.h:142:5: 警告：this statement may fall through [-Wimplicit-fallthrough=]
do { \
^
/postgres16/server/include/server/utils/elog.h:164:2: 附注：in expansion of macro ‘ereport_domain’
ereport_domain(elevel, TEXTDOMAIN, __VA_ARGS__)
^~~~~~~~~~~~~~
src/tds_fdw.c:1126:6: 附注：in expansion of macro ‘ereport’
ereport(ERROR,
^~~~~~~
src/tds_fdw.c:1131:5: 附注：here
default:
^~~~~~~
src/tds_fdw.c: 在函数‘tdsConvertToCString’中:
src/tds_fdw.c:1314:7: 警告：this statement may fall through [-Wimplicit-fallthrough=]
if (erc == SUCCEED)
^
src/tds_fdw.c:1325:3: 附注：here
default:
^~~~~~~
In file included from /postgres16/server/include/server/postgres.h:46,
from src/tds_fdw.c:32:
src/tds_fdw.c: 在函数‘tdsImportSqlServerSchema’中:
/postgres16/server/include/server/utils/elog.h:142:5: 警告：this statement may fall through [-Wimplicit-fallthrough=]
do { \
^
/postgres16/server/include/server/utils/elog.h:164:2: 附注：in expansion of macro ‘ereport_domain’
ereport_domain(elevel, TEXTDOMAIN, __VA_ARGS__)
^~~~~~~~~~~~~~
src/tds_fdw.c:3524:6: 附注：in expansion of macro ‘ereport’
ereport(ERROR,
^~~~~~~
src/tds_fdw.c:3529:5: 附注：here
case FAIL:
^~~~
In file included from /postgres16/server/include/server/postgres.h:46,
from src/tds_fdw.c:32:
/postgres16/server/include/server/utils/elog.h:142:5: 警告：this statement may fall through [-Wimplicit-fallthrough=]
do { \
^
/postgres16/server/include/server/utils/elog.h:164:2: 附注：in expansion of macro ‘ereport_domain’
ereport_domain(elevel, TEXTDOMAIN, __VA_ARGS__)
^~~~~~~~~~~~~~
src/tds_fdw.c:3530:6: 附注：in expansion of macro ‘ereport’
ereport(ERROR,
^~~~~~~
src/tds_fdw.c:3535:5: 附注：here
default:
^~~~~~~
In file included from /postgres16/server/include/server/postgres.h:46,
from src/tds_fdw.c:32:
src/tds_fdw.c: 在函数‘tdsImportSybaseSchema’中:
/postgres16/server/include/server/utils/elog.h:142:5: 警告：this statement may fall through [-Wimplicit-fallthrough=]
do { \
^
/postgres16/server/include/server/utils/elog.h:164:2: 附注：in expansion of macro ‘ereport_domain’
ereport_domain(elevel, TEXTDOMAIN, __VA_ARGS__)
^~~~~~~~~~~~~~
src/tds_fdw.c:3907:6: 附注：in expansion of macro ‘ereport’
ereport(ERROR,
^~~~~~~
src/tds_fdw.c:3912:5: 附注：here
case FAIL:
^~~~
In file included from /postgres16/server/include/server/postgres.h:46,
from src/tds_fdw.c:32:
/postgres16/server/include/server/utils/elog.h:142:5: 警告：this statement may fall through [-Wimplicit-fallthrough=]
do { \
^
/postgres16/server/include/server/utils/elog.h:164:2: 附注：in expansion of macro ‘ereport_domain’
ereport_domain(elevel, TEXTDOMAIN, __VA_ARGS__)
^~~~~~~~~~~~~~
src/tds_fdw.c:3913:6: 附注：in expansion of macro ‘ereport’
ereport(ERROR,
^~~~~~~
src/tds_fdw.c:3918:5: 附注：here
default:
^~~~~~~
gcc -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Werror=vla -Wendif-labels -Wmissing-format-attribute -Wimplicit-fallthrough=3 -Wcast-function-type -Wshadow=compatible-local -Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard -Wno-format-truncation -Wno-stringop-truncation -O2 -fPIC -fvisibility=hidden -I./include/ -fvisibility=hidden -I. -I./ -I/postgres16/server/include/server -I/postgres16/server/include/internal -D_GNU_SOURCE -c -o src/options.o src/options.c
In file included from /postgres16/server/include/server/postgres.h:46,
from src/options.c:4:
src/options.c: 在函数‘tdsGetForeignServerOptions’中:
src/options.c:326:14: 警告：格式 ‘%s’ expects argument of type ‘char *’, but argument 2 has type ‘int’ [-Wformat=]
errmsg("Redundant option: sqlserver_ansi_mode (%s)", defGetBoolean(def))
^~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ ~~~~~~~~~~~~~~~~~~
/postgres16/server/include/server/utils/elog.h:147:4: 附注：in definition of macro ‘ereport_domain’
__VA_ARGS__, errfinish(__FILE__, __LINE__, __func__); \
^~~~~~~~~~~
src/options.c:324:5: 附注：in expansion of macro ‘ereport’
ereport(ERROR,
^~~~~~~
gcc -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Werror=vla -Wendif-labels -Wmissing-format-attribute -Wimplicit-fallthrough=3 -Wcast-function-type -Wshadow=compatible-local -Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard -Wno-format-truncation -Wno-stringop-truncation -O2 -fPIC -fvisibility=hidden -I./include/ -fvisibility=hidden -I. -I./ -I/postgres16/server/include/server -I/postgres16/server/include/internal -D_GNU_SOURCE -c -o src/deparse.o src/deparse.c
gcc -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Werror=vla -Wendif-labels -Wmissing-format-attribute -Wimplicit-fallthrough=3 -Wcast-function-type -Wshadow=compatible-local -Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard -Wno-format-truncation -Wno-stringop-truncation -O2 -fPIC -fvisibility=hidden -shared -o tds_fdw.so src/tds_fdw.o src/options.o src/deparse.o -L/postgres16/server/lib -Wl,--as-needed -Wl,-rpath,'/postgres16/server/lib',--enable-new-dtags -fvisibility=hidden -lsybdb
cp sql/tds_fdw.sql sql/tds_fdw--2.0.3.sql
cp README.md README.tds_fdw.md
[postgres16@Server tds_fdw-master]$
步骤2：
[postgres16@Server tds_fdw-master]$ sudo make USE_PGXS=1 PG_CONFIG=/postgres16/server/bin/pg_config install
[sudo] postgres16 的密码：
/usr/bin/mkdir -p '/postgres16/server/lib'
/usr/bin/mkdir -p '/postgres16/server/share/extension'
/usr/bin/mkdir -p '/postgres16/server/share/extension'
/usr/bin/mkdir -p '/postgres16/server/share/doc/extension'
/usr/bin/install -c -m 755 tds_fdw.so '/postgres16/server/lib/tds_fdw.so'
/usr/bin/install -c -m 644 .//tds_fdw.control '/postgres16/server/share/extension/'
/usr/bin/install -c -m 644 .//tds_fdw--2.0.1--2.0.2.sql .//tds_fdw--2.0.2--2.0.3.sql .//sql/tds_fdw--2.0.3.sql '/postgres16/server/share/extension/'
/usr/bin/install -c -m 644 .//README.tds_fdw.md '/postgres16/server/share/doc/extension/'
[postgres16@Server tds_fdw-master]$
3.2、创建扩展 tds_fdw
[postgres16@Server tds_fdw-master]$
[postgres16@Server tds_fdw-master]$ /postgres16/server/bin/psql -U postgres16 -d postgres -p 5433
psql (16.0)
Type "help" for help.
postgres=#
postgres=#
postgres=# create extension tds_fdw ;
CREATE EXTENSION
postgres=#
3.3、创建tds_fdw服务器
postgres=#
postgres=#
postgres=#
postgres=# CREATE SERVER mssql_server FOREIGN DATA WRAPPER tds_fdw OPTIONS (servername '192.168.4.9', port '1433', database 'PACKPRESSDB', tds_version '7.1');
CREATE SERVER
postgres=#
3.4、创建tds_fdw外部访问表
postgres=#
postgres=# CREATE FOREIGN TABLE mssql_table1 (
postgres(# PatientID int NOT NULL,
postgres(# PatientName varchar(50) NOT NULL,
postgres(# PatientCode varchar(36) NULL,
postgres(# MedicalRecordCode varchar(36) NULL,
postgres(# PatientSex bit NULL,
postgres(# PatientBirthday varchar(25) NULL,
postgres(# PatientAge int NULL,
postgres(# PatientIDCard varchar(20) NULL,
postgres(# SocialSecurityCard varchar(50) NULL,
postgres(# PatientAddress varchar(500) NULL,
postgres(# PatientPhone varchar(20) NULL,
postgres(# HighRiskInfectivity int NULL,
postgres(# BaseFlag varchar(50) NULL,
postgres(# CreatedTime DATE NULL,
postgres(# UpdatedTime DATE NULL,
postgres(# CreatedBy varchar(50) NULL,
postgres(# UpdatedBy varchar(50) NULL,
postgres(# IsDeleted bit NOT NULL,
postgres(# RegisterCode varchar(50) NULL,
postgres(# InPatientCode varchar(50) NULL,
postgres(# InternalMedicalRecordCode varchar(50) NULL)
postgres-# SERVER mssql_server
postgres-# OPTIONS (table_name 'dbo.TMasterPatient', row_estimate_method 'showplan_all');
CREATE FOREIGN TABLE
postgres=#
3.5、用户映射
说明：将tds_fdw服务的mssql_server映射给用户postgres16
postgres=#
postgres=# CREATE USER MAPPING FOR postgres16 SERVER mssql_server OPTIONS (username 'sa', password 'cssd20151231@');
CREATE USER MAPPING
postgres=#
四、测试
postgres=# select * from mssql_table1 limit 3;
patientid | patientname | patientcode | medicalrecordcode | patientsex | patientbirthday | patientage | patientidcard | socialsecuritycard | patientaddress | p
atientphone | highriskinfectivity | baseflag | createdtime | updatedtime | createdby | updatedby | isdeleted | registercode | inpatientcode | internalmedicalrec
ordcode
-----------+-------------+-------------+-------------------+------------+-----------------+------------+---------------+--------------------+----------------+--
------------+---------------------+----------+-------------+-------------+-----------+-----------+-----------+--------------+---------------+-------------------
--------
1 | | | | 0 | | 0 | | | |
| 0 | | 2018-06-20 | | lch | | 0 | | |
2 | | | | 0 | | 0 | | | |
| 0 | | 2018-06-20 | | lch | | 0 | | |
3 | | | | 0 | | 0 | | | |
| 0 | | 2018-06-20 | | lch | | 0 | | |
(3 rows)
postgres=#
postgres=#
五、总结
tds_fdw 为 PostgreSQL 用户提供了一个便捷的方法来访问远程的 Sybase 或 SQL Server 数据库，它简化了在不同数据库系统间的数据集成工作。通过上述步骤，您可以在自己的 PostgreSQL 数据库中无缝地整合来自其他数据库系统的数据，充分发挥 PostgreSQL 丰富的功能。
更多资料，请关注博主其他平台：
墨天轮主页：
https://www.modb.pro/topic/659255
链接二维码如下：
PGFans社区主页
https://pgfans.cn/user/home?userId=5710
公众号：
扫一扫
487
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
