PostgreSQL Integration with MS SQL Server

In a previous article, my colleague Dmitry Vasiliev described how to configure PostgreSQL integration with MySQL and described how to perform some queries more efficiently.

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

Install and configure tds_fdw

For integration of PostgreSQL and MS SQL Server, tds_fdw is used . This module communicates with the database via the TDS (Tabular Data Stream) protocol . TDS is used by such DBMSs as MS SQL Server and Sybase SQL Server.

Before building and installing tds_fdw, you need to 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 build tds_fdw (for assembly 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. Configuring FreeTDS to connect to MS SQL Server is performed 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 name of the server in freetds.conf, pguser is the PostgreSQL user, msuser is the MS SQL Server user.

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

Import table definition with MS SQL Server

The team IMPORT FOREIGN SCHEMAwas implemented in PostgreSQL 9.5. Third-party data wrappers such as oracle_fdw, mysql_fdw implemented its support. But tds_fdw did not have support.

For one of our projects, tds_fdw also needed support for this team. We have implemented the command and created a pull request . The developers joyfully received it that day. Before that, they fixed scripts for testing that are implemented in Python in a couple of hours, because tests are performed for PostgreSQL 9.2, 9.3, 9.4, and 9.5. But the command IMPORT FOREIGN SCHEMAwas 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');

Schemes msschema01 and pgschema01 must already exist. The command accepts the following options:

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

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, you do not need to import each table separately.

Thanks for attention!

UPDATE The type mapping table has been updated. Now the rowversion and timestamp types of MS SQL Server correspond to the bytea PostgreSQL type (see commit ).

References

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