More and more businesses are choosing to switch from Oracle to PostgreSQL for their database needs. This shift is driven by the desire to reduce costs and avoid Oracle’s strict licensing requirements.
Nowadays the trend of migrating databases from Oracle to PostgreSQL becomes stronger. Oracle is a very powerful DBMS, but it has a high cost of ownership and strict licensing policy. So, it is not surprising that many businesses are exploring alternate database management systems to get data hosting, administration and development at more affordable rates.
Obviously, it is expected that the new system provides similar capabilities compared to the original DBMS. When talking about Oracle, none of the other relational database management systems can have equal capabilities and performance.
However, PostgreSQL gets much closer to Oracle than other systems as it is an advanced standard-compliant RDBMS combining object-oriented and relational features.
Oracle to PostgreSQL Migration
One of the most popular approaches to converting an Oracle database to PostgreSQL is based on the free tool ORA2PG written in the Perl programming language. The method includes connecting to Oracle database, extracting all schemas and data in form of DDL statements and INSERT-operations. Then all those scripts are loaded into the PostgreSQL database via the Perl:PG module.
ORA2PG has a list of prerequisites as follows:
- Development and JDBC packages of Oracle
- Client components of PostgreSQL
- Perl distribution version 5.10 and higher
- Perl modules DBD::Oracle and DBI::PG
Once all components are available, an ORA2PG script must be installed and config files are set up for the source and destination database connections.
This tool can convert schema, data, indexes, constraints, sequences and triggers. Views, stored procedures and functions are not supported and must be converted manually. Some tips on how to convert Oracle views into PostgreSQL format are listed in the next section.
Views
Source: dbconvert.com
To get list of all Oracle views run this statement from any client tool:
select VIEW_NAME, TEXT from SYS.USER_VIEWS;
The statements must be transformed to comply with PostgreSQL format. First, remove specific Oracle keywords having no equivalent in the target DBMS:
- DEFAULT
- FORCE / NO FORCE
- WITH CHECK OPTION
- WITH OBJECT IDENTIFIER
- WITH READ ONLY
- UNDER
Custom syntax of JOIN operator (+) supported by Oracle must be converted according to SQL standard, for example:
SELECT t1.f2, t2.f2 FROM t1, t2 WHERE t1.f1=t2.f1 (+)
must be replaced by:
SELECT t1.f2, t2.f2 FROM t1 LEFT JOIN t2 ON t1.f1=t2.f1
All embedded Oracle functions must be replaced by PostgreSQL equivalents according to this table:
| Oracle | PostgtreSQL |
| CURTIME() | LOCALTIME(0) |
| DAY($a), DAYOFMONTH($a) | EXTRACT(day from date($a))::integer |
| DateAdd($Date, $Format, $Days, $Months, $Years) | $Date + cast(‘$Days day’ as interval) + cast(‘$Months months’ as interval) + cast(‘$Years years’ as interval) |
| HOUR($a) | EXTRACT(hour from $a) |
| INSTR($str1, $str2) | POSITION($str2 in $str1) |
| LCASE($a) | LOWER($a) |
| LOCATE($str1,$str2) | POSITION($str1 in $str2) |
| MINUTE($a) | EXTRACT(minute from $a) |
| MONTH($a) | EXTRACT(month from $a) |
| NVL($a, $replace_with) | COALESCE($a, $replace_with) |
| REGEXP_LIKE($string, $pattern) | $string LIKE $pattern |
| SECOND($a) | EXTRACT(second from $a) |
| SYSDATE | CURRENT_DATE |
| SYS_GUID() | uuid_generate_v1() |
| UCASE($a) | UPPER($a) |
| WEEK($a) | EXTRACT(week from $a) |
| YEAR($a) | EXTRACT(year from date($a)) |
Oracle to PostgreSQL Conversion Tools
Source: percona.com
The explored approach to database migration from Oracle to PostgreSQL appears to be a complicated process requiring some manual post-processing. The manual part of migration may cause data loss or corruption due to human factors and it obviously makes the overall process more expensive.
Due to this reason, many database professionals use special software to automate migration from Oracle to PostgreSQL with just a few button clicks.
One of the most famous companies specializing in database migration and synchronization, Intelligent Converters developed such a tool enriched with the following features:
- All recent on-premise versions of Oracle and PostgreSQL are supported
- All popular SAAS variations of both DBMS (such as Amazon RDS and Heroku) are supported
- Table definitions, data, views, indexes and constraints are converted with all necessary properties
- Option to export Oracle database into PostgreSQL script to get even more control over the migration
- Option to convert partial or pre-processed data using SELECT-queries
- Command line support allow scripting and scheduling the migration job
