How to Access Oracle and SQL Server Tables from PostgreSQL

 

How to Access Oracle and SQL Server Tables from PostgreSQL

 

A Popular DBA Interview Question Explained

PostgreSQL is not just a database. It can also work as:

·         Federation database

·         Central reporting layer

·         Cross-platform query engine

 

One of the most common DBA interview questions today is:

“How do you access Oracle or SQL Server tables directly from PostgreSQL?”

 

The answer is: Foreign Data Wrappers (FDW)

 

Interview Question

 

Q1. How can PostgreSQL access Oracle database tables? Answer: PostgreSQL uses: oracle_fdw

 

This extension allows PostgreSQL to:

 

·         Connect Oracle remotely

·         Query Oracle tables

·         Perform joins

·         Read/write data without migrating the data.

Oracle → PostgreSQL Architecture

Oracle Database

      ↓

 oracle_fdw

      ↓ PostgreSQL

Step-by-Step Oracle FDW Example

Step 1) Install Oracle Instant Client - Download Oracle client libraries: Oracle Instant Client Downloads

Install:

dnf install oracle-instantclient-basic*.rpm dnf install oracle-instantclient-devel*.rpm

 

Step 2) Install oracle_fdw

 

git clone https://github.com/laurenz/oracle_fdw.git cd oracle_fdw

make

make install

Step 3) Create Extension

Login PostgreSQL: CREATE EXTENSION oracle_fdw;

Step 4) Create Foreign Server

 

CREATE SERVER oracle_server FOREIGN DATA WRAPPER oracle_fdw OPTIONS (dbserver '//192.168.233.134:1521/XEPDB1');

 

Step 5) Create User Mapping

CREATE USER MAPPING FOR postgres SERVER oracle_server OPTIONS ( user 'system', password 'oraclepass' );

 

Step 6) Create Foreign Table - Suppose Oracle table:

HR.EMPLOYEES

 

Map it into PostgreSQL:

 

CREATE FOREIGN TABLE oracle_employees ( emp_id integer, emp_name text, salary numeric) SERVER oracle_server OPTIONS (schema 'HR', table 'EMPLOYEES');

Step 7) Query Oracle Table

 

SELECT * FROM oracle_employees;

 

PostgreSQL reads Oracle table directly.

Interview Question

 

Q2. How can PostgreSQL access SQL Server tables? Answer: PostgreSQL uses:

 

·         tds_fdw

·         ODBC FDW

Most commonly: tds_fdw

It connects PostgreSQL with:

·         Microsoft SQL Server

·         Sybase using TDS protocol.

SQL Server → PostgreSQL Architecture

SQL Server

    ↓

 tds_fdw

    ↓ PostgreSQL

 

Step-by-Step SQL Server FDW Example

Step 1) Install FreeTDS

dnf install freetds freetds-devel

 

Step 2) Install tds_fdw

git clone https://github.com/tds-fdw/tds_fdw.git cd tds_fdw

make USE_PGXS=1

make USE_PGXS=1 install Step 3) Create Extension CREATE EXTENSION tds_fdw; Step 4) Create Foreign Server

CREATE SERVER sqlserver_svr FOREIGN DATA WRAPPER tds_fdw OPTIONS (servername ‘192.168.223.132', port '1433', database 'company' );

Step 5) Create User Mapping

 

CREATE USER MAPPING FOR postgres SERVER sqlserver_svr OPTIONS ( username 'sa', password 'sqlpass');

 

Step 6) Create Foreign Table

Suppose SQL Server table: dbo.customers

Create mapping:

CREATE FOREIGN TABLE customers_sqlserver ( customer_id integer, customer_name text, city text) SERVER sqlserver_svr OPTIONS (schema_name 'dbo', table_name 'customers');

 

Step 7) Query SQL Server Table

 

SELECT * FROM customers_sqlserver; PostgreSQL reads SQL Server data directly. Advanced Interview Question

Q3. Can PostgreSQL join Oracle and SQL Server tables together?

Answer Yes. Example:

SELECT o.emp_name, s.city FROM oracle_employees o JOIN customers_sqlserver s ON o.emp_id = s.customer_id;

PostgreSQL can join tables from different database technologies.