こちらはPostgreSQL Advent Calendar 2022 13日目の投稿となります。
昨日は、@hmatsu47さんのSupabase で TCE（透過的列暗号化）をアプリケーションから使ってみたでした。
初めに
・初めてのDB移行なため、間違いが多々あるかもしれません。（ご指摘等大歓迎です！）
・PostgreSQLを使用した経験が少ないため、その点もご了承ください。
今回の目的
現状、Oracleで使用しているDBをPostgreSQLに移行する想定となります。
メリットとしては、下記が挙げられるのではないかと思います。
- コストダウン
- 他の商用DBMSと変わらないパフォーマンス
DB移行ツールについて
今回はora2pgを使用し、OracleからDDL定義等を一通り抽出して、PostgreSQLのバージョン15に移行いたしました。
ora2pgとは
Perlで実装されているOSSであり、OracleやMySQLからPostgreSQLへのマイグレーションが可能なツールとなります。ダウンロード先はこちらとなります。現在の最新バージョンは、Version 23.2となります。
環境
OS：CentOS
バージョン：7.5
DB①：Oracle12c
DB②：PostgreSQL15
インストール手順
今回はオフラインのため、資材をダウンロードしてMakeコマンドでビルドを通したりしました。
CPANなどからインストールする方法もある。
またora2pgのインストール前にPerlのバージョンも5.16以上であることを確認すること
$ perl -v
This is perl 5, version 30, subversion 3 (v5.30.3) built for darwin-thread-multi-2level
(with 2 registered patches, see perl -V for more detail)
準備した資材は下記となります。
資材はWinSCPなどを使用して、所定の位置に格納を行う
$ tar zxf ora2pg.tar.gz
$ cd ora2pg
$ perl Makefile.PL
$ make & make install
$ ll /usr/local/bin/ora2pg
$ tar zxf DBD-Oracle-1.83.tar.gz
$ cd DBD-Oracle-1.83
$ perl Makefile.PL
$ make & make install
$ tar zxf DBI-1.643.tar.gz
$ cd DBI-1.643
$ perl Makefile.PL
$ make & make install
$ tar zxf Time-HiRes-1.9764.tar.gz
$ cd Time-HiRes-1.9764
$ perl Makefile.PL
$ make & make install
実行コマンド
1.confファイルの修正
# ORACLE接続情報
ORACLE_DSN dbi:Oracle:host=localhost;sid=userinfo;port=1521
ORACLE_USER user
ORACLE_PWD password
SCHEMA USERINFO
# 拡張機能：ORAFCEを使用する場合は、1を設定
ORAFCE 0
2.Oracleとの接続確認
$ ora2pg -c ./config/ora2pg.conf -t SHOW_VERSION
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0
3.一括抽出の場合
テンプレートフォルダを作成して、生成されたシェルを実行する
# mkdir ./db_migration
# ora2pg --project_base ./db_migration --init_project test_project
Creating project test_project.
/root/db_migration/test_project/
schema/
dblinks/
directories/
functions/
grants/
mviews/
packages/
partitions/
procedures/
sequences/
synonyms/
tables/
tablespaces/
triggers/
types/
views/
sources/
functions/
mviews/
packages/
partitions/
procedures/
triggers/
types/
views/
data/
config/
reports/
Generating generic configuration file
Creating script export_schema.sh to automate all exports.
Creating script import_all.sh to automate all imports.
設定ファイルを再度修正いたします。
修正後、一括抽出を実行いたします。
$ sh -x export_schema.sh
実行後の出力ファイルの確認 （treeコマンド）
[root@localhost:test_project]# tree
.
├── config
│ └── ora2pg.conf
├── data
├── export_schema.sh
├── import_all.sh
├── reports
│ ├── columns.txt
│ ├── report.html
│ └── tables.txt
├── schema
│ ├── dblinks
│ ├── directories
│ │ └── directories.sql
│ ├── functions
│ ├── grants
│ ├── mviews
│ │ └── mviews.sql
│ ├── packages
│ ├── partitions
│ ├── procedures
│ │ ├── procedure.sql
│ │ └── TEST_ADD_procedure.sql
│ ├── sequences
│ │ └── sequence.sql
│ ├── synonyms
│ ├── tables
│ │ ├── CONSTRAINTS_table.sql
│ │ ├── FKEYS_table.sql
│ │ ├── INDEXES_table.sql
│ │ └── table.sql
│ ├── tablespaces
│ ├── triggers
│ ├── types
│ └── views
│ ├── TESTview.sql
│ └── view.sql
└── sources
├── functions
├── mviews
│ └── mviews.sql
├── packages
├── partitions
├── procedures
│ ├── procedure.sql
│ └── TEST_ADD_procedure.sql
├── triggers
├── types
└── views
├── TEST_view.sql
└── view.sql
4.個別抽出の場合
ツールを使用していて、一部気になっているのが、一部テーブルが抽出できないことがあることです。
抽出できないのはOracle側の設定なのか、Ora2pgの設定なのかわからないため、投稿時点でも解決できていないです。
そのため、抽出できていないテーブルだけのDDLを抜き出して、ora2pgで変換してもらいたい時が来るはずです。
ora2pg -c ora2pg.conf -t TABLE -b ~/table/ -o ora2pg_TABLE.sql
TYPEはora2pgの公式に記載があるので、使用するTYPEを指定してください
PostgreSQLとOracleのデータ互換性テスト
前提
・PostgreSQLが環境に入っていること
1.PostgreSQLデータベースへのアクセスのモジュールをインストール
$ tar zxf DBD-Pg-3.16.0.tar.gz
$ cd DBD-Pg-3.16.0
$ perl Makefile.PL
$ make & make install
2.confファイルの修正
テンプレートで作成したora2pg.confを修正する。
PG_SCHEMA userinfo
PG_DSN dbi:Pg:dbname=userinfo;host=localhost;port=5432
PG_USER user
PG_PWD password
3.テストコマンドの実行
ora2pg -t TEST -c ./config/ora2pg.conf > diff.txt
4.出力形式の確認
一部抜粋した形となります。下記のように表示されていれば差分がないと見えるでしょう。
エラーが出力される場合もあるため、内容によって確認するのが望ましいと言える。
[TEST TABLE COUNT]
ORACLEDB:TABLE:5
POSTGRES:TABLE:5
[ERRORS TABLE COUNT]
OK, Oracle and PostgreSQL have the same number of TABLE.
拡張機能
| 拡張機能 | 説明 |
|---|---|
| external_file | OracleのディレクトリをPostgreSQLでも使用したい時 |
| pgtt | PostgreSQL Global Temporary Tablesを使いたい時 |
| orafce | Oracleの関数を一部使用できるようにする |
| postgresql-fdw | 外部データとの連携したい時 |
$ tar zxf external_file-1.0.tar.gz
$ cd external_file-1.0
$ make&make install
⇨pgttも同様
$ rpm -ivh orafce_15-4.0.1-1.rhel7.x86_64.rpm
上記作業にて、PostgreSQLのExtensionフォルダに各拡張機能がインストールされました。
次はDBに拡張機能が使用できるようにします。
# CREATE EXTENSION external_file;
CREATE EXTENSION
# CREATE EXTENSION pgtt;
CREATE EXTENSION
# CREATE EXTENSION orafce;
CREATE EXTENSION
# CREATE EXTENSION postgres_fdw;
CREATE EXTENSION
# \dn
List of schemas
Name | Owner
--------------+----------
dbms_alert | user
dbms_assert | user
dbms_output | user
dbms_pipe | user
dbms_random | user
dbms_utility | user
oracle | user
plunit | user
plvchr | user
plvdate | user
plvlex | user
plvstr | user
plvsubst | user
public | postgres
utl_file | user
pgtt_schema | user
external_file| user
userinfo | user
# SHOW search_path;
search_path
--------------------------------
"$user", public
# ALTER ROLE user SET search_path = userinfo,oracle,external_file,pgtt_schema,,,;
ALTER ROLE
PostgreSQLファイル取り込みコマンド
①個別取込
psql -U user -d userinfo -f ./schema/tables/table.sql
②一括取込
$ sh -x import_all.sh
注意点
- OracleとPostgreSQLでは、データ操作クエリにて一部使用できない関数が使われている。
例えば、ROWNUMが使えないなどあるため、互換性一覧を随時確認する必要がある - データの最大バイト数が違う
PostgreSQL：1GB
Oracle：2GB - 一部変換できないところがあるため、手作業が必要となります。
おわり
最後まで閲覧いただきありがとうございます。