Data integration between PostgreSQL and MS SQL Server

In this topic I would like to describe setup of PostgreSQL integration, which works on a Linux, with MS SQL Server. As well as how to import all tables from a specific schema of MS SQL Server database into PostgreSQL without having to specify the structure of each table.

Installing tds_fdw

tds_fdw is used for the integration between PostgreSQL and MS SQL Server. This module communicates with external database using TDS protocol (Tabular Data Stream). TDS is used by such DBMS as MS SQL Server and Sybase SQL Server.

We need to install FreeTDS library before making and installing tds_fdw. For Ubuntu it is freetds-dev and freetds-common packages:

sudo apt-get install freetds-dev freetds-common

After this we need to download and install tds_fdw (we need installed PostgreSQL and pg_config entry in the PATH environment variable):

git clone https://github.com/tds-fdw/tds_fdw.git
cd tds_fdw
make USE_PGXS=1 install

It is necessary to configure FreeTDS before using tds_fdw. Configure file of FreeTDS is located in the /etc/freetds/freetds.conf. For example, add the following entry into the configuration file:

[mssql01]
        host = 192.168.0.1
        port = 1433
        tds version = 7.1
        instance = MSSQL01

Now we can create necessary objects in the PostgreSQL database:

-- Wrapper module
CREATE EXTENSION tds_fdw;
-- Foreign server
CREATE SERVER sql01 FOREIGN DATA WRAPPER tds_fdw
 OPTIONS (servername 'mssql01', database 'test_sql01', msg_handler 'notice');
-- User mapping
CREATE USER MAPPING FOR pguser SERVER sql01 OPTIONS (username 'msuser', password 'userpass');

After that we could create external tables on the PostgreSQL database that point to tables on the MS SQL Server database. Instead of this we can use the IMPORT FOREIGN SCHEMA instruction.

Import tables from the MS SQL Server database

The IMPORT FOREIGN SCHEMA instruction was introduced by PostgreSQL 9.5. It is supported by such wrappers as oracle_fdw, mysql_fdw. But tds_fdw had not supported it before now.

Supporting of this instruction by tds_fdw was necessary for one of our projects. We have implemented this instruction in tds_fdw and created the pull request for tds_fdw developers. tds_fdw developers joyfully have accepted it on the same day. Before that they fixed their Python test scripts, since tests perform on PostgreSQL 9.2, 9.3, 9.4 and 9.5. But the IMPORT FOREIGN SCHEMA instruction was introduced in the 9.5.

Now we can execute the query:

IMPORT FOREIGN SCHEMA msschema01 FROM SERVER sql01
 INTO pgschema01
 OPTIONS (import_default 'true');

msschema01 and pgschema01 should already exist. The instruction accepts the following options:

• import_default - controls whether column DEFAULT expressions are included in the definitions of foreign tables (default false).
• import_not_null - controls whether column NOT NULL constraints are included in the definitions of foreign tables (default true).

The following data type matching is used on schema importing:

MS SQL Server data type	PostgreSQL data type
bit smallint tinyint	smallint
int	integer
bigint	bigint
decimal(p[ ,s])	decimal(p[ ,s])
numeric(p[ ,s])	numeric(p[ ,s])
money smallmoney	money
float float(n), where 25<=n<=53	double precision
real float(n), where 1<=n<=24	real
date	date
datetime datetime2 smalldatetime timestamp	timestamp without time zone
datetimeoffset	timestamp with time zone
time	time
char(n) nchar(n)	char(n)
varchar(n) nvarchar(n)	varchar(n)
varchar(MAX) text ntext	text
binary varbinary image	bytea
xml	xml

Data migration became simpler usign tds_fdw and it isn't necessary to import each table with separate commands.

References

1. Download tds_fdw
2. Writing A Foreign Data Wrapper
3. List of foreign data wrappers