Access the MSSQL database from PostgreSQL using TDS Foreign Data Wrapper (TDS_FDW)

The Tabular Data Source (TDS) Foreign Data Wrapper (TDS_FDW) is a Postgres extension that allows you to access data that resides on an MSSQL database from Postgres Server. TDS_FDW depends on a database access library called FreeTDS. FreeTDS is a set of libraries that enables Linux to make direct calls to SQL Server. FreeTDS is an implementation of the Tabular Data Source (TDS) protocol that SQL Server uses. We need to install the EPEL (Extra Packages for Enterprise Linux) repository. EPEL is a free and community-based repository of high-quality Linux packages. We need to install the EPEL repository because the FreeTDS library is part of EPEL and TDS_FDW depends on FreeTDS.

In the previous 2 blog post we saw 1.: Steps to Setup an Oracle Foreign Data Wrapper (oracle_fdw) in PostgreSQL Click here to read more. 2.: Steps to Setup an MYSQL Foreign Data Wrapper (mysql_fdw) in PostgreSQL Click here to read more. To access tables present in a remote MSSQL database server from a local PostgreSQL database, we can use TDS_FDW Foreign Data Wrapper.

For this demonstration, we’ll use the PostgreSQL DB local_db and MSSQL DB [bidding system]. We will access table users that exist in MSSQL [bidding system] from PostgreSQL local_db.

Below are the high-level steps we will follow to set up tds_fdw.
1. Verify MSSQL Users access
2. Setup PostgreSQL Users
3. Setup EPEL repository for FreeTDS
4. Install-Package freetds freetds-devel
5. Download the source code and install tds_fdw
6. Install the postgresql13-devel package
7. Install missing devtoolset* llvm-toolset-7* llvm5* rpm
8. Configure the TDS_FDW package
9. Create the TDS_FDW extension
10. Create the Foreign Server
11. Create User Mapping
12. Grant the Local User Access to the Foreign Server
13. Create a foreign Table

Below are setup details and the same will be used in this demonstration.

Sr. No.	DB Name	Username	Hostname	Version	Port
1	MSSQL-bidding system	test_readonly	MSSQLDBCS01	Standard Edition 2017	1433
2	PostgreSQL-local_db	app_user	test-machine01	13.0	5432

Step 1. Verify MSSQL Users access: We already have a running default instance of MSSQL Server on Server: MSSQLDBCS01 on Default Port: 1433. Also User: test_readonly has already been created and has access to DB: [bidding db] on Table: users. You can verify this using utility sqlcmd.

C:\Users\jkhan>
C:\Users\jkhan>sqlcmd -S MSSQLDBCS01,1433 -U test_readonly -P ucas123
1>
2> use [bidding db]
3> select count(*) from users
4> go
Changed database context to 'bidding db'.
 
-----------
       1114
 
(1 rows affected)
1>
2>quit
C:\Users\jkhan>

Step 2. Setup PostgreSQL Users: Login to the postgresql cluster using psql utility and create DB: local_db and role: app_user.

[root@test-machine01 ~]# su - postgres
Last login: Mon Jun 27 08:55:09 +03 2022 on pts/0
-bash-4.2$
-bash-4.2$ psql
psql (9.2.24, server 13.7)
WARNING: psql version 9.2, server version 13.0.
         Some psql features might not work.
Type "help" for help.

postgres=#
postgres=# create database local_db;
CREATE DATABASE
postgres=#
postgres=#
postgres=# \l+
                                                                    List of databases
   Name    |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileges   |  Size   | Tablespace |                Description
-----------+----------+----------+-------------+-------------+-----------------------+---------+------------+--------------------------------------------
 local_db  | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |                       | 8153 kB | pg_default |
 postgres  | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |                       | 16 MB   | pg_default | default administrative connection database
 template0 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +| 8009 kB | pg_default | unmodifiable empty database
           |          |          |             |             | postgres=CTc/postgres |         |            |
 template1 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | postgres=CTc/postgres+| 8153 kB | pg_default | default template for new databases
           |          |          |             |             | =c/postgres           |         |            |
(4 rows)

postgres=#
postgres=#
postgres=# create role app_user with LOGIN PASSWORD 'Root@1234';
CREATE ROLE
postgres=#
postgres=#\q
-bash-4.2$
-bash-4.2$ psql -htest-machine01 -Uapp_user -W local_db
Password for user app_user:
psql (9.2.24, server 13.7)
WARNING: psql version 9.2, server version 13.0.
         Some psql features might not work.
Type "help" for help.

local_db=>
local_db=>

Step 3. Setup EPEL repository for FreeTDS: Go to yum directory and add EPEL repository using the below mention code. Once the repository is added we can use a command like yum search to confirm package freetds is available.

[root@test-machine01 ~]#
[root@test-machine01 ~]# cd /etc/yum.repos.d/
[root@test-machine01 yum.repos.d]#
[root@test-machine01 yum.repos.d]# vi epel-yum-ol7.repo
[ol7_epel]
name=Oracle Linux $releasever EPEL ($basearch)
baseurl=http://yum.oracle.com/repo/OracleLinux/OL7/developer_EPEL/$basearch/
gpgkey=file:///etc/pki/rpm-gpg/RPM-GPG-KEY-oracle
gpgcheck=1
enabled=1
:wq!
[root@test-machine01 yum.repos.d]#
[root@test-machine01 yum.repos.d]# yum repolist
Loaded plugins: langpacks, ulninfo
ol7_epel                                                                                                                                                               | 3.6 kB  00:00:00
(1/3): ol7_epel/x86_64/group_gz                                                                                                                                        |  88 kB  00:00:00
(2/3): ol7_epel/x86_64/updateinfo                                                                                                                                      | 701 kB  00:00:01
(3/3): ol7_epel/x86_64/primary_db                                                                                                                                      |  15 MB  00:00:02
ol7_UEKR5/x86_64                                                    Latest Unbreakable Enterprise Kernel Release 5 for Oracle Linux 7Server (x86_64)                                       744
ol7_UEKR6/x86_64                                                    Latest Unbreakable Enterprise Kernel Release 6 for Oracle Linux 7Server (x86_64)                                       730
ol7_epel/x86_64                                                     Oracle Linux 7Server EPEL (x86_64)                                                                                  43,190
ol7_latest/x86_64                                                   Oracle Linux 7Server Latest (x86_64)                                                                                24,495
repolist: 79,324
[root@test-machine01 yum.repos.d]# 

[root@test-machine01 yum.repos.d]# yum search freetds
Loaded plugins: langpacks, ulninfo
==================================================================================== N/S matched: freetds ====================================================================================
freetds-devel.x86_64 : Header files and development libraries for freetds
freetds-doc.noarch : Development documentation for freetds
freetds-libs.x86_64 : Libraries for freetds
freetds.x86_64 : Implementation of the TDS (Tabular DataStream) protocol

  Name and summary matches only, use "search all" for everything.
[root@test-machine01 yum.repos.d]#
[root@test-machine01 yum.repos.d]#

Step 4. Install-Package freetds freetds-devel: Install freetds package using yum install command.

[root@test-machine01 yum.repos.d]#
[root@test-machine01 yum.repos.d]# yum install -y freetds freetds-devel
Loaded plugins: langpacks, ulninfo
Resolving Dependencies
--> Running transaction check
---> Package freetds.x86_64 0:1.3.3-1.el7 will be installed
--> Processing Dependency: freetds-libs(x86-64) = 1.3.3-1.el7 for package: freetds-1.3.3-1.el7.x86_64
--> Processing Dependency: libsybdb.so.5()(64bit) for package: freetds-1.3.3-1.el7.x86_64
---> Package freetds-devel.x86_64 0:1.3.3-1.el7 will be installed
--> Running transaction check
---> Package freetds-libs.x86_64 0:1.3.3-1.el7 will be installed
--> Finished Dependency Resolution

Dependencies Resolved

==============================================================================================================================================================================================
 Package                                          Arch                                      Version                                         Repository                                   Size
==============================================================================================================================================================================================
Installing:
 freetds                                          x86_64                                    1.3.3-1.el7                                     ol7_epel                                    290 k
 freetds-devel                                    x86_64                                    1.3.3-1.el7                                     ol7_epel                                     51 k
Installing for dependencies:
 freetds-libs                                     x86_64                                    1.3.3-1.el7                                     ol7_epel                                    373 k

Transaction Summary
==============================================================================================================================================================================================
Install  2 Packages (+1 Dependent package)

Total download size: 714 k
Installed size: 2.3 M
Downloading packages:
(1/3): freetds-devel-1.3.3-1.el7.x86_64.rpm                                                                                                                            |  51 kB  00:00:01
(2/3): freetds-1.3.3-1.el7.x86_64.rpm                                                                                                                                  | 290 kB  00:00:02
(3/3): freetds-libs-1.3.3-1.el7.x86_64.rpm                                                                                                                             | 373 kB  00:00:05
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Total                                                                                                                                                         100 kB/s | 714 kB  00:00:07
Running transaction check
Running transaction test
Transaction test succeeded
Running transaction
  Installing : freetds-1.3.3-1.el7.x86_64                                                                                                                                                 1/3
  Installing : freetds-libs-1.3.3-1.el7.x86_64                                                                                                                                            2/3
  Installing : freetds-devel-1.3.3-1.el7.x86_64                                                                                                                                           3/3
  Verifying  : freetds-libs-1.3.3-1.el7.x86_64                                                                                                                                            1/3
  Verifying  : freetds-1.3.3-1.el7.x86_64                                                                                                                                                 2/3
  Verifying  : freetds-devel-1.3.3-1.el7.x86_64                                                                                                                                           3/3

Installed:
  freetds.x86_64 0:1.3.3-1.el7                                                               freetds-devel.x86_64 0:1.3.3-1.el7

Dependency Installed:
  freetds-libs.x86_64 0:1.3.3-1.el7

Complete!
[root@test-machine01 yum.repos.d]#

Step 5. Download the source code and install tds_fdw: Download tds_fdw source code using wget command. wget command will download the source code zip file directly inside the server. Unzip the downloaded zip file. We are using the make and make install commands to install tds_fdw. The command make is used to build the software. This runs a series of tasks defined in a Makefile to build the finished program from its source code. The make install command will copy the built program and its libraries and documentation to the correct locations. As you notice while executing make command we received error “Makefile:54: /usr/pgsql-13/lib/pgxs/src/makefiles/pgxs.mk: No such file or directory“. This error is because of the missing packages postgresql13-devel.

[root@test-machine01 ~]# 
[root@test-machine01 ~]# cd /tmp
[root@test-machine01 tmp]#
[root@test-machine01 tmp]# wget https://github.com/GeoffMontee/tds_fdw/archive/master.zip
--2022-06-26 16:15:26--  https://github.com/GeoffMontee/tds_fdw/archive/master.zip
Resolving github.com (github.com)... 140.82.121.3
Connecting to github.com (github.com)|140.82.121.3|:443... connected.
HTTP request sent, awaiting response... 301 Moved Permanently
Location: https://github.com/tds-fdw/tds_fdw/archive/master.zip [following]
--2022-06-26 16:15:27--  https://github.com/tds-fdw/tds_fdw/archive/master.zip
Reusing existing connection to github.com:443.
HTTP request sent, awaiting response... 302 Found
Location: https://codeload.github.com/tds-fdw/tds_fdw/zip/refs/heads/master [following]
--2022-06-26 16:15:27--  https://codeload.github.com/tds-fdw/tds_fdw/zip/refs/heads/master
Resolving codeload.github.com (codeload.github.com)... 140.82.121.9
Connecting to codeload.github.com (codeload.github.com)|140.82.121.9|:443... connected.
HTTP request sent, awaiting response... 200 OK
Length: unspecified [application/zip]
Saving to: âmaster.zipâ

    [  <=>                                                                                                                                                ] 128,490      362KB/s   in 0.3s

2022-06-26 16:15:29 (362 KB/s) - âmaster.zipâ saved [128490]

[root@test-machine01 tmp]#

[root@test-machine01 tmp]#
[root@test-machine01 tmp]# ls -l
total 54864
-rw-r--r--. 1 root root    17515 Jun 22 09:19 index.html?id=511389
-rw-r--r--. 1 root root   128490 Jun 26 16:15 master.zip
-rw-------. 1 root root        0 Jun 22 09:32 tmp4xVhW_
drwx------. 2 root root        6 Jun 21 15:51 vmware-root_862-2731217798
[root@test-machine01 tmp]#

[root@test-machine01 tmp]#
[root@test-machine01 tmp]# unzip master.zip
Archive:  master.zip
1611a2805f85d84f463ae50c4e0765cb9bed72dc
   creating: tds_fdw-master/
  inflating: tds_fdw-master/.gitattributes
   creating: tds_fdw-master/.github/
  inflating: tds_fdw-master/.github/ISSUE_TEMPLATE.md
  inflating: tds_fdw-master/.gitignore
  inflating: tds_fdw-master/ForeignSchemaImporting.md
  inflating: tds_fdw-master/ForeignServerCreation.md
  inflating: tds_fdw-master/ForeignTableCreation.md
[root@test-machine01 tmp]#
[root@test-machine01 tmp]#
[root@test-machine01 tmp]# ls -l
total 54868
-rw-r--r--. 1 root root    17515 Jun 22 09:19 index.html?id=511389
-rw-r--r--. 1 root root   128490 Jun 26 16:15 master.zip
drwxr-xr-x. 8 root root     4096 Dec 14  2021 tds_fdw-master
[root@test-machine01 tmp]#

[root@test-machine01 tmp]#
[root@test-machine01 tmp]# cd tds_fdw-master
[root@test-machine01 tds_fdw-master]#
[root@test-machine01 tds_fdw-master]# PATH=/usr/pgsql-13/bin:$PATH
[root@test-machine01 tds_fdw-master]#
[root@test-machine01 tds_fdw-master]# which psql
/usr/pgsql-13/bin/psql
[root@test-machine01 tds_fdw-master]#
[root@test-machine01 tds_fdw-master]# whereis psql
psql: /usr/bin/psql /usr/pgsql-13/bin/psql /usr/share/man/man1/psql.1 /usr/share/man/man1/psql.1.gz
[root@test-machine01 tds_fdw-master]#
[root@test-machine01 tds_fdw-master]# make USE_PGXS=1
Makefile:54: /usr/pgsql-13/lib/pgxs/src/makefiles/pgxs.mk: No such file or directory
make: *** No rule to make target `/usr/pgsql-13/lib/pgxs/src/makefiles/pgxs.mk'.  Stop.
[root@test-machine01 tds_fdw-master]#
[root@test-machine01 tds_fdw-master]#

Step 6. Install postgresql13-devel package : Let try to install the package postgresql13-devel. But this package depends on devtoolset* llvm-toolset-7* llvm5* rpms. So we need to install first missing RPMs and then we will install package postgresql13-devel.

[root@test-machine01 lib]#
[root@test-machine01 lib]# yum install postgresql13-devel
Loaded plugins: langpacks, ulninfo
Resolving Dependencies
--> Running transaction check
---> Package llvm5.0.x86_64 0:5.0.1-7.el7 will be installed
---> Package llvm5.0-devel.x86_64 0:5.0.1-7.el7 will be installed
--> Processing Dependency: libedit-devel for package: llvm5.0-devel-5.0.1-7.el7.x86_64
---> Package llvm5.0-libs.x86_64 0:5.0.1-7.el7 will be installed
---> Package postgresql13-contrib.x86_64 0:13.4-1PGDG.rhel7 will be updated
---> Package postgresql13-contrib.x86_64 0:13.7-1PGDG.rhel7 will be an update
---> Package postgresql13-devel.x86_64 0:13.7-1PGDG.rhel7 will be installed
--> Processing Dependency: llvm-toolset-7-clang >= 4.0.1 for package: postgresql13-devel-13.7-1PGDG.rhel7.x86_64
---> Package postgresql13-server.x86_64 0:13.4-1PGDG.rhel7 will be updated
---> Package postgresql13-server.x86_64 0:13.7-1PGDG.rhel7 will be an update
--> Finished Dependency Resolution
Error: Package: llvm5.0-devel-5.0.1-7.el7.x86_64 (ol7_epel)
           Requires: libedit-devel
Error: Package: postgresql13-devel-13.7-1PGDG.rhel7.x86_64 (pgdg13)
           Requires: llvm-toolset-7-clang >= 4.0.1
 You could try using --skip-broken to work around the problem
 You could try running: rpm -Va --nofiles --nodigest
[root@test-machine01 lib]#

Step 7. Install missing devtoolset* llvm-toolset-7* llvm5* rpm: Use OS command wget –no-check-certificate <rpm url> to download required RPMs directly in PosrgreSQL Server. Download all listed rpm and install in the same sequence as shown below. Once all listed RPMs are installed. Try to install a package: postgresql13-devel.

[root@test-machine01 ~]#
[root@test-machine01 ~]# cd /tmp
[root@test-machine01 tmp]#
[root@test-machine01 tmp]# wget  --no-check-certificate https://download-ib01.fedoraproject.org/pub/epel/7/x86_64/Packages/l/llvm5.0-libs-5.0.1-7.el7.x86_64.rpm
[root@test-machine01 tmp]# wget  --no-check-certificate https://download-ib01.fedoraproject.org/pub/epel/7/x86_64/Packages/l/llvm5.0-devel-5.0.1-7.el7.x86_64.rpm
[root@test-machine01 tmp]# wget  --no-check-certificate https://download-ib01.fedoraproject.org/pub/epel/7/x86_64/Packages/l/llvm5.0-5.0.1-7.el7.x86_64.rpm

[root@test-machine01 tmp]#  ls -ltr llvm5*
-rw-r--r--. 1 root root  2755155 Mar 30  2018 llvm5.0-5.0.1-7.el7.x86_64.rpm
-rw-r--r--. 1 root root 13960979 Mar 30  2018 llvm5.0-libs-5.0.1-7.el7.x86_64.rpm
-rw-r--r--. 1 root root  2424903 Mar 30  2018 llvm5.0-devel-5.0.1-7.el7.x86_64.rpm
[root@test-machine01 tmp]#

[root@test-machine01 tmp]# wget  --no-check-certificate http://mirror.centos.org/centos/7/os/x86_64/Packages/scl-utils-build-20130529-19.el7.x86_64.rpm
[root@test-machine01 tmp]# wget --no-check-certificate  http://mirror.centos.org/centos/7/sclo/x86_64/rh/Packages/l/llvm-toolset-7-runtime-5.0.1-4.el7.x86_64.rpm
[root@test-machine01 tmp]# wget --no-check-certificate https://buildlogs.centos.org/centos/7/sclo/x86_64/rh/Packages/l/llvm-toolset-7-clang-5.0.1-4.el7.x86_64.rpm
[root@test-machine01 tmp]# wget --no-check-certificate http://buildlogs.cdn.centos.org/centos/7/sclo/x86_64/rh/Packages/l/llvm-toolset-7-clang-libs-5.0.1-4.el7.x86_64.rpm
[root@test-machine01 tmp]# wget --no-check-certificate http://mirror.centos.org/centos/7/sclo/x86_64/rh/Packages/l/llvm-toolset-7-build-5.0.1-4.el7.x86_64.rpm
[root@test-machine01 tmp]# wget  --no-check-certificate http://mirror.centos.org/centos/7/sclo/x86_64/rh/Packages/l/llvm-toolset-7-compiler-rt-5.0.1-2.el7.x86_64.rpm
[root@test-machine01 tmp]# wget --no-check-certificate http://mirror.centos.org/centos/7/sclo/x86_64/rh/Packages/l/llvm-toolset-7-libomp-5.0.1-2.el7.x86_64.rpm
[root@test-machine01 tmp]# wget --no-check-certificate http://mirror.centos.org/centos/7/sclo/x86_64/rh/Packages/l/llvm-toolset-7-llvm-libs-5.0.1-8.el7.x86_64.rpm

[root@test-machine01 tmp]# ls -l scl-utils-build*
-rw-r--r--. 1 root root 17944 Nov 12  2018 scl-utils-build-20130529-19.el7.x86_64.rpm
[root@test-machine01 tmp]#

[root@test-machine01 tmp]# ls -lr llvm-toolset*
-rw-r--r--. 1 root root  1198572 Mar 12  2020 llvm-toolset-7-runtime-5.0.1-4.el7.x86_64.rpm
-rw-r--r--. 1 root root 13965916 Mar 12  2020 llvm-toolset-7-llvm-libs-5.0.1-8.el7.x86_64.rpm
-rw-r--r--. 1 root root   252240 Mar 12  2020 llvm-toolset-7-libomp-5.0.1-2.el7.x86_64.rpm
-rw-r--r--. 1 root root  1788824 Mar 12  2020 llvm-toolset-7-compiler-rt-5.0.1-2.el7.x86_64.rpm
-rw-r--r--. 1 root root 13568616 Jul 10  2018 llvm-toolset-7-clang-libs-5.0.1-4.el7.x86_64.rpm
-rw-r--r--. 1 root root   557892 Jul 10  2018 llvm-toolset-7-clang-5.0.1-4.el7.x86_64.rpm
-rw-r--r--. 1 root root     4472 Mar 12  2020 llvm-toolset-7-build-5.0.1-4.el7.x86_64.rpm
[root@test-machine01 tmp]#

[root@test-machine01 tmp]# wget --no-check-certificate https://buildlogs.centos.org/centos/7/sclo/x86_64/rh/Packages/d/devtoolset-7-binutils-2.28-7.el7.x86_64.rpm
[root@test-machine01 tmp]# wget --no-check-certificate https://buildlogs.centos.org/centos/7/sclo/x86_64/rh/Packages/d/devtoolset-7-gcc-7.2.1-1.el7.x86_64.rpm
[root@test-machine01 tmp]# wget --no-check-certificate http://mirror.centos.org/centos/7/sclo/x86_64/rh/Packages/d/devtoolset-7-gcc-c++-7.2.1-1.el7.x86_64.rpm
[root@test-machine01 tmp]# wget --no-check-certificate http://mirror.centos.org/centos/7/sclo/x86_64/rh/Packages/d/devtoolset-7-libstdc++-devel-7.2.1-1.el7.x86_64.rpm
[root@test-machine01 tmp]# wget --no-check-certificate https://buildlogs.centos.org/centos/7/sclo/x86_64/rh/Packages/d/devtoolset-7-runtime-7.0-8.el7.x86_64.rpm

[root@test-machine01 tmp]#
[root@test-machine01 tmp]# ls -ltr devtoolset*
-rw-r--r--. 1 root root  5505848 Jul 30  2017 devtoolset-7-binutils-2.28-7.el7.x86_64.rpm
-rw-r--r--. 1 root root    19836 Oct 24  2017 devtoolset-7-runtime-7.0-8.el7.x86_64.rpm
-rw-r--r--. 1 root root 30450984 Oct 24  2017 devtoolset-7-gcc-7.2.1-1.el7.x86_64.rpm
-rw-r--r--. 1 root root  2704244 Mar 23  2020 devtoolset-7-libstdc++-devel-7.2.1-1.el7.x86_64.rpm
-rw-r--r--. 1 root root 11690356 Mar 23  2020 devtoolset-7-gcc-c++-7.2.1-1.el7.x86_64.rpm
[root@test-machine01 tmp]#
[root@test-machine01 tmp]#


[root@test-machine01 tmp]# wget --no-check-certificate http://mirror.centos.org/centos/7/os/x86_64/Packages/libedit-devel-3.0-12.20121213cvs.el7.x86_64.rpm
[root@test-machine01 tmp]# wget --no-check-certificate http://mirror.centos.org/centos/7/os/x86_64/Packages/ncurses-devel-5.9-14.20130511.el7_4.x86_64.rpm

[root@test-machine01 tmp]# ls -ltr libedit-devel*
-rw-r--r--. 1 root root 33256 Jul  4  2014 libedit-devel-3.0-12.20121213cvs.el7.x86_64.rpm
[root@test-machine01 tmp]#
[root@test-machine01 tmp]# ls -ltr ncurses-devel*
-rw-r--r--. 1 root root 729508 Sep  7  2017 ncurses-devel-5.9-14.20130511.el7_4.x86_64.rpm
[root@test-machine01 tmp]#
[root@test-machine01 tmp]#

[root@test-machine01 tmp]#
[root@test-machine01 tmp]# yum localinstall scl-utils-build*
Loaded plugins: langpacks, ulninfo
Resolving Dependencies
--> Running transaction check
---> Package scl-utils-build.x86_64 0:20130529-19.el7 will be installed
--> Processing Dependency: iso-codes for package: scl-utils-build-20130529-19.el7.x86_64
--> Processing Dependency: redhat-rpm-config for package: scl-utils-build-20130529-19.el7.x86_64
--> Running transaction check
---> Package iso-codes.noarch 0:3.46-2.el7 will be installed
--> Processing Dependency: xml-common for package: iso-codes-3.46-2.el7.noarch
---> Package redhat-rpm-config.noarch 0:9.1.0-88.0.1.el7 will be installed
--> Processing Dependency: dwz >= 0.4 for package: redhat-rpm-config-9.1.0-88.0.1.el7.noarch
--> Processing Dependency: perl-srpm-macros for package: redhat-rpm-config-9.1.0-88.0.1.el7.noarch
--> Processing Dependency: python-srpm-macros for package: redhat-rpm-config-9.1.0-88.0.1.el7.noarch
--> Running transaction check
---> Package dwz.x86_64 0:0.11-3.el7 will be installed
---> Package perl-srpm-macros.noarch 0:1-8.el7 will be installed
---> Package python-srpm-macros.noarch 0:3-34.el7 will be installed
---> Package xml-common.noarch 0:0.6.3-39.el7 will be installed
--> Finished Dependency Resolution

Dependencies Resolved

==============================================================================================================================================================================================
 Package                                     Arch                            Version                                   Repository                                                        Size
==============================================================================================================================================================================================
Installing:
 scl-utils-build                             x86_64                          20130529-19.el7                           /scl-utils-build-20130529-19.el7.x86_64                           18 k
Installing for dependencies:
 dwz                                         x86_64                          0.11-3.el7                                ol7_latest                                                        98 k
 iso-codes                                   noarch                          3.46-2.el7                                ol7_latest                                                       2.7 M
 perl-srpm-macros                            noarch                          1-8.el7                                   ol7_latest                                                       4.1 k
 python-srpm-macros                          noarch                          3-34.el7                                  ol7_latest                                                       8.2 k
 redhat-rpm-config                           noarch                          9.1.0-88.0.1.el7                          ol7_latest                                                        81 k
 xml-common                                  noarch                          0.6.3-39.el7                              ol7_latest                                                        26 k

Transaction Summary
==============================================================================================================================================================================================
Install  1 Package (+6 Dependent packages)

Total size: 2.9 M
Total download size: 2.9 M
Installed size: 14 M
Is this ok [y/d/N]: y
Downloading packages:
(1/6): dwz-0.11-3.el7.x86_64.rpm                                                                                                                                       |  98 kB  00:00:01
(2/6): perl-srpm-macros-1-8.el7.noarch.rpm                                                                                                                             | 4.1 kB  00:00:00
(3/6): python-srpm-macros-3-34.el7.noarch.rpm                                                                                                                          | 8.2 kB  00:00:00
(4/6): redhat-rpm-config-9.1.0-88.0.1.el7.noarch.rpm                                                                                                                   |  81 kB  00:00:00
(5/6): xml-common-0.6.3-39.el7.noarch.rpm                                                                                                                              |  26 kB  00:00:00
(6/6): iso-codes-3.46-2.el7.noarch.rpm                                                                                                                                 | 2.7 MB  00:00:02
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Total                                                                                                                                                         1.0 MB/s | 2.9 MB  00:00:02
Running transaction check
Running transaction test
Transaction test succeeded
Running transaction
  Installing : python-srpm-macros-3-34.el7.noarch                                                                                                                                         1/7
  Installing : dwz-0.11-3.el7.x86_64                                                                                                                                                      2/7
  Installing : perl-srpm-macros-1-8.el7.noarch                                                                                                                                            3/7
  Installing : redhat-rpm-config-9.1.0-88.0.1.el7.noarch                                                                                                                                  4/7
  Installing : xml-common-0.6.3-39.el7.noarch                                                                                                                                             5/7
  Installing : iso-codes-3.46-2.el7.noarch                                                                                                                                                6/7
  Installing : scl-utils-build-20130529-19.el7.x86_64                                                                                                                                     7/7
  Verifying  : xml-common-0.6.3-39.el7.noarch                                                                                                                                             1/7
  Verifying  : perl-srpm-macros-1-8.el7.noarch                                                                                                                                            2/7
  Verifying  : dwz-0.11-3.el7.x86_64                                                                                                                                                      3/7
  Verifying  : python-srpm-macros-3-34.el7.noarch                                                                                                                                         4/7
  Verifying  : iso-codes-3.46-2.el7.noarch                                                                                                                                                5/7
  Verifying  : redhat-rpm-config-9.1.0-88.0.1.el7.noarch                                                                                                                                  6/7
  Verifying  : scl-utils-build-20130529-19.el7.x86_64                                                                                                                                     7/7

Installed:
  scl-utils-build.x86_64 0:20130529-19.el7

Dependency Installed:
  dwz.x86_64 0:0.11-3.el7            iso-codes.noarch 0:3.46-2.el7   perl-srpm-macros.noarch 0:1-8.el7   python-srpm-macros.noarch 0:3-34.el7   redhat-rpm-config.noarch 0:9.1.0-88.0.1.el7
  xml-common.noarch 0:0.6.3-39.el7

Complete!
[root@test-machine01 tmp]#

[root@test-machine01 tmp]#
[root@test-machine01 tmp]#  yum localinstall devtoolset-7* llvm-toolset-7*
Loaded plugins: langpacks, ulninfo

Marking llvm-toolset-7-clang-5.0.1-4.el7.x86_64.rpm to be installed
Examining llvm-toolset-7-clang-libs-5.0.1-4.el7.x86_64.rpm: llvm-toolset-7-clang-libs-5.0.1-4.el7.x86_64
Marking llvm-toolset-7-clang-libs-5.0.1-4.el7.x86_64.rpm to be installed
Examining llvm-toolset-7-compiler-rt-5.0.1-2.el7.x86_64.rpm: llvm-toolset-7-compiler-rt-5.0.1-2.el7.x86_64
Marking llvm-toolset-7-compiler-rt-5.0.1-2.el7.x86_64.rpm to be installed
Examining llvm-toolset-7-libomp-5.0.1-2.el7.x86_64.rpm: llvm-toolset-7-libomp-5.0.1-2.el7.x86_64
Marking llvm-toolset-7-libomp-5.0.1-2.el7.x86_64.rpm to be installed
Examining llvm-toolset-7-llvm-libs-5.0.1-8.el7.x86_64.rpm: llvm-toolset-7-llvm-libs-5.0.1-8.el7.x86_64
Marking llvm-toolset-7-llvm-libs-5.0.1-8.el7.x86_64.rpm to be installed
Examining llvm-toolset-7-runtime-5.0.1-4.el7.x86_64.rpm: llvm-toolset-7-runtime-5.0.1-4.el7.x86_64
Marking llvm-toolset-7-runtime-5.0.1-4.el7.x86_64.rpm to be installed
Resolving Dependencies
--> Running transaction check

--> Processing Dependency: libqpol.so.1()(64bit) for package: policycoreutils-python-2.5-34.0.1.el7.x86_64
--> Running transaction check
---> Package audit-libs-python.x86_64 0:2.8.5-4.el7 will be installed
---> Package checkpolicy.x86_64 0:2.5-8.el7 will be installed
---> Package libcgroup.x86_64 0:0.41-21.el7 will be installed
---> Package libsemanage-python.x86_64 0:2.5-14.el7 will be installed
---> Package python-IPy.noarch 0:0.75-6.el7 will be installed
---> Package setools-libs.x86_64 0:3.3.8-4.el7 will be installed
--> Finished Dependency Resolution

Dependencies Resolved

==============================================================================================================================================================================================
 Package                                          Arch                       Version                               Repository                                                            Size
==============================================================================================================================================================================================
Installing:
 devtoolset-7-binutils                            x86_64                     2.28-7.el7                            /devtoolset-7-binutils-2.28-7.el7.x86_64                              21 M
 devtoolset-7-gcc                                 x86_64                     7.2.1-1.el7                           /devtoolset-7-gcc-7.2.1-1.el7.x86_64                                  67 M
 devtoolset-7-gcc-c++                             x86_64                     7.2.1-1.el7                           /devtoolset-7-gcc-c++-7.2.1-1.el7.x86_64                              27 M
 devtoolset-7-libstdc++-devel                     x86_64                     7.2.1-1.el7                           /devtoolset-7-libstdc++-devel-7.2.1-1.el7.x86_64                      17 M
 devtoolset-7-runtime                             x86_64                     7.0-8.el7                             /devtoolset-7-runtime-7.0-8.el7.x86_64                               3.0 k
 llvm-toolset-7-build                             x86_64                     5.0.1-4.el7                           /llvm-toolset-7-build-5.0.1-4.el7.x86_64                              44
 llvm-toolset-7-clang                             x86_64                     5.0.1-4.el7                           /llvm-toolset-7-clang-5.0.1-4.el7.x86_64                             6.6 M
 llvm-toolset-7-clang-libs                        x86_64                     5.0.1-4.el7                           /llvm-toolset-7-clang-libs-5.0.1-4.el7.x86_64                         49 M
 llvm-toolset-7-compiler-rt                       x86_64                     5.0.1-2.el7                           /llvm-toolset-7-compiler-rt-5.0.1-2.el7.x86_64                        18 M
 llvm-toolset-7-libomp                            x86_64                     5.0.1-2.el7                           /llvm-toolset-7-libomp-5.0.1-2.el7.x86_64                            706 k
 llvm-toolset-7-llvm-libs                         x86_64                     5.0.1-8.el7                           /llvm-toolset-7-llvm-libs-5.0.1-8.el7.x86_64                          49 M
 llvm-toolset-7-runtime                           x86_64                     5.0.1-4.el7                           /llvm-toolset-7-runtime-5.0.1-4.el7.x86_64                           611
Installing for dependencies:
 audit-libs-python                                x86_64                     2.8.5-4.el7                           ol7_latest                                                            76 k
 checkpolicy                                      x86_64                     2.5-8.el7                             ol7_latest                                                           294 k
 libcgroup                                        x86_64                     0.41-21.el7                           ol7_latest                                                            66 k
 libsemanage-python                               x86_64                     2.5-14.el7                            ol7_latest                                                           112 k
 policycoreutils-python                           x86_64                     2.5-34.0.1.el7                        ol7_latest                                                           457 k
 python-IPy                                       noarch                     0.75-6.el7                            ol7_latest                                                            32 k
 setools-libs                                     x86_64                     3.3.8-4.el7                           ol7_latest                                                           620 k

Transaction Summary
==============================================================================================================================================================================================
Install  12 Packages (+7 Dependent packages)

Total size: 257 M
Total download size: 1.6 M
Installed size: 261 M
Is this ok [y/d/N]: y
Downloading packages:
(1/7): audit-libs-python-2.8.5-4.el7.x86_64.rpm                                                                                                                        |  76 kB  00:00:03
(2/7): libcgroup-0.41-21.el7.x86_64.rpm                                                                                                                                |  66 kB  00:00:00
(3/7): checkpolicy-2.5-8.el7.x86_64.rpm                                                                                                                                | 294 kB  00:00:03
(4/7): libsemanage-python-2.5-14.el7.x86_64.rpm                                                                                                                        | 112 kB  00:00:00
(5/7): python-IPy-0.75-6.el7.noarch.rpm                                                                                                                                |  32 kB  00:00:00
(6/7): policycoreutils-python-2.5-34.0.1.el7.x86_64.rpm                                                                                                                | 457 kB  00:00:00
(7/7): setools-libs-3.3.8-4.el7.x86_64.rpm                                                                                                                             | 620 kB  00:00:00
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Total                                                                                                                                                         303 kB/s | 1.6 MB  00:00:05
Running transaction check
Running transaction test
Transaction test succeeded
Running transaction
  Installing : llvm-toolset-7-runtime-5.0.1-4.el7.x86_64                                                                                                                                 1/19
  Installing : llvm-toolset-7-llvm-libs-5.0.1-8.el7.x86_64                                                                                                                               2/19
  Installing : llvm-toolset-7-compiler-rt-5.0.1-2.el7.x86_64                                                                                                                             3/19
  Installing : llvm-toolset-7-libomp-5.0.1-2.el7.x86_64                                                                                                                                  4/19
  Installing : setools-libs-3.3.8-4.el7.x86_64                                                                                                                                           5/19
  Installing : libcgroup-0.41-21.el7.x86_64                                                                                                                                              6/19
  Installing : audit-libs-python-2.8.5-4.el7.x86_64                                                                                                                                      7/19
  Installing : checkpolicy-2.5-8.el7.x86_64                                                                                                                                              8/19
  Installing : libsemanage-python-2.5-14.el7.x86_64                                                                                                                                      9/19
  Installing : python-IPy-0.75-6.el7.noarch                                                                                                                                             10/19
  Installing : policycoreutils-python-2.5-34.0.1.el7.x86_64                                                                                                                             11/19
  Installing : devtoolset-7-runtime-7.0-8.el7.x86_64                                                                                                                                    12/19
  Installing : devtoolset-7-libstdc++-devel-7.2.1-1.el7.x86_64                                                                                                                          13/19
  Installing : devtoolset-7-binutils-2.28-7.el7.x86_64                                                                                                                                  14/19
  Installing : llvm-toolset-7-build-5.0.1-4.el7.x86_64                                                                                                                                  15/19
  Installing : devtoolset-7-gcc-7.2.1-1.el7.x86_64                                                                                                                                      16/19
Installed:
  devtoolset-7-binutils.x86_64 0:2.28-7.el7       devtoolset-7-gcc.x86_64 0:7.2.1-1.el7      devtoolset-7-gcc-c++.x86_64 0:7.2.1-1.el7     devtoolset-7-libstdc++-devel.x86_64 0:7.2.1-1.el7
  devtoolset-7-runtime.x86_64 0:7.0-8.el7         llvm-toolset-7-build.x86_64 0:5.0.1-4.el7  llvm-toolset-7-clang.x86_64 0:5.0.1-4.el7     llvm-toolset-7-clang-libs.x86_64 0:5.0.1-4.el7
  llvm-toolset-7-compiler-rt.x86_64 0:5.0.1-2.el7 llvm-toolset-7-libomp.x86_64 0:5.0.1-2.el7 llvm-toolset-7-llvm-libs.x86_64 0:5.0.1-8.el7 llvm-toolset-7-runtime.x86_64 0:5.0.1-4.el7

Dependency Installed:
  audit-libs-python.x86_64 0:2.8.5-4.el7                  checkpolicy.x86_64 0:2.5-8.el7          libcgroup.x86_64 0:0.41-21.el7             libsemanage-python.x86_64 0:2.5-14.el7
  policycoreutils-python.x86_64 0:2.5-34.0.1.el7          python-IPy.noarch 0:0.75-6.el7          setools-libs.x86_64 0:3.3.8-4.el7

Complete!
[root@test-machine01 tmp]#

[root@test-machine01 tmp]#
[root@test-machine01 tmp]# rpm -ivh llvm5.0-libs-5.0.1-7.el7.x86_64.rpm
warning: llvm5.0-libs-5.0.1-7.el7.x86_64.rpm: Header V3 RSA/SHA256 Signature, key ID 352c64e5: NOKEY
Preparing...                          ################################# [100%]
Updating / installing...
   1:llvm5.0-libs-5.0.1-7.el7         ################################# [100%]
[root@test-machine01 tmp]# rpm -ivh ncurses-devel-5.9-14.20130511.el7_4.x86_64.rpm
warning: ncurses-devel-5.9-14.20130511.el7_4.x86_64.rpm: Header V3 RSA/SHA256 Signature, key ID f4a80eb5: NOKEY
Preparing...                          ################################# [100%]
Updating / installing...
   1:ncurses-devel-5.9-14.20130511.el7################################# [100%]
[root@test-machine01 tmp]# rpm -ivh libedit-devel-3.0-12.20121213cvs.el7.x86_64.rpm
warning: libedit-devel-3.0-12.20121213cvs.el7.x86_64.rpm: Header V3 RSA/SHA256 Signature, key ID f4a80eb5: NOKEY
Preparing...                          ################################# [100%]
Updating / installing...
   1:libedit-devel-3.0-12.20121213cvs.################################# [100%]
[root@test-machine01 tmp]# 
[root@test-machine01 tmp]# rpm -ivh llvm5.0-5.0.1-7.el7.x86_64.rpm
warning: llvm5.0-5.0.1-7.el7.x86_64.rpm: Header V3 RSA/SHA256 Signature, key ID 352c64e5: NOKEY
Preparing...                          ################################# [100%]
Updating / installing...
   1:llvm5.0-5.0.1-7.el7              ################################# [100%]
[root@test-machine01 tmp]#
[root@test-machine01 tmp]# rpm -ivh llvm5.0-devel-5.0.1-7.el7.x86_64.rpm
warning: llvm5.0-devel-5.0.1-7.el7.x86_64.rpm: Header V3 RSA/SHA256 Signature, key ID 352c64e5: NOKEY
Preparing...                          ################################# [100%]
Updating / installing...
   1:llvm5.0-devel-5.0.1-7.el7        ################################# [100%]
[root@test-machine01 tmp]#
[root@test-machine01 tmp]#

[root@test-machine01 tmp]#
[root@test-machine01 tmp]#  yum install postgresql13-devel
Loaded plugins: langpacks, ulninfo
Resolving Dependencies
--> Running transaction check
---> Package postgresql13-devel.x86_64 0:13.7-1PGDG.rhel7 will be installed
--> Processing Dependency: postgresql13-libs(x86-64) = 13.7-1PGDG.rhel7 for package: postgresql13-devel-13.7-1PGDG.rhel7.x86_64
--> Processing Dependency: postgresql13(x86-64) = 13.7-1PGDG.rhel7 for package: postgresql13-devel-13.7-1PGDG.rhel7.x86_64
--> Processing Dependency: libicu-devel for package: postgresql13-devel-13.7-1PGDG.rhel7.x86_64
--> Running transaction check
---> Package libicu-devel.x86_64 0:50.2-4.el7_7 will be installed
---> Package postgresql13.x86_64 0:13.4-1PGDG.rhel7 will be updated
--> Processing Dependency: postgresql13(x86-64) = 13.4-1PGDG.rhel7 for package: postgresql13-server-13.4-1PGDG.rhel7.x86_64
--> Processing Dependency: postgresql13(x86-64) = 13.4-1PGDG.rhel7 for package: postgresql13-contrib-13.4-1PGDG.rhel7.x86_64
---> Package postgresql13.x86_64 0:13.7-1PGDG.rhel7 will be an update
---> Package postgresql13-libs.x86_64 0:13.4-1PGDG.rhel7 will be updated
---> Package postgresql13-libs.x86_64 0:13.7-1PGDG.rhel7 will be an update
--> Running transaction check
---> Package postgresql13-contrib.x86_64 0:13.4-1PGDG.rhel7 will be updated
---> Package postgresql13-contrib.x86_64 0:13.7-1PGDG.rhel7 will be an update
---> Package postgresql13-server.x86_64 0:13.4-1PGDG.rhel7 will be updated
---> Package postgresql13-server.x86_64 0:13.7-1PGDG.rhel7 will be an update
--> Finished Dependency Resolution

Dependencies Resolved

==============================================================================================================================================================================================
 Package                                             Arch                                  Version                                            Repository                                 Size
==============================================================================================================================================================================================
Installing:
 postgresql13-devel                                  x86_64                                13.7-1PGDG.rhel7                                   pgdg13                                    2.4 M
Installing for dependencies:
 libicu-devel                                        x86_64                                50.2-4.el7_7                                       ol7_latest                                703 k
Updating for dependencies:
 postgresql13                                        x86_64                                13.7-1PGDG.rhel7                                   pgdg13                                    1.4 M
 postgresql13-contrib                                x86_64                                13.7-1PGDG.rhel7                                   pgdg13                                    613 k
 postgresql13-libs                                   x86_64                                13.7-1PGDG.rhel7                                   pgdg13                                    384 k
 postgresql13-server                                 x86_64                                13.7-1PGDG.rhel7                                   pgdg13                                    5.4 M

Transaction Summary
==============================================================================================================================================================================================
Install  1 Package  (+1 Dependent package)
Upgrade             ( 4 Dependent packages)

Total download size: 11 M
Is this ok [y/d/N]: y
Downloading packages:
Delta RPMs disabled because /usr/bin/applydeltarpm not installed.
(1/6): postgresql13-contrib-13.7-1PGDG.rhel7.x86_64.rpm                                                                                                                | 613 kB  00:00:02
(2/6): postgresql13-13.7-1PGDG.rhel7.x86_64.rpm                                                                                                                        | 1.4 MB  00:00:03
(3/6): postgresql13-libs-13.7-1PGDG.rhel7.x86_64.rpm                                                                                                                   | 384 kB  00:00:00
(4/6): postgresql13-devel-13.7-1PGDG.rhel7.x86_64.rpm                                                                                                                  | 2.4 MB  00:00:02
(5/6): libicu-devel-50.2-4.el7_7.x86_64.rpm                                                                                                                            | 703 kB  00:00:04
(6/6): postgresql13-server-13.7-1PGDG.rhel7.x86_64.rpm                                                                                                                 | 5.4 MB  00:00:04
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Total                                                                                                                                                         1.2 MB/s |  11 MB  00:00:08
Running transaction check
Running transaction test
Transaction test succeeded
Running transaction
Warning: RPMDB altered outside of yum.
  Updating   : postgresql13-libs-13.7-1PGDG.rhel7.x86_64                                                                                                                                 1/10
  Updating   : postgresql13-13.7-1PGDG.rhel7.x86_64                                                                                                                                      2/10
  Updating   : postgresql13-server-13.7-1PGDG.rhel7.x86_64                                                                                                                               3/10
  Installing : libicu-devel-50.2-4.el7_7.x86_64                                                                                                                                          4/10
  Installing : postgresql13-devel-13.7-1PGDG.rhel7.x86_64                                                                                                                                5/10
  Updating   : postgresql13-contrib-13.7-1PGDG.rhel7.x86_64                                                                                                                              6/10
  Cleanup    : postgresql13-contrib-13.4-1PGDG.rhel7.x86_64                                                                                                                              7/10
  Cleanup    : postgresql13-server-13.4-1PGDG.rhel7.x86_64                                                                                                                               8/10
  Cleanup    : postgresql13-13.4-1PGDG.rhel7.x86_64                                                                                                                                      9/10
  Cleanup    : postgresql13-libs-13.4-1PGDG.rhel7.x86_64                                                                                                                                10/10
  Verifying  : postgresql13-server-13.7-1PGDG.rhel7.x86_64                                                                                                                               1/10
  Verifying  : postgresql13-13.7-1PGDG.rhel7.x86_64                                                                                                                                      2/10
  Verifying  : libicu-devel-50.2-4.el7_7.x86_64                                                                                                                                          3/10
  Verifying  : postgresql13-contrib-13.7-1PGDG.rhel7.x86_64                                                                                                                              4/10
  Verifying  : postgresql13-libs-13.7-1PGDG.rhel7.x86_64                                                                                                                                 5/10
  Verifying  : postgresql13-devel-13.7-1PGDG.rhel7.x86_64                                                                                                                                6/10
  Verifying  : postgresql13-contrib-13.4-1PGDG.rhel7.x86_64                                                                                                                              7/10
  Verifying  : postgresql13-libs-13.4-1PGDG.rhel7.x86_64                                                                                                                                 8/10
  Verifying  : postgresql13-13.4-1PGDG.rhel7.x86_64                                                                                                                                      9/10
  Verifying  : postgresql13-server-13.4-1PGDG.rhel7.x86_64                                                                                                                              10/10

Installed:
  postgresql13-devel.x86_64 0:13.7-1PGDG.rhel7

Dependency Installed:
  libicu-devel.x86_64 0:50.2-4.el7_7

Dependency Updated:
  postgresql13.x86_64 0:13.7-1PGDG.rhel7    postgresql13-contrib.x86_64 0:13.7-1PGDG.rhel7    postgresql13-libs.x86_64 0:13.7-1PGDG.rhel7    postgresql13-server.x86_64 0:13.7-1PGDG.rhel7

Complete!
[root@test-machine01 tmp]#

Step 8. Configure TDS_FDW package: Once above all missing packages and RPMs are installed. we can go ahead to compile the source code with make command. Once source code is compiled use make install command to copy binaries to required tds_fdw folders.

[root@test-machine01 tmp]#
[root@test-machine01 tmp]# cd /tmp/tds_fdw-master
[root@test-machine01 tds_fdw-master]#
[root@test-machine01 tds_fdw-master]#
[root@test-machine01 tds_fdw-master]# which pg_config
/usr/pgsql-13/bin/pg_config
[root@test-machine01 tds_fdw-master]#
[root@test-machine01 tds_fdw-master]# whereis pg_config
pg_config: /usr/bin/pg_config /usr/pgsql-13/bin/pg_config /usr/share/man/man1/pg_config.1.gz
[root@test-machine01 tds_fdw-master]#
[root@test-machine01 tds_fdw-master]#


[root@test-machine01 tds_fdw-master]#  make USE_PGXS=1
gcc -std=gnu99 -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Werror=vla -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard -O2 -g -pipe -Wall -Wp,-D_FORTIFY_SOURCE=2 -fexceptions -fstack-protector-strong --param=ssp-buffer-size=4 -grecord-gcc-switches   -m64 -mtune=generic -fPIC -I./include/ -fvisibility=hidden  -I. -I./ -I/usr/pgsql-13/include/server -I/usr/pgsql-13/include/internal  -D_GNU_SOURCE -I/usr/include/libxml2  -I/usr/include  -c -o src/tds_fdw.o src/tds_fdw.c
gcc -std=gnu99 -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Werror=vla -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard -O2 -g -pipe -Wall -Wp,-D_FORTIFY_SOURCE=2 -fexceptions -fstack-protector-strong --param=ssp-buffer-size=4 -grecord-gcc-switches   -m64 -mtune=generic -fPIC -I./include/ -fvisibility=hidden  -I. -I./ -I/usr/pgsql-13/include/server -I/usr/pgsql-13/include/internal  -D_GNU_SOURCE -I/usr/include/libxml2  -I/usr/include  -c -o src/options.o src/options.c
gcc -std=gnu99 -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Werror=vla -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard -O2 -g -pipe -Wall -Wp,-D_FORTIFY_SOURCE=2 -fexceptions -fstack-protector-strong --param=ssp-buffer-size=4 -grecord-gcc-switches   -m64 -mtune=generic -fPIC -I./include/ -fvisibility=hidden  -I. -I./ -I/usr/pgsql-13/include/server -I/usr/pgsql-13/include/internal  -D_GNU_SOURCE -I/usr/include/libxml2  -I/usr/include  -c -o src/deparse.o src/deparse.c
gcc -std=gnu99 -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Werror=vla -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard -O2 -g -pipe -Wall -Wp,-D_FORTIFY_SOURCE=2 -fexceptions -fstack-protector-strong --param=ssp-buffer-size=4 -grecord-gcc-switches   -m64 -mtune=generic -fPIC -shared -o tds_fdw.so src/tds_fdw.o src/options.o src/deparse.o -L/usr/pgsql-13/lib   -L/usr/lib64/llvm5.0/lib  -L/usr/lib64 -Wl,--as-needed -Wl,-rpath,'/usr/pgsql-13/lib',--enable-new-dtags  -lsybdb
cp sql/tds_fdw.sql sql/tds_fdw--2.0.2.sql
cp README.md README.tds_fdw.md
/opt/rh/llvm-toolset-7/root/usr/bin/clang -Wno-ignored-attributes -fno-strict-aliasing -fwrapv -Wno-unused-command-line-argument -O2  -I./include/ -fvisibility=hidden  -I. -I./ -I/usr/pgsql-13/include/server -I/usr/pgsql-13/include/internal  -D_GNU_SOURCE -I/usr/include/libxml2  -I/usr/include -flto=thin -emit-llvm -c -o src/tds_fdw.bc src/tds_fdw.c
/opt/rh/llvm-toolset-7/root/usr/bin/clang -Wno-ignored-attributes -fno-strict-aliasing -fwrapv -Wno-unused-command-line-argument -O2  -I./include/ -fvisibility=hidden  -I. -I./ -I/usr/pgsql-13/include/server -I/usr/pgsql-13/include/internal  -D_GNU_SOURCE -I/usr/include/libxml2  -I/usr/include -flto=thin -emit-llvm -c -o src/options.bc src/options.c
/opt/rh/llvm-toolset-7/root/usr/bin/clang -Wno-ignored-attributes -fno-strict-aliasing -fwrapv -Wno-unused-command-line-argument -O2  -I./include/ -fvisibility=hidden  -I. -I./ -I/usr/pgsql-13/include/server -I/usr/pgsql-13/include/internal  -D_GNU_SOURCE -I/usr/include/libxml2  -I/usr/include -flto=thin -emit-llvm -c -o src/deparse.bc src/deparse.c
[root@test-machine01 tds_fdw-master]#
[root@test-machine01 tds_fdw-master]#


[root@test-machine01 tds_fdw-master]#
[root@test-machine01 tds_fdw-master]#  make USE_PGXS=1 install
/bin/mkdir -p '/usr/pgsql-13/lib'
/bin/mkdir -p '/usr/pgsql-13/share/extension'
/bin/mkdir -p '/usr/pgsql-13/share/extension'
/bin/mkdir -p '/usr/pgsql-13/doc/extension'
/bin/install -c -m 755  tds_fdw.so '/usr/pgsql-13/lib/tds_fdw.so'
/bin/install -c -m 644 .//tds_fdw.control '/usr/pgsql-13/share/extension/'
/bin/install -c -m 644 .//sql/tds_fdw--2.0.2.sql  '/usr/pgsql-13/share/extension/'
/bin/install -c -m 644 .//README.tds_fdw.md '/usr/pgsql-13/doc/extension/'
/bin/mkdir -p '/usr/pgsql-13/lib/bitcode/tds_fdw'
/bin/mkdir -p '/usr/pgsql-13/lib/bitcode'/tds_fdw/src/
/bin/install -c -m 644 src/tds_fdw.bc '/usr/pgsql-13/lib/bitcode'/tds_fdw/src/
/bin/install -c -m 644 src/options.bc '/usr/pgsql-13/lib/bitcode'/tds_fdw/src/
/bin/install -c -m 644 src/deparse.bc '/usr/pgsql-13/lib/bitcode'/tds_fdw/src/
cd '/usr/pgsql-13/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
[root@test-machine01 tds_fdw-master]#
[root@test-machine01 tds_fdw-master]#

Step 9. Create the TDS_FDW extension: Once all the above steps are done. We are ready to create tds_fdw extension using create extension command. Please note all below commands we are executing in DB: local_db as we want to avoid using default postgres DB.

[root@test-machine01 ~]#
[root@test-machine01 ~]# su - postgres
Last login: Mon Jun 27 08:55:09 +03 2022 on pts/0
-bash-4.2$
-bash-4.2$ psql local_db
psql (9.2.24, server 13.7)
WARNING: psql version 9.2, server version 13.0.
         Some psql features might not work.
Type "help" for help.

local_db=#
local_db=# CREATE EXTENSION tds_fdw;
CREATE EXTENSION
local_db=#
local_db=# \dx
                                            List of installed extensions
  Name   | Version |   Schema   |                                    Description
---------+---------+------------+-----------------------------------------------------------------------------------
 plpgsql | 1.0     | pg_catalog | PL/pgSQL procedural language
 tds_fdw | 2.0.2   | public     | Foreign data wrapper for querying a TDS database (Sybase or Microsoft SQL Server)
(2 rows)

local_db=#  select * from pg_extension;
  oid  | extname | extowner | extnamespace | extrelocatable | extversion | extconfig | extcondition
-------+---------+----------+--------------+----------------+------------+-----------+--------------
 14161 | plpgsql |       10 |           11 | f              | 1.0        |           |
 19795 | tds_fdw |       10 |         2200 | t              | 2.0.2      |           |
(2 rows)

local_db=#
local_db=#

Step 10. Create the Foreign Server: Now we can create a server definition. This foreign server is created using the connection details of the remote MSSQL DB running on host “MSSQLDBCS01“. Let’s name the foreign server as “mssql_fdw“. Use \des to list foreign servers or query view pg_foreign_server.

local_db=#
local_db=# CREATE SERVER mssql_fdw FOREIGN DATA WRAPPER tds_fdw OPTIONS (servername 'MSSQLDBCS01', port '1433', database 'bidding system');
CREATE SERVER
local_db=#
local_db=# \des+
                                                                        List of foreign servers
   Name    |  Owner   | Foreign-data wrapper | Access privileges | Type | Version |                              FDW Options                              | Description
-----------+----------+----------------------+-------------------+------+---------+-----------------------------------------------------------------------+-------------
 mssql_fdw | postgres | tds_fdw              |                   |      |         | (servername 'MSSQLDBCS01', port '1433', database 'bidding system') |
(1 row)

local_db=#
local_db=# select * from pg_foreign_server;
  oid  |  srvname  | srvowner | srvfdw | srvtype | srvversion | srvacl |                           srvoptions
-------+-----------+----------+--------+---------+------------+--------+-----------------------------------------------------------------
 19808 | mssql_fdw |       10 |  19798 |         |            |        | {servername=MSSQLDBCS01,port=1433,"database=bidding system"}
(1 row)

local_db=#
local_db=#

Step 11. Create User Mapping: Create a mapping on the PostgreSQL local database: local_db user app_user to remote MSSQL database: [bidding system] user test_readonly.

local_db=#
local_db=# CREATE USER MAPPING FOR app_user SERVER mssql_fdw OPTIONS (username 'test_readonly', password 'test123');
CREATE USER MAPPING
local_db=#
local_db=# \deu+
                         List of user mappings
  Server   | User name |                  FDW Options
-----------+-----------+------------------------------------------------
 mssql_fdw | app_user  | (username 'test_readonly', password 'test123')
(1 row)

local_db=#
local_db=# select * from pg_user_mappings;
 umid  | srvid |  srvname  | umuser | usename  |                 umoptions
-------+-------+-----------+--------+----------+-------------------------------------------
 19800 | 19799 | mssql_fdw |  19794 | app_user | {username=test_readonly,password=test123}
(1 row)

local_db=#
local_db=#

Step 12. Grant the Local User Access to the Foreign Server: Grant our local user app_user access to the foreign server mssql_fdw. Without the below permission, you will receive the error “ERROR: permission denied for foreign server mssql_fdw” if you try to create Foreign Table as we are doing in Step 13.

local_db=#
local_db=# grant usage on foreign server mssql_fdw to app_user;
GRANT
local_db-# \q
-bash-4.2$

Step 13. Create a foreign Table: Create a foreign table in the local_db using our app user app_user with the same structure or its specific columns subset as the remote MSSQL table, but with OPTIONS specifying target schema_name and table_name. Now we can use this foreign table in local_db to access MSSQL [bidding system] table users.

-bash-4.2$
-bash-4.2$ psql -h test-machine01 -U app_user local_db
Password for user app_user:
psql (9.2.24, server 13.7)
WARNING: psql version 9.2, server version 13.0.
         Some psql features might not work.
Type "help" for help.

local_db=>
local_db=>
local_db=> CREATE FOREIGN TABLE mssql_users (
local_db(> user_id int,
local_db(> user_name varchar(20),
local_db(> payroll_number varchar(20),
local_db(> full_name varchar(20),
local_db(> email varchar(20)
local_db(> )
local_db-> SERVER mssql_fdw
local_db-> OPTIONS (query 'SELECT user_id, user_name, payroll_number, full_name, email FROM dbo.users');
CREATE FOREIGN TABLE
local_db=>
local_db=>

local_db=>
local_db=> \dE+
                            List of relations
 Schema |    Name     |     Type      |  Owner   |  Size   | Description
--------+-------------+---------------+----------+---------+-------------
 public | mssql_users | foreign table | app_user | 0 bytes |
(1 row)

local_db=>
local_db=> select * from information_schema.foreign_tables;
 foreign_table_catalog | foreign_table_schema | foreign_table_name | foreign_server_catalog | foreign_server_name
-----------------------+----------------------+--------------------+------------------------+---------------------
 local_db              | public               | mssql_users        | local_db               | mssql_fdw
(1 row)

local_db=>
local_db=>

local_db=>
local_db=> select count(*) from mssql_users ;
NOTICE:  tds_fdw: Query executed correctly
NOTICE:  tds_fdw: Getting results
 count
-------
  1114
(1 row)

local_db=>

Reference: https://www.mssqltips.com/sqlservertip/3663/sql-server-and-postgresql-foreign-data-wrapper-configuration-part-3/

This document is just for learning purposes and always validate in the LAB environment first before applying in the LIVE environment.

Hope so you like this article!
Please share your valuable feedback/comments/subscribe and follow us below and don’t forget to click on the bell icon to get the most recent update. Click here to understand more about our pursuit.

DBLINK, MSSQL, POSTGRESQL, TDS_FDW

Access the MSSQL database from PostgreSQL using TDS Foreign Data Wrapper (TDS_FDW)

Related Article