Oracle to Postgres Migration (The Whys & Hows)

Why migrate from Oracle to Postgres? The reasons for an Oracle to Postgres migration are many, but it starts with what the Oracle and PostgreSQL databases are all about, their features and pros and cons. In this blog we learn about the reasons why you should migrate from Oracle to Postgres, the Postgres benefits you could enjoy, and some phases, approaches, and methods of Oracle to Postgres migration. Also learn how BryteFlow as an automated Oracle to Postgres migration tool can shorten and smoothen your journey of moving data from Oracle to Postgres.

Quick Links

• What is the Oracle Database?
• What is the PostgreSQL Database?
• Moving from Oracle to PostgreSQL – Some Benefits
• Oracle to Postgres Migration Phases
• Oracle to Postgres Migration: What to keep in mind
• Challenges in moving from Oracle to PostgreSQL
• Oracle to Postgres Migration Methods
  • Manual methods for moving data from Oracle to Postgres
  • Automated method with BryteFlow to move data from Oracle to Postgres
• How to move data from Oracle to Postgres Step by Step with BryteFlow

What is the Oracle Database?

Oracle Database (Oracle DB) is a relational database management system used by some of the world’s largest enterprises and it is a commercial database licensed by the Oracle Corporation. It is also referred to as Oracle DBMS, Oracle Autonomous Database, or simply Oracle. It is a multi-model database management system that is mostly used for storing, organizing, and retrieving data, online transaction processing (OLTP), Data Warehousing (DW) and running mixed database workloads (combination of OLTP and DW). Oracle DB is one of the most reliable and widely used relational database engines in the world. Oracle Database can be run on-prem, on Cloud or even as a hybrid cloud installation. It offers the capability to be run on third-party servers, and on Oracle hardware (Exadata, Oracle Cloud, Oracle Cloud at Customer). BryteFlow for Oracle CDC

The Oracle DB architecture centers around a relational database framework that enables users and application front ends to access data objects directly through Structured Query Language (SQL). The Oracle DB system is highly scalable and used by large enterprises to access, process, manage and consolidate their data across wide and local area networks. Oracle is equipped with its proprietary network components that enable easy communication over the network. Oracle CDC and 13 things you should know

What is the PostgreSQL Database?

PostgreSQL or Postgres as it is popularly known, is the most advanced open-source object-relational database system in the world and has been under active development for over 35 years. As an enterprise-grade database, Postgres has a well-deserved reputation for reliability, versatility, robustness, and performance. Postgres is used by many web, mobile, analytical, and geo-spatial applications as a data repository and data warehouse. Most startups and enterprises today use PostgreSQL to power their dynamic websites and applications. Postgres supports SQL and JSON queries and supports advanced data types and multiple programming languages ​​such as Python, Java, C#, Ruby etc. BryteFlow for Postgres CDC

Why is Postgres becoming so popular?
Firstly, Postgres is an open-source RDBMS (no expensive licensing fees), secondly it is highly extensible and fault-tolerant. Efforts are always on by companies as well as the Postgres community to increase the extensibility for different use cases and to extend the functionality of the database. Postgres provides users an ever-expanding number of functions that assist tech teams in building applications, maintaining data integrity, and building secure data environments. Postgres can have customized extensions to cater to very specific needs (e.g., extensions to handle time-series, geo-spatial data, extended logging etc.). PostgreSQL is being updated continually and adapted for various platforms. Postgres CDC (6 Easy Methods to Capture Data Changes)

Postgres has powerful SQL querying capabilities and can function as a cost-effective data warehouse. It is compatible with many BI tools and can be used for data analytics, data mining and business intelligence objectives. Being highly scalable, Postgres enables users to process and manage their data efficiently no matter what the size of the database. PostgreSQL Data Integration

Postgres is highly flexible and being open source, it is easy to upgrade or extend. Users can define their own data types, create custom functions, and write code in a different programming language without needing to recompile the database. It can be run on Windows, Mac OS X, and almost all Linux and Unix distributions. Postgres is highly compliant with SQL standards leading to increased interoperability with other applications. SQL Server to Postgres – A Step-by-Step Migration Journey

Moving from Oracle to PostgreSQL – Some Benefits

Oracle to Postgres migrations can confer many advantages to organizations. Here are some Postgres benefits listed for you:

Moving to Postgres from Oracle can save on data costs
Additional features such as data partitioning and high availability may need to be paid for when using Oracle DB. These expenses can add up quickly in addition to Oracle’s licensing costs. In contrast open-source Postgres is free to install and use, and new features and functionalities are being added all the time, which again, are free to use. Postgres acquisition, installation and support is completely free of charge. Oracle to Postgres with BryteFlow

Migrating Oracle data to Postgres avoids vendor lock in
Postgres has open-source licensing and is widely available online and from public cloud providers. PostgreSQL is widely supported as a DBaaS. These services are provided by Amazon RDS, Microsoft’s Azure Database for PostgreSQL, and Google’s Cloud SQL for PostgreSQL. With Postgres (unlike Oracle) you do not risk being locked into a vendor.

Moving to Postgres means avoiding expensive Oracle licensing
Oracle being a commercial database comes with heavy licensing costs. For e.g. additional tools like Oracle GoldenGate for real-time replication may need additional licensing and may require installation of the Oracle Enterprise Edition to unlock advanced features. Oracle support too, is not free and must be paid for. In contrast Postgres has an active developer community so tweaks, patches and updates are easy to source and free. Answers to queries about installation, upgrades and usage can be found quickly with no charge. Postgres extensions are ever growing and there are thousands of plugins available, supported by an enthusiastic community. Commercial support for Postgres is available and is largely cheaper than Oracle support. Oracle CDC (Change Data Capture): 13 Things to Know

Unlike Oracle, Postgres has thousands of free extensions
Since Postgres is an open-source DB, there are thousands of free extensions and add-ons that can greatly enhance database performance. At Oracle, the cost of similar functionality adds up fast. Though Oracle does offer provide many tools, they are add-on solutions that attract additional processor licensing costs, software update licensing costs and support fees. About Real-time Oracle Replication

Benefit from Easy Application Programming with Postgres
Both Oracle and Postgres provide application APIs for communicating with databases. However, a big Postgres benefit is that it is open source. A developer can simply include the header files in the project to directly access any of the Postgres components.

Benefit from flexible Host-based Postgres Authentication
While Oracle has an integrated authentication system, Postgres is based on host-based authentication, which means it can support a variety of authentication methods (Trust authentication, Peer authentication, Password authentication etc.) This provides more authentication flexibility and enables delegation of the process.

Postgres supports many procedural languages
PL/SQL language is built into Oracle, but PostgreSQL allows you to write directly to the database using language handlers for multiple languages ​​(PL/pgSQL, PL/Tcl, PL/Perl, and PL/Python). It also supports several external languages. In fact, users can even define a new procedural language as per requirement in the form of plugins and create bindings for additional programming languages. Postgres supports the JSON data type, which is lightweight and flexible, due to which PostgreSQL provides support for many programming languages and protocols such as Python, Perl, Ruby, Java, .Net, C/C++, ODBC, and Go.
SQL Server to Postgres – A Step-by-Step Migration Journey

Postgres is highly compatible with other databases and platforms as compared to Oracle
PostgreSQL is compatible with most RDBMS including Oracle which makes it easy to migrate to Postgres from Oracle. It also has excellent operating system compatibility with platforms like HP-UX, Linux, NetBSD, OpenBSD, FreeBSD, OS X, Unix, Solaris, and Windows which is a great benefit. Connect Oracle to Databricks to Load Data

Postgres provides automatic character encoding
Oracle has the Globalization Development Kit and Unicode character support to function as globalization support tools, while Postgres has integrated localization system services to provide automatic character encoding and collation support.

Postgres provides high performance at low cost
Postgres can create an unlimited number of nodes in a read cluster, it can reduce the cost of any given read to near zero. This enables the provision of different settings for each workload. This can also be done in Oracle, but at an additional cost per node. Oracle to SQL Server: Reasons, Challenges and Tools

Postgres has unlimited scalability that is cost-efficient unlike Oracle
Though Oracle has strong vertical read scalability, Postgres can scale almost infinitely and can create a virtually unlimited number of nodes in a read cluster, depending on resources available. Oracle can also be used for heavy workloads but scaling an Oracle database always comes at a cost. PostgreSQL can scale and process millions of transactions per second and handle very high data volumes.
Postgres CDC (6 Easy Methods to Capture Data Changes)

Postgres releases major updates and bug fixes frequently
Unlike Oracle where major releases happen every 2 to 4 years, PostgreSQL releases major updates and bug fixes often – approximately every three months.

Postgres has low-cost data security as compared to Oracle
Oracle does offer advanced data security tools, but these are costly add-ons. PostgreSQL has role-based access control and free security authentications such as Host, LDAP, and PAM. Data encryption is also available through advanced security extensions.

Oracle to Postgres Migration Phases

Migrating Oracle to Postgres involves several phases including the assessment of the existing database and potential needs, right schema selection, doing compatibility checks, conversion of incompatible objects, performance and functional testing, data migration and post-migration checks.

Steps in migrating from an Oracle to Postgres database
Database migration is driven by the goals you need to achieve. Here are some common steps you need to take when moving from Oracle to Postgres.

Briefing and Preparation
Brief and educate the team about the transition, provide technical knowledge so they can collaborate seamlessly, maintain business continuity and good data governance practices.

Advance check: Find out exactly what you need to migrate. Find and exclude data that does not need to be replicated, such as old backups, old temporary tables etc.

Evaluate your migration: Analyze objects in your apps and databases, find differences between databases, and export sample test data to migration reports to understand the estimated time and resource consumption of the migration process. Data Migration 101

Assess Requirements: You should assess your requirements before migrating from Oracle to Postgres. Compatibility of application servers, clients, data access, data type conversion, code conversion, and database functionality should also be assessed. Migrate Oracle to Databricks

Schema conversion and migration: In Oracle, there is a one-to-one relationship between schemas and users, and there is no real difference between users and schemas. In PostgreSQL all user-created objects are created in a specific schema or namespace. Oracle requires each schema to be defined but in PostgreSQL the schema defaults to the public schema even if the schema has not been defined. When a role, schema and their association are specified, a schema conversion report should be generated that will provide details on conversion of objects – ones that can be seamlessly converted and others that will require manual coding. Manual conversions must be performed after analyzing the report, so they can work with the application. Postgres CDC (6 Easy Methods to Capture Data Changes)

Functional testing: It’s important to test the migrated schema with a sample dataset. Load some sample data from your target database or test environment into PostgreSQL and connect to the application with the appropriate driver or data access. After connecting your application to the database, you can use DML to test the functionality of the transformed objects. Load an identical sample dataset into both PostgreSQL and Oracle and verify that the SQL results are the same.

Performance testing: Performance testing is of paramount importance since transactions built into Oracle are different in PostgreSQL. Proper tuning at the app, driver, and database level can help you find and fix differences. SQL Server to Postgres – A Step-by-Step Migration Journey

Data Migration: The actual Oracle to Postgres migration may take time on account of them being heterogenous databases. Broadly three data migration approaches can be followed, either in solo fashion or in conjunction with each other. Oracle CDC (Change Data Capture): 13 Things to Know

• Snapshot Approach: Data is moved across in a single go.
• Snapshot in Parallel Approach: Data is moved in chunks (schema or table)
• Change Data Capture Approach (Continuous Replication): Data is loaded continuously syncing changes at source with the target. CDC with BryteFlow

The first two approaches may need application downtime and may impact source systems while the CDC approach means very little downtime and almost no impact on source systems.
Data Validation: Since Oracle to PostgreSQL is a heterogeneous migration, it is advisable to validate data after migration. This can verify that all data has been migrated without error and that the target database can be used safely. It can also validate the correct conversion of downstream objects (Views, PL/SQL, etc.). Learn about Oracle to SQL Server Migration

Oracle to Postgres Migration: What to keep in mind

Prerequisites for Oracle to Postgres Migration
Take care that the machine or system where the migration will take place has both Postgres and an Oracle client installed. Server resources like memory and network ports must be opened between source and target. The compatibility of the operating system must be verified in both source and destination along with the installed and configured data migration software and related drivers. Please note that the PostgreSQL target server should be large enough to scale up for the data migrating from Oracle. Oracle to SQL Server Migration: Reasons, Challenges and Tools

Transactions differ in Oracle and Postgres
Keep in mind that implementations of transactions in Oracle and PostgreSQL are a little different. Each executed statement starts a transaction in Oracle, and a COMMIT statement ends a transaction. PostgreSQL requires a BEGIN statement before activating a transaction and ends with the COMMIT statement. The default isolation level for both databases is Read Committed.

Language conversion between Oracle and Postgres
Although PL/pgSQL and the PL/SQL language are very similar, there are some important differences to be aware of. These two languages implement data types differently, so some work is required to convert properly. Since PostgreSQL doesn’t have packages, schemas are used to group functions as needed. This also means that you cannot use package level variables. Also, cursor variables are represented differently in these two languages and PostgreSQL function bodies must be written as string literals, so you must use dollar signs or escape single quotes in the function body. Oracle To Azure Cloud Migration (Know 2 Easy Methods)

Mapping data types between Oracle and Postgres
What is remarkable is that PostgreSQL has many more data types than Oracle. Here are some data mappings

Source

Some technical differences and points to keep in mind during the Oracle to Postgres migration process

• Empty strings and NULLs – In PostgreSQL, the IS NULL operator returns FALSE when applied to an empty string, but in Oracle it returns TRUE in such cases.
• Dual tables – Oracle databases require a FROM clause for every SELECT statement, so DUAL tables are used for such statements. PostgreSQL, on the other hand, does not require a FROM clause (and thus DUAL tables).
• DELETE statement – ​​Unlike Oracle, PostgreSQL’s DELETE statement works only in the FROM clause.
• SUBSTR – This function returns different results in Oracle and PostgreSQL and can cause problems.
• SYSDATE – PostgreSQL has no equivalent to Oracle’s SYSDATE function that provides execution date and time.

Challenges in moving from Oracle to PostgreSQL

Many of the challenges in moving from Oracle to PostgreSQL stem from the basic difference in the databases – Oracle DB being a purely commercial database while Postgres is open-source. The challenges in Oracle to Postgres migration range from those that can be easily fixed to some which are almost permanent blocks. Oracle To Azure Cloud Migration (Know 2 Easy Methods)

Packaged applications that do not support Postgres
If you are using a packaged business application for ERP/CRM that does not support the Postgres database, then migration is out of the question. Your best bet is to ask the vendor to add Postgres to the list of supported databases, or else adopt a new business application. Oracle to Redshift Migration Made Easy (2 Methods)

Mapping of Data Types and Schema Conversion
Moving from Oracle to Postgres involves mapping data from Oracle types to Postgres types. Luckily core Oracle data types can be mapped to Postgres data types quite easily. Some may need more work, for e.g.  the Numeric field in PostgreSQL can be used to map any Number data types. However, when used for joins (using a foreign key), it is less performant than using an int or bigint. Another difference is with the treatment of Timestamps with Time Zone field. The PostgreSQL Timestamp with time zone field varies a bit from the Oracle Timestamp with time zone field – it is actually the same as Oracle’s Timestamp with local time zone. Please note, you don’t need to bother about this with BryteFlow. It automates data mapping and schema and creates tables automatically on destination. Source to Target Mapping Guide

Moving heavy data volumes from Oracle to Postgres
In cases where data is larger than 1 TB, online migration might take too much time or bandwidth. In this case, an export, reload and resync strategy might be appropriate. Date-based partitioning can help to select data that needs to be moved offline. Historical data with time-stamp records can be moved from a particular date, data coming in after that can be moved with a good migration tool using CDC. Data can also be moved using an Oracle log sequence number (LSN) or application-specific sequential ID as the cutoff point from where data needs to be exported. The Complete Guide to Real-Time Data Replication

Migrating Code from Oracle to Postgres migration
This is a complex task and in moving code from the Oracle implementation to Postgres, you need to include all the packages, stored procedures, database functions and the application code that reads and writes to the database. The programming language in Postgres is PL/pgSQL which is quite similar to PL/SQL so software engineers should not find it a challenge to learn the syntax. Automated tools like BryteFlow provide automated conversions which can help a lot.
Oracle to Snowflake: Everything You Need to Know

Oracle niche features may not be supported in Postgres
Not all Oracle features may be supported by PostgreSQL or workarounds. One example is that there is no Postgres support for autonomous transactions which enable changes from a stored procedure to be committed within a rolled back larger transaction. One workaround is to use a DBLink “remote” connection to the same database. The function you want committed is executed as a “remote” call. Other issues are that core PostgreSQL does not support externally organized tables or enable access of files on the database file system directly. The latter two may be resolved by using a Foreign Data Wrapper (FDW). In some cases, if the Oracle system uses technical innovations or specific features only available in Oracle, it can also pose considerable challenges. Learn about Oracle to SQL Server Migration

Learning a new set of skills
If your DBAs and engineers are used to Oracle technology, they will now need to invest time and effort in learning Postgres technologies, advanced features and how to optimize performance to deliver the best ROI on new data initiatives. Though Oracle and PostgreSQL are both relational databases and use similar SQL syntax, do not underestimate the learning curve that may be required. Start training your team in advance for the transition which will sensitize them to the database differences, help them to avoid errors, and make for a relatively smoother migration.

Reducing downtime during Oracle to Postgres migration
By taking care of certain things, you can reduce the dreaded downtime during your migration. These include archiving old, unused data and cleaning up your source database. You will also have to test the Oracle Postgres migration early with different test scenarios and test cases. For large volumes of data, you could move data in batches or do a full initial refresh of data followed up by incremental data capture using CDC. Some migration tools like BryteFlow can provide nearly zero downtime. BryteFlow provides extremely high throughput with multi-threaded parallel loading and smart configurable partitioning for both initial and incremental data. About BryteFlow Ingest

Oracle to Postgres Migration Methods

Manual methods for moving data from Oracle to Postgres

Foreign Data Wrappers are extensions to access data from external databases
Foreign Data Wrappers (FDW) are libraries for PostgreSQL databases that allow you to communicate, access and extract data and schema from one database to another, while keeping the connection details hidden. Foreign Data Wrappers enable Postgres to connect with data sources and extract data. When a query like SELECT is executed on the external table, the resulting output from the external data source is extracted by the FDW and loaded to the external table. FDW was based on a SQL specification called SQL/MED (SQL Management of External Data) which was created to provide remote access to SQL databases. PostgreSQL uses parts of the SQL/MED specification, enabling users to access data that exists external to PostgreSQL with ordinary SQL queries. Some foreign data wrappers are available as contrib modules. Other types of foreign data wrappers may exist as third-party products. You can even code a customized foreign data wrapper for your requirements. 

• Oracle_fdw supports data migration from Oracle to Postgres
  Oracle_fdw is a PostgreSQL extension that provides a foreign data wrapper for easy and efficient access to Oracle databases. Oracle_fdw is supported since PostgreSQL 12.7. It includes WHERE clause and desired column pushdown, and extensive EXPLAIN support. Oracle_fdw is used to perform DML operations (SELECT, INSERT, UPDATE, DELETE) on Oracle servers in PostgreSQL, which is covered under the SQL Management of External Data (SQL/MED) standard.
  Learn more (external link)

• Orafce enables Oracle functions and packages in Postgres
  The Orafce extension enables use of some common Oracle functions and packages within PostgreSQL. It provides functions and operators that emulate some of the functions and packages from the Oracle database. The Orafce extension makes it easy to port Oracle applications to PostgreSQL. It also supports Oracle date formats and additional Oracle data types. RDS for PostgreSQL version 9.6.6 and above supports this extension. As an extension, Orafce’s job is to implement some Oracle functions and data types in PostgreSQL to support data migration. It delivers some Oracle packages as PL/pgSQL functions, and an implementation of the DUAL table.
  Learn more (external link)

Oracle to Postgres DB Link to enable selection of data from a remote database
You can move data from Oracle to Postgres by creating a DB Link. A DB Link or Database Link is a schema object whose function is to interact with remote database objects such as tables. Database Links can be used to select data from tables that are part of a remote database. DB Links are heterogeneous connections and allow you to select data from the Oracle DB using Oracle’s SELECT statement from within Postgres. You can then use Materialized Views to plan and store the results of those selections.
Learn more (external link)

Automated method with BryteFlow to move data from Oracle to Postgres

BryteFlow, a real-time CDC tool migrates data from Oracle to Postgres with zero coding
BryteFlow is a Cloud-native tool that specializes in the movement of enterprise-scale data from transactional databases (on-prem and Cloud) like SAP, Oracle, PostgreSQL, MySQL and SQL Server to popular destinations and cloud warehouses like Amazon S3, Amazon Redshift, Snowflake, Azure Synapse, Azure Data Lake 2, PostgreSQL, Google BigQuery, SQL Server, Kafka and Databricks. BryteFlow replicates data in real-time using Change Data Capture (CDC) and creates schema and tables automatically on the destination.

BryteFlow as an Oracle to Postgres Migration Tool

• BryteFlow’s Oracle to Postgres CDC has minimal impact on source systems.

• Fast Oracle to Postgres initial full refresh with multi-threaded, parallel loading and partitioning.
• Incremental data (deltas) replicated with continual log-based CDC.
• Postgres replication best practices built into the software.
• BryteFlow extraction is at least 6x faster than GoldenGate (1,000,000 rows in 30 secs).
• Oracle to Postgres replication has zero coding for extraction, merging, masking, or type 2 history. About Postgres CDC
• Data Reconciliation with BryteFlow TruData is automated with row counts and columns checksum.
• Supports high volume enterprise-scale data ingestion from Oracle to Postgres, both initial and incremental. About Oracle CDC
• Highly Available and Automatic Network Catchup.
• BryteFlow Ingest supports replication to Amazon Aurora Postgres on AWS. Aurora Postgres and How to Set Up Logical Replication
• Replication support for all Oracle versions, including Oracle 12c, 19c, 21c and future releases for the long term.

How to move data from Oracle to Postgres Step by Step with BryteFlow

Prerequisites: Please ensure that all prerequisites have been met and all security and firewalls have been opened between all the components.

Use the BryteFlow Ingest software and follow these steps
Set up the source connection details, assuming the source is Oracle.
Set up the source connection details. The source can be on the Cloud or on-premise.

 

Set up destination as Postgres:

Set up the table(s) to be replicated, select the primary key and the transfer type. ‘By Primary key with History’ automatically keeps SCD Type2 history on Postgres. ‘By Primary Key’ transfer type keeps a mirror of the source, without history.

 

Schedule your data replication time and get your data flowing to Postgres in near real-time.

Monitor the progress of the Oracle to Postgres replication

 

BryteFlow XL Ingest for Initial sync for really large tables
Use the BryteFlow XL Ingest software and follow these steps:
The tables can be any size, as terabytes of data can be brought across efficiently.
Configure the large table to be brought across. Then slice them

Load the slices in parallel

Monitor the process, as the table is brought across. The table is replicated on Postgres and is ready to use with SCD Type2 history, if configured. Overall status is displayed on the Dashboard.

Monitor everything through Dashboard

Validating the Data using TruData

BryteFlow TruData is an automated data reconciliation and validation software that checks for completeness and accuracy of your data with row counts and columns checksum.

Use the BryteFlow TruData software and follow these steps:
Configure the tables to be brought across and then slice them

Then validate the tables which will tell you whether the data is loaded correctly or not

 

Monitor the overall performance through the Dashboard.