postgresql端使用tds_fdw建立訪問sqlserver的linked server的操作說明

參考文件
--postgres-fdw只能用於pg與pg直接的linked server


/blob/master/InstallCentOS.md
github.com/tds-fdw/tds_fdw提供的安裝包postgresql-11-tds_fdw不建議使用，因為sqlserver 2017居然沒有對應的tds版本號，參見

總結
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的方法，參見
[root@ecmpgdbdev ~]# curl  -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');
原因
目前只有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下，來自哪個外部伺服器