The Ultimate Oracle to PostgreSQL Migration Checklist

Commercial solutions like Oracle Database present numerous challenges to the modern customer – licensing costs, choosing from various complex packages, inconsistent customer support, and audit risk being just a few of them. PostgreSQL, also known as Postgres, eliminates most of these issues.

The potential problems mentioned above don’t mean that commercial database solutions are going away, but more and more companies are migrating to PostgreSQL due to its inherited advantages. But how should you make this move? Let’s take a closer look at Oracle to PostgreSQL migration.

Potential Challenges While Migrating

The common perception is that migrating databases is an easy process, but there can be many hiccups if the following issues are not addressed.

1. Performance Bugs – Most PostgreSQL data types are easier to work with than their corresponding Oracle ones. However, some differences can cause issues if not tested properly. For example, The PostgreSQL “Timestamp With Time Zone” field is the actual equivalent to Oracle’s “Timestamp With Local Time Zone”.
2. Packaged Applications – PostgreSQL is ACID-compliant and conforms to the ANSI SQL:2008 standard. However, if you’re using a packaged software app which does not certify on PostgreSQL, migrating cannot be commenced without asking the vendor to move PostgreSQL to its supported database list.
3. Large Data Volumes – Migrating small databases (100GB or less) is quite easy. However, bigger databases can be time and resource (bandwidth) consuming if done in the traditional way. You may want to use date-based partitions, which makes it easier to choose and isolate the data that is moved offline.
4. Code Migration Issues – Porting the code to work with PostgreSQL is a big challenge. The good news – most of the code can be migrated automatically using tools such as the AWS Schema Conversion Tool (AWS SCT) or Ora2Pg. However, the rest has to be done manually and tested accordingly.
5. Version Compatibility – Please note that Store Procedures were not introduced until PostgreSQL Version 11. Earlier versions worked only with functions.

For example, Npgsql .net data Provider doesn’t support CommandType.StoredProcedure. It performs CommandType.Text Query instead of the required Call operation. A workaround for this would be creating a wrapper class to convert Executes based on CommandType.

There is also the case of inexperienced professionals. If your organization has been using Oracle for a while, your team is probably very capable of working in this environment. Migration will require you to invest time and resources in educating them about PostgreSQL, along with proper support documentation.

The Checklist: Oracle to PostgreSQL Migration

Before getting started, it’s important to understand that no tool that can automate the process completely. Migration requires a hands-on approach.

1- Preliminary Checking
Make sure you understand what needs to be migrated. This requires systematic mapping and checking. Also, look for any historical data that you may need. Don’t waste time replicating unnecessary data. For example, this can be backup data and temporary tables from past maintenance procedures.

2 – Migration Assessment
Once you have completed your preliminary checking, you need to analyze app and database objects to locate incompatibilities between the databases.

Tools such as Ora2pg are very helpful with migration assessment. They hook up seamlessly to the Oracle database, scan it automatically and extract the data to generate a comprehensive database migration report. To sum it up, this step will help you estimate the time and cost required for the migration process.

3 – Data Type Mapping
Oracle and PostgreSQL have quite a lot of differences when it comes to data types. You need to do some mapping and make sure things are being done correctly.

4 – Watch the Transactions
Oracle has built-in transaction functionality, which has to be activated manually in PostgreSQL. In Oracle, the transaction starts when executing any statement and ends with the COMMIT statement. In PostgreSQL, the transaction commences with BEGIN and ends with the COMMIT statement.

PostgreSQL possesses all the isolation levels just like Oracle. However, keep in mind that the default isolation level of PostgreSQL is Read Committed.

5 – Don’t Ignore the Differences
There are quite a few technical issues when it comes to migrating Oracle to PostgreSQL. You need to make sure your team is aware of the following:

• • Dual Table – In Oracle, the FROM clause is mandatory for every SELECT statement. Hence, Oracle database uses DUAL table for SELECT statements where the table name is not required. In PostgreSQL, the FROM clause is not mandatory. Hence, DUAL table is not required.
  • SYSDATE – Oracle’s SYSDATE function returns date and time. On the contrary, PostgreSQL doesn’t have any function corresponding to the SYSDATE function. Hence, you will need to use one of the following methods to get the date and time, as per the application purpose.

• • Empty String and NULL – There is a clear difference in behavior when it comes to these two important parameters. In Oracle, the IS NULL operator is used to check whether a string is empty or not. However, in PostgreSQL, the result is FALSE for empty string and TRUE for NULL.
  • SUBSTR – SUBSTR behaves differently in Oracle and PostgreSQL. It works smoothly in PostgreSQL but returns a different result in Oracle. This difference can lead to potential issues. The solution is to use the Oracle SUBSTR function which returns the same result as Oracle.
  • The DELETE Statement – In Oracle, the DELETE statement can work without the FROM clause, which is not the case in PostgreSQL. You will need to add the FROM clause in PostgreSQL DELETE statement manually. Ignoring this step can cause a plethora of issues going ahead.

6 – PLSQL to PLPGSQL Conversion
PostgreSQL’s PL/pgSQL language is similar to Oracle’s PL/SQL language in many ways. It is a block-structured, imperative language, and all variables have to be declared. Assignments, loops, conditionals are similar in both databases. However, the main differences you should keep in mind while porting are:

• • In PostgreSQL, the function body must be written as a string literal. You need to use a dollar quoting or escape single quotes within the function body.
  • There are no packages in PostgreSQL. Instead, use schemas to organize your functions into the required/ desired groups.
  • Data type names often need translation. For example, in Oracle string values are commonly declared as “varchar2”, a non-SQL-standard type.
  • There are various notational differences for the use of cursor variables.
  • Since there are no packages, there are no package-level variables either. You can keep per-session state in temporary tables instead.

7 – Testing
Testing the newly the migrated database is crucial because despite many functions being the same in both databases, the outcome can turn out to be extremely different in PostgreSQL. You probably don’t have unlimited time for this, but the following scenarios must be tested to ensure smooth performance.

• • Make sure all objects are correctly converted.
  • Check whether all the DMLS’s are working properly.
  • Load some sample data in both databases to check the results. The SQL results from both databases should be identical.
  • Assess the DML performance and work on it if necessary.

Automate for Optimal Results

Completing your database migration successfully with minimal issues is a great achievement, but you cannot get optimal results if you are not automating your DevOps. Today’s dynamic development environment requires a comprehensive solution to address the ever-growing list of challenges and requirements.

Integrating automation into PostgreSQL ecosystem can help you make multiple cross-platform deliveries, while establishing a wide range of performance and business KPIs. These can then be tracked and monitored 24/7 via a centralized dashboard, eliminating the need for cumbersome manual supervision.

Automation helps improve speed, accuracy, consistency and reliability, while helping you pin-point issues as they arise and minimize remediation times. Compliment your Oracle to PostgreSQL migration with an automation solution to boost your brand value and customer satisfaction. Fly safe!