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

這種方式有很多bug，不建議使用，因為sqlserver 2016居然沒有對應的tds版本號，參見http://www.freetds.org/userguide/choosingtdsprotocol.htm



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

2、PostgreSQL端安裝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;


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');
--sqlserver資料庫版本是2012-2014，則tds_version選項7.4，sqlserver 2016居然沒有對應版本
--servername指的是hostname或ip，不能是sqlserver的例項名稱

5、PostgreSQL端建立使用者對映，MAPPING是固定的不是postgresql的使用者名稱，不能把MAPPING換成其他的如sqlserveruser
CREATE USER MAPPING FOR postgres SERVER mssql_svr OPTIONS (username 'domain\sqluser', password 'X');

6、把sqlserver的dbo.Activity表對映到postgresql的public下
IMPORT FOREIGN SCHEMA dbo limit to (Activity) FROM SERVER mssql_svr INTO public OPTIONS (import_default 'true');
--查不到public.Activity,很奇怪

CREATE FOREIGN TABLE test1."Activity" (
    "ActivityID" INTEGER NOT NULL,
    "ActivityTypeID" INTEGER NOT NULL,
)  SERVER mssql_svr OPTIONS (table_name 'dbo.Activity');
--可以查到test1."Activity"，但是有如下報錯
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




FDW的一些參考語句
select * from pg_extension;
select * from pg_foreign_data_wrapper;
select * from pg_foreign_server;
select * from pg_foreign_table;
select * from pg_user_mappings;

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/30126024/viewspace-2695939/，如需轉載，請註明出處，否則將追究法律責任。