In the last posts I looked at how you can connect Oracle and MariaDB/MySQL to a PostgreSQL instance. In this post I’ll do the same with a MS SQL Server instance.

What you need before you can start is a library that implements the Tabular Data Stream protocol. For Linux based systems there is FreeTDS. Installation is quite easy:

1
2
3
4
5
6
7
8
9
10
11
12
13
postgres@pgreporting:/var/tmp/ [pg952] wget ftp://ftp.freetds.org/pub/freetds/stable/freetds-patched.tar.gz
postgres@pgreporting:/var/tmp/ [pg952] ls
freetds-patched.tar.gz
postgres@pgreporting:/var/tmp/ [pg952] tar -axf freetds-patched.tar.gz
postgres@pgreporting:/var/tmp/freetds-0.95.95/ [pg952] ls
aclocal.m4  CMakeLists.txt  configure     doc              INSTALL       m4           mkinstalldirs  samples      vms
AUTHORS     compile         configure.ac  freetds.conf     install-sh    Makefile.am  NEWS           src          win32
autogen.sh  config.guess    COPYING       freetds.spec     interfaces    Makefile.in  Nmakefile      tds.dox
BUGS        config.rpath    COPYING.LIB   freetds.spec.in  locales.conf  misc         PWD.in         test-driver
ChangeLog   config.sub      depcomp       include          ltmain.sh     missing      README         TODO
postgres@pgreporting:/var/tmp/freetds-0.95.95/ [pg952] ./configure
postgres@pgreporting:/var/tmp/freetds-0.95.95/ [pg952] make
postgres@pgreporting:/var/tmp/freetds-0.95.95/ [pg952] sudo make install

At this point in time it is advisable to test the connection to the MS SQL Server instance. FreeTDS will install a default configuration which you can adjust to fit your environment:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
postgres@pgreporting:/var/tmp/freetds-0.95.95/ [pg952] cat /usr/local/etc/freetds.conf
#   $Id: freetds.conf,v 1.12 2007-12-25 06:02:36 jklowden Exp $
#
# This file is installed by FreeTDS if no file by the same
# name is found in the installation directory. 
#
# For information about the layout of this file and its settings,
# see the freetds.conf manpage "man freetds.conf". 
 
# Global settings are overridden by those in a database
# server specific section
[global]
        # TDS protocol version
;   tds version = 4.2
 
    # Whether to write a TDSDUMP file for diagnostic purposes
    # (setting this to /tmp is insecure on a multi-user system)
;   dump file = /tmp/freetds.log
;   debug flags = 0xffff
 
    # Command and connection timeouts
;   timeout = 10
;   connect timeout = 10
     
    # If you get out-of-memory errors, it may mean that your client
    # is trying to allocate a huge buffer for a TEXT field. 
    # Try setting 'text size' to a more reasonable limit
    text size = 64512
 
# A typical Sybase server
[egServer50]
    host = symachine.domain.com
    port = 5000
    tds version = 5.0
 
# A typical Microsoft server
[mssql]
    host = 192.168.22.102
    port = 1433
        database = ds2
    tds version = 7.3

The last block specifies the parameters for connecting to my MS SQL Server instance. Lets try:

1
2
3
4
5
6
7
8
9
10
11
postgres@pgreporting:/home/postgres/ [pg952] tsql -I /usr/local/etc/freetds.conf -S mssql -U ds2user -P xxxxx -o v
locale is "LC_CTYPE=en_US.UTF-8;LC_NUMERIC=de_CH.UTF-8;LC_TIME=en_US.UTF-8;LC_COLLATE=en_US.UTF-8;LC_MONETARY=de_CH.UTF-8;LC_MESSAGES=en_US.UTF-8;LC_PAPER=de_CH.UTF-8;LC_NAME=de_CH.UTF-8;LC_ADDRESS=de_CH.UTF-8;LC_TELEPHONE=de_CH.UTF-8;LC_MEASUREMENT=de_CH.UTF-8;LC_IDENTIFICATION=de_CH.UTF-8"
locale charset is "UTF-8"
using default charset "UTF-8"
1> select count(*) from sys.databases;
2> go
 
using TDS version 7.3
5
(1 row affected)
using TDS version 7.3

Cool, works. If you wonder where the ds2user comes from then check this post.

Once this works we can proceed with installing the foreign data wrapper for Sybase / MS SQL Server which is quite easy as well:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
postgres@pgreporting:/home/postgres/ [PGREP] git clone https://github.com/tds-fdw/tds_fdw.git
Cloning into 'tds_fdw'...
remote: Counting objects: 588, done.
remote: Compressing objects: 100% (27/27), done.
remote: Total 588 (delta 11), reused 0 (delta 0), pack-reused 561
Receiving objects: 100% (588/588), 242.61 KiB | 0 bytes/s, done.
Resolving deltas: 100% (335/335), done.
postgres@pgreporting:/home/postgres/ [PGREP] cd tds_fdw
postgres@pgreporting:/home/postgres/tds_fdw/ [PGREP] PATH=$PGHOME:$PATH make USE_PGXS=1
gcc -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard -O2 -fpic -I./include/ -I. -I./ -I/u01/app/postgres/product/95/db_2/include/server -I/u01/app/postgres/product/95/db_2/include/internal -D_GNU_SOURCE -I/usr/include/libxml2   -c -o src/tds_fdw.o src/tds_fdw.c
gcc -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard -O2 -fpic -I./include/ -I. -I./ -I/u01/app/postgres/product/95/db_2/include/server -I/u01/app/postgres/product/95/db_2/include/internal -D_GNU_SOURCE -I/usr/include/libxml2   -c -o src/options.o src/options.c
gcc -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard -O2 -fpic -I./include/ -I. -I./ -I/u01/app/postgres/product/95/db_2/include/server -I/u01/app/postgres/product/95/db_2/include/internal -D_GNU_SOURCE -I/usr/include/libxml2   -c -o src/deparse.o src/deparse.c
gcc -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard -O2 -fpic -shared -o tds_fdw.so src/tds_fdw.o src/options.o src/deparse.o -L/u01/app/postgres/product/95/db_2/lib -Wl,--as-needed -Wl,-rpath,'/u01/app/postgres/product/95/db_2/lib',--enable-new-dtags  -lsybdb
cp sql/tds_fdw.sql sql/tds_fdw--2.0.0-alpha.1.sql
cp README.md README.tds_fdw.md
postgres@pgreporting:/home/postgres/tds_fdw/ [PGREP] sudo PATH=$PGHOME/bin:$PATH make USE_PGXS=1 install
/bin/mkdir -p '/u01/app/postgres/product/95/db_2/lib'
/bin/mkdir -p '/u01/app/postgres/product/95/db_2/share/extension'
/bin/mkdir -p '/u01/app/postgres/product/95/db_2/share/extension'
/bin/mkdir -p '/u01/app/postgres/product/95/db_2/share/doc/extension'
/bin/install -c -m 755  tds_fdw.so '/u01/app/postgres/product/95/db_2/lib/tds_fdw.so'
/bin/install -c -m 644 .//tds_fdw.control '/u01/app/postgres/product/95/db_2/share/extension/'
/bin/install -c -m 644 .//sql/tds_fdw--2.0.0-alpha.1.sql  '/u01/app/postgres/product/95/db_2/share/extension/'
/bin/install -c -m 644 .//README.tds_fdw.md '/u01/app/postgres/product/95/db_2/share/doc/extension/'

Continue with the usual steps to create the extension in your PostgreSQL instance:

1
2
3
4
5
6
7
8
9
10
postgres= create extension tds_fdw;
CREATE EXTENSION
postgres= dx
                                                List of installed extensions
   Name    |    Version    |   Schema   |                                    Description                                   
-----------+---------------+------------+-----------------------------------------------------------------------------------
 mysql_fdw | 1.0           | public     | Foreign data wrapper for querying a MySQL server
 plpgsql   | 1.0           | pg_catalog | PL/pgSQL procedural language
 tds_fdw   | 2.0.0-alpha.1 | public     | Foreign data wrapper for querying a TDS database (Sybase or Microsoft SQL Server)
(3 rows)

If this succeeds we’ll need to create the foreign server and the user mapping:

1
2
3
postgres= create server mssql_svr foreign data wrapper tds_fdw options ( servername '192.168.22.102', port '1433',  database 'ds2', tds_version '7.3', msg_handler 'notice' );
CREATE schema ds2_mssql;
CREATE USER MAPPING FOR postgres SERVER mssql_svr  OPTIONS (username 'ds2user', password 'xxxxx');

As I am lazy I want to import the foreign the schema for not needing to specify each table on my own:

1
2
postgres=# IMPORT FOREIGN SCHEMA ds2 FROM SERVER mssql_svr into ds2_mssql;
ERROR:  foreign-data wrapper "tds_fdw" does not support IMPORT FOREIGN SCHEMA

Hm, not available. This is tracked here and might be available in future versions. No other choice than to do some hand work:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
create foreign table ds2_mssql.customers (
 CUSTOMERID            int
,FIRSTNAME             varchar(50)
,LASTNAME              varchar(50)
,ADDRESS1              varchar(50)
,ADDRESS2              varchar(50)
,CITY                  varchar(50)
,STATE                 varchar(50)
,ZIP                   int
,COUNTRY               varchar(50)
,REGION                int
,EMAIL                 varchar(50)
,PHONE                 varchar(50)
,CREDITCARDTYPE        int
,CREDITCARD            varchar(50)
,CREDITCARDEXPIRATION  varchar(50)
,USERNAME              varchar(50)
,PASSWORD              varchar(50)
,AGE                   int
,INCOME                int
,GENDER                varchar(50))
SERVER mssql_svr
    OPTIONS (table 'dbo.customers', row_estimate_method 'showplan_all');

Lets see if it works:

1
2
3
4
5
postgres=# select count(*) from ds2_mssql.customers;
 count 
--------
 200000
(1 row)

Perfect. Have fun getting data from MS SQL Server.

Post Views: 3,001
Thumbnail [60x60]
by
Daniel Westermann