There could be many reasons to migrate from Oracle to Postgres. It might be to reduce cost, simplify use, prevent vendor lock-ins, and for open source DBMS.
This post describes the overall approach for migrating an application from Oracle to PostgreSQL.
What Does Migration to Postgres Mean?
Migration means moving definitions, data, stored procedures from one platform to another, and making application changes. Migration to Postgres will undergo different phases—like picking the right schema, compatibility check, convert incompatible objects, functional & performance testing, data migration, and post-migration check.
Oracle to PostgreSQL Migration Phases
- Assessment - This is the first step in planning the migration and analyzing the application to estimate how easy or difficult it will be to migrate it from Oracle to PostgreSQL. In this phase, a thorough analysis should be conducted on technology related issues and to evaluate the compatibility of client, application server, data access and database features.
- Schema Conversion - PostgreSQL supports ANSI SQL standard SQL syntax and datatypes, whereas Oracle does support the same standard; additionally, with some non-ansi sql syntaxes. Using tools, unsupported objects should be identified and they have to be converted manually with PostgreSQL supported syntax or feature workaround.
- Functional Testing - Before functional testing, a recommended approach is to load some sample data into PostgreSQL from source database development or testing environment where there’s a production sample data and then setup application connection using appropriate data access(drivers). After application connection to the database, allow it to do full functional testing on the converted objects with DMLs.
- Performance Testing - Performance testing is important in the migration phase, because some of the Oracle built-in transaction or features functionality might be slightly different in PostgreSQL and application might see some difference. In this phase, we can capture all those differences and fix it at application, data access(drivers) and database level with proper tuning.
- Data Migration - There are different approaches for data migration and tools available in the market. Usually, they are classified in three ways
a) Snapshot - Data moved at once
b) Snapshot in Parallel - Data moved in chunks(schema or table)
c) Change Data Capture(Replication) - Data loaded continuously.
For approaches a) and b) we need an application downtime because data is written one time from Oracle to PostgreSQL, whereas in approach c) data is loaded continuously and there is partial to small downtime window. Pick the right data migration approach that fits in the downtime window.
- Cutover - Once the data is in sync between Oracle to PostgreSQL, carefully switch the application to PostgreSQL.
Key Differences (Oracle vs. PostgreSQL)
- Transactions - In Oracle, the transactions are with auto BEGIN and need an explicit COMMIT statement to end. In PostgreSQL, we need to manually enable the transaction block with BEGIN and close with END/COMMIT/ROLLBACK. Also, PostgreSQL does not allow transaction control (COMMIT/ROLLBACK) inside the PL/PgSQL, so they have to be handled in BEGIN..EXCEPTION...END.
- Data Types - There are data types differences between Oracle vs PostgreSQL, and they should be handled with extra care and mapped to the right data type in PostgreSQL.
|varchar,varchar2, nvarchar, nvarchar2||varchar or text|
|clob, long||varchar or text|
|number||bigint, int, smallint, real, double precision||Limited control of scale|
|binary_integer, binar_float||integer, float|
|blog, raw, long raw||bytea||Size limitation|
|Date||Date or timestamp||Postgres, returns timestamp with date|
|Timestamp with timezone||Timestamptz|
- Identifiers - Oracle identifiers are in UPPER CASE unless quoted, however PostgreSQL converts them to LOWER CASE unless quoted. Identifiers in PostgreSQL are case insensitive and in post-migration application should test it thoroughly.
- NULLs vs Empty String - In Oracle, empty string is equal to NULL, however in PostgreSQL empty string is different to NULL as it follows ANSI-SQL standards.
- Objects - Some of the Oracle objects are not supported in PostgreSQL like Synonyms, DUAL table and Packages.
Oracle to PostgreSQL Incompatibility Challenges
In this section, we will discuss some of the Oracle functionalities and support in PostgreSQL, also we will discuss other challenges we might face while migrating from Oracle to PostgreSQL. All the challenges and limitations identified in PostgreSQL, those objects should be rewritten manually with a workaround if they exist.
- PostgreSQL has a limit on MAXVALUE upto 2^63
- No support NOCACHE & NOORDER
- .nextval, .curval should be converted to nextval() & currval()
- No Synonyms in PostgreSQL
- Oracle User Defined Types - Nested and Multi-Level TYPE should be converted to ARRAYs
- No KEEP, CACHE, IOT, EXTERNAL, COMPRESSED, CLUSTER, NESTED tables in PostgreSQL, using extension some of the functionalities can be achieved(pg_prewarm, file_fdw and so on)
- Most of the indexes are supported in PostgreSQL, except Reverse Key and Global Indexes.
- No Packages, using SCHEMA architecture in PostgreSQL, functions/procedures can be grouped. Use “orafce” library, which supports some of the standard packages or EDB Postgres Advanced Server which has built-in Packages.
- Accessing Remote Objects, DBLINK module or Foreign Data Wrapper(Oracle_fdw) can be used to access any other database.
- No optimizer hints
- Hierarchical Queries (START WITH… CONNECT BY) - Need to convert using CTE or Tablefunc module.
- ROWNUM - Use LIMIT clause in the query(SELECT * FROM table LIMIT 10)
- ROWID - Its ROW Physical Address in Oracle (CTID in PostgreSQL, use ROW_NUMBER OVER() windows function)
- Usage of Oracle Built-in functions
- NVL - Use COALESCE()
- DECODE - Use CASE clause
- SYSDATE - Use current_timestamp
Migrating Oracle database to standard PostgreSQL, we need to handle all the incompatibility challenges that we have discussed so far, however EDB has developed database compatibility for Oracle based on popular features across many versions of Oracle.
EDB Postgres Advanced Server databases behave or produce the identical result as they would in Oracle. It natively supports some of the Oracle functionalities in the database which makes migration exercise easy to migrate the existing Oracle Database Objects as is and work with similar functionality and performance.
- What are all natively supported in EDB Postgres Advanced Server ?
- All Data types (except BINARY_FLOAT, BINARY_INTEGER,.,..)
- Sequences/Synonyms/Database Links
- Partition/Subpartition like Oracle Syntax
- Some of standard Oracle built-in Packages and Functions
- User defined Packages/Procedures/Type(UDT)
- Supporting Drivers (JDBC, ODBC, .NET)
EDB Postgres Advanced Server database is a license database, however its free to use, try to give a shot in parallel in your migration exercise.
Oracle to PostgreSQL Migration Tools
Most of the tools support assessment and conversion, and some of them are free and others free to use models. Below are the tools you can try for free for converting the Oracle objects to PostgreSQL.
- EDB Migration Portal
- EDB Migration ToolKit
The below tools comparison matrix will give you a high level picture on what they can do and how far they get you in the migration exercise to a specific target.
Watch our on-demand webinar, Oracle to Postgres Schema Migration Hustle, for a deep dive.
For more information on data migration from Oracle to Postgres, check out our free Oracle to Postgres Migration Tool!
Raghavendra Rao is a Managing Consultant in Professional Services Division in EnterpriseDB(Headquarters). He has been working with PostgreSQL since 2009 and over 20 years of working experience with relation database systems. He architects, migrates, manages, consults, speaks, teaches & blogs about PostgreSQL and provide enterprise solutions. Prior to EDB, he has worked with companies to model data, visualize and deliver critical information to make informed business decisions; using the relational database systems. Raghavendra holds a Masters Degree in Computer Information Systems from the University of Harvard. He holds several certifications including EDB PostgreSQL Certified Professional and Certified Training Instructor.