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.
In order to install the Tds_fdw extension, we first run the following script.
Yum install gcc-y
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.
rpm -ivh epel-release-7-11.noarch.rpm
INSTALL FREEDTS and FREEDTS-DEVEL:
After installing Epel repos, we install Freetds and Freetds-devel libraries.
yum install freetds -y
yum install freetds-devel -y
Download tds_fdw extension after the above processes are completed.
If wget is not installed, you can install it with the following command.
yum install wget
After downloading we need to open the zip file as follows.
If unzip is not installed, you can install it with the following command.
yum install unzip
After opening the zip file, we go to the tds_fdw folder.
Run the following commands in sequence.
PATH=/usr/pgsql-10/bin:$PATH make USE_PGXS=1
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.
PATH=/usr/pgsql-10/bin:$PATH make USE_PGXS=1 install
Then we can connect to Psql and create extension as below.
create extension tds_fdw;
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.
CREATE SERVER ms FOREIGN DATA WRAPPER tds_fdw OPTIONS(servername 'MSSQL_IP ',database 'fdw_d1',port '1433');
“fdw_d1” is the MSSQL database name.
After the server installation process is completed, you can use the following command to perform user mapping operations.
CREATE USER MAPPING FOR postgres SERVER ms OPTIONS (username 'MSSQL_UserName', password 'MSSQL_UserPassword');
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.
CREATE FOREIGN TABLE ms_d1(id int ,ad nchar(10)) SERVER ms OPTIONS (query 'SELECT id,ad FROM dbo.d1_1');
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.
select * from ms_d1;
Let’s add a record to the Ms_d1 table.
INSERT INTO [dbo].[d1_1]
Let’s check through Postgresql.
As seen above, we can see the newly added records via postgresql foreign data wrapper.