Foreign Data Wrapper Between PostgreSQL and MSSQL

 

In the article “Postgresql Foreign Data Wrapper“, we created Foreign Data Wrapper to read data between two PostgreSQLs.

And we have created Foreing Data Wrapper Between PostgreSQL and Oracle in the article named “PostgreSQL Foreign Data Wrapper Between PostgreSQL and Oracle“.

In this article, we will create Foreign Data Wrapper by using tds_fdw extension between Mssql and Postgresql.

Before we create Foreign Data Wrapper, there are a few packages that we need to install. We need to install these packages as follows.

Before all of this, if you have installed Postgresql without installing lib and dev packages, you need to install rpm packages according to your version.

We continue to assume that we have installed lib and dev packages and continue as root.

 

PREPERATION:

In order to install the Tds_fdw extension, we first run the following script.

 

INSTALL EPEL:

The next step is to install the epel repo.

First, we download the rpm package as follows.

Note: When you run the following command, it will download to the current directory. Before you download Repo, you can use “pwd” command to see your directory.

 

Then run the downloaded rpm package as follows.

 

 

INSTALL FREEDTS and FREEDTS-DEVEL:

After installing Epel repos, we install Freetds and Freetds-devel libraries.

 

Download tds_fdw extension after the above processes are completed.

If wget is not installed, you can install it with the following command.

After downloading we need to open the zip file as follows.

If unzip is not installed, you can install it with the following command.

After opening the zip file, we go to the tds_fdw folder.

Run the following commands in sequence.

 

If you get a Makefile54 error, you can use the article named “Solution of Makefile54 Error On PostgreSQL“.

After the above operations are completed successfully, we install the previous library files as follows.

 

CREATE EXTENSION:

Then we can connect to Psql and create extension as below.

 

CREATE SERVER:

After the installation is complete, we will create a foreing data wrapper by using tds_fdw extension between MSSQL and PostgreSQL.

For this, we will first create a server for Foreign Data Wrapper on postgresql as follows.

“fdw_d1” is the MSSQL database name.

 

USER MAPPING:

After the server installation process is completed, you can use the following command to perform user mapping operations.

 

CREATE FOREIGN TABLE:

We have determined which user to connect to MSSQL.

Then, on PostgreSQL, we create the table of MSSQL that we want to access.

In the fdw_d1 database on MSSQL, when we select from the d1_1 table under the dbo scheme, 6 records return as a result.

 

Now, let’s take the data through PostgreSQL using the foreign data wrapper architecture and see if the data is the same.

 

Let’s add a record to the Ms_d1 table.

 

 

Let’s check through Postgresql.

 

As seen above, we can see the newly added records via postgresql foreign data wrapper.