- はじめに
- 基本的な作業の流れ
- 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
なお、トリガの無効化は、上記の他にもいろいろとやり方があります。
まとめ
Ora2Pg による Oracle から PostgreSQL への移行手順について説明しました。
Ora2Pg には多くのオプションがあり、ここでは説明しきれませんので、詳細は以下のドキュメントを参照してください。
https://ora2pg.darold.net/documentation.html