天天看點
傳回

Migration Oracle to PostgreSQL "百家"文檔集

标簽

PostgreSQL , Oracle

https://github.com/digoal/blog/blob/master/201805/20180505_06.md#%E8%83%8C%E6%99%AF 背景

https://github.com/digoal/blog/blob/master/201805/20180505_06.md#2002-porting-from-oracle-to-postgresql 2002 Porting from Oracle to PostgreSQL

《PDF Download》

https://github.com/digoal/blog/blob/master/201805/20180505_06.md#agenda Agenda

  • SQL Syntax, Functions, Sequences, Etc.
  • Database Server General Characteristics
  • Data Types and JDBC
  • Other Considerations:
  • References:

https://github.com/digoal/blog/blob/master/201805/20180505_06.md#2008-porting-oracle-applications-to-postgresql 2008 Porting Oracle Applications to PostgreSQL

https://github.com/digoal/blog/blob/master/201805/20180505_06.md#agenda-1

  • Porting the SQL
  • Porting Tools
  • PL/SQL vs. PL/pgSQL
  • Interfaces
  • Project Management

https://github.com/digoal/blog/blob/master/201805/20180505_06.md#2011-oracle-to-postgres-migration 2011 Oracle to Postgres Migration

https://github.com/digoal/blog/blob/master/201805/20180505_06.md#agenda-2

  • Schema Migration
  • Data Type Migration
  • Data Migration
  • Business Logic Migration
  • Other Objects
  • Connectors / Drivers / Libraries
  • Application / SQL Migration
  • DBA Migration
  • Tools
  • Ora2pg

https://github.com/digoal/blog/blob/master/201805/20180505_06.md#2012-migrating-oracle-queries-to-postgresql 2012 Migrating Oracle queries to PostgreSQL

推薦

https://github.com/digoal/blog/blob/master/201805/20180505_06.md#agenda-3

Why?

How?

Data migration

Query conversion

https://github.com/digoal/blog/blob/master/201805/20180505_06.md#2016-postgresql-porting-guide 2016 PostgreSQL Porting Guide

https://github.com/spacewalkproject/spacewalk/wiki/PostgreSQLPortingGuide

https://github.com/digoal/blog/blob/master/201805/20180505_06.md#agenda-4

  • General rules
  • Problems and how to solve them
    • The VARCHAR-NULL problem
    • The DECODE/NVL2 functions problem
    • The NVL function problem
    • JOIN in ANSI syntax
    • SELECT column AS alias
    • Default cast to integer
    • The sysdate problem
    • Date arithmetics
    • Triggers must return something
    • Triggers mustn't touch old if they are on insert
    • Procedure call from Hibernate
    • ORDER BY expression in DISTINCT select
    • Rownum problem
    • Subquery with no alias
    • Composite type accessing
    • Concatenating of evr
    • Global function evr_t_as_vre_simple
    • No autonomous transactions
    • NUMBER to NUMERIC
    • Calling procedures
    • SELECT UNIQUE
    • TO_NUMBER function
    • TO_DATE function
    • DELETE without FROM
    • DUAL table
    • MINUS keyword
    • Bind parameter with space
    • Portable nextval
    • Recursion with opened cursors
    • Anonymous procedural SQL blocks, in Python (backend)
    • Relation (table) does not exists
    • Inserting / writing blob in Python (backend)

https://github.com/digoal/blog/blob/master/201805/20180505_06.md#2016-migrations-to-postgresql-from-oracle 2016 Migrations to PostgreSQL (from Oracle)

https://github.com/digoal/blog/blob/master/201805/20180505_06.md#agenda-5

  • Why migrate to PostgreSQL ?
  • Migration process - overview
  • Preliminary Migration Analysis
  • Migration challenges
  • Database Design / Architecture
  • High Availability challenges
  • Development challenges (database)
  • Database migration
  • PL/SQL Migration
  • Migration Large Objects
  • Pg_largeobjects - Limitation
  • Migrating JSON Data
  • Development challenges (Migrating Oracle SQLs for Application)
  • SQLs Migration for application

https://github.com/digoal/blog/blob/master/201805/20180505_06.md#2016-oracle-to-postgres-migration 2016 Oracle to Postgres Migration

https://github.com/digoal/blog/blob/master/201805/20180505_06.md#agenda-6

  • part 1
    • Oracle Database ™
    • how an application interacts with an RDBMS
    • the ora2pg tool
  • part 2
    • PostgreSQL features for DBAs and developers

https://github.com/digoal/blog/blob/master/201805/20180505_06.md#2016-postgresql-for-oracle-dba 2016 PostgreSQL for Oracle DBA

https://github.com/digoal/blog/blob/master/201805/20180505_06.md#agenda-7

  • Mind Migration
  • Some terminology
  • “Architecture”
  • Security
  • Backup and Recovery
  • High Availability / Disaster recovery
  • Other unordered stuff to consider

https://github.com/digoal/blog/blob/master/201805/20180505_06.md#%E5%85%B6%E4%BB%96-postgresql-for-oracle-dba 其他 PostgreSQL for Oracle DBA

https://wiki.postgresql.org/wiki/PostgreSQL_for_Oracle_DBAs 《Become a PostgreSQL DBA》

https://github.com/digoal/blog/blob/master/201805/20180505_06.md#2017-oracle-to-postgresql-migrations 2017 Oracle to PostgreSQL Migrations

https://github.com/digoal/blog/blob/master/201805/20180505_06.md#agenda-8

  • Introduction
  • Executive Summary
  • Benefits of Migrating to PostgreSQL
  • When to Migrate
  • Common Database Migration Challenges and Risks
  • Migration Life Cycle
  • Migration Service
    • Scope of Service
    • Identifying Migration Candidates
  • Analyzing Migration Candidates
  • Planning a Migration
  • Migrating an Application
  • Testing the Application
  • Production Deployment
  • Conclusion
  • About OpenSCG

https://github.com/digoal/blog/blob/master/201805/20180505_06.md#2018-oracle-database-11g12c-to-amazon-aurora-with-postgresql-compatibility-96x 2018 Oracle Database 11g/12c To Amazon Aurora with PostgreSQL Compatibility (9.6.x)

極為詳細

https://github.com/digoal/blog/blob/master/201805/20180505_06.md#agenda-9

  • Disclaimer
  • Automatic Migration of Oracle Schema Objects Using the AWS Schema Conversion Tool
  • Migration SQL & PL/SQL (Manual)
  • Migration Tables & Indexes (Manual)
  • Migration Database Objects (Manual)
  • Migration Database Administration (Manual)

https://github.com/digoal/blog/blob/master/201805/20180505_06.md#oracle-to-postgresql-migration---automatic-tool-research Oracle to PostgreSQL migration - automatic tool research

http://wiki.openbravo.com/wiki/ERP_2.50:Oracle_to_PostgreSQL_migration_-_automatic_tool_research

https://github.com/digoal/blog/blob/master/201805/20180505_06.md#agenda-10

  • Guideline compliant database
  • Database full of Oracle specific elements
    • Syntax
    • Data Types
    • NULL
    • Sequences
    • Other Joins

    • NLS * vs. LC *
    • ROWNUM and ROWID
    • Things That Won’t Work Directly
    • PL/SQL
      • Triggers
      • Procedures/ Functions
    • ora2pg
      • Installation
      • Configuration
      • Execution
      • Results
    • orafce
  • Links
  • Conclusions

https://github.com/digoal/blog/blob/master/201805/20180505_06.md#oracle-to-postgres-conversion Oracle to Postgres Conversion

https://wiki.postgresql.org/wiki/Oracle_to_Postgres_Conversion https://wiki.postgresql.org/wiki/Oracle

https://github.com/digoal/blog/blob/master/201805/20180505_06.md#agenda-11

  • What you should know before you begin
  • Transactions
  • Grammar Differences
    • Sysdate
    • The Dual Table
    • Decode
    • NVL
    • Subquery in FROM
  • Functional Differences
    • Outer Joins
    • CONNECT BY
    • NO_DATA_FOUND and TOO_MANY_ROWS
    • Empty strings and NULL values
    • Numeric Types
    • Date and Time
    • CLOBs
    • BLOBs
  • External Tools
sql Oracle 關系型資料庫 資料庫 postgresql oracle sql結果 oracle官方文檔 oracle sql問題 sql oracle查詢 oracle sql索引
上一篇: ETL for Oracle to PostgreSQL 3 - DATAX
下一篇: Spring-Beans小結Spring-Beans

繼續閱讀