PostgreSQL foreign data wrapper for MS-SQL-Server "tds_fdw"

To implement cross database connection from PostgreSQL toward MS-SQL Server you need yo configure tds_fdw (This is a PostgreSQL foreign data wrapper that can connect to databases that use the Tabular Data Stream (TDS) protocol, such as Sybase databases and Microsoft SQL server.) 


This should support PostgreSQL 9.2+.

The current version does not yet support JOIN push-down, or write operations.

It does support WHERE and column pushdowns when match_column_names is enabled.

--------------------------------------------------------------------------------------------------------------------------

Following are the steps how to implement FDW with PostgreSQL to perform operation on MS-SQL-Server Database .

I am considering below things are with you .

• PostgreSQL DB server up and running ((9.2 And above).
• MS-SQL server up and running .

1) Steps to install tds_fdw extension

Download tds_fdw from git 

git clone https://github.com/GeoffMontee/tds_fdw.git

cd tds_fdw

Install some prerequisites package .

sudo yum install epel-release

yum install freetds freetds-devel

Start installation for  tds_fdw

export PATH=/opt/PostgreSQL/9.5/bin/:$PATH       //PostgreSQL binary path .

make USE_PGXS=1

make USE_PGXS=1 install

2) Install/Create extension inside database .

- Create  tds_fdw extension in PostgreSQL database .

CREATE EXTENSION tds_fdw;

- Create server object

CREATE SERVER mssql_svr    FOREIGN DATA WRAPPER tds_fdw    OPTIONS (servername '192.168.213.136', port '1433', database 'test' );

Where 
     
      Server Name - Is IP address of MS-SQL server where it's running .
      Port - MS-SQL server's port on which it's accepting connection.
      Database - MS-SQL Database Name which need to connect from PostgreSQL

- Create user mapping

CREATE USER MAPPING FOR postgres    SERVER mssql_svr    OPTIONS (username 'SA', password 'crossconn1');

Where  

     User Name  - SA is user name of MS-SQL DB user .
     Password     - is MS-SQL DB user's password . 

- Create foreign table

CREATE FOREIGN TABLE employee (    id integer,    name varchar(50),role varchar(50),Address varchar(50))    SERVER mssql_svr    OPTIONS (schema_name 'dbo', table_name 'employee');

Where 

    Schema_Name - Schema name from database of MS-SQL server .
    Table Name - Table Name which you want to access from MS-SQL Server .

3) Verify all this created or not .

Below image contain all .

4) Fetch data from MS-SQL into PostgreSQL .

Below image show it .