The four quadrants of an Oracle migration

Most migrations are simple; the exceptions aren't

Oracle-to-Postgres migrations are commonly described as easy, and for the bulk of databases that holds true. Those workloads move without a large-scale project or unusual complexity, which is exactly why it makes little sense to spend serious budget on them — even for standardization purposes, the opposite is often the case.

The picture changes with the big fish. A database can carry an alternate signature to its low-maintenance peers, or it can be the devil in disguise with only one or two difficult characteristics: a tiny but extremely busy database is not necessarily easy to move from Oracle to Postgres. To reason about this reliably, a migration is best viewed through the four quadrants that make up the complete setup:

1. Infrastructure
2. Data structure
3. Data
4. Application

Each quadrant contains elements that must be addressed for the migration to succeed.

Infrastructure

The first step is having infrastructure available for the database and for the workload that will land on it. Source environments for larger databases can be highly specific: Oracle Exadata machines with Infiniband connections and extreme internal configurations, or Oracle RAC multi-node clusters tailor-made for one workload.

Frequently, though, these are poorly designed or badly executed implementations of very powerful but very specific hardware and software — encountering a RAC cluster that fails completely when a single node crashes is not unusual. The source environment's distinctive hardware is therefore no reason to shy away. What matters is investigating whether the workload actually needs those bells and whistles, whether it performs to specification, and what the bare requirements for uptime and disaster resilience are. The paradigm should not be the best money can buy, but rather this is good enough to do the job. Against those KPIs, a build combined with Postgres will often match the existing Oracle setup — and sometimes, by thinking beyond the box both literally and figuratively, a Postgres setup outperforms a comparable Oracle, DB2, or other architecture.

It is not the easiest quadrant, but it can be tackled.

Data structure

PostgreSQL's extensibility system can do anything, and what it cannot do today, you can teach it to do tomorrow.

That spans defining your own bespoke data types, defining operators, and even creating your own server-side programming language in Postgres. Postgres can jump any hoop you can think of, which is nifty when moving off Oracle — and not daunting, because most of the tooling exists, with the CYBERTEC Migrator leading the pack.

The data structure, or data model, exists to achieve the goals set for the migration and the application. Depending on project requirements, this is a good moment to change the physical data model: different physical storage options or other changes to the underlying structures can prepare the application for further growth and development.

Data

The third quadrant is less about the data itself than about what is needed from it. Moving the data between the two systems is not the hassle — the target structures are defined and the data has a new home. The interesting elements are:

1. How much data needs to be moved
2. How frequently the data changes
3. How long the data can be unavailable

A small database flagged as not very important and updated infrequently presents little challenge; as these criteria get more demanding, complexity grows. Quantity is a problem of time, which in turn feeds the other points: sizes running into multiple terabytes interact with distance — source and target in one rack on one switch is a different proposition from a source in your data center and a target somewhere in some cloud.

Change rate matters almost regardless of dataset size. A system doing 100 TPS that takes 20 minutes to move leaves 120,000 transactions to catch up on, which you need to be sufficiently aware of. Availability matters when the database is super important: a web store backend, or part of a chained data management solution where external systems depend on it for answers or persistence, may not tolerate any downtime. The criteria are the same as for disaster recovery planning — RTO (Recovery Time Objective: how much time the data can be unavailable) and RPO (Recovery Point Objective: how many transactions you can afford to lose).

Common remedies include an online migration: a big bulk initial migration followed by a mechanism that keeps source and target up to date with incoming changes, usually a form of Change Data Capture (CDC) that processes transaction logs and forwards relevant changes in either direction. Segmentation is another avenue: dividing data into multiple logical units or tiers, each with different demands, can yield one small critical piece that can still be migrated without breaking SLAs. Segmentation can be done at the source, but most such tricks are easier and cheaper at the target, where Postgres can be made to jump through your architectural hoops.

Application

What is data worth if you cannot work with it? With Oracle and Postgres, "application" points in two directions: the screens used for Create, Read, Update, and Delete operations, and the database-side code for efficient data management — stored procedures, triggers, constraints, and the like.

Both Oracle and Postgres have strong procedural languages embedded in the server; PostgreSQL additionally lets you build your own languages, complete with data types and operators. Database-side application code is where the CYBERTEC Migrator helps with automatic translations, a knowledge base, and many suggestions; its integrated migration recommendation includes estimates for completing the migration, database-side code included.

The traditional application is usually more painful. It may be written in almost any programming language and may embed dialect-specific SQL — where Oracle deviates from the more general ANSI-SQL interpretations — or procedural code, both invisible from the database. Depending on the availability of the original programmers and the complexity of the code, a formidable refactoring may be required.

CYPEX and the migration tool chain

A chain is only as strong as its weakest link: if the application cannot be made to work, the migration has little use, and the last few meters can consume disproportionate time, effort, and money. CYPEX is a rapid web application development framework that predicts what the target-side application should be — you make it pretty and you are done. The foundation lives in quadrant two: look at the data structure with an intelligent view and everything needed is already there, from relationships to dependencies to representations of values.

CYPEX provides:

• Smart recognition of father-child relationships between tables
• Exposure of check constraints to further safeguard data integrity
• Access to applications and data based on the system's role based access control, or RBAC
• Integrated and configurable table management
• Built-in and configurable abstraction layers protecting your data
• Automatic documentation, versioning, and integrated application deployment
• Application-level data manipulation audit capabilities
• Full integration of pg_timetable's workflow management automation
• Configurable levels of LDAP integration
• Internal and external marketplace for application and workflow components

The result is the logical application; you paint it on the screen and you are done. Placed alongside migration experience measured in hundreds of hands-on projects rather than PowerPoint, this covers all four quadrants: quadrant one through designing and building mission-critical infrastructure; quadrant two through the migration tool chain anchored by the CYBERTEC Migrator; quadrant three through advice, coaching, and execution, whether big bang or step by step; and quadrant four through CYPEX, both to close the gap and to extend existing applications.