Database migration – in layman’s term – means moving your database server from one database engine to another.
Why Migrate from Oracle to PostgreSQL?
Organizations choose to migrate from commercial databases like Oracle, SQL Server to Open-source database like PostgreSQL for a variety of reasons, the important out which is to reduce database licensing costs.
PostgreSQL is the world’s most advanced open source database. PostgreSQL community is very strong and they are continuously improving existing PostgreSQL features and also add new features. This is the main reason why many vendors including AWS, Microsoft, Google provide cloud platforms for PostgreSQL database.
Managing databases to run at scale with high availability and reliability is difficult, time consuming and expensive. This is why, many companies are moving their databases to cloud to explore fully managed, high performance, and cost effective databases.
Types of Database Migration
- Homogenous Database Migration : When both the source and target database engines are same, this is referred as Homogenous Database Migration.
- Heterogeneous Database Migration : When the source and target database engines are different, this is referred as Heterogeneous Database Migration.
What You Must Know Before You Start Database Migration
Database migration is a complex, multiphase process, which usually includes assessment, database schema conversion (if you are changing engines), script conversion, data migration, functional testing, performance tuning, and many other steps.
You must be aware of all the information required to finish your database migration project successfully. Following are few items to be considered before you get started.
- Understand your requirements, your business growing demands
- Decide your target database and understand your source and target databases
- Plan your time as database migration projects usually include refactoring of the application and database code, and also the schema, which is a time-consuming, iterative process
- Plan your resources including database migration experts, security, networking.
- Additionally, you can get more info in Database Migration—What Do You Need to Know Before You Start?
There are many commercial tool available based on the type of database migration. If you are thinking of migrating from self maintained Oracle database to Cloud, then each cloud vendor provides tools to ease your migration task. When it comes, Oracle to PostgreSQL database migration, there is no tool as such that can migrate 100% of your Oracle database workloads to PostgreSQL. In that case, you need both tools that migrate some of your Oracle database functionalities and rest your database migration experts have to do manually.
There are some open-source tools which are also very helpful for an Oracle to PostgreSQL database migration.
Oracle compatible functions, data type and packages can be use as it is in PostgreSQL. This is an open source tool with BSD licence so anyone can use this tool.
Most of the major functions are covered in Orafce.
Applications usually use those functions with multiple occurrences. You can reduce the modification cost of SQL by using this tool. All the functions and packages are implemented correctly and it is well tested.
Some of the functions:
- utl_file – filesystem related functions
- Dbms_pipe and dbms_alert
- PLVdate,PLVstr, PLVchr
- Oracle compatible DATE data type and functions like ADD_MONTHS, LAST_DAY,NEXT_DAY and so on.
- NVL function
- SUBSTR and SUBSTRB function
- VARCHAR2 and NVARCHAR2 support
Ora2Pg is a free tool used to migrate an Oracle database to a PostgreSQL compatible schema.
It connects to the Oracle database, scans it automatically, extracts its structure or data and then generates SQL scripts that you can load into your PostgreSQL database.
Ora2Pg inspects all database objects, all functions and stored procedures to detect if there’s still some objects and PL/SQL code that cannot be automatically converted by Ora2Pg.
This tool is very helpful for the following conversions:
- Schema conversion
- PLSQL to PLPGSQL conversion