Oracle to PostgreSQL migration with ora2pg, Part I

From postgresql.org: 

PostgreSQL has earned a strong reputation for its proven architecture, reliability, data integrity, robust feature set, extensibility, and the dedication of the open source community behind the software to consistently deliver performant and innovative solutions.  

As PostgreSQL now offers even more “enterprise” features than ever before, it has become the preferred target database for Oracle stack modernization. The similarities between both engines are endless. Of course, one must remember that they remain two different database engines, but there are more similarities between these two than any other database on the market.  

The other factor that raised the curve of migrations substantially is rise of Cloud services, especially so-called PaaS (Platform-as-a-Service) services. Having backups, High Availability, monitoring and security tools built into the service eased a transition to a new technology for a lot of Oracle DBAs. Configuring a backup strategy is just few clicks away, and High Availability comes out-of-the-box with each deployment of Azure Database for PostgreSQL - Single Server. 

But how do you get there? How do you make your database that has been running on Oracle for years run on PostgreSQL, whether it is Cloud or on-prem installation? How long will it take? And how difficult will the process be? 

The Assessment 

Well, it depends. And it depends on many factors. But the good news is: there is a free, open-source tool called ora2pg that you can use, and it will give you a hint about the effort needed and level of complexity you are dealing with. You can find an example assessment report here: https://ora2pg.darold.net/report.html. As you can see, each object type is listed with a count of objects that belong to each type. Where objects cannot be converted automatically, the manual effort is estimated, e.g. a function get_tab_tf will require probably 15 minutes of manual adjustments. 15 minutes because the cost in the report is 3 and the default unit (meaning 1) for the assessment is 5 minutes of Postgres expert work: 3x5 minutes give you 15 minutes. But be very cautious about these numbers. Setting a budget for the migration project based on those numbers might not be the best idea ever. It will give you a high-level estimate, that’s for sure, but it might not be representative for more complex databases with a lot of logic within (by logic I mean packages, functions, procedures etc.). It could give you both: much higher as well as much lower estimates. From my experience the margin could be counted in months.  

Installation 

Don’t even try to install ora2pg from scratch. Please don’t! It will take you ages. Even experienced DBAs and developers spend at least one day struggling with the installation. A much quicker way is to use the scripts provided by Microsoft Data Migration Jumpstart Engineering Team, found on Github, or use one of the docker images from Docker hub. The How-To Guides are here (for the former) and here (for the latter).

If you feel like you want to try the tool out straight away I've created a simple, pretty much copy&paste workshop which you can find here: https://github.com/AwdotiaRomanowna/ora2pgWorkshop.