Open-source software continues to be on the rise. Not only do consumers use very powerful applications in their daily lives, but companies as well. More and more organizations adopt open source solutions (in the cloud) to fulfill their business needs. The same is true for third-party dependencies and/or libraries for custom applications that their DevOps teams create. Another category would be the shift from closed source to open source in the cloud. Think of monitoring solutions, firewall-related software, load balancers but also databases.
Features that were previously only offered by commercial vendors are now available at their community-driven counterparts as well. Those software packages add enterprise-grade capabilities in the cloud to compete with well-established names in the software industry. Databases are no different here. In this article, we’ll take a look at the steps and considerations to migrate Oracle DBs to PostgreSQL (in the cloud). Open-source software in the cloud – from a closed-door to an open world.
First of all, why would you migrate from Oracle to PostgreSQL in the first place? From a functional point of view, you might require features that are not offered nowadays. If the roadmap of your commercial vendor doesn’t list them as well, it’s unlikely you will get the feature you want. Furthermore, there could be other technical reasons such as scalability, security, or other issues which are currently not supported.
Besides the technical issues, there should always be a business justification. One of the most prominent ones is the reduction of Operational Expenditure. Commercial solutions require expensive license fees per user, installation, and/or node. Besides these costs, many companies also require professional support from expensive consultants.
Suppose you have 15 applications running in production which all require a production and development license. A production license would cost you 12.000 euros per year per application. The development one costs 4.000 euro per application. 15 * (12.000 + 4.000) = 240.000 euros a year. Let’s estimate the number of required support on 8 weeks a year. 8 * 40 (hours per week) * 120 (hourly rate) = 38400. In total, this is about ~300.000 euros for every year you run your applications. You can save this amount of costs when switching to open source solutions.
Apart from the commercial reasons, there are other considerations. Applications become more “fluid” and portability is a key requirement if companies want to run them on different platforms. This also reduces the likelihood of a so-called vendor lock-in. Comparing open source-based solutions is easier when you don’t need to consider expensive licensing models and professional support. It’s possible to migrate from one cloud provider to another more easily, thus making companies more flexible.
It’s important to generate a complete list of features before you can make an informed decision whether other not to move everything over. The following key topics are in scope here.
Your database solution needs to be performant to serve your data in all circumstances. The design of your database tables and other database objects is an important factor that contributes to your overall performance. Besides this, the number of (compute) nodes also plays an important role here. Adding more nodes on Oracle incurs more costs since there is a license fee per node. PostgreSQL can be extended with any number of nodes without extra costs. Furthermore, there is no need to wait for the vendor to extend your compute workloads so you can start right away.
Both solutions offer ways to scale their databases and to support a high number of concurrent users and thus connections. In the cloud, you can utilize virtually unlimited resources to achieve horizontal scalability. On Azure, you can utilize Azure Database for PostgreSQL Hyperscale to enable horizontal sharding for your read operations. In AWS you can modify the EC2 instance template to a larger type. Both solutions do not incur any downtime at all.
Oracle offers the vertical read scalability feature to handle big loads while serving data from its tables. You can extend the number of Oracle Application Server Clusters to form a single cluster that serves your data. Be aware that there are additional costs to this.
Many developers are specialized in one or two programming languages. It helps if systems support more than one language. Oracle and PostgreSQL both support PL/SQL as their main query language. Besides PL/SQL, The core product of PostgreSQL also supports other languages such as PL/Perl and PL/Python as well as additional modules that support Java, R, or Bash. Those languages are also used by application developers and/or system administrators so it helps to pave the way to embed the migration-related activities into their daily work.
For authentication, it’s also best to have several options at hand. While Oracle offers its built-in authentication system, PostgreSQL used host-based authentication that supports a wide range of authentication methods. Authentication in PostgreSQL can be achieved using LDAP, Password authentication (using various encryption methodologies), Ident (in tightly controlled environments), and many methods more.
Everything you store in your database needs to have a proper localization to be processed correctly. This means that your character set needs to be supported for all the languages you wish to use. Characters need to be “encoded” to fit into the locale which is being used. Oracle offers a globalization toolkit and Unicode character support (so that all locales are supported). On the other hand, PostgreSQL offers automatic character encoding and collation support.
The power of the community prevails when they offer a bunch of high-quality add-ons, plugins, and other features on top of the core product. It’s easy to extend the usage of PostgreSQL with the numerous plugins and add-ons which are available and free to use. Oracle offers its proprietary plugins using its plug-in manager. The website of plug-ins-prod.com offers a wide list of plugins that can be tested out for free. PostgreSQL offers all plug-ins for free. See the top 5 here.
Integration / APIs
Modern (cloud-native) applications can’t survive without a decent API. A standard API is crucial to develop applications that talk to the database or its underlying components. Communities can develop best practices to interact with those APIs as well as design patterns for common problem areas. Flexible APIs help to ease the paths to use those APIs and to grow the number of (active) users for your database (system). It also helps to build loosely coupled applications (components) that communicate in an easy fashion with each other. Since this is all needed in a cloud (native) environment, it’s a big plus.
Role of Cloud providers
Every major cloud provider offers flexible ways to migrate from Oracle to PostgreSQL. In fact, it is a very common pattern for which they offer a lot of documentation and help.
- AWS offers several blog posts that assist you with the usage of their AWS Schema Conversion Tool (AWS SCT) and AWS Database Migration Service (AWS DMS). Their PostgreSQL services offering are based on RDS or Aurora PostgreSQL. Both are pure cloud-native services so they blend in with modern application deployment options in the cloud. Developers are already familiar with it. Several websites also offer lists of specific prerequisites and any migration limitations. This helps to define if this solution is suitable for your situation or not.
- Microsoft has plenty of documentation at hand to guide your migration efforts from the very start to the end. This also includes a typical Oracle2PostgreSQL migration architecture that includes Orable2pg VM pools and Azure Database for PostgreSQL. You can use the ora2pg command-line tool to assess your existing Oracle database environment and see if there are any pending issues that you need to fix prior to the actual migration. It’s even possible to estimate the costs that the migration typically would cost. Great input for your business plan.
- Google cloud offers various tutorials to assist you with your migration efforts. It includes a demonstration schema to test the migration. It’s good to note that this guide offers a GUI-based set of instructions you can automate later on. Furthermore, it (deep)links to the Ora2PG documentation which consists of basically everything you need to know about the conversion. This website is a valuable source of reference. Once you’re happy with the results, you can set up CloudSQL for production use.
As you notice, there are many sources to help you in your journey.
Based on the previous pros and cons, your business department might have given you a “go” to migrate your Oracle Database to PostgreSQL. Consider the following steps as a high-level plan that guides you there in your journey.
Tell everyone that is involved about the migration. Train and educate your developers and database specialists. And also don’t forget the data governance-related aspects, the processes to collaborate, etc.
Complete assessment of the environment
This step is needed to verify if you can really migrate from Oracle to PostgreSQL. Start to see if you can stop using third-party applications and components such as add-ons and modules since they are not supported anymore. Find alternatives and weigh the effort of switching to them. Some functions need to be updated or corrected to function correctly in the new situation. Only after the complete assessment with a positive outcome, you can give it a final “go”.
Strategy and planning
A complete migration requires thorough planning and strategy. It’s critical to identify potential conflicts, problem areas to reduce risks during the migration itself as much as possible. Create deployment plans, define bandwidth and performance plans as well as data migration strategies and data transfer plans. All with minimum downtime and data loss in mind.
Select and evaluate the tools which are available to assist your migration efforts. Specialized tools help to pull the data from your existing Oracle database, transform it to a PostgreSQL compatible format and help to transfer it.
The migration itself consists of a number of steps: DB schema migration, functional testing, performance testing, and the actual data migration. Generally speaking, you need to scan and extract your Oracle databases to generate scripts that feed the data into PostgreSQL afterward. This includes the actual schema itself, the execution of tasks, SQL query migration, and finding substitutes for functions in the new solution.
It’s important to measure the business impact during the migration and right after the migration is finished. This includes estimating the (expected) downtime during the migration as well as identifying any data loss that is acceptable. Be sure to track all operational changes and make sure you have adapted the processes that are involved once the complete migration is finished. In the end, your business case should be on the positive side to make sure you gain the most benefits.
Only after a couple of workloads are migrated do you need to evaluate the migration program. This gives you a good overview of what migration entails. Collect pros and cons and map them against your business targets. It helps to improve the next iterations of upcoming migrations and that helps to create smoother processes. Note down lessons learned and feed them back to the responsible teams. In the end, your staff will be more experienced and this speeds things up. Sooner or later you have finished the migration of all applications and database systems and you can save a lot of money on licensing fees and utilize the power of open source solutions.
If you have questions related to this topic, feel free to book a meeting with one of our solutions experts, mail to firstname.lastname@example.org.