How to migrate from Oracle to Postgres

[Oracle to Postgres Converter] [Validate Migration Results] [Triggers] [Types Mapping] [Views]

Oracle is a powerful object-relational DBMS suitable for handling huge enterprise scale databases. However, it has high cost of ownership and strict licensing policy. This valuable disadvantage forces some companies and organizations to migrate their databases from Oracle to an open-source DBMS with similar capabilities and features, for example PostgreSQL.

Why PostgreSQL?

Database admin or another person responsible for database migration should expect that new system provides similar set of features compared to the original DBMS. When talking about Oracle database, it is obvious that none of other relational database management systems is so enriched with powerful features such as:

extensive backup
multi-level compression
flexible storage customization

Being powerful standards-compliant RDBMS that combines object-oriented and relational database functionality, PostgreSQL gets much closer to Oracle than other systems. Here is the brief list of its advanced features:

asynchronous replication
multi-version concurrency control
nested transactions
point-in-time recovery
sophisticated locking mechanism

Those benefits make PostgreSQL excellent solution for complicated projects demanding high reliability and data integrity i.e. the best alternative to Oracle database.

Oracle to Postgres Database Migration

The process of Oracle to Postgres database migration consists of the following steps:

export Oracle table definitions into "CREATE TABLE" statements
make these SQL-statements compatible with PostgreSQL format and load to the target server
export Oracle data into intermediate storage such as CSV files
convert it into the target format (if necessary) and import in PostgreSQL database
export Oracle views, triggers, stored procedures and functions into SQL statements and plain text source code
transform these statements and code according to PostgreSQL syntax and load to the target server

Table definitions

Below we consider basic steps from this list in more details. Anywhere below SQL*Plus is used as default Oracle client application. This is the command line to connect to the database via SQL*Plus:

sqlplus username/password@database

First, it is necessary to get list of all tables:

SQL> select table_name from user_tables;

And this is how definition of particular Oracle table can 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...")
convert all data types into PostgreSQL equivalents according to this table

Data

Next step of Oracle to Postgres database migration is to export the source data into CSV format. It can be done via the following sequence of commands:

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;

The resulting CSV file can be imported into PostgreSQL table via "COPY" command:

COPY <table name> FROM <path to csv file> DELIMITER ',' CSV;

If there is "Permission denied" error after running this statement, 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>';

Keep in mind that Oracle stores table names as upper case by default unless lower case was specified directly by enclosing table name 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;

Empty String vs NULL

Oracle does not distinguish empty strings from NULL values in string context. The concatenation of NULL and string obtain string as a result. In PostgreSQL the result of concatenation above is NULL.

Oracle uses operator IS NULL to check if string is empty, in PostgreSQL the same expression is FALSE for empty string and TRUE for NULL values.

Synonym

Oracle uses CREATE SYNONYM statements to access remote objects. PostgreSQL does not support CREATE SYNONYM, it can set up remote definition via SET search_path. For example, in Oracle:

CREATE SYNONYM loc.tbl FOR rem.tbl;

In PostgreSQL the equivalent is:

SET search_path TO 'loc.tbl';

Sequences

Although syntax of creating sequences in Oracle and PostgreSQL is quite similar, there is minor difference in getting next value. In Oracle:

Seq_name.nextval

The same in PostgreSQL is:

Nextval('Seq_name')

Oracle to Postgres Migration Tools

There are a number of free and commercial tools that can help customers automate Oracle to Postgres database migration:

Ora2pg is a handy migration tool that connects to an Oracle database, extracts schemas and tables and generates SQL scripts that can be loaded into Postgres.

Ora_migrator is an extension that uses foreign data wrapper approach to extract data from an Oracle database.

Orafce is an extension that helps to implement Oracle built-in functions in Postgres. It also supports Oracle date formatting and resolves other specific data types issues.

Amazon AWS Schema Conversion Tool (AWS SCT) - automates schema analysis and conversion. It can also recognize embedded SQL statements and convert them as part of a database-schema-conversion project.

Oracle to Postgres converter an easy-to-use migration tool having intuitive wizard-style interface. It can completely automate migration of schemas, data, indexes, constraints and views from Oracle to Postgres.

Have questions? Contact us