Being a powerful object-relational DBMS, Oracle is ideal choice for building enterprise-scale databases. However, some companies and organizations consider migration from Oracle to another DBMS due to high cost of ownership and strict licensing policy.
The process of database migration can take much time and efforts. It can be simplified when choosing new DBMS with similar set of features compared to the original system. There are unique Oracle features that cannot be found in other DBMS such as:
- multi-level compression
- extensive backup
- flexible storage customization
However, most of other compatibilities are present in some modern relational database management systems.
PostgreSQL
PostgreSQL is a powerful standards-compliant DBMS providing both object-oriented and relational functionality. It gets much closer to Oracle than other systems due to wise range of advanced features:
- sophisticated locking mechanism
- nested transactions
- asynchronous replication
- point-in-time recovery
- multi-version concurrency control
These advantages make PostgreSQL the best alternative to Oracle database for complicated projects demanding high reliability and data integrity.
Oracle to PostgreSQL Migration
The process of database migration from Oracle to PostgreSQL consists of the following stages:
- exporting Oracle tables into data definition language (DDL) statements
- translating these DDL-statements according to PostgreSQL format and loading to the target server
- exporting Oracle data into intermediate comma separated values storage
- converting data into the target format if it is necessary and loading to PostgreSQL database
- exporting Oracle triggers, views, stored procedures and functions into SQL statements and source code
- translating these statements and code according to PostgreSQL syntax and loading to the target database
Now let us consider all differences between the two database management systems that should be handled properly during the migration:
- Oracle gives every user her own schema that is equal to username. PostgreSQL allows users to create schemas with custom names, access to multiple schemas or share the same schema between multiple users.
- In Oracle names of schema, tables, columns and other entries are UPPER CASE unless enclosed in quotes. PostgreSQL treats database object names lowercase unless quoted
- CREATE TABLE statements are similar in Oracle and PostgreSQL except specific keywords like INITTRANS, MAXEXTENTS. They must be removed before loading those statements into PostgreSQL.
- Migration of data types is described in the ‘Types’ section below
- To migrate sequences extract nextval from Oracle and translate is as setval(‘sequence_name’, value) in PostgreSQL. In the same way sequence_name.currval becomes currval(‘sequence_name’)
- Synonyms are not supported in PostgreSQL and must be replaced by views for tables and wrappers for functions.
- ‘CONNECT BY’ part of recursive queries must be placed by ‘WITH RECURSIVE’
- Oracle uses rownum to limit result of a query by number of returned rows like where rownum <= 1000. In PostgreSQL those expressions are replaced by limit 1000
- Oracle triggers must be split into trigger function and trigger itself according to PostgreSQL syntax.
- To migrate views from Oracle to PostgreSQL it is required to replace embedded functions and operators that are not supported by target DBMS by the most accurate equivalents as described in the ‘Views’ section below.
- All global variables of Oracle packages must be collected in temporary table in PostgreSQL to preserve the same semantic.
Types
Although most of Oracle data types have direct equivalents in PostgreSQL, some of them require accurate conversion. Below is the list of such types:
| Oracle | PostgreSQL |
| BFILE | VARCHAR(255) |
| BINARY_FLOAT | REAL |
| BINARY_DOUBLE | DOUBLE PRECISION |
| BLOB | BYTEA |
| CLOB | TEXT |
| LONG | TEXT |
| LONG RAW | BYTEA |
| NCHAR(n), NCHAR VARYING(n) | CHAR(n), VARCHAR(n) |
| NCLOB | TEXT |
| NUMBER(p,s) | DECIMAL(p,s) |
| RAW(n) | BYTEA |
| ROWID | CTID |
| RAW(n) | BYTEA |
| XMLTYPE | XML |
Views
Oracle supports various options of creating views that are missing in PostgreSQL and so must be removed during migration:
- DEFAULT
- FORCE / NO FORCE
- WITH CHECK OPTION
- WITH OBJECT IDENTIFIER
- WITH READ ONLY
- UNDER
Custom join operator (+) is allowed Oracle but not in PostgreSQL, it must be replaced by standard JOIN statements during migration:
(ORACLE) SELECT t1.f2, t2.f2 FROM t1, t2 WHERE t1.f1=t2.f1 (+)
(POSTGRESQL) SELECT t1.f2, t2.f2 FROM t1 LEFT JOIN t2 ON t1.f1=t2.f1
Every Oracle embedded function that is not supported by PostgreSQL must be replaced by the equivalent according to this table:
| Oracle | PostgreSQL |
| CURTIME() | LOCALTIME(0) |
| DAY($a) or 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) |
| SUBSTR($string, $from, $for) | SUBSTRING($string, $from, $for) |
| SYSDATE | CURRENT_DATE |
| UCASE($a) | UPPER($a) |
| WEEK($a) | EXTRACT(week from $a) |
| YEAR ($a) | EXTRACT($a) |
Migration Tools
The validation steps specified above indicate that migration of Oracle database to PostgreSQL is a complicated procedure. Manual approach takes a lot of human resources and it can cause some errors or data integrity loss. That’s why it is recommended to consider special tools that can migrate database from Oracle to PostgreSQL with just a few clicks of mouse button.
Intelligent Converters, software company specializing in database conversion, migration and synchronization, offers a tool for this purpose – Oracle to PostgreSQL converter. It supports all versions of Oracle and PostgreSQL and can automate migration of the following database objects:
- Table definitions
- Data
- Indexes and constraints
- Foreign keys
- Views
Command line support allows to script and schedule the database migration procedure. The program also allows to export Oracle data into PostgreSQL script instead of migrating to the target server directly. This approach may help when there is no direct connection to PostgreSQL server.
Another useful option provided by Oracle to PostgreSQL converter is filtering data for migration via SELECT-queries. The goal is to migrate result of the specified SELECT-query as if it would be a table. This feature allows user select particular columns, data subset, merge multiple tables into a single one or simply rename columns in the resulting table.