Home Internet Database Migration from Oracle to PostgreSQL

Database Migration from Oracle to PostgreSQL

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:

  1. 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.
  2. 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
  3. 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.
  4. Migration of data types is described in the ‘Types’ section below
  5. 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’)
  6. Synonyms are not supported in PostgreSQL and must be replaced by views for tables and wrappers for functions.
  7. ‘CONNECT BY’ part of recursive queries must be placed by ‘WITH RECURSIVE’
  8. 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
  9. Oracle triggers must be split into trigger function and trigger itself according to PostgreSQL syntax.
  10. 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.
  11. 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.

LEAVE A REPLY

Please enter your comment!
Please enter your name here

Most Popular