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

原创

Hehuyi_In_666 2023-05-06 22:42:41 博主文章分类：PostgreSQL ©著作权

文章标签 postgresql tds_fdw sqlserver dblink fdw 文章分类 Python 后端开发

©著作权归作者所有：来自51CTO博客作者Hehuyi_In_666的原创作品，请联系作者获取转载授权，否则将追究法律责任

之前整理了一篇 postgresql FDW概念、用法与原理小结_Hehuyi_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_3027541

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?

上一篇：Linux 内存回收，思维导图记录

下一篇：postgresql源码学习（54）—— HotStandby从库必须设置大于等于主库的参数

提问和评论都可以，用心的回复会被更多人看到评论

发布评论

相关文章

官方博客	全部文章	热门标签	班级博客
了解我们	网站地图	意见反馈

鸿蒙开发者社区	51CTO学堂
51CTO	软考资讯