Oracle is known as powerful object-relational database management system that can manage huge and complicated enterprise scale data warehouses and applications. Main disadvantages of this DBMS are high total cost of ownership and strict licensing policy. This unique drawback makes some companies and organizations to move from Oracle to different systems and platforms.
Why choose PostgreSQL?
Database admin or some other person in charge of database migration should be expecting that new system offers similar range of features when compared to original DBMS. It is obvious that no other database can compare with Oracle by number of powerful features including:
- extensive backup
- multi-level compression
- flexible storage customization
However, PostgreSQL gets much closer to Oracle than others as powerful standards-compliant database management system that mixes object-oriented and relational database functionality. Here’s the partial list of PostgreSQL superior features:
- asynchronous replication
- multi-version concurrency control
- nested transactions
- point-in-time recovery
- sophisticated locking mechanism
Those advantages make it excellent solution for projects demanding high power, reliability and data integrity i.e. the most effective replacement for Oracle.
Oracle to PostgreSQL Migration
The procedure of database migration from Oracle to PostgreSQL includes the following steps:
- Oracle table definitions are exported into “CREATE TABLE” SQL commands
- these SQL commands have to be made suitable for PostgreSQL format and then loaded to the target server
- Oracle data is exported into intermediate storage such as CSV files
- those CSV files must be converted into the target format (when required) and imported in PostgreSQL database
- Oracle views, triggers, stored procedures and processes are exported into the appropriate SQL statements and plain text source code
- those statements and code must be transformed according to PostgreSQL syntax and loaded to the target server
Table Definitions
Here we consider simple measures through this list in many details. SQL*Plus is employed as default Oracle client application in the statements anywhere below. Here is the command line to connect with the database via SQL*Plus:
sqlplus username/password@database
This is how to get list of all tables:
SQL> select table_name from user_tables;
Now the definition of particular Oracle table should be extracted:
SQL> set long 1000
SQL> set pagesize 0
SQL> select DBMS_METADATA.GET_DDL(‘TABLE’,'<TABLE NAME>'[,’SCHEMA’]) from DUAL
The resulting script must be corrected before loading to PostgreSQL as follows:
- remove Oracle specific statements at the end of table DDL (starting from “USING INDEX PCTFREE…”)
- all data types must be converted into PostgreSQL equivalents according to this table
Data
Oracle data must be exported into CSV format through the following statements:
SQL> set heading off
SQL> spool filename.csv
SQL> select column1 || ‘,’ || column2 || … from mytable;
SQL> set colsep ‘,’
SQL> select * from my_table;
SQL> spool off;
PostgreSQL can import the resulting CSV file into table via “COPY” command:
COPY <table name> FROM <path to csv file> DELIMITER ‘,’ CSV;
In case of “Permission denied” error try to use “\COPY” command instead.
Indexes
Let’s get list of all indexes that belong to table “mytable”:
SQL> select * from all_indexes where table_name = ‘<TABLE NAME>’;
Remember that Oracle preserves table names as upper case by default unless lower case is specified by table name enclosed in quotes in “CREATE TABLE” statement.
And this is how definition of particular indexes can be extracted:
SQL> set long 1000
SQL> set pagesize 0
SQL> select DBMS_METADATA.GET_DDL(‘INDEX’,'<INDEX NAME>’) from DUAL;
Views
The following query can get list of all views in the Oracle database:
select VIEW_NAME, TEXT from SYS.USER_VIEWS;
Now the resulting queries must be converted in the destination format. First step is to remove specific Oracle keywords that are not supported by PostgreSQL:
- DEFAULT
- FORCE / NO FORCE
- WITH CHECK OPTION
- WITH OBJECT IDENTIFIER
- WITH READ ONLY
- UNDER
Oracle supports custom syntax of LEFT JOIN using (+) operator that is not accepted by PostgreSQL:
SELECT t1.f2, t2.f2 FROM t1, t2 WHERE t1.f1=t2.f1 (+)
Such patterns must be converted according to ANSI SQL standard on LEFT JOIN:
SELECT t1.f2, t2.f2 FROM t1 LEFT JOIN t2 ON t1.f1=t2.f1
Finally, all embedded Oracle functions that are missing in PostgreSQL must be replaced by the appropriate equivalents:
- Oracle function CURTIME() is replaced by LOCALTIME(0) in PostgreSQL
- All occurrences of DAY($a) or DAYOFMONTH($a) must be converted into the following expression: EXTRACT(day from date($a))::integer
- DateAdd($Date, $Format, $Days, $Months, $Years) can be converted into this expression:
$Date + cast(‘$Days day’ as interval) + cast(‘$Months months’ as interval) + cast(‘$Years years’ as interval)
- HOUR($a) is converted into EXTRACT(hour from $a)
- Simple form of using Oracle function INSTR($str1, $str2) can be replaced by POSITION($str2 in $str1).
More comprehensive porting of this function can be found here - Oracle function LCASE must be converted into PostgreSQL equivalent LOWER
- All occurrences of LOCATE($str1,$str2) must be replaced by POSITION($str1 in $str2)
- MINUTE($a) is converted into EXTRACT(minute from $a)
- MONTH($a) is converted into EXTRACT(month from $a)
- NVL($a, replace_with) can be converted into COALESCE($a, replace_with)
- Oracle function RAND must be replaced by PostgreSQL equivalent RANDOM
- Regexp support function REGEXP_LIKE($string, $pattern) is converted into PostgreSQL expression $string LIKE $pattern
- SECOND($a) is converted into EXTRACT(second from $a)
- Function SUBSTR($string, $from, $for) is converted into SUBSTRING($string, $from, $for)
- All occurrences of SYSDATE must be replaced by PostgreSQL equivalent CURRENT_DATE
- All occurrences of SYS_GUID() can be replaced by uuid_generate_v1(). In early versions of PostgreSQL (before v9.4) this function depended on the OSSP UUID library. In order to get version-independent solution, the following expression may be used:
SELECT md5(random()::text || clock_timestamp()::text)::uuid
- Oracle function UCASE is converted into UPPER that is synonym in PostgreSQL
- WEEK($a) is converted into EXTRACT(week from $a)
- Oracle function YEAR($a) must be replaced by EXTRACT(year from date($a))
Oracle to PostgreSQL Conversion Tools
The methods explained in this article confirm that moving Oracle database to PostgreSQL is really a complex process. Manual migration requires a good deal of efforts also it can cause loss of data or corruption as a result of human factor. It’s reasonable to consider special migration tools for this purpose.
Ora2Pg
This is a free and reliable Perl script to migrate Oracle DDL statements into PostgreSQL format. The tool can export full database schema including tables, views, sequences and indexes with the appropriate attributes. Ora2Pg converts Oracle BLOBs into PostgreSQL bytea, exports Oracle views as regular PostgreSQL tables, handles sequences and user defined types.
Commercial tools
When capabilities of Ora2Pg are not enough or it is requires to implement direct migration from source database server to the target one, that could convert Oracle to PostgreSQL with only a couple of clicks of mouse button. Intelligent Converters created this type of tool – Oracle to PostgreSQL converter. It can certainly automate migration of the following database objects:
- Table definitions
- Data
- Indexes and constraints
- Foreign keys
- Views
The program can also migrate result of SELECT-query as regular table. This feature can be used to filter data, to rename columns or tables in the destination database or to merge multiple tables into a single one.
Oracle to PostgreSQL converter has basic synchronization capabilities. It is implemented as combination of insert Oracle records missing in PostgreSQL table and update existing PostgreSQL records with Oracle data. It is required that source and destination tables have equal structures and have primary key or unique index defined for synchronization purpose.