postgresql端使用tds_fdw创建访问sqlserver的linked server的操作说明

参考文档
https://www.postgresql.org/docs/11/postgres-fdw.html --postgres-fdw只能用于pg与pg直接的linked server
https://www.postgresql.org/message-id/CAA7biFNNDzmmL3O4fOm4h=Tf08BaFWS_sQXS98qHPHqNYKJyBw@mail.gmail.com
https://github.com/tds-fdw/tds_fdw
https://github.com/tds-fdw/tds_fdw/blob/master/InstallCentOS.md
github.com/tds-fdw/tds_fdw提供的安装包postgresql-11-tds_fdw不建议使用，因为sqlserver 2017居然没有对应的tds版本号，参见https://www.freetds.org/userguide/ChoosingTdsProtocol.html

总结
1、建议使用Linux repo源默认的tds_fdw安装包tds_fdw11.x86_64,因为此安装包最简单
2、只有postgresql自己的postgres_fdw支持IMPORT FOREIGN SCHEMA语法, 其他fdw无法做到
3、外部表不能建立主键，否则会有报错ERROR: primary key constraints are not supported on foreign tables
4、在postgresql中遇到查询foreign table权限问题,就在postgresql中创建public的账号映射,这样postgresql数据库中的用户都可以使用这个public用户就能访问外部表，只要映射用户对应在sqlserver中的用户有足够权限访问sqlserver中的表，postgresql用户也就可以访问到这些外部表在sqlsever中的数据,语法CREATE USER MAPPING FOR public SERVER XX OPTIONS (username ‘UXX’, password ‘PXX’);假如sqlserver中A用户可以访问sqlserver的所有表，把postgresql的public的用户映射到A用户，则所有postgresql用户都可以访问sqlserver的所有表，如果只是把postgresql的user1的用户映射到A用户，则只有postgresql的user1用户可以访问sqlserver的所有表
5、根据sql查询语句来创建外部表，则在外部表的Options里面使用query，query里面的内容完全按sqlserver的写法来。因为有时候query里面的字段名称会出现是数字的情况，sqlserver中一般加中括号而不加双引号，因为sqlserver查询中双引号括起来的字段就是表示别名字段名称而没有内容

1、PostgreSQL端安装依赖包
[root@ecmpgdbdev ~]# yum install epel-release
[root@ecmpgdbdev ~]# yum install freetds-devel
[root@ecmpgdbdev ~]# yum install gcc make wget

2、PostgreSQL端安装tds_fdw
选择Linux repo源默认的tds_fdw
[root@ecmpgdbdev ~]# yum search ‘fdw’
[root@ecmpgdbdev ~]# yum install tds_fdw11.x86_64
以下是安装postgresql-11-tds_fdw的方法，参见https://github.com/tds-fdw/tds_fdw
[root@ecmpgdbdev ~]# curl https://tds-fdw.github.io/yum/tds_fdw.repo -o /etc/yum.repos.d/tds_fdw.repo
[root@ecmpgdbdev ~]# yum install postgresql-11-tds_fdw

3、PostgreSQL端创建拓展包
-bash-4.2$ psql
postgres=# CREATE EXTENSION tds_fdw;
备注：如果没有安装tds_fdw，创建拓展包会报错ERROR: could not open extension control file “/usr/pgsql-10/share/extension/tds_fdw.control”:
删除语法：drop extension tds_fdw;

4、PostgreSQL端创建foreign server
CREATE SERVER mssql_svr FOREIGN DATA WRAPPER tds_fdw OPTIONS (servername ‘172.22.136.190’, port ‘49787’, database ‘ECMDB’, tds_version ‘7.4’);
–使用tds_fdw11.x86_64不需要tds_version '7.4’这个选项
–使用postgresql-11-tds_fdw的话，sqlserver数据库版本是2012-2016，则tds_version选项7.4，sqlserver 2017居然没有对应版本
–servername指的是hostname或ip，不能是sqlserver的实例名称
–如果没有创建拓展包会报错ERROR: foreign-data wrapper “tds_fdw” does not exist
删除语法：DROP SERVER mssql_svr;

5、PostgreSQL端创建用户映射，MAPPING是固定的不是postgresql的用户名，不能把MAPPING换成其他的如sqlserveruser
CREATE USER MAPPING FOR postgres SERVER mssql_svr OPTIONS (username ‘domain\sqluser’, password ‘X’);
备注：把postgresql的postgres用户映射到外部服务器，对应外部服务器的domain\sqluser
FOR postgres中，可以把postgres修改为public，即所谓的公共映射会被创建，当没有特定用户的映射可用时将会使用它。
删除语法：DROP USER MAPPING FOR postgres SERVER mssql_svr;

6、创建外部表test1."Activity"来自sqlserver的dbo.Activity
CREATE FOREIGN TABLE test1.“Activity” (
“ActivityID” INTEGER NOT NULL,
“ActivityTypeID” INTEGER NOT NULL
) SERVER mssql_svr OPTIONS (table_name ‘dbo.Activity’);
–可以查到test1.“Activity”，但是有如下报错，解决方法就是在OPTIONS上加上, match_column_names '0’即SERVER mssql_svr OPTIONS (table_name ‘dbo.Activity’, match_column_names ‘0’)
WARNING: Table definition mismatch: Foreign source has column named , but target table does not. Column will be ignored.
WARNING: Table definition mismatch: Could not match local column ActivityID with column from foreign table
WARNING: Table definition mismatch: Could not match local column Description with column from foreign table
删除语句：DROP FOREIGN TABLE test1.“Activity”;

不使用create foreign table而是直接import时遇到一些bug,下面语法执行正常，但是postgresql中查不到public.Activity这张外部表
把sqlserver的dbo.Activity表映射到postgresql的public下
IMPORT FOREIGN SCHEMA dbo limit to (Activity) FROM SERVER mssql_svr INTO public OPTIONS (import_default ‘true’);
原因https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=59efda3e50ca4de6a9d5aa4491464e22b6329b1e
目前只有postgres_fdw支持IMPORT FOREIGN SCHEMA语法, 其他fdw无法做到

遇到过的错误
1、不能直接删除FOREIGN DATA WRAPPER，而是直接删除扩展包drop extension;
postgres=# DROP FOREIGN DATA WRAPPER tds_fdw;
ERROR: cannot drop foreign-data wrapper tds_fdw because extension tds_fdw requires it
HINT: You can drop extension tds_fdw instead.

2、
BroadScale=# \x
Expanded display is on.
BroadScale=# select * from broadscale.activitydataes limit 1;
NOTICE: tds_fdw: Query executed correctly
NOTICE: tds_fdw: Getting results
ERROR: DB-Library error: DB #: 2403, DB Msg: Some character(s) could not be converted into client’s character set. Unconverted bytes were changed to question marks (’?’), OS #: 0, OS Msg: Success, Level: 4

针对某个sql查询创建外部表，如下，query里面的字段316是字段名称，sqlserver中一般加中括号而不加双引号，因为sqlserver查询中双引号括起来的字段就是表示别名字段名称而没有内容，query里面的内容就完全按sqlserver的写法来
CREATE FOREIGN TABLE schema1.lmdata (
hubid INTEGER NOT NULL,
databasetypeid INTEGER NOT NULL,
“316” varchar(50),
“268” varchar(50),
“272” varchar(50)
) SERVER mssql_svr OPTIONS (query ‘select HUBID, DatabaseTypeID, [316], [268], [272] from lm.lmdata where DatabaseTypeID=3’,match_column_names ‘0’);

FDW的一些参考语句
select * from pg_extension; CREATE EXTENSION tds_fdw时就产生了pg_extension
select * from pg_foreign_data_wrapper; CREATE EXTENSION tds_fdw时就产生了pg_foreign_data_wrapper
select * from pg_foreign_server;
select * from pg_foreign_table;
select * from pg_user_mappings;
select regnamespace(relnamespace),regclass(ftrelid),c.srvname,c.srvoptions,a.ftoptions from pg_foreign_table a,pg_class b,pg_foreign_server c where a.ftrelid=b.relfilenode and a.ftserver=c.oid;–查询外部表在哪个schema下，来自哪个外部服务器