Convert Oracle Database to PostgreSQL
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 the powerful features of Oracle database such as:
- An extensive backup
- A multi-level compression
- A totally flexible storage customization
However, the capabilities of PostgreSQL are much closer to Oracle than other DBMSs 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
- the extremely sophisticated locking mechanism
The considerations above make PostgreSQL ideal choice for most complicated database projects which demand high performance and data integrity. This is the reason why many database specialists consider PostgreSQL as best alternative to Oracle.
Migration from Oracle to PostgreSQL
Database migration from Oracle to PostgreSQL is usually occurred according to the 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 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. It is connected to the Oracle database as follows:
sqlplus username/password@database
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 the following sequence of SQL 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 loaded into the destination PostgreSQL table via the “COPY” statement:
COPY <table name> FROM <path to csv file> DELIMITER ‘,’ CSV;
If you encounter “Permission denied” error after you ran this statement, you can try out this common “\COPY”.
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;
Conversion Tools for Oracle to PostgreSQL Migration
From this brief description of database migration from Oracle to PostgreSQL you may see that it 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. Another approach eliminating all specified difficulties and risks is to use special software tools that can smoothly migrate your database from Oracle to PostgreSQL with a click of a few mouse buttons. Make sure the migration tool you choose supports generic database objects:
- Table definitions
- Data
- Indexes and constraints
- Foreign keys
- Views