Cheat Sheet: 5 Things to Know Before Migrating from Oracle to PostgreSQL
Author: Will Fike | 3 min read | 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.
