Oracle to PostgreSQL migrations: How it works?
PostgreSQL is the most advanced open source database, which relies on 15 years of active development and architecture evolved and proven. It has earned a solid reputation for reliability and data integrity. Like any enterprise-class database, PostgreSQL boasts sophisticated features such as Multi-Version Concurrency Control (MVCC), point in time recovery (PITR), tablespaces, asynchronous and synchronous hot standby replication, nested transactions (savepoints), online / hot backups, a cost query planner/optimizer, but also a planner with searching query path heuristic.
It is highly scalable both in the sheer amount of data it can handle and the number of concurrent users it can accommodate and for the possibility with hot standby to create replicas of the data in different geographical location. There are active PostgreSQL systems in production environments that manage more than 4 terabytes of data.
Benefits of Migrating to PostgreSQL
Are you sick of footing unbelievably large bills for Oracle licensing and looking for viable alternatives? You’re definitely not alone! There is good news. Migrating from an Oracle database to PostgreSQL gives organizations benefits that range from lowered costs to a better technical architecture. SourceX speeds the process while reducing risk.
The most obvious benefit of migrating to PostgreSQL is cost. Oracle’s list pricing is based on a per-core model with additional costs for features like partitioning and high availability. For example, the three-year total cost of ownership of an existing 32 core environment with partitioning for scalability and Active Data Guard for high availability can be near $1,000,000. A possibly more compelling long-term benefit of PostgreSQL is the ability to create technical solutions based on need not licensing. It is all too common for people to deploy things sub-optimally to save money on licenses instead of the right technical solution.
PostgreSQL needs no license fee. The capital cost is zero, so business projects can start easily as prototypes and develop quickly into successful projects. For these reasons, projects can deliver business benefits quicker, experience shorter and shallower cash flow curves and move into profit more quickly. Licenses for production use cost nothing, licenses for developers cost nothing and licenses for partner companies cost nothing. There is zero risk of retrospective license fees and zero risks that a vendor will perform a costly license audit.
- 100% open source
- Active community
- No vendor lock-in
- Cost savings
- Proven reliability & data integrity
- High performance
- Large scale
- Enterprise class support
- ANSI SQL & extensible
Stages of migration from Oracle to PostgreSQL:
- Audit of the customer’s system
- Making a decision on migration based on audit results
- Deployment of the stand for migration
- Data Conversion
- Code Conversion
- Functional testing
- Development of tools and scenarios for rollback to the source system (optional).
- Optimizing storage structures and code
- Stress Testing
- Optimizing the performance of a migration decision
- Technical support after migration
Analyzing database migration has three main factors when determining the size of a project: the number and complexity of database objects, the SQL embedded in the application code and the size of the overall data. Combining the estimates of these three factors will result in an accurate estimate of the project scope and timeline.
Database Objects When analyzing an Oracle database for a migration, the number of database objects is usually the dominating factor in a project. Some migrations where the only database objects are Tables and Indexes are very simple, while others may have hundreds or thousands of stored procedures. The objects you need to consider for the migration include: Schemas, Tables, Indexes, Constraints, Stored Procedures, User Defined, Functions, Custom Types, Views, Triggers, Packages.
When analyzing the database objects, it is important to remember that the development time to translate an object from Oracle to PostgreSQL syntax is a minor percentage of the overall effort. Over the years, and many migrations, time and time again, the effort needed to translate objects from Oracle to PostgreSQL account for only 20%-30% of the project time. Take for example a view in Oracle that uses ANSI SQL. It can be created in PostgreSQL with no changes at all, but there is significantly more effort for a migration. It runs in PostgreSQL, but how do you know it is the same as in Oracle? Each object needs a set of unit test scripts to make sure the results out of the database are the same in both Oracle and PostgreSQL and if they are not, that everyone understands why. Believe it or not, people have bugs in their database code which end up being caught in the migration. Catching these bugs are only possible by using a comprehensive unit test suite across all database objects which is an added benefit of a migration project. Doing a migration without a deep testing strategy at the object level will more often than not lead to a delayed or failed project.