Oracle and PostgreSQL are the most powerful and popular object-relational DBMS. Both of them are supplied with a lot of advanced features and wide range of administration and development tools. However, PostgreSQL has important benefit compared to Oracle: it is open source and has more liberal license than another database management system. In view of this fact many companies migrate their databases from Oracle to PostgreSQL to reduce total cost of the ownership.
Manual migration runs according to extract-transform-load technique. According to this approach, Oracle to PostgreSQL database migration is a sequence of following steps:
- Export Oracle schemas, indexes and constraints with all necessary attributes in form of data definition language (DDL) statements
- Convert those statements into PostgreSQL format with respect to type mapping, replacing Oracle built-in functions that are not supported in PostgreSQL, etc
- Import the resulting CREATE-statements into the destination database
- Export Oracle data into CSV files (one file per table)
- Make the data conformed with PostgreSQL format that includes correct processing of binary data and dates, escaping special symbols when it is necessary
- Import the resulting CSV files into the PostgreSQL tables
- Extract views, stored procedures and triggers from Oracle database in form of SELECT-queries and PL/SQL source code
- Transform these queries and source code according to PostgreSQL dialect and load it into the destination database
The steps of manual database migration from Oracle to PostgreSQL listed above proves that it is not an easy task taking many efforts. There is high risk of losing data integrity and breaking semantic because of human errors. In order to avoid those risks and to automate database migration, some dedicated software solutions should be used.
One of such database migration tool is Oracle to PostgreSQL converter. It has been developed by Intelligent Converters, a software vendor focused on database conversion and synchronization for more than 10 years. The product supports all versions of Oracle and PostgreSQL and has outstanding performance due to direct reading and writing data (no middleware components are used). Other features:
- Converts schemas, data, indexes, constraints, foreign keys and views
- For scripting and scheduling the migration process Oracle to PostgreSQL converter is supplied with command line version
- Merges and to synchronizes existing PostgreSQL database with Oracle data
- Converts data obtained as result of SELECT-queries
The fourth feature is very powerful and can be used for multiple purposes, for example:
- Filtering rows:
SELECT * FROM Orders WHERE StartDate BETWEEN to_date(’15-JAN-16’) AND to_date(’15-FEB-16’)
- Select and rename particular columns:
SELECT fname AS FirstName, lname AS LastName, addr as Address FROM People
- Skip NULL values: SELECT * FROM Employees WHERE Comments IS NOT NULL
- Merge two tables:
SELECT T1.code, T1.name, T1.description, T2.image
FROM Products T1
INNER JOIN ProductLines T2 ON T1.productline = T2.productline;
For more information about Oracle to PostgreSQL database migration tool visit the official site: https://www.convert-in.com/ora2pgs.htm
