BONUS SECTION: Oracle to EDB Postgres Advanced Server Migration Percentages
Migrating from Oracle database to PostgreSQL is a worthwhile endeavor. It can save thousands of dollars per year – or even per month! – and gives you access to one of the most tried and tested databases. Getting from Oracle to PostgreSQL isn’t without its challenges though.
We’ve done our best in the Migration Portal to streamline the process of getting from Oracle database to EDB Postgres Advanced Server (EPAS). The portal shows you the compatibility percentage of your current database schema with EPAS. While this number is a good starting point, the reality is that you’ll need to drill down into the details for a better understanding of how complex your migration will be. That is, a compatibility score of 95% isn’t necessarily easier to migrate than one with a score of 70%, and vice versa. But how can that be?
Let’s have a look.
How is Compatibility Measured?
Migration Portal calculates the compatibility percentage by counting the number of Data Definition Language (DDL) constructs in the schema that you upload, and making that the denominator. In the numerator, we place the number of DDLs in the schema that are compatible with EPAS. This gives us the percentage of DDLs in the schema that are EPAS-compatible.
Compatibility % =
# of Compatible DDLs
__________________ x 100%
Total # of DDLs
We have a large number of what we call repair handlers, which change Oracle syntax into equivalent EPAS syntax. The compatibility percentage is calculated after we make those transformations.
Variations in the Complexity of Incompatibilities
Sometimes an error in a DDL is caused by a dependent object that’s missing. This is usually pretty simple to fix: include the dependent schema, and the problem goes away. That could take 5 minutes or less if the dependent schema doesn’t have errors or incompatibilities. However, sometimes an error (or incompatibility) is caused by an issue that takes more time to resolve. In addition, a simple-to-fix issue may occur hundreds or thousands of times, increasing the amount of time it takes to fix. The below examples should be taken with a grain of salt, because each database is unique. With those caveats stated, below are some examples of incompatibilities and errors that tend to be simple to fix, and some that tend to take more time.
Examples of straightforward issues:
- Missing dependent schema/object: dependent schemas must be included
- BITAND function: the syntax must be modified slightly
- BYTE keyword: remove the use of BYTE keyword
Examples of more complex issues:
- Syntax error at or near “<>”: sometimes the underlying issue is easy to fix, but it usually requires more research and troubleshooting to understand the root cause
- DBMS_XMLDOM usage: Oracle’s XML document object model requires design work, implementation changes, and testing to implement similarly in PostgreSQL
Future Enhancements & Support for Migration Projects
As we continue to invest in the Migration Portal, we’re considering more advanced ways to show compatibility. We’d like to be able to show each underlying incompatibility, give you a better understanding of how complex each issue is, and even provide a rough estimation of how long we think it might take to fix. That would make it easier for you to plan migration projects and decide whether or not you want help migrating.
You can read more about how to generate your own migration report here in our docs, or utilize the in-portal wiki once you’ve registered and logged in. For an in-depth analysis of migration efforts, you can get help from our professional services team. Our PostgreSQL experts come from a variety of backgrounds and can help assess effort to migrate not only from Oracle but also Sybase, MySQL, and pretty much any other database you can think of. They offer migration assessments and migration assistance, and can help you strategize which databases to move first.
In time, we hope to bring all of their expertise right into the migration portal. That’s an ongoing effort here at EDB, and naturally takes a lot of effort. For now, you can always reach out to us for expert guidance by reaching out to migration-services@enterprisedb.com.