Meet the CYBERTEC Migrator

A free tool to migrate Oracle databases to PostgreSQL

We at CYBERTEC are proud to release the CYBERTEC Migrator Standard Edition, the fastest tool to migrate a database to PostgreSQL. And the best part of it is-

The CYBERTEC Migrator Standard Edition

is available free of charge

What? Another free (as in beer) tool to migrate databases to PostgreSQL? We already have free tools to migrate Oracle databases to PostgreSQL and they are even open source. So why should I care?

If you are not a curious person and don’t care about the why, skip the next section and jump to the interesting part of this blog post where we walk you through the migration of Oracle's HR schema using the CYBERTEC Migrator Standard Edition. In case you don't have the time to read the article, watch the screen cast.

This article is going to be the first of a series of blog posts about how to migrate from Oracle to PostgreSQL. The articles will cover the challenges you may face, possible solutions and how the CYBERTEC Migrator reduces both your effort and the risks of botched migrations.

Here are some links to help you navigate the post:

• History of the CYBERTEC Migrator
• Migrator Installation
  • Requirements
  • CYBERTEC Migrator Dashboard
  • Set up Demo Environment
  • Users and Privileges
• Migrating Oracle HR
  • Create a Migration
  • The Migration Page
  • Overview Tab
  • Just Migrate
  • Jump to Errors
  • Code Editor Showing Errors
  • Search and Replace
  • Fix Remaining Errors
  • Other Features
• CYBERTEC Migrator Feature Roadmap
• Download the CYBERTEC Migrator Standard Edition

A History of the CYBERTEC Migrator

So you haven’t skipped this section and are curious. Why did we develop the CYBERTEC Migrator?

The simplest answer would be to have an additional option on how to migrate a database to PostgreSQL. And we all agree on one truth: “A world with one Oracle database less and one PostgreSQL database more is a better world”.

As you may know, we at CYBERTEC use open source tools and even contribute to the open source community. One of our most used software products - the Foreign Data Wrapper for Oracle - is used by many open source projects.

So why develop the Migrator?

At the beginning there was speed! Or better, the lack of speed. "Necessity is the mother of invention."

Improving our own lives

Here at CYBERTEC we often see customers with extreme requirements. We assume that’s probably why they come to us, because people out there tell them “If someone knows how to do this with PostgreSQL, and is crazy enough to try it out, try CYBERTEC!”. We are their last hope to solve their problems.

Long story short - some of our customers have databases in the terabyte regions. And they ask for ways to reduce the service downtime imposed by a database migration.

Previously, we didn’t have the resources to implement an asynchronous data replication pipeline. (Such a pipeline captures data changes in the source database during the migration, and synchronizes those changes to PostgreSQL after the migration finishes).

Reducing Migration Time

This left us with the option to reduce migration time by improving the speed of the data transfer. The solution to this problem was ora_migrator, a plugin based on our Foreign Data Wrapper for Oracle. But this solution had some disadvantages:

• A requirement for the ora_migrator is the use of PostgreSQL extensions. This isn’t always a viable solution, especially if you want to migrate the data into a managed PostgreSQL database service in the cloud.
• The migration was not flexible enough. Customers asked to tweak the target database structure and tune it for PostgreSQL. Those changes were hard to achieve - or sometimes impossible when using foreign data wrappers.
• It’s a tool for specialists. For people who have no experience with PostgreSQL, it is not easy to use. The ramp-up phase for onboarding new employees was steep.
• The changes performed by the person migrating the database have to be reproducible for audit purposes. This was hard to achieve and collided with the requirements of increased flexibility.

Getting it Right

Looking back at what features we implemented, the why and the how,  we noticed another motivation for the existence of our Migrator: convenience.

We do not like to do repetitive, boring stuff. We want to concentrate on the challenges rather than the kinds of mundane tasks we had to do when we used the existing tooling.

And since we are faced with migrating databases to PostgreSQL on a daily basis, we want to have tools which get the boring stuff out of the way. We want to be productive where it matters, so we need tools which provide a working solution.

Don’t get me wrong, using vim (as everyone knows, the only true editor) makes me productive. But switching between different terminals, exporting code to the file system, checking log files on another machine, and … “what state was the migration in before I left for coffee?” All wrapped up in a plethora of shell and SQL scripts, which at the end may fail because the production system differs from the test system! That’s not productive.

The raison d'etre of the CYBERTEC Migrator is so that customers do not have to know how to map Oracle data types to PostgreSQL or all the details explained in the technical guide on how to migrate from Oracle to PostgreSQL.

Easy, Fast Migration

If you want a tool which frees the cognitive capacity of your brain from the boring stuff so you can use it on porting the database over to PostgreSQL, that’s what the CYBERTEC Migrator is - The easiest way to migrate to PostgreSQL - in a professional way.

And by the way - it is also the fastest way to PostgreSQL!

So why would you give such a tool away for free?

We wanted to provide persons and organisations with small budgets an easy way to migrate their databases from Oracle Express or/and Personal Edition to PostgreSQL. We even bundle an hour of consulting for free to provide them with the impetus to migrate to PostgreSQL.

The CYBERTEC Migrator Professional and Enterprise Edition are meant for customers with stringent database SLAs and high availability. More details about the differences between the editions can be found on the CYBERTEC Migrator web page.

Easy Installation

First you'll see how to install the CYBERTEC Migrator Standard Edition. Then, you'll walk you through the migration of Oracle’s HR demo database to PostgreSQL.

The CYBERTEC Migrator is a so-called web application, which means its main functionality, the Migrator core, runs on a server. The user configures and controls the database migration via a graphical user interface that runs on a web browser.

As we mentioned above, one of the hassles we had with existing tools was a complicated installation process. The CYBERTEC Migrator is deployed as a set of container images. CYBERTEC provides a public Github repository (cybertec_migrator) which facilitates the installation process. We use Docker Compose to configure and orchestrate the containers. The images for the Migrator Professional and Enterprise Edition are pulled from the Docker Hub container registry.

As a side note - we also have customers who run the Migrator on Kubernetes in their private cloud, mainly on OpenShift.

For an installation on an air gapped system we provide an archive file which contains the git repository mentioned above including the container images. The CYBERTEC Migrator Standard Edition is available as an archive file. Visit this CYBERTEC Migrator link to get it.

Requirements for running the CYBERTEC Migrator Standard Edition

• Install Docker Engine
• Install Docker Compose (>= 1.27.0)
• Bash >= 4.0
• Git is installed
• CYBERTEC Migrator Standard Edition as archive file (see Download Migrator Standard Edition)

Desktop systems like Docker Desktop for Mac and Windows include Docker Engine and Docker Compose as part of those desktop installs. If you use an MS Windows desktop system, install Docker for Desktop with the WSL 2 backend.

Git comes installed with most of the Windows Subsystem for Linux distributions. Should this not be the case, open a shell in Ubuntu/Debian and enter the command:

Assuming all preconditions are met, follow the instructions provided in the offline installation section on the Migrator's installation page:

1. Extract the downloaded archive file, change into the extracted directory
2. Generate the default configuration
3. Import the container images from the archive file
4. Start the Migrator

Note: the Migrator version and the hashes may differ from the file you downloaded.

Host name

orcus is the name of the host, it will differ from the one you installed. Open the web browser of your choice and visit the URL shown in the terminal, in this case http://orcus. If you can’t reach the Migrator on the URL provided due to name resolution problems in your Docker setup, try http://localhost which should work.

You probably are asking yourself, “What? HTTP is not secure!”

For the sake of simplicity we do not use HTTPS in this demo run. It would just complicate the setup and configuration process and not contribute to the goal of this article. In a production environment, you would configure the NGINX server providing the Web GUI to use your SSL certificates.

CYBERTEC Migrator Standard Edition Dashboard

If all went well, you should see the Migrator Dashboard which provides the status of CPU's and RAM of the host where the Migrator is running. The dashboard also displays the most recently changed migrations. Since we haven’t created one yet, the page is empty.

In case the Migrator failed to start with an error Bind for 0.0.0.0:80 failed: port is already allocated you have to change the default port configuration since the default port 80 is already in use. Change the value of the environment variable EXTERNAL_HTTP_PORT in the .env file located in the directory to an unused port.

Migrator installed and running - check.

Set up Demo Environment for the CYBERTEC Migrator Standard Edition

For the demo, we need an Oracle database containing the HR sample schema and a PostgreSQL database. In case you have access to both, you are set up for the demo and you can skip to the next section, Database Users and Privileges.

If you don't have access to both databases servers, we provide a git repository containing a helper script demo-env which facilitates the setup of the demo environment. Clone the git repository of the demo database environment:

The following command will start a PostgreSQL server demo_db providing a demo database owned by the demo user, having the password demo. Easy to remember, right?

In case you get an error because the port is already in use, change  the environment variable EXTERNAL_DEMO_POSTGRES_PORT in the demo/.env file.

Source Database

All we are missing now is the Oracle source database with the HR schema. The following command is going to pull Oracle’s Express Edition provided by Oracle's Container Registry. Be aware that you need around 5 GB for the Oracle container. And yes, Oracle is bigger and slower than an elephant.

We have to wait until Oracle is up and running, showing the status healthy.

Once again, in case the Oracle default port 1521 is already occupied, the command above will fail. You know the drill, adjust the environment variable EXTERNAL_DEMO_ORACLE_PORT in the directory demo/.env.

The output shows the mapped ports for the various services:

• the Migrator GUI via HTTP on port 80
• the PostgreSQL demo_db on port 5432 providing the database demo
• the Oracle oracle service on port 1521, providing the pluggable database xepdb1

Both database services started by the demo environment do not use volume containers. In case you're not familiar with containers, it means the data is not persisted. On restarting the demo environment, you end up with an empty PostgreSQL demo database.

After you finish the demo, you can shut down the demo environment with

Database Users and Privileges in the CYBERTEC Migrator Standard Edition

The Oracle database user we are going to employ needs read-only privileges. No changes to the source database are necessary when migrating the database:

• SELECT_CATALOG_ROLE grants users SELECT privileges on data dictionary views
• SELECT ANY TABLE privileges users to access views and tables in other schemas
• FLASHBACK ANY TABLE privileges users to issue an Oracle Flashback Query. This guarantees that we migrate a consistent snapshot at a specific point in time (SCN).

The demo environment from the previous chapter provides a user "migrator" with the above privileges.

For the target database, the CYBERTEC Migrator expects to have a PostgreSQL database and a user with CREATE privilege on the database. The demo user provided with the database demo environment was created with following statements:

If you use the demo environment, we are ready to go.

Migrating Oracle HR

So, the Migrator is installed, the demo environment is set up. Let's migrate Oracle’s HR demo database to PostgreSQL. We start by creating a migration.

Use the CYBERTEC Migrator Standard Edition to Create a Migration

On the left side of the Dashboard select MIGRATION to change to the migrations page. Select the button “ADD NEW MIGRATION” which opens the wizard for creating a migration.

Provide the information for the Source Connection 

• Name: HR Migration Test
• Description: Demo how to migrate Oracle's HR schema
• Connection String: oracle://oracle:1521/xepdb1
• Username: migrator
• Password: migrator

In case you don't want to use the database demo environment, you will need to adjust the values to your environment.

Selecting CHECK performs a connectivity check from the Migrator core with the source connection provided. If the connection is successful, we can proceed to the Schema Selection with NEXT.

In case the connectivity check fails, an error message is displayed. Check the connection string and your credentials. In case the database user is missing privileges, they are shown.

Select the Schemas

Let’s select the Oracle HR demo schema we want to migrate, and proceed with NEXT to provide the Target Connection.

Provide the Target Connection

Now we have to provide the connection to the target database and check the connectivity:

• Connection String: postgresql://demo_db:5432/demo
• Username: demo
• Password: demo

On selecting NEXT the Migrator reads the meta-data from the Oracle system catalogs. Assuming we don’t encounter an unexpected error (yes, sometimes Oracle databases have a screwed up system catalog) the Migrator persists the meta-data of the source database and creates a configuration based on this meta-data.

The migration configuration is later used to migrate the data structure to PostgreSQL. It takes care of mapping data types, table structure, constraints and default values, adjusting indexes and much more.

The creation of the migration configuration is the result of the cumulative knowledge gathered over years of migrating hundreds of databases to PostgreSQL.

As you see in the screenshot above, one of the few features the Standard Edition does not contain is the migration of partitioned tables. The Standard Edition will migrate partitioned tables as normal tables.

Selecting NEXT creates a migration for the HR schema and we end up in the migration overview page.

What is a Migration composed of?

When we create a migration, a set of data consists of:

• The migration meta-data, like name and description
• Source and target connections with credentials
• The source database meta-data
• A configuration which is used by the Migrator to create the database database objects (DBO) on the target database
• Logs created by the execution of the migration

Note that once a migration is created, you can not change the source, nor the target database, nor the meta-data of the source database. This is an important concept which has an impact on how to roll out migrations to production systems. The life cycle of a migration and how it is used merits its own article.

Migration Page of the CYBERTEC Migrator Standard Edition

The screenshot below shows the landing page of the newly created migration for the HR schema.

The migration page is divided into four areas:

• On the top we have a header providing information about the migration: its name, source and target database. Hovering with the mouse over the migration name will show a tool tip with the migration description.
• The main area shows a tabbed page. When you enter into a migration, you will see the Overview tab. It provides concise information about the source database.
• On the bottom we have access to the Log and Search views.
• On the left we have the sidebar which provides access to detailed information about the source database objects as a tree structure. We can filter the database objects by object name, object type or a combination of both.

For now, let's take a detailed look at the overview page.

The CYBERTEC Migrator's Overview Tab

The Overview tab shows analytical information about the source database. The left hand side shows a table with the database objects grouped by object type, their cardinality and the stage (more about stages in the next sections) and the order in which they are going to be migrated. The right hand side of the overview page provides information about the column data types and their cardinality. In both tables we may drill down to get more specific information.

Our PostgreSQL experts are able to use the Overview page to make a rough assessment of how much effort it will take to migrate the database.

Wait, what? Do we need an expert to make the assessment?

Don’t worry - it is on our roadmap and we are currently working on it. The migration assessment will include our 20 years of knowledge of how to migrate databases to PostgreSQL, including an analysis of the PL/SQL code. Coming soon!

If you want to know what we are currently working on, check out the CYBERTEC Migrator Feature Roadmap at the end of this article. But enough about “what's going to be” - let’s start with the migration.

Just Migrate

Selecting the Stages tab will lead you to the heart of our Migrator. This is the place where you run, change, resume and replay a migration.

Before we start with the migration of the HR schema, a word about migration stages. The CYBERTEC Migrator divides the execution of a migration into so-called migration stages, natural synchronization points in the migration process:

• Structure stage: create database objects necessary to migrate the table data
• Data stage: migrate table data in parallel
• Integrity stage: parallel creation of primary keys, indices, foreign keys
• Logic stage: create functions, triggers, views

Now for a demonstration...

Select the START button which - you guessed right - starts the migration. Starting a migration job will automatically open the Log view and follow the log messages showing what’s currently happening. Migrating the HR schema we end up with an error at the Logic stage.

In the default settings, starting a migration executes all the stages until it succeeds with a complete migration or it reaches the first error. In our example, the Migrator executed Structure, Data and Integrity stages successfully. However, it hit an error in the Logic stage on migrating procedure:”HR”.”ADD_JOB_HISTORY”. This means our PostgreSQL demo database contains the migrated tables, with the data, constraints and indexes.

Data Migration

The HR schema contains only a few rows of data. This means the Data and Integrity stage are fast.

The takeaways here are:

• Tables are copied in parallel
• Indices and foreign keys are created in parallel
• In case your source database is huge and the data transfer takes a while you may skip the data stage (check the Data stage context menu); instead, concentrate on the structural migration (tables, indexes, etc).

The CYBERTEC Migrator Standard Edition transfers at maximum three tables in parallel. The same goes for creating indexes and foreign keys. If you need a faster migration, you can upgrade to the Migrator Professional Edition.

Jump to Errors

Back to our demo to the log view.

Reading the the Log view from the bottom up, we are informed about

• The overall migration failed due to an error in the Logics stage
• The stage summary tells us that no DBO was created, we failed on the first DBO, 3 DBO's are left for migration.
• The migration failed on the DBO procedure:”HR”.”ADD_JOB_HISTORY”
• Error details: in our case a syntax error at or near “IS”

Note that all DBO's in the log view are hyperlinked. Selecting the hyperlink for the procedure:”HR”.”ADD_JOB_HISTORY” opens the code editor for the stored procedure. Hyperlinking error messages to the DBO that caused them saves me at least a couple weeks of work (and frustration) each year.

Code Editors Showing Errors

The screenshot below shows the code editor of the erroneous procedure.

On the top of the editor we see the DBO’s fully qualified name as provided in the source database. In our case HR > ADD_JOB_HISTORY.  The code editor contains the PL/SQL extracted from the Oracle source database, which visually marks the same syntax error reported by the migration.

So why this error?  Anyone with experience porting code from Oracle PL/SQL to PL/pgSQL knows that the SQL procedural languages between database technologies are not 100% compatible. So Oracle's PL/SQL code has to be translated to PL/pgSQL, PostgreSQL’s SQL procedural language.

But Max, why doesn't the CYBERTEC Migrator translate PL/SQL code to PL/pgSQL automatically?

The answer is simple. Our in-house PostgreSQL experts are really skeptical about automatic code conversion tools. This probably stems from the situation that at CYBERTEC, we mostly deal with databases with complex PL/SQL code. To achieve the quality of code translation we would like to have, we would need an Oracle PL/SQL parser - which you guessed right - is on our feature roadmap.

That’s why our experts use the Migrator Search and Replace feature.

What to do about it for the time being?

For now, let’s change the code in the editor by replacing “IS BEGIN” with “LANGUAGE SQL AS $$” and “END add_job_history;” with “$$” (without double quotes). Selecting SHOW DIFFERENCE shows the difference between the original Oracle code and the changed procedure code.

What you do not see is what happens behind the scenes. In the background, the CYBERTEC Migrator Standard Edition validates the content of the code editor against the target database. It drops and recreates the procedure and in case of an error we show it in the editor. Don’t worry about unwanted changes, due to PostgreSQL transactional DDL's, the validation is performed in a transaction with rollback.

The background validation is maybe not so much of use in a stored procedure, but saves a lot of time when adapting views. It prevents unnecessary round trips due to typos in column or table names.

Save the changes by selecting the SAVE button, which persists the changes we just made in the Migrator’s internal database. Leave the editor and go back to the "Stages" tab by selecting ↑ on top of the page, near the migration name.

So how do we proceed?

The migration controls provide us with the following options:

• Resume from where the migration failed due to the error
• Rerun the current stage
• Start the migration process from the beginning at the Structure stage
• The Continue to next stage isn't available since we are in the last stage

We want to resume the migration where it was stopped due to the first error. To avoid the “resume, show error, fix error”-cycle over and over again, we disable “Abort stage on first error”. This will execute the stage until its end and report all detected errors.

Select RESUME. This will create the fixed procedure but leave us with errors in another procedure, view and trigger.

Let's fix the trigger and select the link in the error log line of trigger:”HR”.”UPDATE_JOB_HISTORY” which opens the code editor.

Search and Replace

An Oracle trigger is migrated to PostgreSQL by wrapping the trigger code into a PostgreSQL function which is called by the trigger. The function name matches the trigger name.

The screenshot below shows an error near the call to add_job_history.

PostgreSQL is picky when calling a function with no return value. So let’s change the code to PERFORM add_job_history. This will lead to the editor complaining about a syntax error at or near “:”.

:old, respective :new are unknown variables to PostgreSQL data triggers. Since this shows up multiple times, we are going to use the Search and Replace functionality. On the bottom of the migration window open the Search view.

We are going to use regular expressions to replace all occurrences of :old with old, respectively :new with new.

Select the "regex" mode by selecting .* and search for :(new|old). This uses regex capture groups to capture the parameter $1 which we use as a replacement, removing the colon.

In the HR schema we have only one trigger function, so not much to replace. Note that the scope for the Search and Replace operation can be tweaked on the right hand side. The use of the Search and Replace feature deserves its own article.

Select Replace All fixes the rest of the errors in the trigger function.

Fix Remaining Errors

Let’s fix the remaining errors. Switch back to the Log view and select the view:"HR"."EMP_DETAILS_VIEW" link, which opens the code editor with the erroneous view. Scroll to the bottom and remove the last line WITH READ ONLY, which has no equivalent in PostgreSQL.

I already mentioned the code validation performed by the migrator against the database. Try to change a table name in the view and see what happens.

Undo the change to the table name (CTRL-z) so the view works again, and select the last erroneous DBO procedure:"HR"."SECURE_DML" in the log view.

Let's assume we are not interested in migrating this procedure and exclude it from the migration by enabling Exclude in the editor's upper right corner. The sidebar shows the exclusion of the procedure with SECURE_DML.

Now we SAVE our changes and resume the execution. Try the floating migration control on the right side of the screen to resume the migration. It is handy when you don’t want to return to the Stages tab.

Congratulations - you just migrated your first database using the CYBERTEC Migrator. If you go back to the migration page all your stages will show up green.

If needed you can download the content of the log view by selecting the ↓ for audit purposes. Yes, some customers need this.

Additional Features of the CYBERTEC Migrator Standard Edition

If you reached this part of the article and you followed all the steps - kudos.

One important part left to demonstrate is the table editor. Selecting one of the tables on the sidebar will open the table editor. This is the place where you can change the database objects which end up in PostgreSQL.

There you can exclude one or more columns from the table, or the whole table. Change the data type, the nullable attribute, default values. Constraints, indices, triggers and partitions can be adapted.

There are a couple of features left to mention. These will be featured in future articles:

• Configuring a migration in detail (data types, default values, etc.)
• Bulk changes
• Stage hook scripts
• Use of Search and Replace in combination with the sidebar filter
• Tuning the migrations for speed
• A migration life cycle: reproducible migrations and deployment of a migration from test into production environment
• The Migrator’s REST API

CYBERTEC Migrator Feature Roadmap

We at CYBERTEC are constantly improving the Migrator. The priority of the features included depends on customer feedback and the migration projects CYBERTEC is currently working on.

Our paying customers have access to the monthly feature releases of the Migrator.

Here's just a teaser of what we have planned for 2023:

• Add database volume distribution to the overview page
• Conditional data migration
• Migration report - provides a summary of the migration
• Support migrations from Microsoft SQL Server to PostgreSQL
• GUI integration of CYBERTEC’s Asynchronous Data Replicator to make nearly-zero-downtime migrations accessible for non-experts.
• Code translation from PL/SQL to PL/pgSQL

How many of these features and in which order we can implement them depends on the resources we can reserve.

Download the CYBERTEC Migrator Standard Edition FREE

If you want to help the PostgreSQL community, download the free Migrator, try it out, provide feedback and spread the word about a new tool to migrate databases to PostgreSQL.