Role of Foreign Data Wrappers in Migrations to PostgreSQL

Millions of databases are being migrated to PostgreSQL today. This is because of the liberal PostgreSQL license in particular that invited more contributions to PostgreSQL and eventually made it Enterprise Grade. I have presented and also blogged about some of the rich enterprise features as following, those are otherwise available for an additional cost with some commercial databases.

While it is great that there are several features available with Postgres by default, it may be challenging if there are no solutions to help with migrations to postgres. Fortunately, there are several contributions through extensions and Open source tools that made migrations to PostgreSQL very easy. In this blog post, we are going to discuss just one of those features.

Foreign Data Wrappers (FDW)

As of the most recently updated SQL standard, ISO/IEC 9075:2016 aka SQL:2016, PostgreSQL tops the compliance chart when compared with Oracle, DB2, SQL Server and MySQL, qualifying 170 of the 177 SQL standards.

SQL/MED is one of the standards that was originally defined as part of the SQL standard updated in 2003 (SQL:2003). MED stands for Management of External Data, the data that is accessible but not managed by the DBMS itself. Using FDW(s), objects of a heterogeneous database can be transparently mapped to PostgreSQL. This helps a user to SELECT FROM or WRITE TO a remote homogeneous/heterogeneous database from a Postgres database.

As seen in the following image, we can join an Oracle table with a Postgres table using Oracle FDW on a postgres server.

FDW

Features offered by some of the foreign data wrappers
  1. Optimization of remote queries to reduce the amount of data transferred between the servers.
  2. Predicate push down
  3. Join push down
  4. Aggregate push down
Advantages of FDW(s) while migrating to PostgreSQL

Following are some of the advantages of foreign data wrappers while migrating to PostgreSQL. 

Schema Migrations to PostgreSQL

Some of the database migrations from Oracle like databases can be simply done using oracle_fdw like foreign data wrappers. Using the IMPORT FOREIGN SCHEMA syntax, a schema similar to Oracle can be created in the postgres database. Oracle_FDW does an automatic conversion of Oracle data types to an appropriate postgres data type and creates tables and views with the same structure as of the Oracle schema. 

Following is an example of how the schema : HR of an Oracle database : xepdb1 running on host : 192.168.10.1 is being migrated to PostgreSQL using oracle_fdw. All these steps will be executed on the Postgres database and nothing on the Oracle database. 

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

GRANT USAGE ON FOREIGN SERVER oracle_server TO postgres;

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

CREATE SCHEMA hr_fdw;

IMPORT FOREIGN SCHEMA "HR" FROM SERVER oracle_server INTO hr_fdw;

postgres=# select count(*) from hr_fdw.countries;
 count
-------
    25
(1 row)

As seen in the above log, we could run a select on a postgres database that is getting the count of records of a table from an Oracle table. 

Data Migration to PostgreSQL

FDW(s) can be used to migrate data from a remote heterogeneous database to PostgreSQL. As seen in the following example, using oracle_fdw, we can run a simple insert command that can select data from an oracle database table and insert it into a postgres table. 

INSERT INTO hr.countries (country_id, region_id, country_name) 
SELECT (country_id, country_name, region_id) FROM hr_fdw.countries;

Data validation upon migration to PostgreSQL

One of the challenges faced by developers and admins migrating to PostgreSQL is the data validation. It is very difficult to validate the data between 2 heterogeneous databases. One may have to write an application comparing the data or find some other complex ways to do the same. Foreign data wrappers can be a saviour here. We can join the postgres table and the source table to check for any missing data using different combinations of the powerful SQL language. 

SELECT id
FROM oracle_table ora
WHERE NOT EXISTS
    (SELECT *
     FROM postgres_table pg
     WHERE pg.id = ora.id);

Please note :

Not all DBaaS platforms allow us to create foreign data wrappers. For example, Amazon RDS or Aurora does not list oracle_fdw as one of the supported extensions. What to do in such cases ? Let us see a step by step technical approach on how to deal with that in our next blog post. Please subscribe to our blog posts to follow our latest updates on migrations to postgres. Please contact us to know how we can assist you while performing migrations to PostgreSQL. 

2 thoughts on “Role of Foreign Data Wrappers in Migrations to PostgreSQL”

Leave a Comment

Your email address will not be published.

Scroll to Top