DBのマイグレーションツールを使ったみた（OracleからPostgreSQL）

こちらは[PostgreSQL Advent Calendar 2022](https://qiita.com/advent-calendar/2022/postgresql "PostgreSQL advents") 13日目の投稿となります。

昨日は、[@hmatsu47さん](https://qiita.com/hmatsu47 "hmatsu")の[Supabase で TCE（透過的列暗号化）をアプリケーションから使ってみた](https://qiita.com/hmatsu47/items/d3cf24f0e462628cd700 "tcf")でした。

## 初めに
・初めてのDB移行なため、間違いが多々あるかもしれません。（ご指摘等大歓迎です！）
・PostgreSQLを使用した経験が少ないため、その点もご了承ください。

## 今回の目的
現状、Oracleで使用しているDBをPostgreSQLに移行する想定となります。
メリットとしては、下記が挙げられるのではないかと思います。
- コストダウン　
- 他の商用DBMSと変わらないパフォーマンス 

## DB移行ツールについて
今回は[ora2pg](https://ora2pg.darold.net/ "ora2pg")を使用し、OracleからDDL定義等を一通り抽出して、PostgreSQLのバージョン15に移行いたしました。
>ora2pgとは
>>Perlで実装されているOSSであり、OracleやMySQLからPostgreSQLへのマイグレーションが可能なツールとなります。ダウンロード先は[こちら](https://github.com/darold/ora2pg/releases "ora2pg")となります。現在の最新バージョンは、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などを使用して、所定の位置に格納を行う
- [ora2pg](https://ora2pg.darold.net/ "ora2pg")
- [DBD::Oracle](https://metacpan.org/pod/DBD::Oracle "Oracle")
- [DBI](https://metacpan.org/pod/DBI "DBI")
- [Time-Hires](https://metacpan.org/pod/Time::HiRes "Time")

```text:ora2pg
$ tar zxf ora2pg.tar.gz
$ cd ora2pg
$ perl Makefile.PL
$ make & make install
$ ll /usr/local/bin/ora2pg
```

```text:DBD::Oracle
$ tar zxf DBD-Oracle-1.83.tar.gz
$ cd DBD-Oracle-1.83
$ perl Makefile.PL
$ make & make install
```

```text:DBI
$ tar zxf DBI-1.643.tar.gz
$ cd DBI-1.643
$ perl Makefile.PL
$ make & make install
```

```text:Time-Hires
$ tar zxf Time-HiRes-1.9764.tar.gz
$ cd Time-HiRes-1.9764
$ perl Makefile.PL
$ make & make install
```

## 実行コマンド
### 1.confファイルの修正
```conf:ora2pg.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データベースへのアクセスのモジュールをインストール
- [DBD::Pg](https://metacpan.org/pod/DBD::Pg "dbdpg")
```text:DBD-Pg-3.16.0
$ 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`を修正する。
```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  | 外部データとの連携したい時 ~~（あまりよくわかっていない）~~  |

```text:tar.gz
$ tar zxf external_file-1.0.tar.gz
$ cd external_file-1.0
$ make&make install
⇨pgttも同様
```

```text:rpm
$ rpm -ivh orafce_15-4.0.1-1.rhel7.x86_64.rpm
```

上記作業にて、PostgreSQLのExtensionフォルダに各拡張機能がインストールされました。
次はDBに拡張機能が使用できるようにします。
```sql:postgresql
# 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ファイル取り込みコマンド
①個別取込
```sql:postgresql
psql -U user -d userinfo -f ./schema/tables/table.sql
```

②一括取込
```sh:shell
$ sh -x import_all.sh
```

## 注意点
- OracleとPostgreSQLでは、データ操作クエリにて一部使用できない関数が使われている。
例えば、ROWNUMが使えないなどあるため、[互換性一覧](https://www.pgecons.org/wp-content/uploads/PGECons/2012/WG2/10_Built-inFunctionMigrationResearch/10_Appendix_01_Built-inFunctionComparativeTable(Oracle-PostgreSQL).pdf "gokan")を随時確認する必要がある
- データの最大バイト数が違う
PostgreSQL：1GB
Oracle：2GB
- 一部変換できないところがあるため、手作業が必要となります。

## おわり
最後まで閲覧いただきありがとうございます。

## 参考
- [ora2pgを使用してOracleからPostgreSQLへ移行する
](https://qiita.com/mkyz08/items/89771d11b73d2a2d58f3 "ora2pg")
- [OracleからPostgreSQL移行について
](https://qiita.com/yaju/items/7cf5ab6987ecd1d83b13#%E7%A7%BB%E8%A1%8C%E6%94%AF%E6%8F%B4%E3%81%99%E3%82%8B%E3%83%84%E3%83%BC%E3%83%ABora2pg "ora2pg")