Problem
In part 2 of this series, we saw how Microsoft SQL Server can be configured to access PostgreSQL data. Our readers have often posted questions about how PostgreSQL could access data from SQL Server. In this final part of the series, we will see how that’s done with the PostgreSQL Foreign Data Wrapper.
Solution
Previously we restored a sample database in PostgreSQL called world and created a corresponding database in SQL Server. We then scripted out two tables from the world database and created them in SQL Server.
In our current setup, we have restored the AdventureWorks2012 database in SQL Server. Using pgAdmin, we created an empty database in PostgreSQL, also called adventureworks2012.
We have scripted out one small table from AdventureWorks2012 in SQL Server and the script command looks like this (we haven’t included the default constraints here):
CREATE TABLE [Sales].[SalesPerson]( [BusinessEntityID] [int] NOT NULL, [TerritoryID] [int] NULL, [SalesQuota] [money] NULL, [Bonus] [money] NOT NULL, [CommissionPct] [smallmoney] NOT NULL, [SalesYTD] [money] NOT NULL, [SalesLastYear] [money] NOT NULL, [rowguid] [uniqueidentifier] ROWGUIDCOL NOT NULL, [ModifiedDate] [datetime] NOT NULL, CONSTRAINT [PK_SalesPerson_BusinessEntityID] PRIMARY KEY CLUSTERED ([BusinessEntityID] ASC) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY]
The script will later have to change to reflect PostgreSQL syntax and data types.
Foreign Data Wrappers
Before we do anything, let’s talk about a couple of things. First of all, let’s define a Foreign Data Wrapper (FDW).
Foreign Data Wrappers are PostgreSQL’s equivalent of SQL Server Linked Servers with ODBC or OLE DB drivers. PostgreSQL – just like any other database platform – often needs to talk to heterogeneous data sources. To make it possible, PostgreSQL uses what’s known as a Foreign Data Wrapper (FDW). A FDW is like a driver that sits between the database engine and its remote counterpart. There are numerous FDWs listed in the PostgreSQL wiki site. There are wrappers available for SQL Server, Oracle, MongoDB, text files, etc.
The wrapper we are interested in is tds_fdw. It’s a project created by Geoff Montee and freely available from both the PGXN (PostgreSQL eXtension Network) site or GitHub. The TDS in the wrapper name stands for Tabular Data Source.
So how does this Foreign Data Wrapper work? Well, tds_fdw depends on a database access library called FreeTDS. FreeTDS is a set of libraries that enables *nix-like operating systems like Linux to make direct calls to SQL Server or Sybase. FreeTDS is an implementation of the Tabular Data Source (TDS) protocol that SQL Server uses. For more information on how FreeTDS works, you can view the official site.
PostgreSQL Extensions
Extensions are add-ons on top of PostgreSQL’s core release. Essentially, extensions – “contrib modules” as they used to be called – are software packages that need to be installed separately. They do what their name suggests – extending the functionality of the core database engine. For example, there are extensions available for encrypting and storing passwords, providing GIS functionality or storing large objects (LOBs). And in-line with true Open Source philosophy, numerous PostgreSQL extensions are available from various sites.
Once an extension is installed, the files are registered under the PostgreSQL program directory. We can then run a command called “CREATE EXTENSION” to load that extension in the current database. Behind the scenes, PostgreSQL runs the SQL scripts that come with the extension’s installer to create database objects like functions and data types. Once the extension is loaded successfully, we can make calls to it from PostgreSQL code as if it were another native function.
To give an example, PostgreSQL 9.3 ships with a standard set of extensions we can load any time. In our world database, we can see a partial list of extensions available for loading:
So why are we talking about extensions? If you are guessing what I am about to say, you are right. Foreign Data Wrappers are PostgreSQL extensions too. So that means we will need to download and install the tds_fdw extension and register it in our adventureworks2012 database before we can use it.
Installing the Foreign Data Wrapper Extension
Step 1: Let’s log in to our PostgreSQL Linux box via Secure SHell (SSH) as the root user or a user with sudo privileges
Step 2: Download the gcc package. That’s the GNU Compiler Collection. When we download the tds_fdw extension, it comes as source code We will need to compile that into binary first.
$ sudo yum install gcc -y
Step 3: Next, we need to install the EPEL (Extra Packages for Enterprise Linux) repository. EPEL is a free, community based repository of high quality Linux packages. We are installing the EPEL repository because the FreeTDS library is part of EPEL and tds_fdw depends on FreeTDS.
$ sudo wget http://dl.fedoraproject.org/pub/epel/7/x86_64/e/epel-release-7-5.noarch.rpm
$ sudo rpm -ivh epel-release-7-5.noarch.rpm
Step 4: Once the RPM package for the EPEL repository has been installed, we install the FreeTDS libraries through this repository.
$ sudo yum install freetds -y
$ sudo yum install freetds-devel -y
Step 5: It’s now time to download the source files for the tds_fdw extension. We can download the zipped source files from GitHub:
$ sudo wget https://github.com/GeoffMontee/tds_fdw/archive/master.zip
We then unzip the source files (you may have to manually install the unzip utility for the next command to work):
$ unzip ~/master.zip
If you look under the current directory, you will find the downloaded master.zip file has been decompressed into a directory called tds_fdw-master. If you look at that directory’s contents, it will have a few files in it:
$ ls -l tds_fdw-master/ total 24 -rw-rw-r--. 1 ec2-user ec2-user 1559 Jul 13 2014 Makefile -rw-rw-r--. 1 ec2-user ec2-user 1153 Jul 13 2014 META.json -rw-rw-r--. 1 ec2-user ec2-user 10882 Jul 13 2014 README.md drwxrwxr-x. 2 ec2-user ec2-user 24 Jul 13 2014 sql drwxrwxr-x. 2 ec2-user ec2-user 22 Jul 13 2014 src -rw-rw-r--. 1 ec2-user ec2-user 856 Jul 13 2014 tds_fdw.control
Step 6: Navigate into the unzipped directory and run the following commands to “make” the binary output from the source files:
$ cd tds_fdw
$ sudo PATH=/usr/pgsql-9.3/bin:$PATH make USE_PGXS=1
The output may look like this:
gcc -O2 -g -pipe -Wall -Wp,-D_FORTIFY_SOURCE=2 -fexceptions -fstack-protector-strong --param=ssp-buffer-size=4 -grecord-gcc-switches -m64 -mtune=generic -DLINUX_OOM_ADJ=0 -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard -fpic -I. -I. -I/usr/pgsql-9.3/include/server -I/usr/pgsql-9.3/include/internal -D_GNU_SOURCE -I/usr/include/libxml2 -I/usr/include -c -o src/tds_fdw.o src/tds_fdw.c
gcc -O2 -g -pipe -Wall -Wp,-D_FORTIFY_SOURCE=2 -fexceptions -fstack-protector-strong --param=ssp-buffer-size=4 -grecord-gcc-switches -m64 -mtune=generic -DLINUX_OOM_ADJ=0 -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard -fpic -shared -o tds_fdw.so src/tds_fdw.o -L/usr/pgsql-9.3/lib -Wl,--as-needed -L/usr/lib64 -Wl,--as-needed -Wl,-rpath,'/usr/pgsql-9.3/lib',--enable-new-dtags -lsybdb
cp sql/tds_fdw.sql sql/tds_fdw--1.0.1.sql
cp README.md README.tds_fdw.md
Step 7: We then install the binary that was built in the previous step:
$ sudo PATH=/usr/pgsql-9.3/bin:$PATH make USE_PGXS=1 install
The output should look like this:
/usr/bin/mkdir -p '/usr/pgsql-9.3/lib' /usr/bin/mkdir -p '/usr/pgsql-9.3/share/extension' /usr/bin/mkdir -p '/usr/pgsql-9.3/share/extension' /usr/bin/mkdir -p '/usr/share/doc/pgsql/extension' /usr/bin/install -c -m 755 tds_fdw.so '/usr/pgsql-9.3/lib/tds_fdw.so' /usr/bin/install -c -m 644 ./tds_fdw.control '/usr/pgsql-9.3/share/extension/' /usr/bin/install -c -m 644 ./sql/tds_fdw--1.0.1.sql '/usr/pgsql-9.3/share/extension/' /usr/bin/install -c -m 644 ./README.tds_fdw.md '/usr/share/doc/pgsql/extension/'
Step 8: Start the pgAdmin client tool and connect to the adventurworks2012 database. Start a new query window and execute the following command:
CREATE EXTENSION tds_fdw;
The result returned will be similar to this:
Query returned successfully with no result in 67 ms.
Step 9: Now we have downloaded, unzipped, compiled, installed and loaded our extension, let’s see if it’s available. Expand the Extensions node under the adventureworks2012 database in pgAdmin and we will see the extension is available.
Accessing SQL Server Data
Now that we have a way to “talk” to SQL Server, let’s create some PostgreSQL objects to access a remote table. If you already don’t have a query window open against the adventureworks2012 database, open it from pgAdmin.
Step 1: Execute the following command to create a foreign server (change whatever IP address SQL Server is running with). The servername parameter can also be the host name of the SQL Server, but we have decided to use the IP address to eliminate any confusion:
CREATE SERVER mssql_adworks2012_server FOREIGN DATA WRAPPER tds_fdw OPTIONS (servername '10.51.2.228', port '1433');
Step 2: Once the query completes, run the following command to make a mapping between the local PostgreSQL user and the remote sa user:
CREATE USER MAPPING FOR postgres SERVER mssql_adworks2012_server OPTIONS (username 'sa', password 'mypassword');
Step 3: And finally, execute the following command to create the foreign table definition:
CREATE FOREIGN TABLE mssql_adworks2012_Sales_SalesPerson_table ( BusinessEntityID int NOT NULL, TerritoryID int NULL, SalesQuota money NULL, Bonus money NOT NULL, CommissionPct real NOT NULL, SalesYTD money NOT NULL, SalesLastYear money NOT NULL, ModifiedDate text NOT NULL )
SERVER mssql_adworks2012_server OPTIONS (database 'AdventureWorks2012', query 'SELECT BusinessEntityID, TerritoryID, SalesQuota, Bonus, CommissionPct, SalesYTD, SalesLastYear,ModifiedDate FROM Sales.SalesPerson');
If you compare the code for the foreign table with the scripted original table definition, you will see some major differences. That’s because of the limitations related to foreign tables. At the time of writing this tip, you can’t define a primary key constraint in a foreign table. We also had to water down the data types to make it work with PostgreSQL. For example, ModifiedDate has a date time value in SQL Server. The values in this column can’t be easily mapped to PostgreSQL date or timestamp data type, so we defined the field with text data type to keep it really simple. Same goes for the rowguid field. It wouldn’t even map to PostgreSQL’ text data type so we decided not to port it across.
Step 4: To check if the foreign objects were created in PostgreSQL, navigate to File > Options… from the pgAdmin main menu. In the dialog box, choose the Display option under the Browser node. Make sure both the Foreign Data Wrappers and Foreign Servers options are checked and then click OK to close the dialog box.
Expand the adventureworks2012 database now and you will see some extra object nodes added under the database. You can drill down to the nodes to see the foreign server just added and the foreign table created in it.
Now if we run a simple SELECT statement against the foreign table, we will be able to see the data:
SELECT * FROM mssql_adworks2012_Sales_SalesPerson_table;
The data in the foreign table is dynamic, which means if we INSERT, UPDATE or DELETE data in the SQL Server table, it will be reflected here. If you want the foreign table data to be static, you can create a table in PostgreSQL first and then use INSERT INTO…SELECT FROM command to copy the data in.
Conclusion
So that’s how we can access SQL Server data from PostgreSQL. This was a very simple exercise, but we hope it shows you the underlying processes involved. If you think it’s a lot of hoops to go through, well yes, this is true when you set it up for the first time. Foreign servers and foreign tables are not as quick to set up as linked servers and the process becomes quite tedious if you need to bring data from a large number of SQL Server tables. Despite all these, it’s still a method to access SQL Server data and once set up properly, it can help developers and report writers write queries seamlessly. It won’t be possible for us to cover every scenario where your data access may hit a snag, but that’s what Google is there for.
Next Steps
- Learn more about PostgreSQL extensions and commonly available extensions from the developer community
- Learn more about PostgreSQL Foreign Data Wrapper
- Visit the GitHub page for tds_fdw
- Get familiar with heterogeneous database access by setting up your own PostgreSQL and SQL Server instances and follow this tutorial series.
- If your organization is already using some process to access SQL Server data from PostgreSQL, investigate and learn how that’s done.
