Porting applications from Oracle to PostgreSQL #

Presentation #

This document is a migration guide from an Oracle database to PostgreSQL. The first part is about differences between Oracle and PostgreSQL, the second about rewriting the database’s schema, the third about rewriting queries, and the fourth (and last) about porting Oracle’s PL/SQL procedures to PostgreSQL’s PL/pgSQL functions.

A migration project is often separated into several phases:

• evaluating database complexity with a cost assessment tool;
• porting the database, with its data;
• porting the program (queries, reports, stored procedures);
• software testing: results comparison, regression testing, benchmarking, etc…

The database complexity evaluation consits in estimating the effort represented by the database migration, its data and its stored procedures. Ora2Pg provides a common way to produce a cost assessment report for each candidate database.

The porting of the database with its data is also done with Ora2Pg. As it has its complete user’s manual, the subject won’t be covered by this document. Nevertheless, some of conversions performed by Ora2Pg are described in this guide.

The porting of the application is a very delicate phase. Indeed, the porting of an application’s queries and stored procedures can be quite complex because of each databases’ specificities. This porting guide’s purpose is to ease this porting.

Software testing is a crucial phase of such a project. Test data has to cover a very large spectrum, to validate the result of each query and function called by the program, if possible by a direct comparison between Oracle and PostgreSQL.

Special thanks #

This guide is a collective work that aims to be open to as many people as possible. We warmly thank here all the people who contributed directly or indirectly to this work, in particular: Gilles Darold, Thomas Reiss, Marc Cousin, Jean-Paul Argudo, Philippe Beaudoin, Florent Jardin, Benoit Lobréau.