Despite of the fact that Oracle and PostgreSQL are both extremely advanced and popular DBMS, the first DBMS has more strict licensing terms and high total cost of ownership while PostgreSQL comes with liberal license and it is open source. For many organizations this is the primary reason to migrate their databases from Oracle to PostgreSQL.
Database migration from Oracle to PostgreSQL is usually implemented though extract-transform-load (ETL) method that consists of the following steps:
- Oracle schemas are exported in form of data definition language (DDL) statements
- Those statements are translated into PostgreSQL format including correct type mapping, replacement of Oracle specific functions and operators that are not supported in PostgreSQL
- The resulting script is imported into the target database
- Oracle data is exported into comma separated values (CSV) format as an intermediate storage
- The data is converted according to PostgreSQLformat with respect to transformation of binary data, dates and escaping special symbols in text fields
- The resulting CSV files are imported into PostgreSQL database
- Oracle views, stored procedures and triggers are exported into SQL statements and source code
- The result of the export above must be translated according to comply with PostgreSQL format and then imported into the target database
The approach described above shows that database migration from Oracle to PostgreSQL is a complicated and tedious procedure that should not be implemented manually. This task requires a lot of efforts especially for huge corporate databases and highly probable human errors may cause data loss or corruption. Therefore, it is suggested to use special software to automate database migration and eliminate the related risks.
There are many solutions to automate Oracle to PostgreSQL database migration ranged from free scripts like ora2pg to powerful commercial frameworks for hundreds of dollars. One of the most efficient tools in terms of price to quality is offered by Intelligent Converters, software company focusing on database migration for more than 20 years.
Product Features
- The product migrates table definitions, data, constraints, indexes, foreign keys with all necessary attributes
- High performance of the database migration process due to direct low-level connection to Oracle and PostgreSQL servers and bulk insert techniques
- All modern versions of Oracle and PostgreSQL are supported including forks and SAAS variations of both database management systems
- The product converts most of Oracle views (about 90% of syntax constructions involved into Oracle “CREATE VIEW” statement are supported)
- Oracle to PostgreSQL converter supports command line for automation purpose
- Existing PostgreSQL database can be overwritten, merged or synchronized with Oracle data
- The product stores conversion settings into profile to simplify next runs
Besides generic advantages listed above, Oracle to PostgreSQL converter offers filtering data via SELECT-queries during migration. This powerful capability has been designed to select particular columns and rows for migration and to transform Oracle data before importing it into PostgreSQL format. Below are the most common cases of using SELECT-queries.
- Filtering records: SELECT id, fname, lname FROM people WHERE birthdate > to_date(’01-JAN-90’,’DD-MON-YY’)
- Select and rename particular columns: SELECT f1 as “First Name” FROM tbl1
- Skip NULL values: SELECT first_name, last_name FROM employee WHERE company IS NOT NULL
- Merge two tables:
SELECT suppliers.id, suppliers.name, orders.date
FROM suppliers INNER JOIN orders ON suppliers.id = orders.supplier_id;
At the same time, Oracle to PostgreSQL converter has some limitations:
- It does not process stored procedures, functions and triggers
- Demo version does not migrate views and foreign keys
- Demo version migrates not more than 50 rows per table
This database migration tool is supplied with unlimited support and 1-year subscription for updates just like any software product provided by Intelligent Converters.
