Select Page

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

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.


Read This Next

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.

12c Upgrade Bug with SQL Tuning Advisor

This blog post outlines steps to take on Oracle upgrade 11.2 to 12.1 if you’re having performance problems. Oracle offers a patch and work around to BUG 20540751.

Megan Elphingstone | March 22, 2017

Scripting Out the Logins, Server Role Assignments, and Server Permissions

Imagine over one hundred logins in the source server, you need to migrate them to the destination server. Wouldn’t it be awesome if we could automate the process?

JP Chen | October 1, 2015
sharepoint ideas

9 Awesome Things You Can Do with SharePoint

This blog post discusses out-of-the-box uses for SharePoint that you can execute in just a few clicks without using any code.

Amol Gharat | May 23, 2017

Work with Us

Let’s have a conversation about what you need to succeed and how we can help get you there.

CONTACT US

Work for Us

Where do you want to take your career? Explore exciting opportunities to join our team.

EXPLORE JOBS