Oracle to Postgres Migration - PGCon

Oracle to Postgres

Migration

Considerations, Hurdles, and possible Solutions

Presented by Gurjeet Singh

May 19, 2011

EnterpriseDB, Postgres Plus and Dynatune are trademarks

of EnterpriseDB Corporation. Other names may be

trademarks

ofCorporation

their respective

? 2010.

All rights

(C) EnterpriseDB

2011. Allowners.

Rights Reserved.

Company

Confidential

reserved.

1

Agenda




Schema Migration




Data Type Migration




Data Migration




Business Logic Migration




Other Objects




Connectors / Drivers / Libraries




Application / SQL Migration




DBA Migration




Tools




Ora2pg

2

(C) EnterpriseDB Corporation 2011. All Rights Reserved. Company Confidential

Schema Migration

3

(C) EnterpriseDB Corporation 2011. All Rights Reserved. Company Confidential

Schema Migration







Schema




A.K.A ¡°User¡± in Oracle




Oracle gives every user her own schema, by default

?

Create a user and schema by the same name

?

The first component in search_path is $user, by default

Identifiers




Names of schema, tables, columns, functions, ¡­




Oracle converts them to UPPER CASE, unless quoted




Postgres converts them to lower case, unless quoted




You're safe if application quotes/does not quote the identifiers

?

Consistency is the key

4

(C) EnterpriseDB Corporation 2011. All Rights Reserved. Company Confidential

Schema Migration




Tables




CREATE TABLE is mostly compatible, except

?

Global Temporary table




?

Partition clauses




?




Use Inheritance, Triggers, and CHECK Constraints

INITTRANS, MAXEXTENTS




?

Use LOCAL TEMP tables

Remove them

PCTFREE : Use fillfactor

Columns




Virtual Columns: Use views




Data Types

5

(C) EnterpriseDB Corporation 2011. All Rights Reserved. Company Confidential

a.k.a Storage Clause

 ................
................

In order to avoid copyright disputes, this page is only a partial summary.

Google Online Preview   Download