Oracle is a very sophisticated and powerful object-relational DBMS which is capable of handling enormous enterprise scale databases. However, great weakness of that database management system is high cost of the ownership due to tough licensing terms. This disadvantage leads many clients to database migration from Oracle to other database management systems.
Why You Should Pick PostgreSQL Over Oracle?
DBA or another person responsible for migration should always remember that a new system should provide similar features before moving to other DBMS. Obviously, no other system can have all powerful features of Oracle database such as:
- An extensive backup
- A multi-level compression
- A totally flexible storage customisation
However, capabilities of PostgreSQL are much closer to Oracle than other DBMS since it also combines object-oriented and relational features and provides some other advanced features:
- asynchronous replication
- multi-version concurrency control system
- nested transactions
- point-in-time recovery
- extremely sophisticated locking mechanism
Those benefits explain why many organizations migrate their databases from Oracle to Postgres. Obviously, PostgreSQL is the ideal choice for the most complicated database projects which demand high performance and data integrity among free relational DBMS available on the market.
Migration Steps
Migration from Oracle to Postgres is usually occurred according to extract-transform-load (ETL) approach that involves the following steps:
- Export Oracle database table definitions to “CREATE TABLE” statements
- Ensure that the SQL-statements are compatible with PostgreSQL format and import it to the destination server
- Export the Oracle data into an intermediate storage like CSV files
- Convert it to the target format (if needed) and then import in PostgreSQL
- Export the Oracle views, triggers, stored procedures and the functions into SQL statements and plain text source code
- Transform all the statements and code as per PostgreSQL syntax and then load to the target server
Table Definitions
For all examples of Oracle statements and queries SQL*Plus is used as default SQL command line client. All Oracle tables available for the specified user are listed as follows:
SQL> select table_name from user_tables;
Oracle table definition is explored via these SQL statements:
SQL> set long 1000
SQL> set pagesize 0
SQL> select DBMS_METADATA.GET_DDL(‘TABLE’,'<TABLE NAME>'[,’SCHEMA’]) from DUAL
Before loading into PostgreSQL database, the resulting data definition language (DDL) script must be corrected as follows:
- Oracle specific statements at the end of table DDL must be removed (starting straight from “USING INDEX PCTFREE…”) as it is not supported by PostgreSQL
- Convert every data types into PostgreSQL equivalents. You can find safe mappings for basic types below
| Oracle | PostgreSQL |
| BFILE | VARCHAR(255) |
| BINARY_FLOAT | REAL |
| BINARY_DOUBLE | DOUBLE PRECISION |
| BLOB | BYTEA |
| CHAR(n), CHARACTER(n) | CHAR(n), CHARACTER(n) |
| CLOB | TEXT |
| DATE | TIMESTAMP |
| DECIMAL(p,s), DEC(p,s) | DECIMAL(p,s), DEC(p,s) |
| DOUBLE PRECISION | DOUBLE PRECISION |
| FLOAT(p) | DOUBLE PRECISION |
| INT, INTEGER | INT, INTEGER |
| LONG | TEXT |
| LONG RAW | BYTEA |
| NCHAR(n) | CHAR(n) |
| NCHAR VARYING(n) | VARCHAR(n) |
| NCLOB | TEXT |
| NUMBER(p,0), NUMBER(p), 1 <= p < 5 | SMALLINT |
| NUMBER(p,0), NUMBER(p), 5 <= p < 9 | INT |
| NUMBER(p,0), NUMBER(p), 9 <= p < 19 | BIGINT |
| NUMBER(p,0), NUMBER(p), p >= 19 | DECIMAL(p) |
| NUMBER(p,s) | DECIMAL(p,s) |
| NUMBER, NUMBER(*) | DOUBLE PRECISION |
| NUMERIC(p,s) | NUMERIC(p,s) |
| NVARCHAR2(n) | VARCHAR(n) |
| RAW(n) | BYTEA |
| REAL | DOUBLE PRECISION |
| ROWID | CHAR(10) |
| SMALLINT | SMALLINT |
| TIMESTAMP(p) | TIMESTAMP(p) |
| TIMESTAMP(p) WITH TIME ZONE | TIMESTAMP(p) WITH TIME ZONE |
| VARCHAR(n) | VARCHAR(n) |
| VARCHAR2(n) | VARCHAR(n) |
| XMLTYPE | XML |
Data export
The source Oracle data can be exported into a comma separate values (CSV) format via Oracle SQL Developer, SQL*Plus or another client tools. This is how to export through Oracle SQL Developer:
- Launch the tool and enter the appropriate connection settings to connect to the source database
- On the SQL sheet write the statement SELECT * FROM {table name} and execute it
- Open the drop-down menu via right-click on the query result and select “Export” item
- Select CSV format in the Export Wizard and choose objects to export
Each CSV file can be imported into the previously created PostgreSQL table via the “COPY” statement:
COPY <table name> FROM <path to csv file> DELIMITER ‘,’ CSV;
The Indexes
Use the following SQL statement to get all indexes that belongs to Oracle table “mytable”:
SQL> select * from all_indexes where table_name = ‘<TABLE NAME>’;
Do not forget that Oracle treats all database object names in upper case by default. Lower case and other case sensitive options may be required by enclosing object name in quotes in SQL statements.
Definition of particular Oracle index can be extracted via the following SQL statements:
SQL> set long 1000
SQL> set pagesize 0
SQL> select DBMS_METADATA.GET_DDL(‘INDEX’,'<INDEX NAME>’) from DUAL;
Migration Tools
As you may see, Oracle to Postgres database migration is a very sophisticated procedure. Manual migration requires a lot of efforts and it is connected with risk of data loss or corruption due to human factor.
Fortunately, there are free tools to automate this database migration routine. One of such solutions is ora2pg, open-source tool that support Oracle schemas, views, sequences, indexes, constraints, SQL and partially PL/SQL code. It connects to the source database, automatically recognizes all supported objects, extracts the appropriate definitions and generates SQL scripts to load into the target Postgres database.
Being a command line script written on Perl, ora2pg requires some prerequisites:
- Oracle instant client or database server
- Perl version 5.10 or higher
- Perl modules DBI, DBD::Oracle and DBD::Pg
Ora2pg requires some efforts and skills to install and configure the application. So, it could be a hard challenge for those users who not familiar enough with the command line.
Another approach eliminating all the specified difficulties is to use commercial software that can smoothly migrate your database from Oracle to Postgres with a click of a few mouse buttons. Make sure the migration tool you choose supports generic database objects such as table definitions, data, indexes and constraints, foreign keys and views.
One of such products is supplied by Intelligent Converters, a software company focused on Oracle to Postgres migration and synchronization for years. Besides the basic features, the program offers filtering data to migrate via SELECT-queries. This feature can also be used to rename columns or tables in the PostgreSQL database and to merge multiple tables into a single one.
The Oracle to Postgres converter provides basic implementation of synchronization capabilities as a combination of inserting Oracle rows missing in the target table and updating Postgres rows with Oracle data. To run the synchronization routine source and destination tables must have equal structures and have primary key or unique index.
