As some of you might know, CYBERTEC has been helping customers with PostgreSQL consulting, tuning and 24x7 support for many years now. However, one should not only see what is going on in the PostgreSQL market. It also makes sense to look left and right to figure out what the rest of the world is up to these days. I tend to read a lot about Oracle, the cloud, their new strategy and all that on the Internet at the moment.
Table of Contents
What I found in an Oracle blog post seems to sum up what is going on: “This is a huge change in Oracle's business model, and the company is taking the transformation very seriously”. The trouble is: Oracle's business model might change a lot more than they think because at this time more and more people are actually moving to PostgreSQL. So yes, the quote is correct - this is a “huge change” in their business model, but maybe not what the way they intended it to be.
As license fees and support costs seem to be ever increasing for Oracle customers, more and more people step back and reflect. The logical consequence is: People are moving to PostgreSQL in ever greater numbers. So why not give people a tool to move to PostgreSQL as fast as possible? In many cases an Oracle database is only used as a simple data store. The majority of systems only contains tables, constraints, indexes, foreign keys and so on. Sure, many databases will also contain procedures and more sophisticated stuff. However, I have seen countless systems, which are simply trivial.
While there are tools such as ora2pg out there, which help people to move from Oracle to PostgreSQL, I found them in general a bit cumbersome and not as efficient as they could be.
So why not build a migration tool, which makes migration as simple as possible? Why not migrate simple database with a single SQL statement?
ora_migrator does exactly that:
|
1 2 |
CREATE EXTENSION ora_migrator; SELECT oracle_migrate(server => 'oracle', only_schemas => '{HANS,PAUL}'); |
Unless you have stored procedures in PL/pgSQL or some other hyper fancy stuff, this is already it. The entire migration will be done in a SINGLE transaction.
First of all the ora_migrator will connect to Oracle using the oracle_fdw, which is the real foundation of the software. Then we will read the Oracle system catalog and store of a copy of the table definitions, index definitions and all that in PostgreSQL. oracle_fdw will do all the data type mapping and so on for us. Why do we copy the Oracle system catalog to a local table and not just use it directly? During the migration process you might want to make changes to the underlying data structure and so on (a commercial GUI to do that is available). You might not want to copy table definitions and so on blindly.
Once the definitions are duplicated and once you have made your modifications (which might not be the case too often - most people prefer a 1:1 copy), the ora_migrator will create the desired tables in PostgreSQL, load the data from Oracle, create indexes and add constraints. Your transaction will commit and, voila, your migration is done.
Experience has shown that a fully automated migrated process for stored procedures usually fails. We have used ora2pg for a long time and procedure conversion has always been a pain when attempted automatically. Therefore, we decided to skip this point entirely. To avoid nasty bugs, bad performance, or simply mistakes we think that it makes more sense to port stored procedures manually. In the past, automatic conversion has led to a couple of subtle bugs (mostly NULL handling issues) and as a consquence we do not attempt things in the first place. In real life this is not an issue and it does not add too much additional work to the migration process - the minimum amount of additional time is worth spending on quality in my judgement.
If you want to move from Oracle to PostgreSQL, you can download ora_migrator for free from our GitHub page. We also have created a GUI for the ora_migrator, which will be available along with our support and consulting services.
For the easiest way to migrate from Oracle to PostgreSQL, check out the CYBERTEC Migrator – get all information and the free standard edition here:
In order to receive regular updates on important changes in PostgreSQL, subscribe to our newsletter, or follow us on Facebook or LinkedIn.
You need to load content from reCAPTCHA to submit the form. Please note that doing so will share data with third-party providers.
More InformationYou are currently viewing a placeholder content from Facebook. To access the actual content, click the button below. Please note that doing so will share data with third-party providers.
More InformationYou are currently viewing a placeholder content from X. To access the actual content, click the button below. Please note that doing so will share data with third-party providers.
More Information
Leave a Reply