среда, 7 сентября 2016 г.

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

Комментариев нет:

Отправить комментарий