How to import data from Oracle into PostgreSQL

In this post, we are going to see how to import Oracle data into PostgreSQL.

If you have been using an Oracle database and now want to move to an open source alternative, then PostgreSQL is the best choice because not only is it free but it’s also a powerful RDBMS (relational database management system) with over 30 years of active development with a great reputation for performance, stability, and a very active open source community. 

More information about PostgreSQL can be found here: https://www.postgresql.org/about/.

There are several ways to import Oracle data into PostgreSQL. Here we will discuss a few of the methods that are well proven and most popular. 

Method 1: Ora2Pg

As the name suggests, Ora2Pg is used to migrate Oracle objects into PostgreSQL. This tool will connect to an Oracle database and generate SQL scripts that contain table structures and data that can be executed against PostgreSQL. 

Some of the features include: 

• Exports full database schema (such as tables, views including materialized view, sequences, and indexes).
• Exports specific tables.
• Exports user-defined functions, triggers, procedures, and packages.
• Migrates Oracle user-defined types.
• Supports Oracle BLOB object as BYTEA. 
• Supports DBLINK as Oracle FDW.
• Supports SYNONYMS as views. 

A sample database migration report can be found here: http://ora2pg.darold.net/report.html.

For more information please visit https://ora2pg.darold.net/start.html.

Method 2: Oracle foreign data wrappers (Oracle_fdw) 

PostgreSQL can link to other systems to fetch data via foreign data wrappers (FDWs). When we fire a query (e.g., SELECT)  against a foreign table, the FDW will fetch the result from the external data source and print the output via foreign table.

Oracle_fdw is used to perform DML operations (SELECT, INSERT, UPDATE, DELETE) on Oracle servers in PostgreSQL, which is covered under the SQL Management of External Data (SQL/MED) standard.

For more information please visit https://github.com/laurenz/oracle_fdw.

Method 3: EnterpriseDB (EDB)  Free Oracle to PostgreSQL Migration Tool

EnterpriseDB develops and integrates performance, security, and manageability enhancements into PostgreSQL to support enterprise-class workloads for its database.

For more information about EnterpriseDB please visit https://www.enterprisedb.com/about-us.

EDB has also developed database compatibility for Oracle to facilitate the migration of workloads from Oracle to EDB PostgreSQL and to support the operation of many Oracle workloads on EDB PostgreSQL. 

The Migration Portal analyzes Oracle database schemas and converts tables, views, stored procedures, functions, indexes, types, sequences, constraints, triggers, packages, dblinks, MV (Materialized views) and many more. It then produces DDLs that are compatible with EDB PostgreSQL Advanced Server (Oracle-compatible PostgreSQL). 

Oracle to EDB Postgres Advanced Server (Oracle-compatible PostgreSQL) migration in 3 easy steps:

1. Schema Assessment

2. Schema Migration

3. Data Migration

The advantage is that not only can you import data from Oracle to EDB PostgreSQL Advanced Server easily but also provide Oracle compatibility in your database (Oracle-compatible PostgreSQL), which means you will get PostgreSQL + Oracle compatibility all together. 

For more details please visit https://www.enterprisedb.com/enterprise-postgres/free-oracle-postgres-migration-tool.

Hope it helps !