Download as PDF, PPTX
Uploaded byUmair Mansoob
Migrating Oracle database to PostgreSQL
This document discusses migrating an Oracle database to PostgreSQL. It covers initial discovery of the Oracle database features and data types used. A migration assessment would analyze data type mapping, additional PostgreSQL features, and testing requirements. Challenges include porting PL/SQL code, minimizing downtime during migration, and comprehensive testing of applications on the new PostgreSQL platform. Migrating large data sets and ensuring performance for critical applications are also challenges.
In this document
Powered by AI
Introduction to the migration process from Oracle Database to PostgreSQL covering topics like initial discovery, assessment, and migration challenges.
Discusses cost-saving benefits of PostgreSQL, including licensing, support, and training. Highlights PostgreSQL as a leading open-source database.
Focuses on the initial discovery phase for migration, detailing preparations like defining success criteria and reviewing database features.
Covers the migration assessment, including analyzing data types, reviewing PL/SQL, and creating a migration plan with a timeline.
Provides a reference for mapping data types from Oracle to PostgreSQL to ensure compatibility and correctness in migration.
Reviews notable differences and considerations between Oracle and PostgreSQL regarding features like DUAL table and transaction control.
Compares PL/SQL with PL/pgSQL, noting syntax changes and operational differences critical for migration.
Outlines a systematic migration plan encompassing schema analysis, data migration, code adjustments, and testing.
Identifies key challenges faced during migration, including data size, performance issues, and training needs.
A concluding slide thanking the audience and providing contact details for further inquiries.
More Related Content
![[EPPG] Oracle to PostgreSQL, Challenges to Opportunity](https://cdn.slidesharecdn.com/ss_thumbnails/eppg-oracletopostgresqlchallengestoopportunity-190409033058-thumbnail.jpg?width=600ounds&width=560&fit=bounds)
Similar to Migrating Oracle database to PostgreSQL
Migrating Oracle database to PostgreSQL
- 1. Migrating Oracle Databaseto PostgreSQL
- 2. Topics Covered • WhyPostgresSQL • Initial Discovery • Migration Assessment • Data Type Mapping • Other Considerations • PL/SQL Vs PLPGSQL • Migration Plan • Migration Challenges Umair Mansoob
- 3. Why PostgreSQL • Savemoney on license cost • Save money on features like partitioning and high availability • Save big money on support • Substantial reduction of training + consulting costs • Break free from vendor lock in • Best in-class open source database Umair Mansoob
- 4. Initial Discovery • Howmuch PL/SQL code reside in the Oracle database • Define success criteria for migration • Review application certification for PostgreSQL • Review all Oracle database features in use • Estimate cost and effort for migration • Review Oracle data types & objects • Check Database Size & Storage requirements • Gather Performance requirements Umair Mansoob
- 5. Migration Assessment • Performdetail analysis of all data types used by Oracle database • Review PL/SQL code in detail to estimate effort and cost for conversion • Analyze possibility of migrating programming logic to application • Analyze additional database features like compression, encryption and HA • Creation detail migration plan with timeline • Review existing application test plans for conversion • Design comprehensive application testing plan for migration Umair Mansoob
- 6. Data Type Mapping OraclePostgreSQL VARCHAR2(n) VARCHAR(n) CHAR(n) CHAR(n) NUMBER(n,m) NUMERIC(n,m) NUMBER(4) SMALLINT NUMBER(9) INT NUMBER(18) BIGINT NUMBER(n) NUMERIC(n) DATE TIMESTAMP(0) TIMESTAMP WITH LOCAL TIME ZONE TIMESTAMPTZ CLOB TEXT BLOB RAW(n) BYTEA(1 GB limit) Large object Umair Mansoob
- 7. Other Considerations Oracle PostgreSQL DualTable In PostgreSQL, FROM clause is not mandatory so DUAL table is not necessary SysDate In PostgreSQL there are multiple methods to get the date and time Synonym CREATE SYNONYM is not supported in PostgreSQL Sequence There is a slight difference in the syntax of sequence in Oracle and PostgreSQL. Autonomous transaction Not supported Externally organized tables Not supported Transaction control PostgreSQL does not allow transaction control inside of PL/pgSQL Oracle RAC No. A shared nothing architecture. Oracle Data Guard PostgreSQL support Master-Master replication Partition Tables Partitioning and sub-partitioning is supported but no interval partitioning yet Umair Mansoob
- 8. PL/SQL Vs PLPGSQL •PL/pgSQL is similar to PL/SQL in many aspects • It is a block-structured, imperative language, and all variables have to be declared. • There is no PRAGMA statement in PostgreSQL. • You cannot issue COMMIT in a PL/pgSQL function • The show errors command does not exist in PostgreSQL, and is not needed since errors are reported automatically. • The type name varchar2 has to be changed to varchar or text. Umair Mansoob
- 9. Migration Plan Analyze SchemaData Code Test Cutover Umair Mansoob
- 10. Migration Challenges • Roadfor migration is not always smooth • Migrating large data set ( Network Bandwidth ) • Porting PL/SQL Code to PostgreSQL • Performance compression for critical application • Minimize migration downtime • Testing your applications is a huge part of the total effort in a migration • Training for your lead architects, DBAs, and developers Umair Mansoob
- 11.