The Complete Oracle to Postgres Migration Guide: Tools, Schema, and Data

There are five steps or phases when dealing with Oracle to Postgres migration:

1. Assessment

What are the Oracle to Postgres pre-migration assessment steps and phases? This process involves assessment, compatibility assessment, application code assessment, architecture assessment, and cleanup and schema assessment.

The first step in planning the migration and analyzing the application to estimate how easy or difficult the Oracle to Postgres migration can be. In this phase, a thorough analysis should be conducted on technology-related issues to evaluate the compatibility of the client, application server, data access, and database features.

One concern that can easily be overlooked when considering a move to Postgres is confirming that, if you do not control your application, the packaged software application you use certifies for Postgres. If not, you must either convince your application’s vendor to add Postgres support or choose another application.
After verifying the source and target database compatibility, the following prerequisites should be met for data migration:

• Server resources (memory/disk space/network ports opened between source and destination)
• Operating system
• Data migration software and related drivers installed and configured

It should go without saying that your target server resources are large enough and have the scalability to handle the volume of data they will be receiving. If the volume of data is substantial, then a purely online migration may not be advisable, and you should consider an export-and-reload approach. It may also be wise to follow a migration strategy that divides the migration into parts (see the section on migration strategies below).

The more your application code relies on Oracle-specific frameworks instead of open classes, the more intricate your migration becomes. The amount of adjustment you will need to make depends on your application architecture and database connection layer. The conversion should be relatively easy if you have Java code that uses generic JDBC classes rather than Oracle-specific ones. It should be easy to use object-relational mapping (ORM), such as Hibernate or JCA. Switching the dialect from Oracle to Postgres could be trivially easy, though some adjustments will still need to be made.

The ease or difficulty of a migration can be significantly impacted by the setup and architecture of your database and its contents. Migration presents an excellent opportunity to clean up your architecture and database contents. Deprecate objects you no longer need, such as old temporary tables or backup copies of data—they’re not worth migrating if no one cares about them. If you store large files, like images or PDFs, in your database, consider separating them into a lower-cost storage option to reduce database size and resources needed for backup and restores. You may want to purge static historical data or move into an archival store option.

Moving from Oracle to Postgres also opens the possibility of separating online transaction processing (OLTP) and analytics into different warehouses, which can improve both responsiveness and analytics capabilities

Following the assessment, the next step in the migration process is identifying and addressing schema and data formatting differences between Oracle and Postgres. Making these adjustments before the data migration is crucial to avoid frustrating and time-consuming errors in Postgres.

Postgres supports ANSI SQL standard SQL syntax and data types, whereas Oracle does not support the same standard; additionally, it includes some non-ANSI SQL syntaxes. With the help of Oracle migration tools, unsupported objects can be identified and converted manually with Postgres-supported syntax or feature workarounds.

2. Schema Migration

What is schema in Oracle and Postgres?

A schema is also known as a “user” in Oracle and has the same name as the user. The default is for each Oracle user to have their schema. In Postgres, these are not the same and if you do not explicitly specify a schema, new objects will go to a public schema by default.

One concern that can easily be overlooked when considering a move to Postgres is confirming that, if you do not control your application, the packaged software application you use certifies for Postgres. If not, you must either convince your application’s vendor to add Postgres support or choose another application.
After verifying the source and target database compatibility, the following prerequisites should be met for data migration:

• Create a user and schema with the same name
• The first component in the schema search_path is $user, by default

One advantage of the setup in Postgres is that a user can create multiple schemas without creating separate users and grant permissions to others for creating objects in those schemas.

What are the Oracle to Postgres schema migration tools?

Several migration tools are available that can help users automate schema conversion. Below are the tools you can try for free to convert Oracle objects to Postgres.

• Ora2pg: a robust migration tool that connects to an Oracle database, extracts schemas and tables, and generates SQL scripts that can be loaded into Postgres.
• Ora_migrator: an extension that uses an oracle_fdw foreign data wrapper to extract data from an Oracle database.
• Orafce: this extension allows you to implement many Oracle functions in Postgres. It also provides support for Oracle date formatting and additional Oracle data types.
• EDB Migration Portal: a web-based service for migrating from Oracle to EDB Postgres Advanced Server that features Assessment, Schema conversion, and Compatibility reporting.

Take a quick look at how EDB Migration Portal works in this tutorial.

In addition, there are a number of commercial conversion tools available, including Amazon’s AWS Schema Conversion Tool (AWS SCT).

Oracle to Postgres migration tools comparison matrix

The below tools comparison matrix will give you a high-level picture of what they can do and how far they get you in the migration exercise to a specific target.

The key schema differences to take note of before Oracle to Postgres migration

Consider the following differences between Oracle and Postgres when preparing for schema conversion:

Columns
Until version 12, Postgres had no equivalent to virtual columns, so users were encouraged to change these to views when migrating. Now, Postgres offers generated columns, which share many traits with Oracle’s virtual columns.

Constraints
The Primary and Foreign Key, Check, Not-Null, and Unique constraints operate similarly in both database systems.

Identifiers
Oracle converts names of schema, tables, columns, and functions to uppercase unless the name is given in quotes, while Postgres converts them to lowercase unless given in quotes. You should be safe as long as the application consistently quotes or does not quote the identifiers.

Partitions
Hash, List, and Range partitions should all work in Postgres following migration.

Tablespaces
There are differences between Oracle and Postgres’ versions of tablespaces, but they serve the same purpose and should work.

Data types
The following chart lists notable differences between Oracle and Postgres data types.

What are the challenges while migrating from Oracle to Postgres?

In this section, we discuss some of the challenges we might face while migrating from Oracle to Postgres. To address these challenges and limitations identified in Postgres, those objects should be rewritten manually with a workaround if they exist.

1. Constraints
While Oracle allows users to disable and enable constraints as often as you want, this is not generally recommended practice for any RDBMS, because it can lead to data corruption if not performed with proper care.

In Postgres, constraints are instead created as deferrable, and the SET CONSTRAINTS command can be used to defer them. The deferrable setting indicates the default time for activating the constraint. If the constraint in Oracle is not deferrable, it will need to be dropped and re-created as deferrable, though it is sometimes possible to alter the constraint without having to drop it. Note: to avoid potential errors or bad data, place the commands for dropping and re-creating the constraint in a transaction, marked with a BEGIN/COMMIT block, which will lock the tables during the transaction.

2. DELETE
The FROM clause for specifying the table is required with DELETE statements in Postgres but not in Oracle.

Oracle:

DELETE mytable WHERE column_name = 'column_amount';

Postgres:

DELETE FROM mytable WHERE column_name = 'column_amount';

3. Dropping database objects
In Postgres, permission to drop objects is restricted to only a database table’s owner or a super user. It is not a grantable privilege, although membership in the role that owns the object can be granted. If an action in Oracle depends on this ability, it may need to be rewritten or reconfigured.

4. Dual Table
Because the FROM clause is mandatory in Oracle for every SELECT statement, FROM DUAL is used for SELECT statements where the table name is not necessary. Postgres does not require the FROM clause, so FROM DUAL is not necessary and can usually be omitted. If the Dual table is needed in Postgres, it can be generated as a view.

5. Empty Strings and NULL
In Oracle, empty strings have NULL values, but they are not considered NULL in Postgres.

In Oracle, you can check whether a string is empty or not using the IS NULL operator, but in Postgres, it would return FALSE for an empty string (and TRUE for a NULL one).

6. Federation to Foreign Data Wrappers
Oracle’s Federation feature allows users to treat tables from other databases as local data. Postgres' foreign data wrappers are more versatile and allow you to connect to a wider range of data.

7. GRANT
The GRANT command behaves similarly in Oracle and Postgres. There are two basic variants—it can be used to grant privileges on a database object and to grant membership to a role.

Not all privileges that are grantable in Oracle are grantable in Postgres. For example, granting the trigger privilege to a table allows users to create triggers; but, as opposed to Oracle, only the owner of the table can drop triggers.

8. Hierarchical queries
Postgres does not support the START WITH . . . CONNECT BY syntax that Oracle uses for hierarchical queries. Instead, Postgres uses WITH RECURSIVE.

Oracle:

SELECT
business_name,
city_name
FROM
businesses bs
START WITH bs.city_name = 'BOSTON'
CONNECT BY PRIOR bs.business_name = bs.city_name;

Postgres:

WITH RECURSIVE tmp AS (SELECT business_name, city_name
FROM businesses
WHERE city_name = 'BOSTON'
UNION
SELECT t.business_name, t.city_name
FROM businesses t
JOIN tmp ON tmp.business_name = t.city_name)
SELECT business_name, city_name FROM tmp;

9. Joins with (+)
Oracle has a special shorthand (+) operator for performing left and right outer joins. Postgres lacks this feature, so the JOIN command would need to be supplied.

Oracle:

SELECT table1.firstname, table2.lastname
FROM table1, table2
WHERE table1.customer = table2.customer (+);

Postgres:

SELECT table1.firstname, table2.lastname
FROM table1
LEFT OUTER JOIN table2 ON table1.customer = table2.customer;

10. NOT NULL checking
To determine which columns in an Oracle table are NOT NULL, you would use the command CHECK ( IS NOT NULL).

Postgres instead has a NOT NULL constraint column named attnotnull in pg_attribute, the systems catalog where information about table columns is stored.

11. Packages
Postgres does not have packages, but, using schema architecture, functions, and procedures can be grouped. Use the “orafce” migration tool library, which supports some of the standard packages, or EDB Postgres Advanced Server, which has built-in Packages.

12. PL/SQL to PL/pgSQL Conversion
Postgres' PL/pgSQL procedural language is similar in many respects to Oracle’s PL/SQL. Both are block-structured, imperative languages, with similar formats for assignments, loops, and conditionals. The Postgres documentation has a thorough run-down of considerations that need to be made when porting from PL/SQL to PL/pgSQL.

13. Remote objects
To access remote objects, the DBLINK module or Foreign Data Wrapper(Oracle_fdw) can be used to access any other database.

ROWID, CTID and Identity columns

Postgres does not have an exact equivalent to the ROWID pseudocolumn in Oracle, which provides the address of a row in a table. CTID in Postgres is similar, except that its value changes every time VACUUM is performed. Instead, you can use identity columns, whose value is self-generated when a row is created and never changes. The value can be specified to be GENERATED ALWAYS or GENERATED BY DEFAULT. GENERATED BY DEFAULT allows the user the option to insert or update a value rather than use the system-generated value.

14. Sequences
Sequences have a different syntax in Oracle and Postgres and will need to be updated either manually or using a script.

Oracle:

Mysequence.nextval;

Postgres:

nextval(‘mysequence’);

15. SUBSTR
The SUBSTR function behaves differently in Oracle and Postgres. In Oracle the statement SELECT SUBSTR(‘ABC’,-1) FROM DUAL; returns ‘C’, while the equivalent SELECT SUBSTR('ABC',-1); in Postgres would return ABC. The Orafce migration tool includes a SUBSTR function that returns the same result in both databases.

16. Synonyms
Postgres does not support synonyms. In place of Oracle’s CREATE SYNONYM for accessing remote objects, Postgres has you use SET search_path to include the remote definition.

Oracle:

CREATE SYNONYM abc.mytable FOR xyz.mytable;

Postgres:

SET search_path TO 'abc.mytable';

17. SYSDATE
Oracle's SYSDATE function returns date and time (in the server’s timezone). Postgres does not have a corresponding function, but there are a range of methods for retrieving the date and time for different purposes: statement_timestamp() gives current date and time from the beginning of the current statement; now() and transaction_timestamp() give the date and time from the beginning of the current transaction; and clock_timestamp() gives current date and time from the execution of the function.

18. TO_DATE
The to_date() function in both Oracle and Postgres return the date data type. However, Postgres' date data type provides the date (year, month, day), while Oracle’s date data type value provides the date and time (year, month, day, hour, minute, second). To avoid this incompatibility, use Postgres' to_timestamp().

The solution for this incompatibility is to convert TO_DATE() to TO_TIMESTAMP(). If you use Orafce tool then it is not necessary to change anything because Orafce implemented this function so we get the same result as Oracle.

Oracle:

SELECT TO_DATE ('20180314121212','yyyymmddhh24miss') FROM dual;

Postgres:

SELECT TO_TIMESTAMP ('20180314121212', 'yyyymmddhh24miss' )::TIMESTAMP(0);

19. Transactions
Oracle always uses transactions, but in Postgres, they must be activated. In Oracle, executing any statement starts a transaction, and it ends with the COMMIT statement. In Postgres, the transaction starts with the BEGIN statement, and it also ends with the COMMIT statement. Transaction isolation levels are the same in Postgres and Oracle, and Read Committed is the default isolation level for both.

ERROR: current transaction is aborted, commands ignored until end of transaction block.
Be sure that you have an exception handler set up where errors might be occurring and either to a savepoint or close the connection before trying another database operation.

• Use a BEGIN…EXCEPTION…END block for exception handling so your code catch any errors that occur. This automatically sets a savepoint before the block and rolls back to it when it encounters an exception. Keep in mind that because exception blocks create a savepoint, they are expensive, so add them carefully.
• Map the error codes and exception types from Oracle to Postgres. While some error codes are the same in both, others are different. Your programming language affects this as well. For example, Oracle-specific JDBC exceptions need to be replaced with either generic cross-database exceptions or Postgres-specific ones.
• Ensuring your Postgres database handles transactions and errors correctly is a critical part of the migration process and usually requires a careful review of the database and application code.

For enterprises looking to retain some oracle functionality in Postgres and wanting to migrate faster without many code rewrites an option is to move to EDB Postgres Advanced Server. EDB Postgres Advanced server is Oracle compatible Postgres with native PL/SQL compatibility. The below video demonstrates how the Advanced Server handles Oracle queries:

Take a quick look at Oracle compatibility with EDB Postgres Advanced Server.

3. Functional Testing

Functional testing after scheme migration from Oracle to Postgres

Before proceeding further, testing the converted schema on a sample dataset is vital. A recommended approach is to load some sample data into Postgres from a source database development or testing environment with production sample data and then set up an application connection using appropriate data access (drivers). After the application has connected to the database, allow it to do complete functional testing on the converted objects with DMLs.

It is advisable to load the same sample dataset in the Oracle and Postgres databases and then test both to ensure the SQL results are identical. Review and address any issues revealed by the functional tests.

4. Postgres Performance Testing After Migration

Performance testing is essential in the migration phase because some Oracle built-in transactions or feature functionality might differ slightly in Postgres, and the application might see some differences. In this phase, we can capture and fix all those differences at the application, data access (drivers), and database level with proper tuning.

5. Postgres Performance Testing After Migration

There are different approaches for data migration and tools available in the market. Usually, they are classified in three ways:

1. Snapshot: Data moved at once
2. Snapshot in Parallel: Data moved in chunks (schema or table)
3. Change Data Capture (Replication): Data loaded continuously.

For approaches 1 and 2, we need application downtime because data is being written one time from Oracle to Postgres, whereas in approach 3 data is loaded continuously, and there is a smaller downtime window. Pick the right data migration approach that fits in the downtime window.

Breaking down the strategies

Let's discuss these three data migration strategies in a little more detail.

Snapshot (One Big Bang)

In this approach, a snapshot of the source database state is taken and applied on the target database. Data is moved from Oracle to Postgres all at once. During the snapshot process, no WRITE operations are allowed on the source database. It's one of the cleaner and easier methods of data migration.

Snapshot - Parallel in Chunks (Trickle)

Parallel in chunks is another type of snapshot approach where data objects are broken into chunks and snapshots are taken in parallel. Most of the tools support snapshot and the process are invoked in tandem. There are two ways to perform a snapshot in chunks: 1) table by table or 2) a large table split into small sets using primary keys or any unique row identifiers. In this approach, the snapshot duration and downtime window is greatly reduced. Good scripting skills required to prepare data migration tools for table or large table migration.

Change Data Capture (CDC Data Sync)

Different traditional Change Data Capture (CDC) approaches have been available for decades. In the CDC model data migration, the software is designed to determine/track/capture the data that has changed on the source database in real time and replay the same on the target database. Currently, CDC model software is in high demand because it distributes data between heterogeneous databases with low-latency, reliable, and scalable data. The most common CDC approaches for migrating Oracle to Postgres databases are:

• Trigger-based (CDC)
  A remote trigger is created to capture DMLs (insert/update/delete) before or after and the sequence of transaction events is captured in changelog (shadow tables). They are processed by the software to replay on the target database.
• Transaction Log-based (CDC)
  Every database will have transaction logs (redos) to store all transactions/events sequentially and be used in case of database crash recovery. The transactions (DMLs) can be captured to change logs with filters, transforms, and aggregations using native database transaction log plug-ins. Later, the sequence of captured DMLs will be replicated to the target database.
  
  Both CDC approaches can be used to replicate data from Oracle to Postgres. However, each has its merits and limitations. Depending on the requirements, we can choose one of the available approaches. There are excellent software options available to support both CDC approaches.