Case Study: Migrating from Oracle to PostgreSQL with Ora2PG - DEEP
Case Study: Migrating from Oracle to PostgreSQL with Ora2PG
29 November 2021

Why Migrate to PostgreSQL? What Are the Advantages and Limitations of Ora2Pg? Our Expert, Bertrand, Shares His Insights and Client Experience.
Why Migrate from Oracle to PostgreSQL?
The client, a large automotive maintenance and repair group, aimed to reduce its long-term costs. PostgreSQL also allows for increased flexibility.
After studying its IT assets (servers, databases, etc.) in terms of technologies, volumes, and versions, it was decided to migrate to the AWS cloud. Indeed, in the client's case, migrating to PostgreSQL also enabled long-term savings.
Migration Context: 14 Custom Applications
Of course, any migration project begins with an architecture phase followed by the implementation phase.
The production environment did not change infrastructure to limit migration risk (physical machine to physical machine), while other environments were moved to the cloud.
In total, the project involved migrating 14 custom applications from Oracle to PostgreSQL, including more than 1400 functions and procedures for the main application.
All these applications operated on the same principle: a DAL (Data Access Layer) and procedures for each database call. No queries were managed within the applications. Therefore, it was decided not to modify the applications but only the connection part. This is a rather rare case for a migration from Oracle to PostgreSQL, but it was possible thanks to this application architecture.
The bulk of the migration project consisted of application work at the database level, namely code rewriting, technical and functional validations, and performance validations.
Methodology for Migrating from Oracle to PostgreSQL
To match the various characteristics of the project, we opted for a methodology summarized in 5 steps:
- Use of Ora2Pg: A utility that estimates the time required for the migration and converts Oracle syntax to PostgreSQL.
- Proof of Concept (PoC): Migrating a "small" representative application to PostgreSQL.
- Manual Correction: Fixing functions with errors using replayable scripts.
- Automation of Certain Corrections: Necessary because PostgreSQL and Oracle drivers do not work the same way. For example, changing a refcursor return to a record return. While it would have been possible to make the modification on the application side, the decision was made to shift the modification cost to the database side.
- Implementation of Automated Non-Regression Tests: For all procedures.
Example of Migrating an Application via Ora2Pg
- Initialization of an Ora2Pg project.
- First dry run migration of the application using Ora2Pg.
- Correction of functions, procedures, views, and flows that do not compile, and fixing tables and indexes with errors.
- Automated unit tests.
- Correction of functions, procedures, views, and flows with errors or generating timeouts due to performance issues.
- Non-regression and performance tests.
- Correction of reported issues (errors not caught by unit tests, performance problems, etc.).
- For simpler applications: Stopping production and then replaying the migration via Ora2Pg.
- For the main application: Production is regenerated only with the table structure. Unmodified data is migrated in advance. On the day of migration, production is stopped, remaining data is migrated, sequences are retrieved (via Ora2Pg), and indexes and constraints are recreated.
Experience Feedback on Migrating from Oracle to PostgreSQL Using Ora2Pg
Our experts have listed various positive and negative points regarding the use of Ora2Pg, as well as tips for migrating more smoothly from Oracle to PostgreSQL.
These are not exhaustive lists but rather feedback from the migration carried out for our client, a specialist in automotive repair.
Advantages of Ora2Pg
- Data migration did not pose any problems, and the various conversions went smoothly.
- A significant number of procedures could be converted and function directly without major modifications.
Limitations of Ora2Pg
- Ora2Pg is not a panacea. We encountered several limitations with the tool: no conversion of character strings, bugs with variables ending in "return," about 25% manual corrections (more or less complex) on procedures (for example, the MERGE statement must be transformed into INSERT ON CONFLICT DO UPDATE).
- Ora2Pg only modifies data and code in the database. It does not touch the application or flows outside the database. In our case, the flows were not too numerous (about 50), but their migration was almost as time-consuming as that of the database.
Points of Attention When Migrating from Oracle to PostgreSQL
Unsurprisingly, some Oracle features simply do not exist in PostgreSQL. This needs to be considered in advance. In our case, this only affected a small number of procedures: 4, of which 2 were no longer in use.
Specificities of PostgreSQL
Each technology has its own specificities. Here are some cases we encountered:
- One of the most frequent issues is that PostgreSQL is very strict on typing; you cannot assign a char to a varchar or an integer to a numeric, which can cause numerous errors.
- Another issue due to strong typing in PostgreSQL: we had to add a cast for all occurrences of clock_timestamp() (equivalent to sysdate), which by default is a timestamp with timezone, whereas a timestamp without timezone was desired.
- Another typing issue: a default type is given for constants in select clauses, which is not always the desired type, so casts must also be added at this level (for example, null is text by default).
- There were also performance issues with complex queries (especially flows/batches). The PostgreSQL query planner is less efficient than Oracle's, particularly regarding the integration of inline views in the query. Additionally, there are no hints, so functions must be used on columns to disable certain indexes, etc.
- The client chose to use only the numeric type for numbers to simplify the migration. This may not be the best choice (performance on operations with this type of field is worse than with integers).
- There are no packages in PostgreSQL. Packages are transformed into schemas containing functions and procedures, so all global variables/constants must be replaced.
- Testing and correcting performance issues on flows can be very time-consuming (e.g., 6 days on a single flow).
- When switching to PostgreSQL, the monitoring solution had to be adapted, requiring tools to identify the most resource-consuming queries (Dynatrace / DataSentinel / pganalyze).
Tips for Easier Migration
Three tips shared by our expert regarding the use of Ora2Pg:
- Unit tests for functionalities were implemented for the main application, allowing errors to be quickly identified following the initial migration via Ora2Pg.
- It is important to plan performance tests on essential functionalities of the application. In our case, significant differences were found and corrected.
- With Ora2Pg, it is possible to copy data, including some in advance, if you can define unmodified data in the database.
Conclusion: Should You Use Ora2Pg?
For simpler applications, Ora2Pg did 80% to 100% of the work, and there were almost no issues. However, the migration of the main application was postponed several times to conduct performance and non-regression tests.
Regarding the migration of the largest application, some functionalities heavily loaded the database for a few hours following the migration. Additionally, the number of sessions on pgpool (connection proxy) was insufficient, causing blockages. Ultimately, all critical issues were quickly resolved, and significant performance problems were addressed on the first day.
The performance of the application and flows on PostgreSQL met the client's expectations. This does not mean everything is as fast as on Oracle. Some flows or procedure/function calls are slower (up to 50% slower), while others are naturally faster or optimized (up to 30% faster). Nevertheless, overall performance is balanced between the old and new infrastructure.
In conclusion, Ora2Pg was a tool that enabled us to successfully carry out this migration, but it required the involvement of various colleagues to make the migration a success. Additionally, the tool helps but does not do everything, and a particular effort was invested in enabling automated unit/performance tests to facilitate the migration.
Contact us
Do you have any questions about an article? Do you need help solving your IT issues?
Contact an expertOther articles in the category Data & AI
Federated Governance: A Key Pillar for Successful Data Mesh Implementation
Learn why federated governance is a critical organizational pillar in a Data Mesh architecture. A strategic issue for data-driven companies.
Published on
12 December 2023
Top 10 Databases of 2020: Popularity Ranking
Explore the ranking of the top 10 most popular databases in 2020 according to DB-Engines, including Oracle, MySQL, and Microsoft SQL Server.
Published on
14 November 2023








Our experts answer your questions
Do you have any questions about an article? Do you need help solving your IT issues?