Ora2Pg による Oracle から PostgreSQL へのデータ移行手順

はじめに
基本的な作業の流れ
Ora2Pg 設定ファイルの準備
Oracle への接続確認
テーブル定義のエクスポート
シーケンスのエクスポート
各種エクスポートオプション
データのエクスポート
テーブルのインポート
テーブルデータのインポート
まとめ

はじめに

Ora2Pg は、Oracle データベースを PostgreSQL に移行するためのツールです。

Oracle データベースに接続してスキャンし、自動的にその構造またはデータを抽出し、PostgreSQL データベースにロードできるSQLスクリプトを生成します。

Github darold / ora2pg
Home Ora2Pg

ツールは、 Perl 製だったり、ドライバの設定などもあり、セットアップが面倒なのですが、セットアップ済みの Docker コンテナが存在するので、これを使ったデータベース移行についての流れを説明します。

基本的な作業の流れ

大きな作業の流れは以下のようになります。

Ora2Pg の設定ファイル ora2pg.conf に Oracle の接続設定などを定義する
エクスポートタイプ -t TABLE を指定してテーブル定義をSQLスクリプトとしてエクスポートする
エクスポートタイプ -t SEQUENCE を指定してシーケンス定義をSQLスクリプトとしてエクスポートする
その他必要なオブジェクトをエクスポートタイプで指定してエクスポートする
テーブルデータをエクスポートタイプ -t COPY を指定してシーケンス定義をSQLスクリプトとしてエクスポートする
移行先の PostgreSQL へテーブル定義など、エクスポートしたSQLスクリプトを適用する
移行先の PostgreSQL の外部キー制約など、トリガを無効化する
移行先の PostgreSQL へエクスポートしたデータを投入する
移行先の PostgreSQL の外部キー制約など、トリガを有効化する

通常は、別サーバで動作している Oracle データベースが移行元の対象となりますが、ここでは、ローカルの Podman コンテナで移行元の Oracle が動作しているものとして手順を説明します。

Podman コンテナで Oracle を立ち上げるには以下を参照してください。

https://blog1.mammb.com/entry/2023/08/06/090000

Ora2Pg 設定ファイルの準備

ここでは、Docker イメージとして georgmoser/ora2pg を利用します。このイメージで引数を指定してコンテナ起動することもできますが、ここでは設定ファイル ora2pg.conf を定義して実行することにします。

最初に作業ディレクトリと、設定ファイルを配備する config ディレクトリ、移行スクリプト出力先となる data ディレクトリを作成しておきます。

$ mkdir ora2pg
$ cd ora2pg
$ mkdir config
$ mkdir data

設定ファイルのひな形を config に配備します(georgmoser/ora2pg イメージに含まれる ora2pg.conf を取得します)。

$ curl -o config/ora2pg.conf https://raw.githubusercontent.com/Guy-Incognito/ora2pg/master/config/ora2pg.conf

config/ora2pg.conf を編集します。

同ホストのコンテナで起動している Oracle23c Free を移行元とすると、接続先は以下のように指定できます。

ORACLE_DSN   dbi:Oracle:host=host.containers.internal;service_name=FREEPDB1;port=1521
ORACLE_USER <user>
ORACLE_PWD  <pass>

ホストの指定 host.containers.internal は、ローカルで動作している Podman コンテナをターゲットにしています。Docker を使う場合は host.docker.internal を指定します(ホストがWindows/MacOSの場合)。

上記では、サービス名での接続指定 service_name=FREEPDB1 としています。SIDを指定する場合は sid=orcl のように指定します。

エクスポート対象とするスキーマを指定するには以下のディレクティブで指定します。スキーマが明示されていない場合は、システムスキーマ/システムロール以外のすべてのオブジェクトをエクスポートします。

# Oracle schema/owner to use
#SCHEMA     SCHEMA_NAME

スキーマ指定しない場合、例えば Oracle23c では、GGSHAREDCAP スキーマの change_table_inc_segment$ テーブルといった不要なものがエクスポート対象に入ってしまうなどするため、通常はスキーマを明示した方が良い。

Oracle への接続確認

以下で接続確認します。

$ podman run -it --rm --name ora2pg -v ${pwd}/config:/config -v ${pwd}/data:/data georgmoser/ora2pg ora2pg -t SHOW_VERSION -c /config/ora2pg.conf

-t SHOW_VERSION でOracleデータベースサーバーのバージョンが表示されます。例えば以下のようにバージョンが表示されれば接続成功です。

Oracle Database 23c Free, Release 23.0.0.0.0

接続できない場合は、大抵の場合、ホストのファイアウォールでブロックされているので、受信接続を許可するよう構成します。

テーブル定義のエクスポート

テーブル定義をエクスポートするには、エクスポートタイプに -t TABLE を指定します。

エクスポート用のフォルダを作成し、

$ mkdir data/tables

以下でエクスポートします。

$ podman run -it --rm --name ora2pg -v ${pwd}/config:/config -v ${pwd}/data:/data georgmoser/ora2pg ora2pg -t TABLE -c /config/ora2pg.conf -b /data/tables

-b /data/tables で出力先のベースディレクトリを指定しています。/config/ora2pg.conf の以下で設定すればコマンドラインからの指定は不要です。

# Base directory where all dumped files must be written
OUTPUT_DIR  /data

${pwd}/data/tables に output.sql が以下のような内容で生成されます。

-- Generated by Ora2Pg, the Oracle database Schema converter, version 24.0
-- Copyright 2000-2023 Gilles DAROLD. All rights reserved.
-- DATASOURCE: dbi:Oracle:host=host.containers.internal;service_name=FREEPDB1;port=1521

SET client_encoding TO 'UTF8';

\set ON_ERROR_STOP ON

CREATE TABLE employee (
    id bigint NOT NULL,
    ...

シーケンスのエクスポート

シーケンス定義をエクスポートするには、エクスポートタイプに -t SEQUENCE を指定します。エクスポート先の PostgreSQL のバージョン(メジャーバージョン)は PG_VERSION ディレクティブで指定することができます。

エクスポート用のフォルダを作成し、

$ mkdir data/sequence

以下でエクスポートします。

$ podman run -it --rm --name ora2pg -v ${pwd}/config:/config -v ${pwd}/data:/data georgmoser/ora2pg ora2pg -t SEQUENCE -c /config/ora2pg.conf -b /data/sequence

${pwd}/data/sequence に output.sql が以下のような内容で生成されます。

-- Generated by Ora2Pg, the Oracle database Schema converter, version 24.0
-- Copyright 2000-2023 Gilles DAROLD. All rights reserved.
-- DATASOURCE: dbi:Oracle:host=host.containers.internal;service_name=FREEPDB1;port=1521

SET client_encoding TO 'UTF8';

\set ON_ERROR_STOP ON

CREATE SEQUENCE seq_gen_sequence INCREMENT 50 MINVALUE 1 NO MAXVALUE START 99999 CACHE 20;

各種エクスポートオプション

エクスポートには様々なオプションが利用できます。

ごく一部をここに説明します。特に boolean型への変換は特によく使うものと思います。

テーブル名のリマップ

REPLACE_TABLES ディレクティブにより、Oracleテーブル名のリストをPostgreSQLテーブル名にリマップすることができます

ora2pg.conf の以下のディレクティブで指定します。

# You may wish to change table names during data extraction, especally for
# replication use. Give a list of tables separate by space as follow.
#REPLACE_TABLES ORIG_TB_NAME1:NEW_TB_NAME1 ORIG_TB_NAME2:NEW_TB_NAME2

ORIG_TB_NAME1:NEW_TB_NAME1 のように Oracleテーブル名:PostgreSQLテーブル名として指定します。

カラム名のリマップ

REPLACE_COLS ディレクティブにより、カラム名のリマップも可能です。

# You may wish to change column names during export. Give a list of tables
# and columns separate by space as follow.
#REPLACE_COLS   TB_NAME(ORIG_COLNAME1:NEW_COLNAME1,ORIG_COLNAME2:NEW_COLNAME2)

テーブル名(Oracleカラム名:PostgreSQLカラム名) の書式で指定します。

boolean型への変換

Oracle ではboolean型が無いため、多くの場合 number(1,0) や char(1) のような定義で代用していることでしょう。

これらをブールとして移行する場合には、REPLACE_AS_BOOLEAN ディレクティブを使います。

# If you want to replace some columns as PostgreSQL boolean define here a list
# of tables and column separated by space as follows. You can also give a type
# and a precision to automatically convert all fields of that type as a boolean.
# For example: NUMBER:1 or CHAR:1 will replace any field of type number(1) or
# char(1) as a boolean in all exported tables.
#REPLACE_AS_BOOLEAN TB_NAME1:COL_NAME1 TB_NAME1:COL_NAME2 TB_NAME2:COL_NAME2

エクスポート時にOracleカラムの型をPostgreSQLのブール値に変更したい場合は、以下のようにテーブルとカラムのリストをスペースで区切って定義します(REPLACE_TABLESでテーブル名やカラム名を変更した場合は、元のテーブル名やカラム名で指定します)。

replace_as_boolean tb_name1:col_name1 tb_name1:col_name2 tb_name2:col_name2

booleanカラムリストに設定された値は、ディレクティブ BOOLEAN_VALUES で定義された変換テーブルに従い、 t と f への置換が行われます。

number(1,0) や char(1) の型を全てブール値変換する場合は、number:1 char:1 のように一括指定することができます。

REPLACE_AS_BOOLEAN NUMBER:1 CHAR:1 tb_name1:col_name1 tb_name1:col_name2

データのエクスポート

テーブルデータをエクスポートするには、エクスポートタイプで以下を指定します。

INSERT insert 文としてエクスポート
COPY copy 文としてエクスポート

通常は COPY を指定することになるでしょう。

ora2pg.conf の以下のディレクティブに 1 を指定すると、データをロードする前に TRUNCATE TABLE 命令が追加されます(単にCASCADE 指定無しの TRUNCATE TABLE <table>; が COPY 文の前に追加されるだけです)(外部キー制約で TRUNCATE 出来ない場合があるため手動でユーザごと再作成したほうがすっきりします)。

# Add a TRUNCATE TABLE instruction before loading data on COPY and INSERT
# export. When activated, the instruction will be added only if there's no
# global DELETE clause or one specific to the current table (see bellow).
TRUNCATE_TABLE  0

以下の様にエクスポートします。

$ podman run -it --rm --name ora2pg -v ${pwd}/config:/config -v ${pwd}/data:/data georgmoser/ora2pg ora2pg -t COPY -c /config/ora2pg.conf -b /data

${pwd}/data に output.sql が以下のような内容で生成されます。

BEGIN;

SET client_encoding TO 'UTF8';
SET synchronous_commit TO off;

TRUNCATE TABLE employee;

COPY employee (id) FROM STDIN;
1  ...
\.

テーブル毎にファイルを分けたい場合は FILE_PER_TABLE 1 に設定します。

oracle_fdw が有効な場合は、Postgres の以下の接続情報を設定することで、出力ファイルを介さずに、ダイレクトにエクスポート・インポートすることもできます(テーブル定義などは個別に行う必要があります)。

# Define the following directive to send export directly to a PostgreSQL
# database. This will disable file output.
#PG_DSN     dbi:Pg:dbname=test_db;host=localhost;port=5432
#PG_USER    test
#PG_PWD     test

テーブルのインポート

ここでは、コンテナで postgres を起動してインポートしてみましょう。

ボリュームを作成して、インポート先のコンテナを起動します。

> podman volume create ora2pg_pgdata_volume

> podman run -d --name ora2pg-postgres -p 5432:5432 -e POSTGRES_PASSWORD=mysecretpassword -v ora2pg_pgdata_volume:/var/lib/postgresql/data -v ${pwd}/data:/home postgres

ora2pg の出力先ディレクトリを /home にボリュームマウントしています。

起動したら以下のように接続して、生成されたSQLスクリプトを実行するだけです。

$ podman exec -it ora2pg-postgres bash
# psql -U postgres < /home/tables/output.sql

テーブルが生成されていることを確認します。

# psql -U postgres
postgres=# \dt

同様に、シーケンスなどその他のオブジェクトについても反映します。

# psql -U postgres < /home/sequence/output.sql

テーブルデータのインポート

テーブルデータのインポートも先と同様に、SQLスクリプトを実行するだけですが、事前に外部キー制約などを無効化しておく必要があります。

なお、テーブルのエクスポート時に FILE_PER_CONSTRAINT 1 と指定することで、create table の SQLスクリプトと制約作成のSQLスクリプトを分けて出力することもできるので、制約の設定を後から行うことでも対応が可能です。

以下で全テーブルのトリガを無効化/有効化するSQLを作成します。

copy (
select
    'ALTER TABLE ' || schemaname || '.' || tablename || ' DISABLE TRIGGER ALL;'
from
    pg_catalog.pg_tables
where
    schemaname != 'pg_catalog' and
    schemaname != 'information_schema'
order by
    tablename 
) to '/home/disable-trigger.sql';

copy (
select
    'ALTER TABLE ' || schemaname || '.' || tablename || ' ENABLE TRIGGER ALL;'  
from
    pg_catalog.pg_tables
where
    schemaname != 'pg_catalog' and
    schemaname != 'information_schema'
order by
    tablename 
) to '/home/enable-trigger.sql';

作成したSQLでトリガを無効化し、データのインポートができます。

# psql -U postgres < /home/disable-trigger.sql
# psql -U postgres < /home/output.sql
# psql -U postgres < /home/enable-trigger.sql

後片付けは以下。

$ podman stop ora2pg-postgres
$ podman rm ora2pg-postgres
$ podman volume rm ora2pg_pgdata_volume

なお、トリガの無効化は、上記の他にもいろいろとやり方があります。

blog1.mammb.com

まとめ

Ora2Pg による Oracle から PostgreSQL への移行手順について説明しました。

Ora2Pg には多くのオプションがあり、ここでは説明しきれませんので、詳細は以下のドキュメントを参照してください。

https://ora2pg.darold.net/documentation.html

PostgreSQL徹底入門第4版インストールから機能・仕組み、アプリ作り、管理・運用まで

作者:近藤雄太,正野裕大,坂井潔,鳥越淳,笠原辰仁
翔泳社

Amazon

Fundamentals of Data Engineering: Plan and Build Robust Data Systems

作者:Reis, Joe,Housley, Matt
Oreilly & Associates Inc

Amazon