Oracle is the most popular database in use today. It’s also one of the most expensive databases to purchase and maintain. Licensing fees, overall complexity, and support costs all add up, and together, they are prompting many organizations to leave Oracle behind for open-source alternatives.
PostgreSQL is an ACID-compliant, highly functional database that has lately matured to become one of the most popular open source databases on the market. It’s especially easy to see its appeal when you consider that a move to PostgreSQL eliminates licensing costs and can reduce support costs by as much as 80 percent.
While PostgreSQL is a solid choice for migration from Oracle, many businesses have trouble getting such a migration project off the ground. Indeed, one of the most commonly cited challenges when migrating from Oracle to PostgreSQL is the difficulty involved in preparing engineering teams for the migration.
Enterprises are right to be cautious. Equipping your staff so it can meet the technical and procedural challenges that come with moving your database environment to PostgreSQL is a key step to ensuring a successful migration. Here’s how to get started.
Prepare your personnel
The first obstacle you are likely to encounter in migration from Oracle to PostgreSQL is your existing staff’s know-how. Migrating and maintaining a database in PostgreSQL will absolutely require them to learn new skills. More specifically, development and operational teams will almost undoubtedly need PostgreSQL training that includes development, deployment, tuning, security, data governance, and monitoring. While some team members will welcome these new challenges, others will probably be resistant.
You can ease the transition to PostgreSQL by encouraging your team to embrace the progressive culture of open source, which emphasizes innovation, collaboration, and transparency – qualities that developers tend to embrace. You should enlist a core group of your more eager team members to champion the move and lead the shift. Reluctant team members may also be inspired by the fact that successful modern enterprises increasingly rely on open source software, and they will quickly come to see that acquiring new PostgreSQL skills can offer at least as many career benefits as their Oracle knowledge provides.
Assess the Oracle environment
In their zeal to reap the benefits of PostgreSQL as quickly as possible, some companies are tempted to start by moving their most mission-critical apps and databases to the new system. But that’s courting disaster when you’re learning new technology.
Successfully migrating from Oracle to PostgreSQL calls for a more measured approach. Start by evaluating all your Oracle databases to determine the potential difficulty in their migration – and I don’t mean scouring the code base. This assessment should include process and personnel considerations, as well as technical ones. Plan on migrating the easiest environments first. This will allow the team to get a few quick wins under its belt while gaining experience that will give its members more confidence when they move on to more complex, longer-term projects.
It’s a good idea to choose applications that still have some of the original development team in place, as their institutional knowledge can help ensure early migration success. That in-depth knowledge surrounding how the database was architected and how the applications using it were built can be indispensable if any specific dependencies on Oracle features crop up. Systems that already have robust testing and QA procedures in place are also good choices, as those typically make it easier to validate migration results of the transition without incurring additional costs to build new checks.
In the early stages of migration, it’s essential to avoid any third-party applications that don’t support PostgreSQL as well as those that rely heavily on unique Oracle database features like write scalability of Real Application Clusters (RAC) or flashback queries. You should also determine how much Oracle PL/SQL business logic and how many utility functions are in the database being migrated. That will all have to be re-written; for any that can’t be, the team will have to come up with a workaround. In either case, this kind of effort will impact workloads and your migration timeline.
Choose the right tools
As important as which applications you move is how you move them. While you can certainly perform a manual migration, another option is to leverage automated tools (such as ora2pg or ora_migrator) that evaluate and translate code and/or suggest alternatives in PostgreSQL. These tools can significantly shorten time-to-value.
If you decide to use automated tools, it’s important to evaluate if they are only converting schema, tables, indexes, and data, or if they are more advanced and able to include the conversion of procedural language and SQL code. Choosing tools that make developers and database administrators more comfortable working with PostgreSQL can help shorten timelines and prepare the staff for maintaining the new databases after deployment.
Anticipate business impacts
Lastly, you need to anticipate how the migration will impact other business processes and personnel. Making sure the project timing doesn’t interfere with other business initiatives will be critical, as will determining how much downtime your business can withstand during the migration.
You’ll also need to consider how the Oracle and PostgreSQL databases will integrate and exchange information with other parts of the enterprise during the migration period—particularly important for longer migrations—and how you will integrate with other DevOps environments, monitoring and alerting functions, and other IT infrastructure.
Set your business up for success
There are several challenges when migrating from Oracle to PostgreSQL, but enabling your team with the necessary skills, tools, and knowledge is the best way to ensure success. But remember that you don’t have to do it alone: Making sure that PostgreSQL experts are available to support your team while they get up to speed on the new technology will go a long way toward streamlining the migration process.
This post ran initially on the Stratoscale website written by Doug Shelley.
