Oracle to PostgreSQL Migration Guide

With 100s of database management systems in the current landscape, organizations are often faced with critical decisions regarding their choice of platforms. One such pivotal choice involves the migration from Oracle to PostgreSQL.

 
This introductory guide aims to provide a brief overview of the purpose behind such migrations and the compelling reasons that drive organizations to transition from the established Oracle environment to the open-source PostgreSQL system.

Why Migrate from Oracle to PostgreSQL?

The decision to migrate from Oracle to PostgreSQL is a strategic one, influenced by a variety of factors that organizations weigh carefully. Here are some key considerations:

• Cost Efficiency: PostgreSQL eliminates hefty licensing costs associated with Oracle, offering a cost-effective alternative.
• Open-Source Flexibility: PostgreSQL’s open-source nature provides flexibility, agility, and a vibrant community for continuous development.
• Vendor Independence: Migration liberates organizations from vendor lock-in, allowing strategic decision-making based on their unique needs.
• Feature Parity: PostgreSQL’s evolving feature set narrows the gap with Oracle, offering required capabilities without the associated costs.
• Performance and Scalability: PostgreSQL’s improved performance and scalability cater to growing data volumes and user loads effectively.
• Community Support: A robust community ensures prompt responses to challenges, continuous improvements, and shared knowledge.
• Open Standards: PostgreSQL aligns with open standards, making it a choice for organizations committed to interoperability.
• Adaptation to Modern Technologies: PostgreSQL’s commitment to staying current aligns with evolving technology landscapes.

Understanding the Differences Between Oracle and PostgreSQL

This section delves into the key differences in syntax, features, and concepts, shedding light on how PostgreSQL diverges from Oracle in handling transactions, indexing, and stored procedures.

Planning the Oracle to PostgreSQL Migration

Assessment Phase: For the initial assessment, use AWS Schema Conversion Tool (SCT) for Oracle to PostgreSQL. Install SCT, create a project, and analyze the assessment. As per the SCT analysis report, categorize the complexity of the code such as Simple, Medium, Complex.

Database Assessment

• Schema Analysis: Conduct a comprehensive analysis of your Oracle database schema. Understand the structure, relationships, and dependencies.
• Data Examination: Scrutinize the data within your Oracle database. Identify any data types, constraints, or features that may require adjustment during migration.
• Application Code Review: Examine the existing application code that interacts with the Oracle database. Identify SQL queries, stored procedures, and triggers that may need modification.

Tools and Techniques for Migration

There are several methods for migrating data from an Oracle database into PostgreSQL. For example, we can use the following methods:

• Ora2PG: Leverage ora2pg for comprehensive assessment and migration planning. It provides insights into schema structures, dependencies, and potential challenges.
• AWS DMS: The AWS Database Migration Service (DMS) helps you migrate databases to AWS quickly and securely. The source database remains fully operational during the migration, minimizing downtime to applications that rely on the database.
• Oracle_FDW: PostgreSQL has the ability to connect to external systems and retrieve data through foreign data wrappers (FDWs). Upon executing a query (such as SELECT) on a foreign table, the Foreign Data Warehouse retrieves the result from the external data source and outputs it through the foreign table.

Choosing the Right PostgreSQL Version

Considerations

• Stability vs. Features: Balance the stability of long-term support (LTS) versions with the desire for the latest features. Assess the trade-offs and align with organizational priorities.
• Compatibility: Evaluate the compatibility of PostgreSQL versions with your application and its dependencies. Consider potential impacts on third-party extensions.

Compatibility Issues

• Test Environment: Set up a test environment to assess compatibility. Pilot the migration in a controlled setting to identify and address any unexpected issues.
• Third-Party Tools and Extensions: Verify the compatibility of third-party tools and extensions with your chosen PostgreSQL version. Plan for updates or alternatives if needed.
• Data Type Mapping: Understand any changes in data type mapping between Oracle and PostgreSQL. Adaptation may be required for seamless data migration.
• Performance Considerations: Evaluate performance improvements or changes introduced in newer PostgreSQL versions. Optimize configurations based on your application’s requirements.

Architecture Assessment and Cleanup

When migrating, assess and optimize your database architecture. Eliminate unnecessary objects, like old temporary tables or backup data. For large files, explore cost-effective storage options to reduce database size and backup resource requirements. Consider purging static historical data or moving it to archival storage. Transitioning from Oracle to Postgres allows for the separation of OLTP and analytics, enhancing responsiveness and analytical capabilities.

Setting Up PostgreSQL

Installation and configuration:

• Refer to the following link for PostgreSQL installation on Linux: https://www.ubuntumint.com/install-postgresql-rhel/
• After installation, initialize the database cluster using the initdb command.
• Modify the postgresql.conf file to adjust settings like listen_addresses, max_connections, shared_buffers and few more configuration settings as per the CPU and Memory of the target server.
• Configure the pg_hba.conf file to control client authentication.
• Start and enable the PostgreSQL service.
  • sudo systemctl start postgresql-14
  • sudo systemctl enable postgresql-14

Steps to Install Ora2Pg in Windows

Download & Install:

• Strawberry Perl 64-bit
• Ora2Pg

Here is the link for reference to install Ora2Pg on Linux machine.

https://ora2pg-samrat.blogspot.com/

AWS SCT

AWS SCT automates the conversion of database schema definitions, including tables, views, stored procedures, and other database objects, from Oracle syntax to PostgreSQL syntax. It assists in translating database code, such as PL/SQL stored procedures, triggers, and functions, to their equivalent in PostgreSQL’s PL/pgSQL or other supported languages. It tries to convert 50-60% of the code to the equivalent Postgresql.

The following are some key distinctions between Oracle and Postgres to be aware of when getting ready for schema conversion.

Columns During the Postgres to Oracle Migration

Postgres did not support virtual columns until version 12, hence when migrating, users were advised to convert these to views. These days, Postgres supports generated columns, which are similar to Oracle’s virtual columns in many ways.

Identifiers for Transferring from Oracle to PostgreSQL

Oracle changes schema, table, column, and function names to uppercase unless they are included in quotes, whereas Postgres does the opposite. As long as the application consistently quotes or does not quote the IDs, you should be fine.

Indexes When Moving from Oracle to PostgreSQL

B-tree and descending indexes should work with Postgres. The reverse key, bitmap, and join indexes are not yet supported. Postgres does not support global indexes.

Data Types When Migrating from Oracle to Postgres

The following chart lists significant differences between the data types in both of these databases.

By default, it converts all Oracle NUMBER datatype to Postgres NUMERIC.

Be Careful.

Oracle’s NUMBER has a precision up to 38 digits. Postgres NUMERIC can store 131072 digits before decimal point and 16383 after decimal point.

Never change your PK/FK datatype from NUMBER in Oracle to PG NUMERIC. Change them to BIGINT.

BIGINT is more performant than NUMERIC and takes less storage space (8 bytes). Usually, you will see a huge performance hit when you join Tables on PKs that are stored as NUMERIC.

TEXT is faster than VARCHAR in Postgres because VARCHAR is a wrapper around TEXT.

Oracle’s LOBs to be converted to TEXT. TEXT can store up to 1 GB and you do need dbms_lob procedures to work on LOBs in Oracle and cannot perform order by or group by on LOBs whereas in TEXT you can use length() , order, and group by on TEXT columns.

Be aware of Timezone/Timestamps data types. Postgres Timestamp with time zone is equivalent to Oracle’s Timestamp with Local Timezone. Postgres’s clock_timestamp is equivalent to Oracle’s systimestamp.

Oracle to Postgres Data Migration

Selecting the right data migration approach is crucial for a successful transition from Oracle to PostgreSQL. Evaluate each option based on your specific requirements, considering factors such as downtime tolerance, data volume, complexity, and available resources.

There are various techniques to data migration and tools available on the market. Typically, they are classed in three ways.

• Snapshot: Data moved all at once.
• Snapshot in parallel: Data transported in parts (schema or table).
• Change Data Capture (Replication): Data is loaded continually.

Here are some of the most widely used methods for data migration:

1. Ora2pg tool: It assists in migrating data, schema, and even SQL queries from Oracle to PostgreSQL.
2. Oracle FDW: To access tables present in a remote Oracle database server from a local PostgreSQL database, we can use the oracle_fdw Foreign Data Wrapper, which is covered under the SQL Management of External Data standard.
3. Replication tools like HVR, Oracle Goldengate for CDC replication from Oracle to PostgreSQL.
4. Symmetric DS: SymmetricDS is a free tool to migrate small and medium size tables.
5. EDB migration toolkit and EDB replication server are tools from EDB.

Oracle to PostgreSQL Application Changes

Updating SQL Queries:

• Identifying and modifying Oracle-specific SQL queries.
• Considerations for applications relying on Oracle-specific features.
• Convert partitions from Oracle tables into equivalent partitions in PostgreSQL, such as Hash, Range, or List.
• Incorporate the use of partition key columns in reporting queries to maximize the effective utilization of partitions.

 
Connection Pooling and Configuration Changes:

• Adjusting connection pool settings and configurations for the new PostgreSQL environment.
• Use pg_bouncer for connection pool issue along with load balancer.

Testing, Quality Assurance, and Maintenance of Oracle to PostgreSQL Migrations

• Comprehensive testing.
• Detailed testing strategies to ensure data integrity and application functionality.
• Automation tools and scripts for testing.
• Setup monitoring tools for alerts.
• Migrate Database jobs from Oracle.
• Setup Daily\weekly maintenance jobs for reindex, vacuum analyze.
• Setup Pgbackrest for daily\weekly backups and archive_log backups.

Oracle to Postgres Go-Live and Post-Migration Steps

Planning for Downtime:

• Strategies for minimizing downtime during the final migration.
• Rollback plans in case of unforeseen issues.
• Setup CDC jobs for replication using HVR or Golden Gate.
• Conduct testing of diverse data loads, application testing, connection pooling issues in lower environments before progressing to the production phase.
• Guide on monitoring and optimizing PostgreSQL for performance.
• Common performance bottlenecks and solutions

Post-Migration Checks:

• Checklist for verifying data consistency and application performance.
• Addressing issues that may arise after migration.

Want to learn more about Oracle to Postgres migrations? Read our white paper “Going to Open-Source: Why and How to Migrate from Oracle to PostgreSQL.”