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.
- Securing your PostgreSQL database cluster using built-in mechanisms
- High availability and Automatic Failover in PostgreSQL
- Robust PostgreSQL backups using built-in and open source tools.
- Scaling PostgreSQL using connection poolers and load balancers
- Logging and analyzing the logs using Open Source tools in PostgreSQL
- Enterprise Grade Monitoring for your PostgreSQL using open source tools
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.
Features offered by some of the foreign data wrappers
- Optimization of remote queries to reduce the amount of data transferred between the servers.
- Predicate push down
- Join push down
- 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
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;
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.
FROM oracle_table ora
WHERE NOT EXISTS
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.