Skip to content

Postgres 2 MSSQL database link

MSSQL FDW

Postgres foreign data wrapper for MSSQL database

Introduction

This is a continuation of #fdw foreign data wrappers series of posts. In this post we will deal with MSSQL database access from within Postgres.

tds_fdw

MSSQL database uses so-called Tabular Data Stream (TDS) protocol. This protocol was initially designed by Sybase for their RDBMS solutions. As result, tds_fdw wrapper could be used to connect to both MSSQL and Sybase databases. Probably this is the reason why the name chosen is tds_fdw and not database specific. As it usually happens for other Postgres' foreign data wrappers.

Extension requires library that implements TDS protocol to be installed on a system. Common choice for it is usually FreeTDS open source library.

Official GitHub repository for this extension has great documentation and examples but no binaries. You have to install and compile it manually.

After intallation of the TDS library and tds_fdw extension your setup is complete. You will be ready to start using it from within Postgres via SQL. Process is standard: execute CREATE EXTENSION command to install extension into specific database. Afterwards create foreign server via CREATE SERVER command and provide within it connection details to the MSSQL server.

Docker image

As with all other cases in this fdw series of posts there is a postgres image available with tds_fdw extension installed. It's available on a docker hub. It's built on top of Postgres' full size image latest tag. Section below describes how to use the image.

Demo

In order to run commands below you should download or clone GitHub repo. It also contains readme file with project layout and files descriptions.

Demo infrastructure is spinned up via compose files. For our case we need two instances. One for MSSQL and one for Postgres. For MSSQL we will use official Microsoft SQL Server 2019 image available on Docker hub. Inside mssql instance we will create a single table and populate it with couple of rows. From postgres instance we will select from this table as well as check MSSQL server version. Currently tds_fdw extension doesn't support write mode.

Note: If you use docker then just replace podman with docker in all commands below.

Spin up containers

Let's start mssql and postgres instances. Optional switch -p specifies pod name which could be thought of as "namespace". 

$ podman-compose -p test -f postgres_mssql_compose.yml up -d

$ podman-compose -p test -f postgres_mssql_compose.yml ps
CONTAINER ID  IMAGE                                         COMMAND               CREATED             STATUS                 PORTS                                           NAMES
f14a0b0d973a  docker.io/chumaky/postgres_mssql_fdw          postgres              About a minute ago  Up About a minute ago  0.0.0.0:1433->1433/tcp, 0.0.0.0:5432->5432/tcp  postgres
db48eaf051b5  mcr.microsoft.com/mssql/server:2019-latest    /opt/mssql/bin/sq...  About a minute ago  Up About a minute ago  0.0.0.0:1433->1433/tcp, 0.0.0.0:5432->5432/tcp  mssql

Initialize MSSQL database

It's possible to automatically initialize MSSQL database on startup. But it requires to perform a couple of additional steps which are out of scope for this post and will complicate things a bit. Instead we just wait a little bit when MSSQL database within container got initialized and populate the data manually.

First let's connect to the database via sqlcmd command line utility from within the container 

$ podman exec -it mssql /opt/mssql-tools/bin/sqlcmd -S localhost -U sa -P mssql_2019

We got interactive sqlcmd terminal where we can create test database. Within it we create users table and populate it with some data. 

1> create database test;
2> go
1> use test;
2> go
Changed database context to 'test'.

1> create table users(id int, name nvarchar(10));
2> insert into users values (1, 'John');
3> insert into users values (2, 'Mary');
4> go
(1 rows affected)
(1 rows affected)

1> select * from users;
2> go
id          name
----------- ----------
          1 John
          2 Mary
(2 rows affected)

Initialize Postgres database

Within postgres database we automatically create tds_fdw extension and map it to the corresponding mssql host. We also create foreign tables users and banner with the following structure. This happens as part of postgres container launch via initial podman-compose command. 

create extension tds_fdw schema tds_fdw;
create server mssql foreign data wrapper tds_fdw options (servername 'mssql', port '1433', database 'test');
create user mapping for postgres server mssql options (username 'sa', password 'mssql_2019');

create foreign table banner(version text)
server mssql
options (query 'select @@version as version')
;

create foreign table users(id int, name varchar)
server mssql
options (query 'select * from users')
;

You could check postgres_mssql_compose.yml and sql/postgres_mssql_setup.sql files for details.

Access MSSQL from Postgres

Now let's connect to postgres instance. 

$ podman exec -it postgres psql postgres postgres
postgres=#

We can check that we already have tds_fdw extension installed. We also have mssql foreign server as well as users and banner foreign tables defined. 

postgres=> \dx tds_fdw
                                          List of installed extensions
  Name   | Version | Schema  |                                    Description
---------+---------+---------+-----------------------------------------------------------------------------------
 tds_fdw | 2.0.2   | tds_fdw | Foreign data wrapper for querying a TDS database (Sybase or Microsoft SQL Server)

postgres=> \des
         List of foreign servers
 Name  |  Owner   | Foreign-data wrapper
-------+----------+----------------------
 mssql | postgres | tds_fdw

postgres=> \det
  List of foreign tables
 Schema | Table  | Server
--------+--------+--------
 public | banner | mssql
 public | users  | mssql

postgres=> \d banner
                 Foreign table "public.banner"
 Column  | Type | Collation | Nullable | Default | FDW options
---------+------+-----------+----------+---------+-------------
 version | text |           |          |         |
Server: mssql
FDW options: (query 'select @@version as version')

postgres=> \d users
                       Foreign table "public.users"
 Column |       Type        | Collation | Nullable | Default | FDW options
--------+-------------------+-----------+----------+---------+-------------
 id     | integer           |           |          |         |
 name   | character varying |           |          |         |
Server: mssql
FDW options: (query 'select * from users')

Let's check whether we could SELECT data from these tables. 

postgres=> select * from banner;
NOTICE:  tds_fdw: Query executed correctly
NOTICE:  tds_fdw: Getting results
                                version
------------------------------------------------------------------------
 Microsoft SQL Server 2019 (RTM-CU9) (KB5000642) - 15.0.4102.2 (X64)   +
         Jan 25 2021 20:16:12                                          +
         Copyright (C) 2019 Microsoft Corporation                      +
         Developer Edition (64-bit) on Linux (Ubuntu 18.04.5 LTS) <X64>
(1 row)


postgres=> select * from users;
NOTICE:  tds_fdw: Query executed correctly
NOTICE:  tds_fdw: Getting results
 id | name
----+------
  1 | John
  2 | Mary
(2 rows)

As you can see we have successfully connected to MSSQL server from within Postgres and were able to fetch the data from it.

Summary

tds_fdw extension allows to have read access to the MSSQL server from within Postgres database. For quickier setup there is a chumaky/postgres_mssql_fdw docker image is available on the docker hub. It contains tdw_fdw extension pre-installed.

Comments