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=>
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.
Related Article
- Access the MSSQL database from PostgreSQL using TDS Foreign Data Wrapper (TDS_FDW)
- Access MySql database from PostgreSQL using MySQL Foreign Data Wrapper (MYSQL_FDW)
- Configure and monitor using pg_profile in PostgreSQL
- Monitor PostgreSQL Cluster using pgCenter
- Install and Configure pg_repack in PostgreSQL
