When organizations look to migrate from Oracle to PostgreSQL database, they usually see the advanced features, high performance, flexible open source licensing, data integrity and easy availability from public cloud providers.
But the way to migration is not easy, and you need to know how to avoid the obstacles if you are to be successful.
The origins of PostgreSQL date back to 1986 as part of the POSTGRES project at the University of California at Berkeley. And now, after more than 30 years of development, it’s emerged as a powerful, open source relational database management system that uses and extends the SQL language combined with many features that safely store the most complicated databases.
PostgreSQL has since 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.
PostgreSQL characteristics include:
- Highly extensible. You can define your own data types, build out custom functions and write code from different programming languages without recompiling your database.
- Runs on all major operating systems and has been ACID-compliant since 2001. It has powerful add-ons such as the popular PostGIS geospatial database extender.
- Offers a sophisticated locking mechanism.
- Supports multi-version concurrency control.
- Has mature server-side programming functionality.
- Compliant with the ANSI SQL standard.
- Provides full support for client-server network architecture.
- Uses log-based and trigger-based replication SSL.
- Has a standby server and high availability.
- Object-oriented and ANSI-SQL2008 compatible.
- Supports JSON allows linking with other data stores like NoSQL which act as a federated hub for polyglot databases.
- Offers security features such as a robust access control system and column-and-row-level security.
Advantages of PostGRESQL:
- PostgreSQL can run dynamic websites and web apps as a LAMP stack option
- PostgreSQL’s write-ahead logging makes it a highly fault-tolerant database.
- PostgreSQL source code is freely available under an open source license. This allows you the freedom to use, modify, and implement it to your business needs.
- PostgreSQL supports geographic objects so you can use it for location-based services and geographic information systems.
- Learning PostgreSQL does not require massive training.
- Low maintenance administration for both embedded and enterprise use.
- PostgreSQL community is very strong and they are continuously improving existing PostgreSQL features and also add new features.
Migration challenges
Companies are migrating their databases away from Oracle and adopting PostgreSQL as the preferred open source alternative. Some of the top benefits companies expected to gain were around ease of use, deployment flexibility and developer innovation. But database migration from Oracle to PostgreSQL may not be an easy task. Here are some of the challenges you will have during the migration process, and how to overcome them:
1. Maintaining service continuity
Providing continuous and reliable service and minimizing downtime during migrating databases is a primary challenge. To avoid unexpected events, start by evaluating all your Oracle databases to determine which will be the easiest to migrate, and migrate them first. This will allow the team to gain experience and confidence when they move on to more complex databases.
2. Migration Assessment
Migration from Oracle to PostgreSQL is a costly and time-consuming task. The initial challenge is to analyze the application and database object, find out the incompatibilities between Oracle and PostgreSQL and estimate the time and cost required for migration.
3. Training your team to be familiar with PostgreSQL
Your team should acquire the skills needed to support and maintain the daily management, maintenance and provisioning of PostgreSQL databases. 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. You should enlist a core group of your team to champion the move and lead the shift.
4. Data types and schema conversion
PostgreSQL has a comprehensive set of data types and support for custom extension types. The core Oracle data types should be mapped to PostgreSQL types.
5. Migrating database
After you convert your schema and tweak the tables to meet your specific application, the database should be moved from Oracle to your cloud storage. For smaller (100 GB or less) databases, you can use the data migration service of your cloud vendor. For larger data volumes an online migration is time and bandwidth consuming.
The solution can be using an export, reload, and sync approach. In this approach, you export your largest tables. Then, you either compress and push them to Amazon S3 or use AWS Snowball to transport them to an AWS Region and load to Amazon S3. After the data is in Amazon S3, you can bulk load the data files to PostgreSQL.
6. Migrating code
The most intensive effort during an Oracle to PostgreSQL migration is usually porting the code to work with PostgreSQL. This applies to the stored procedures, packages, and functions within the database and the application code that reads and writes to the database.
7. Migrating applications
PostgreSQL supports most development languages, including Java, C#, Python, PHP, C/C++, and JavaScript/Node.js. But if you’re using a packaged software application which are not certified on PostgreSQL, migrating will not be easy.
If you want to move your commercial ERP, CRM, or accounting application from Oracle to PostgreSQL, you might have to get your vendor to add PostgreSQL to their supported database list, or else migrate to a new business application which supports PostgreSQL. Another option is to leverage automated tools that evaluate and translate code and/or suggest alternatives in PostgreSQL.
The bottom line
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 like PostgreSQL.
There are several challenges when migrating from Oracle to PostgreSQL, but giving your team the necessary skills, tools and knowledge is the best way to ensure success.
For aNewDomain, I’m Gilad David Mayaan.
