Postgresql通過td_fdw連接查詢 MS SQL Serever 2008 表

Postgresql通過td_fdw連接查詢 MS SQL Serever 2008 表

一、安裝EPEL源
[root@db2-node01 src]# rpm -ivh http://dl.fedoraproject.org/pub/epel/5/x86_64/epel-release-5-4.noarch.rpm
Retrieving http://dl.fedoraproject.org/pub/epel/5/x86_64/epel-release-5-4.noarch.rpm
warning: /var/tmp/rpm-xfer.IJAs8d: Header V3 DSA signature: NOKEY, key ID 217521f6
Preparing... ########################################### [100%]
1:epel-release ########################################### [100%]


二、安裝freetds軟件
[root@db2-node01 yum.repos.d]# yum install freetds*
Loaded plugins: katello, product-id, security, subscription-manager
Updating certificate-based repositories.
Unable to read consumer identity
addons | 1.9 kB 00:00
addons/primary_db | 1.1 kB 00:00
base | 1.1 kB 00:00
base/primary | 1.3 MB 00:01
base 3667/3667
extras | 2.1 kB 00:00
extras/primary_db | 173 kB 00:00
updates | 1.9 kB 00:00
updates/primary_db | 947 kB 00:00
Setting up Install Process
Resolving Dependencies
--> Running transaction check
---> Package freetds.i386 0:0.91-2.el5 set to be updated
--> Processing Dependency: libodbcinst.so.1 for package: freetds
--> Processing Dependency: libodbc.so.1 for package: freetds
---> Package freetds.x86_64 0:0.91-2.el5 set to be updated
--> Processing Dependency: libodbcinst.so.1()(64bit) for package: freetds
--> Processing Dependency: libodbc.so.1()(64bit) for package: freetds
---> Package freetds-devel.i386 0:0.91-2.el5 set to be updated
---> Package freetds-devel.x86_64 0:0.91-2.el5 set to be updated
---> Package freetds-doc.x86_64 0:0.91-2.el5 set to be updated
--> Running transaction check
---> Package unixODBC-libs.i386 0:2.2.11-10.el5 set to be updated
---> Package unixODBC-libs.x86_64 0:2.2.11-10.el5 set to be updated
--> Finished Dependency Resolution


Dependencies Resolved


=================================================================================================================================================
Package Arch Version Repository Size
=================================================================================================================================================
Installing:
freetds i386 0.91-2.el5 epel 992 k
freetds x86_64 0.91-2.el5 epel 995 k
freetds-devel i386 0.91-2.el5 epel 39 k
freetds-devel x86_64 0.91-2.el5 epel 39 k
freetds-doc x86_64 0.91-2.el5 epel 619 k
Installing for dependencies:
unixODBC-libs i386 2.2.11-10.el5 base 551 k
unixODBC-libs x86_64 2.2.11-10.el5 base 554 k


Transaction Summary
=================================================================================================================================================
Install 7 Package(s)
Upgrade 0 Package(s)


Total download size: 3.7 M
Is this ok [y/N]: y
Downloading Packages:
(1/7): freetds-devel-0.91-2.el5.x86_64.rpm | 39 kB 00:00
(2/7): freetds-devel-0.91-2.el5.i386.rpm | 39 kB 00:00
(3/7): unixODBC-libs-2.2.11-10.el5.i386.rpm | 551 kB 00:01
(4/7): unixODBC-libs-2.2.11-10.el5.x86_64.rpm | 554 kB 00:00
(5/7): freetds-doc-0.91-2.el5.x86_64.rpm | 619 kB 00:00
(6/7): freetds-0.91-2.el5.i386.rpm | 992 kB 00:00
(7/7): freetds-0.91-2.el5.x86_64.rpm | 995 kB 00:00
-------------------------------------------------------------------------------------------------------------------------------------------------
Total 581 kB/s | 3.7 MB 00:06
warning: rpmts_HdrFromFdno: Header V4 DSA signature: NOKEY, key ID 217521f6
epel/gpgkey | 1.7 kB 00:00
Importing GPG key 0x217521F6 "Fedora EPEL " from /etc/pki/rpm-gpg/RPM-GPG-KEY-EPEL
Is this ok [y/N]: y
warning: rpmts_HdrFromFdno: Header V3 DSA signature: NOKEY, key ID e8562897
base/gpgkey | 1.5 kB 00:00
Importing GPG key 0xE8562897 "CentOS-5 Key (CentOS 5 Official Signing Key) " from http://mirror.centos.org/centos/RPM-GPG-KEY-CentOS-5
Is this ok [y/N]: y

Installed:
freetds.i386 0:0.91-2.el5 freetds.x86_64 0:0.91-2.el5 freetds-devel.i386 0:0.91-2.el5 freetds-devel.x86_64 0:0.91-2.el5
freetds-doc.x86_64 0:0.91-2.el5


Dependency Installed:
unixODBC-libs.i386 0:2.2.11-10.el5 unixODBC-libs.x86_64 0:2.2.11-10.el5


Complete!

三、安裝tds-fdw擴展模塊
[root@db2 src]# git clone git://github.com/tds-fdw/tds_fdw.git
[root@db2 tds_fdw]# cd tds_fdw/
[root@db2 tds_fdw]# PATH=/usr/local/pg9.5.5/bin:$PATH make USE_PGXS=1 install
-bash-3.2$ /usr/local/pg9.5/bin/psql
postgres=# CREATE EXTENSION tds_fdw;
CREATE EXTENSION

postgres=# \dx
List of installed extensions
Name | Version | Schema | Description
--------------------+---------------+------------+-----------------------------------------------------------------------------------------------
adminpack | 1.0 | pg_catalog | administrative functions for PostgreSQL
jdbc_fdw | 1.0 | public | Foreign data wrapper for querying JDBC
orafce | 3.3 | public | Functions and operators that emulate a subset of functions and packages from the Oracle RDBMS
pg_pathman | 1.1 | public | Partitioning tool ver. 1.1
pg_stat_statements | 1.3 | public | track execution statistics of all SQL statements executed
plpgsql | 1.0 | pg_catalog | PL/pgSQL procedural language
tds_fdw | 2.0.0-alpha.1 | public | Foreign data wrapper for querying a TDS database (Sybase or Microsoft SQL Server)
(7 rows)


四、新建TDS_FDW服務


postgres=# create server TDS_ghanDB foreign data wrapper tds_fdw options (servername '108.88.3.247', port '1433', database 'ghan', tds_version '4.2', character_set 'UTF-8');
CREATE SERVER
postgres=# grant usage on foreign server TDS_ghanDB to postgres;
GRANT
postgres=# create user mapping for postgres server TDS_ghanDB options (username 'ghan123', password 'ghan123');
CREATE USER MAPPING


postgres=# create foreign table test_ms (id integer, name varchar(20)) server TDS_ghanDB options (table 'TEST');
CREATE FOREIGN TABLE

//*注意由於MS SQL Server默認編碼為GBK,而且Postgresql編碼變ＵＴＦ８，需要改變編碼顯示為ＧＢＫ才能正常顯示中文

postgres=# select * from test_ms;
NOTICE: tds_fdw: Query executed correctly
NOTICE: tds_fdw: Getting results
id | name
----+------
1 | 系統
1 | 系統
1 | 系統
1 | 系統
1 | 系統
1 | 系統
1 | 系統
1 | 系統
1 | 系統
3 | uuu
(10 rows)