Migration techniques

At a high level, a database migration from Oracle to Postgres includes:

• Transforming the schema from Oracle’s extended version of the SQL standard to a Postgres-compliant version, which is generally more compliant with standards
• Translating Oracle-specific data type definitions to Postgres-specific ones
• Rewriting queries and stored procedures
• Copying data
• Updating application APIs to use Postgres JDBC, .NET, ODBC drivers, as Oracle has extended the standard protocols with proprietary extensions
• Verifying that the migrated database meets all the nonfunctional requirements related to performance, manageability, high availability, and integration with enterprise security requirements.

You can perform the migration manually or in an automated fashion.

Manual approach for transforming databases

Executing these transformations manually is expensive and error prone. Except for very small databases without any business logic and extremely simple application logic, this approach isn't practical.

Automated approaches to database migration

Two automated approaches are usually used instead of a pure manual approach:

The native compatibility approach extends one database’s capabilities and creates a native implementation of another database's specific extensions of the SQL standard, including the APIs and protocols. EDB Postgres Advanced Server is an enhanced version of PostgreSQL that offers compatibility with Oracle in the following areas:

• Oracle-specific and syntax-compatible database object types
• Oracle-specific data types
• Oracle-specific SQL extensions
• Oracle PL/SQL support as a built-in native procedural language
• Oracle data dictionary views (i.e., ALL_, DBA_, USER_ views)
• Oracle built in PL/SQL packages
• Oracle-like database drivers
• Oracle work-alike tools for DBAs

EDB Postgres Advanced Server doesn't fully implement all Oracle-specific features in these areas. Nevertheless, the compatibility is extensive and covers many of the most commonly used Oracle constructs. As a result, the compatibility features that were implemented makes most migrations off of Oracle easier.

The translation approach uses automated tools to rewrite (or translate) the Oracle definitions, queries, and stored procedures to Postgres-compatible versions. The EDB Migration Portal repair handlers use this approach to automatically convert a large number of objects that contain constructs that don't have a compatible implementation in EDB Postgres Advanced Server. The translation approach is the only approach available with open-source migration tools and those provided by other vendors to target pure open-source PostgreSQL. However, depending on the number of objects that need to be translated, the time and effort required to get to a pure open-source solution might not be supported by your schedule or resources.

While most migrations are greatly simplified through the combination of these two automation approaches, some objects usually still remain that require manual conversion. Refer to the workarounds in the Migration Portal Knowledge Base or contact EDB's Professional Services to help identify solutions for any manual conversions that remain.