PostgreSQL 通过 tds_fdw 访问 SQL Server数据，常见报错分析及处理

Hehuyi_In

已于 2023-02-09 11:45:03 修改

阅读量4.8k

点赞数 1

分类专栏： PostgreSQL SQLServer 数据同步文章标签： postgresql tds_fdw sqlserver dblink fdw

于 2023-02-07 17:08:08 首次发布

本文链接：https://blog.csdn.net/Hehuyi_In/article/details/90039979

版权

SQLServer 同时被 3 个专栏收录

142 篇文章 26 订阅

订阅专栏

PostgreSQL

140 篇文章 123 订阅

订阅专栏

数据同步

26 篇文章 0 订阅

订阅专栏

文章介绍了如何在PostgreSQL中安装和使用tds_fdw扩展来访问SQLServer数据，包括安装freetds、编译安装tds_fdw、创建server、usermapping和foreigntable的步骤。在配置过程中遇到的错误如编译错误和权限问题进行了分析，并提供了详细的解决方案。

摘要由CSDN通过智能技术生成

之前整理了一篇 postgresql FDW概念、用法与原理小结_Hehuyi_In的博客-CSDN博客记录fdw概念、postgres_fdw、mysql_fdw、file_fdw的用法。

最近遇到pg访问SqlServer数据的需求，过程有点坎坷，记录一下。

一、安装tds_fdw

1. freetds安装

tds_fdw依赖于freetds，因此要先安装freetds

yum install gcc -y

wget http://dl.fedoraproject.org/pub/epel/7/x86_64/e/epel-release-7-5.noarch.rpm

rpm -ivh epel-release-7-14.noarch.rpm 
yum install freetds -y
yum install freetds-devel -y

2. 下载并解压tds_fdw包

下载地址：https://github.com/tds-fdw/tds_fdw

上传至服务器并解压，规范起见一般跟其他插件放在一起

unzip tds_fdw-master.zip
mv tds_fdw-master tds_fdw

3. 编译安装tds_fdw

关于 USE_PGXS=1 参考：USE_PGXS 在 extension 编译中的作用_weixin_30275415的博客-CSDN博客

make USE_PGXS=1
make USE_PGXS=1 install

二、 tds_fdw 访问 SQL Server数据

还是四部曲：extension -> server -> user mapping -> foreign table

1. extension

必须用超级用户postgres在指定db创建

CREATE EXTENSION tds_fdw;

如果后续不打算用postgres创建，需要给普通用户授权

GRANT USAGE ON FOREIGN DATA WRAPPER tds_fdw TO dba_rw;

2. server

CREATE SERVER mssql_server
FOREIGN DATA WRAPPER tds_fdw
OPTIONS (servername '目标端ip', port '1433', database '目标库名', tds_version '7.3');

-- 低版本tds有各种bug，可以手动指定tds版本
-- 还可以指定 msg_handler 'notice'，输出信息会更多，一般用于报错排查

3. user mapping

CREATE USER MAPPING FOR dba_rw
SERVER mssql_server
OPTIONS (username '目标库用户', password 'xxxx');

4. foreign table

CREATE FOREIGN TABLE mssql_fdw_tmptab (id varchar(32) NOT NULL)
SERVER mssql_server 
OPTIONS (schema_name 'dbo', table_name '目标表名');

5. 测试查询外部表

psql -Udba_rw -dmydb

select id from mssql_fdw_tmptab limit 5;

NOTICE:  tds_fdw: Query executed correctly
NOTICE:  tds_fdw: Getting results
                id                
----------------------------------
 007443fc-e602-45fe-bf08-0575dd46
 007bcf86-19ad-49cb-8b8b-6e84177c
 00a12c2e-5fb4-4b0f-bb7f-0b5a9129
 013ccacb-a55d-473a-952d-80703926
 0173c812-f13f-4257-a559-44bb7aed
(5 rows)

三、报错分析处理

1. tds_fdw make USE_PGXS=1报错

-bash-4.2$ make USE_PGXS=1 PG_CONFIG=/bin/pg_config
Makefile:54: /usr/lib64/pgsql/pgxs/src/makefiles/pgxs.mk: No such file or directory
make: *** No rule to make target `/usr/lib64/pgsql/pgxs/src/makefiles/pgxs.mk'. Stop.

网上很多文章都解释是因为缺少 postgresql-devel包，但我们的pg是通过编译安装的，实际并不缺少这个包。find搜索可以查到其路径：

find / -name pgxs.mk

/.../contrib/9.6.2/lib/pgxs/src/makefiles/pgxs.mk
/.../postgresql-9.6.2/src/makefiles/pgxs.mk
/.../tmp_install/.../lib/pgxs/src/makefiles/pgxs.mk

从tds_fdw的Makefile文件可以看到，报错的路径来自

PG_CONFIG = pg_config
PGXS := $(shell $(PG_CONFIG) --pgxs)

也就是 pg_config --pgxs 命令，执行可以看到，确实是 /usr/lib64/pgsql/pgxs/src/makefiles/pgxs.mk

这个命令结果与postgres用户的 LD_LIBRARY_PATH 环境变量有关，因此解决方法就是将 /.../contrib/9.6.2/lib 路径加入LD_LIBRARY_PATH 环境变量。

设置环境变量后重新执行 make USE_PGXS=1 ，编译成功。

2. 外部表查询报错 OS #: -1, OS Msg: , Level: 16

兴高采烈走到最后一步时，测试查询外部表报错

select count(*) from mssql_fdw_tmptab;

ERROR: DB-Library error: DB #: 20018, DB Msg: General SQL Server error: Check messages from the SQL Server, OS #: -1, OS Msg: , Level: 16

这个报错信息简单得令人发指，基本什么都看不出来...

查询文档发现可以给 fdw server 指定 msg_handler 'notice'，查看详细信息（默认不输出）

可以在创建时指定，但平时会有很多多余信息

CREATE SERVER mssql_server
FOREIGN DATA WRAPPER tds_fdw
OPTIONS (servername '目标端ip', port '1433', database '目标库名', tds_version '7.3',msg_handler 'notice');

也可以在需要时设置

ALTER SERVER mssql_server OPTIONS (ADD msg_handler 'notice');
-- 配合客户端 client_min_messages 设置为 debug3，可以看到更多信息（非必须）
SET client_min_messages=DEBUG3;

设置后输出的报错信息如下：

mydb=> select count(*) from mssql_fdw_tmptab;
DEBUG: StartTransactionCommand
DEBUG: StartTransaction
DEBUG: name: unnamed; blockState: DEFAULT; state: INPROGR, xid/subid/cid: 0/1/0, nestlvl: 1, children:
DEBUG: tds_fdw: Using remote estimate
DEBUG: tds_fdw: Getting query
DEBUG: tds_fdw: Value of query is SELECT NULL FROM [dbo].[tmptab]
DEBUG: tds_fdw: Initiating DB-Library
DEBUG: tds_fdw: Getting login structure
DEBUG: tds_fdw: Setting login user to db_r
DEBUG: tds_fdw: Setting login password to xxx
DEBUG: tds_fdw: Connection string is xxx:1433
DEBUG: tds_fdw: Connecting to server
NOTICE: DB-Library notice: Msg #: 5701, Msg state: 2, Msg: Changed database context to 'master'., Server: xxxx, Process: , Line: 1, Level: 0
NOTICE: DB-Library notice: Msg #: 5703, Msg state: 1, Msg: Changed language setting to us_english., Server: xxxx, Process: , Line: 1, Level: 0
DEBUG: tds_fdw: Connected successfully
DEBUG: tds_fdw: Setting database command to SELECT NULL FROM [dbo].[tmptab]
DEBUG: tds_fdw: Executing the query
NOTICE: DB-Library notice: Msg #: 208, Msg state: 1, Msg: Invalid object name 'dbo.tmptab'., Server: xxx, Process: , Line: 1, Level: 16
ERROR: DB-Library error: DB #: 20018, DB Msg: General SQL Server error: Check messages from the SQL Server, OS #: -1, OS Msg: , Level: 16

报错 Invalid object name，其实是因为mssql_server创建时没有指定DB，默认使用了master，因此它找不到这个对象。

解决方法是在create server时指定database参数（参考前面）。

3. 外部表查询报错 OS #: 0, OS Msg: Success, Level: 9

原报错为

ERROR: DB-Library error: DB #: 20002, DB Msg: Adaptive Server connection failed (xxx), OS #: 0, OS Msg: Success, Level: 9

从上面看客户端debug的信息没有太大用处，设置msg_handler基本够了

ALTER SERVER mssql_server OPTIONS (ADD msg_handler 'notice');

设置后输出的报错信息如下：

mydb=> select count(*) from mssql_fdw_tmptab;
NOTICE: DB-Library notice: Msg #: 4060, Msg state: 1, Msg: Cannot open database "work" requested by the login. The login failed., Server: xxxx, Process: , Line: 1, Level: 11
NOTICE: DB-Library notice: Msg #: 18456, Msg state: 1, Msg: Login failed for user 'db_r'., Server: xxxx, Process: , Line: 1, Level: 14
ERROR: DB-Library error: DB #: 20002, DB Msg: Adaptive Server connection failed (xxxx), OS #: 0, OS Msg: Success, Level: 9

可以看到报错为无权限登录目标db 'work'，需要检查并修改SqlServer用户权限。

4. 外部表查询报错 OS #: -1, OS Msg: , Level: 14

原报错为

ERROR: DB-Library error: DB #: 20018, DB Msg: General SQL Server error: Check messages from the SQL Server, OS #: -1, OS Msg: , Level: 14

ALTER SERVER mssql_server OPTIONS (ADD msg_handler 'notice');

设置后输出的报错信息如下：

mydb=> select count(*) from mssql_fdw_tmptab;
NOTICE: DB-Library notice: Msg #: 5701, Msg state: 2, Msg: Changed database context to 'work'., Server: xxxx, Process: , Line: 1, Level: 0
NOTICE: DB-Library notice: Msg #: 5703, Msg state: 1, Msg: Changed language setting to us_english., Server: xxxx, Process: , Line: 1, Level: 0
NOTICE: DB-Library notice: Msg #: 229, Msg state: 5, Msg: The SELECT permission was denied on the object 'DBRequirement', database 'bpm', schema 'dbo'., Server: xxxx, Process: , Line: 1, Level: 14
ERROR: DB-Library error: DB #: 20018, DB Msg: General SQL Server error: Check messages from the SQL Server, OS #: -1, OS Msg: , Level: 14

可以看到报错为无权限访问DBRequirement表，SqlServer用户需要加上对应权限。

参考：

https://github.com/tds-fdw/tds_fdw

SQL Server and PostgreSQL Foreign Data Wrapper Configuration - Part 3

Using Foreign Data Wrapper from PostgreSQL - executing procedures in SQL Server?

USE_PGXS 在 extension 编译中的作用_weixin_30275415的博客-CSDN博客

tds-fdw PostgreSQL said: DB-Library error: DB #: 20002, DB Msg: Adaptive Server connection failed, OS #: 0, OS Msg: Success, Level: 9 问题解决.md - 荣锋亮 - 博客园