PostgreSQL9.3 Installing TDS_FDW Extensions

PostgreSQL supports the so-called appearance function from the 9.x, that is, in PostgreSQL by installing some extensions and then doing some configuration, you can create a local surface mapping to other different types of databases ( Can be broadly understood as dblink across PostgreSQL and other types of databases)

Today we introduce the method of mapping SQL Server database through TDS_FDW extension in PostgreSQL

Environment: ubunut14,postgresql9.3,sqlserver2005

FDW is essentially an extension of PostgreSQL, it cannot communicate directly with other databases, so you must install clients that can communicate with other database servers (such as MYSQLCLIENT,ORACLECLIENT,FREETDS, etc.)

--Installation FreeTDS
Apt-get Install LIBSYBDB5 Freetds-dev Freetds-common
--Installing PostgreSQL
Apt-get Install postgresql-9.3 postgresql-client-9.3 postgresql-server-dev-9.3

--Download, upload to the server, and compile (can be placed in any directory):
git clone https://github.com/GeoffMontee/tds_fdw.git
CD TDS_FDW
Make Use_pgxs=1
Make Use_pgxs=1 Install
--Create plugins (executed in pgadmin)
CREATE EXTENSION TDS_FDW

--Create server (execute in pgadmin)
CREATE Server server
FOREIGN DATA WRAPPER TDS_FDW
OPTIONS (servername ' database IP ', character_set ' UTF-8 ', Port ' 1433 ');

--Create mappings (performed in pgadmin)
CREATE USER MAPPING for Postgres
Server server
OPTIONS (username ' database user name ', password ' database password ');

--Creating appearances

CREATE FOREIGN TABLE Ext_test (
ID2 int not NULL,
Name varchar (255) is not NULL,
Code varchar (255) NULL
)

Server server
OPTIONS (database name, query ' select ID2, name,code from Project ');

--Querying data
SELECT * from Ext_test;

--Remove appearances

Drop FOREIGN Table Ext_project

Ps:

Address of the TDS_FDW project: HTTPS://GITHUB.COM/GEOFFMONTEE/TDS_FDW

MYSQL_FDW Project Address: HTTPS://GITHUB.COM/DPAGE/MYSQL_FDW

PostgreSQL9.3 Installing TDS_FDW Extensions