Description

This course will show you how to migrate Oracle databases to PostgreSQL. You will learn to master the main similarities and differences between the two DBMS, particularly with regard to the SQL language, data types, implementation types, PL/SQL and PL/pgSQL languages...

Who is this training for ?

For whom ?

Project managers and database developers.

Prerequisites

Training objectives

Discover the main equivalences and differences between an Oracle DB and a PostgreSQL DB

Define a methodology for migrating from an Oracle DB to a PostgreSQL DB

Evaluate the differences in the implementation: instance, bases, schemas and users

Define the main differences between Oracle PL/SQL and PostgreSQL PL/pgSQL

Training program

• Introduction and reminders
  • - Reminders on the architecture and administration of an Oracle database.
  • - Reminders on the architecture of a PostgreSQL database.
  • - Reminders of SQL and PL/SQL languages.
  • - Comparison of Oracle and PostgreSQL architecture.
  • - Basics and schemas.
  • - Storage and tablespaces.
  • - Backups and restores.
  • - Practical work Creation of a PostgreSQL database.
  • - Creation of a role with an associated schema .
• Preparing for migration
  • - Migrating to PostgreSQL? The right questions to ask yourself.
  • - Checklist of important migration points.
  • - Study of applications and compatibility with PostgreSQL.
  • - Migration testing procedures.
  • - Procedure for testing the migrated environment.
  • - Practical work Creation of a checklist of points important to consider during a migration.
• Migration of different types and objects
  • - The types of an Oracle database vs the types of a PostgreSQL database.
  • - Tables and tables.
  • - The use of sequences and views .
  • - Table partitioning.
  • - Materialized views.
  • - Practical work Types.
  • - Tables.
  • - Updating views.
• Migration de SQL
  • - Update instructions.
  • - SQL functions.
  • - Pseudo columns.
  • - Syntactic specificities (operators, explicit conversion .
  • - ).
  • - Hierarchical queries and the use of the recursive WITH clause.
  • - The use of analytical functions.
  • - Practical work SQL functions.
  • - Pseudo columns.
  • - Using recursion with WITH.
  • - Analytical functions
• Migration PL/SQL vers PL/pgSQL
  • - Branches and loops.
  • - Cursors and cursor variables.
  • - Stored procedures and functions.
  • - Processing of errors.
  • - Triggers.
  • - Practical work FOR loops.
  • - "Procedures" stored in PostgreSQL.
  • - Error handling.
  • - Triggers.
• Post-migration tasks
  • - Adaptation of scripts.
  • - Statistics and backup management.
  • - Analysis of applications after migration.
  • - Optional tasks.