PostgreSQL integration with MS SQL Server

In a previous article, my colleague Dmitry Vasilyev described the configuration of PostgreSQL integration with MySQL and described how to more efficiently perform some queries.

In this article I would like to describe the configuration of a PostgreSQL connection, running under Linux, to MS SQL Server. As well as how to import all the tables of a specific MS SQL Server database schema into PostgreSQL without describing the structure of each table.

Install and configure tds_fdw

Tds_fdw is used to integrate PostgreSQL and MS SQL Server. This module communicates with the database via the TDS (Tabular Data Stream) protocol. TDS is used by DBMS such as MS SQL Server and Sybase SQL Server.

Before building and installing tds_fdw, you must install the FreeTDS library. For Ubuntu, these are the freetds-dev and freetds-common packages:

sudo apt-get install freetds-dev freetds-common 

Next, download and compile tds_fdw (to build, we also need the installed PostgreSQL and pg_config in the PATH environment variable):

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

Before using tds_fdw, you must configure FreeTDS. FreeTDS is configured to connect to MS SQL Server using the /etc/freetds/freetds.conf file. For example, add the following content:

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

Now we can create the necessary objects in PostgreSQL:

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

Here, mssql01 is the server name in freetds.conf, pguser is a PostgreSQL user, msuser is a MS SQL Server user.

After that, we could create an external table for each MS SQL Server table in PostgreSQL. But instead, we can use the IMPORT FOREIGN SCHEMA command.

Import table definitions from MS SQL Server

The IMPORT FOREIGN SCHEMA command was implemented in PostgreSQL 9.5. Third-party data wrappers such as oracle_fdw, mysql_fdw implemented its support. But there was no support from tds_fdw.

For one of our projects, tds_fdw’s support for this team was also needed. We have implemented the team and created a pull request . The developers happily accepted it the same day. Before that, they fixed the testing scripts for a couple of hours, which are implemented in Python, because tests are performed for PostgreSQL 9.2, 9.3, 9.4 and 9.5. But the IMPORT FOREIGN SCHEMA command was added only in version 9.5.

Now we can execute the following command:

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

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

• import_default - add or not the expression DEFAULT when describing table columns (by default, false).
• import_not_null - add or not the NOT NULL constraint when describing table columns (true by default).

When importing table column descriptions, the following type mapping is used:

 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 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
 rowversion
 timestamp

 bytea

 xml

 xml

Thus, data migration using tds_fdw has become much easier, no need to import each table separately.

Thanks for attention!

UPDATE Updated type mapping table. Now the rowversion and MS SQL Server timestamp types correspond to the bytea PostgreSQL type (see commit ).

Links

1. Download tds_fdw
2. Documentation for Foreign Data Wrapper (en)
3. Documentation for Foreign Data Wrapper (ru)
4. List of third-party data wrappers