PostgreSQL to Microsoft SQL Server Using TDS Foreign Data WrapperAt work I needed to push data out from a Microsoft SQL Server 2005 to a PostgreSQL 11 instance. Foreign Data Wrappers was my first thought! Perl to the rescue was my second, but since I had some time, I decided to investigate the first way first.
The scenario was the following:
- CentOS 7 machine running PostgreSQL 11, it is not my preferred setup (I do prefer either FreeBSD or Ubuntu), but I have to deal with that;
- Microsoft SQL Server 2005 running on a Windows Server
, surely not something I like to work with and to which I had to connect via remote desktop (argh!).
First Step: get TDS workingAfter a quick research on the web, I discovered that MSSQL talks the Table Data Stream (TDS for short), so I don’t need to install an ODBC stack on my Linux box. And luckily, there are binaries for CentOS:
$ sudo yum install freetds $ sudo yum install freetds-devel freetds-doc
freetdscomes along with a
tsqlterminal command that is meant to be a diagnosing tool, so nothing as complete as a
psqlterminal. You should really test your connectivity with
tsqlbefore proceeding further, since it can save you hours of debugging when things do not work.
Thanks to a pragmatic test with
tsqlI discovered that I needed to open port
1433(default MSSQL port) on our enterprise firewall, as well as I had to create a database user on the MSSQL instance (not the right term, I know) and to grant read permissions to it. After that, I spent half an hour trying to understand how to send queries from
tsqlto MSSQL, because the former uses the special
gokeyword to send queries (something like
Once I got
$ tsql -H 192.168.6.53 -p 1433 -U 'fluca1978' -P 'xxxx' 1> select name from [mydb].[dbo].[people] 2> go ...
tsqlconnection working, was time to install the foreign data wrapper.
Second Step: Install
The foreign data wrapper to connect to MSSQL is an FDW that exploits TDS: tds_fdw. Unluckily, binaries are for older PostgreSQL versions and, moreover, there is a problem that prevents compilation against PostgreSQL 11. Luckily there is already a patch, so it is recommended to compile the current HEAD:
$ git show commit 3719a995b0ae3fc4c4b390dd8a2820d54b88e18a Merge: 782883d 3909a44 Author: Geoff Montee <firstname.lastname@example.org> Date: Thu Jan 3 15:21:14 2019 -0800 Merge pull request #190 from l-we/patch-1 Update options.c $ make ... $ sudo make install ... /usr/bin/mkdir -p '/usr/pgsql-11/lib' /usr/bin/mkdir -p '/usr/pgsql-11/share/extension' /usr/bin/mkdir -p '/usr/pgsql-11/share/extension' /usr/bin/mkdir -p '/usr/pgsql-11/doc/extension' /usr/bin/install -c -m 755 tds_fdw.so '/usr/pgsql-11/lib/tds_fdw.so' /usr/bin/install -c -m 644 .//tds_fdw.control '/usr/pgsql-11/share/extension/' /usr/bin/install -c -m 644 .//sql/tds_fdw--2.0.0-alpha.2.sql '/usr/pgsql-11/share/extension/' /usr/bin/install -c -m 644 .//README.tds_fdw.md '/usr/pgsql-11/doc/extension/' /usr/bin/mkdir -p '/usr/pgsql-11/lib/bitcode/tds_fdw' /usr/bin/mkdir -p '/usr/pgsql-11/lib/bitcode'/tds_fdw/src/ /usr/bin/install -c -m 644 src/tds_fdw.bc '/usr/pgsql-11/lib/bitcode'/tds_fdw/src/ /usr/bin/install -c -m 644 src/options.bc '/usr/pgsql-11/lib/bitcode'/tds_fdw/src/ /usr/bin/install -c -m 644 src/deparse.bc '/usr/pgsql-11/lib/bitcode'/tds_fdw/src/ cd '/usr/pgsql-11/lib/bitcode' && /usr/lib64/llvm5.0/bin/llvm-lto -thinlto -thinlto-action=thinlink -o tds_fdw.index.bc tds_fdw/src/tds_fdw.bc tds_fdw/src/options.bc tds_fdw/src/deparse.bc
Third Step: Use the FDWWith the foreign data wrapper in place, it is now time to create PostgreSQL objects. I recommend using
noticeas a message level because it can provide valuable information about the connection to the foreign server! In my case, it helped me understand that the showplan was something I needed to grant permission on to my MSSQL user, otherwise the conenction will fail with a generic “See the server log”, which made me discover that Microsoft is not very good at logging!
testdb=# CREATE EXTENSION tds_fdw; testdb=# CREATE SERVER server_mssql FOREIGN DATA WRAPPER tds_fdw OPTIONS( servername '192.168.6.53', database 'mydb', msg_handler 'notice', -- useful! tds_version '7.2' -- MSSQL 2005 ); testdb=# CREATE USER MAPPING FOR luca SERVER server_mssql OPTIONS ( username 'fluca1978', password 'xxxx' ); testdb=# CREATE FOREIGN TABLE mssql_people ( ... ) SERVER server_mssql OPTIONS ( schema_name 'dbo', table_name 'people', --row_estimate_method 'showplan_all' -- requires extra privileges on MSSQL side! );
Final Step: and here we go!If everything goes ok, it is possible to query
mssql_peopleand get some
noticemessages about retrieving data:
Times are really bad, but this is due to MSSQL database design I’m working with, so I don’t blame nor MSSQL itself, nor
testdb=# select * from people limit 5 NOTIFICA: DB-Library notice: Msg #: 5701, Msg state: 2, Msg: Changed database context to 'mydb'., Server: 192.168.6.53, Process: , Line: 1, Level: 0 NOTIFICA: DB-Library notice: Msg #: 5703, Msg state: 1, Msg: Changed language setting to us_english., Server: 192.168.6.53, Process: , Line: 1, Level: 0 NOTIFICA: tds_fdw: Query executed correctly NOTIFICA: tds_fdw: Getting results NOTIFICA: DB-Library notice: Msg #: 5701, Msg state: 2, Msg: Changed database context to 'mydb'., Server: 192.168.6.53, Process: , Line: 1, Level: 0 NOTIFICA: DB-Library notice: Msg #: 5703, Msg state: 1, Msg: Changed language setting to us_english., Server: 192.168.6.53, Process: , Line: 1, Level: 0 ... (5 rows) Time: 192231,473 ms (03:12,231)
tds_fdw: the table is a few gigabytes in size. I really need to investigate some optimizations.