Cheat Sheet: 5 Things to Know Before Migrating from Oracle to PostgreSQL

Author: Will Fike | | April 9, 2019

Many organizations are migrating from Oracle to open source cloud platforms to unlock advantages. PostgreSQL is among the world’s most advanced open source database platforms.

The benefits of migrating to PostgreSQL include:

Lower costs
No licensing requirements
Flexible architecture
Open source add-ons

Datavail has helped countless customers with these migrations. To avoid unnecessary time and complexity on the road to the cloud, keep reading to find out the five most important things to know before you get started.

1. Migrate the Schema

Before you commit to migration, use a tool like ora2pg to migrate your Oracle schema to a PostgreSQL server. If you discover problems with migration, make changes on the Oracle server for a smoother transition.

2. Export and Load Data

After migrating the schema, export the data and load it into the PostgreSQL server. Look for issues with non-printable values embedded in text data or incompatibilities between Oracle and PostgreSQL character sets.

3. Review Packages and Stored Procedures

Spend time reviewing your packages and stored procedures to identify Oracle-specific features. To the extent possible, modify the code to use standard SQL, like replacing decode() functions with SQL case statements.

4. Document Procedures

It’s not always possible to modify the code for packages or stored procedures which rely on Oracle-specific features. At a minimum, document these procedures to provide a realistic scope for code modifications and testing.

5. Review Dates and Times

Review how you manage date and time information in Oracle. Modify the code to use standard SQL statements and data types.

Streamline Your PostgreSQL Migration

Want to make the move to PostgreSQL, but don’t have the time? Datavail can help you make the move. Learn how we helped another customer quickly transition from Oracle to PostgreSQL, saving hundreds of thousands in licensing and gaining an extensible database along the way.

Going Open-Source: Why and How to Migrate from Oracle to PostgreSQL

In this white paper, we’ll discuss the benefits of PostgreSQL as an alternative to Oracle, reasons to migrate from Oracle, and some important post-migration considerations so that you have all the information to make the right choice for your organization.

Blog Author

Will Fike

Oracle/Postgres Developer

Will is an Oracle/Postgres Developer at Datavail. He brings 27 years of IT experience with system design, development, and testing in a variety of languages and operating systems. He’s been involved in project environments of ranging from multi-national Fortune 500 companies to venture startups. Will’s recent success at Datavail include multiple migration projects that converted a complex, multi-terabyte of a database from the Oracle platform to the PostgreSQL platform. He brings value with his ability to push the limits of technology to create new products and services for Datavail’s customers.