OracleからPostgreSQLに乗り換えたい人に送る何か

Calendar for PostgreSQL | Advent Calendar 2021 - Qiita
の10日目の記事です。

思い当たる節がある人は、今の見積もりの数倍の時間と予算を確保してからキックオフして欲しい。

多分、そのまま走ると誰か倒れるから。。。

で終わりにするわけにはいかないので、いくつか書きます。

もちろん、SQLがDBMSによって「方言」があるので、使える構文、関数名が違うってのは当然あるのですが、それ以外のところを中心に。

PostgreSQLのサーバーサイドエンコーディングはShiftJISをサポートしていない

今どき、新規で立てるデータベースはUTF8を選ぶよねーキャハハ、と思っても、何世代もバージョンアップしてきたOracleや、塩漬けされたOracleなんかは、ShiftJISを使っているところも多いのではないでしょうか。

自分で文字コードマッピングの処理を書かなくても、ETLツール等でShiftJISなOracleからUTF8なPostgreSQLに上手いこと転送できたりはしますが、本当に正しいデータが格納されているかの検証には十分な時間と体制を取ってくださいね、と。

ちなみに、PostgreSQLもクライアント側はShiftJISには対応してる。が、もちろんココでも、期待したとおりに変換されてるかは確認が必要です。

また、WindowsのコマンドプロンプトからpsqlでUTF8なPostgreSQLに対してSQLを叩こうとするときも余計なひと手間入るので要注意。

PostgreSQLにおけるオブジェクト名の評価は、原則「小文字」

Oracle文化圏は、オブジェクト名は大文字で書くことが多いです。が、PostgreSQLにおいてオブジェクト名の管理のデフォルトは「小文字で保存し、小文字で評価する」です。

PostgreSQLでDDLを大文字で発行しても小文字で保存されますし、DMLを大文字ベースで投げても、小文字としてオブジェクト照合されます。

PostgreSQLでは、大文字で定義したオブジェクトと小文字で定義したオブジェクトは別モノとみなされます。

大文字で定義したい場合や、大文字で定義したオブジェクトを参照するには、ダブルクオートで囲う必要があります。

testdb=# create table t1 (
testdb(#  col1 bigint,
testdb(#  col2 character varying (1000)
testdb(# ) ;
CREATE TABLE
testdb=#
testdb=#
testdb=# create table T1 (
testdb(#  col1 bigint,
testdb(#  col2 character varying (1000)
testdb(# ) ;
ERROR:  relation "t1" already exists
testdb=#
testdb=# create table "T2" (
testdb(#  col1 bigint,
testdb(#  col2 character varying (1000)
testdb(# ) ;
CREATE TABLE
testdb=# \d
        List of relations
 Schema | Name | Type  |  Owner
--------+------+-------+----------
 public | T2   | table | postgres
 public | t1   | table | postgres
(2 rows)
testdb=# \d t2
Did not find any relation named "t2".
testdb=# \d T2
Did not find any relation named "T2".
testdb=# \d "T2"
                         Table "public.T2"
 Column |          Type           | Collation | Nullable | Default
--------+-------------------------+-----------+----------+---------
 col1   | bigint                  |           |          |
 col2   | character varying(1000) |           |          |

こういうことです。
ダブルクオート囲みなしで「T1」で投げたCREATE TABLE文は、「t1テーブル既にいるよ」って弾かれてますね。
ダブルクオート囲みありで「T2」で投げたCREATE TABLE文は、大文字「T2」のテーブルとして定義されてますね。
大文字「T2」テーブルを照会するのに、ダブルクオートで囲まないと、「t2テーブルいないよ！」って弾かれてますね。

テーブルだけでのサンプルですが、カラム、インデックス、プロシージャ等、オブジェクト名全部がこのように管理されます。

オブジェクト移行ツールが上手いこと対応してない（ソースDBとなるOracleの定義を常にダブルクオート囲みで抽出してくるような）やつだと、「これってツールで自動でチョチョイノチョイ！（古）」だから見積もりコスト積んでないよ、ってときに悲惨なことに。

ダブルクオート囲みで引っこ抜いてきたOracleのテーブル定義（DDL）を、PostgreSQLで流せる最低限の文法修正だけしてPostgrSQLに流し込むと、全部「大文字」で作成されます。

アプリに埋めてあるSQLが、全DBオブジェクトについてダブルクオート囲みで書いてありますか？まずありえないですね。

よって、SQL文法的にPostgreSQLに通るものでも、小文字で登録されているオブジェクトを探しにいくので「そんなヤツいねーよ」ってエラーになります。

半角英字だけで定義されていれば、一律で小文字に強制変換すれば大丈夫といえば大丈夫なんですけどね。

マルチバイト文字をオブジェクト名に使用しているDBの移行なんかにぶつかった日には、目も当てられないです。

Oracleは、ダブルクオート囲みを「マルチバイトを利用したオブジェクト名」を扱うときに利用します。むしろその目的でのみ使っているケースの方が多いでしょう。

よって、マルチバイトなオブジェクト名を使っているOracleDBを利用するアプリコード内においても、ダブルクオート囲みでSQLが記述されているはずです。

すると今度は、１つのオブジェクト名にマルチバイトと英字が混在したときに、カオスが生まれます。

「その全角英字は、小文字で移植すべきか？大文字で移植すべきか？」

※Oracleはとあるバージョンから、オブジェクト名にマルチバイト文字を使うときはダブルクオート囲みが必須、と規定されました。
そのバージョンより古いとダブルクオートで囲わない実装のママのシステムがある。
そして、それ（ダブルクオートで囲っていない）で、規定後のバージョンを使っていても一応動いているシステムもある。（当然サポート外）

PostgreSQLにはシノニムがない。

Oracle大好きさん、（観測した限り）もれなく、シノニム大好きさん。

でも、PostgreSQLにはシノニムがないのです。

アプリ内に書いてあるSQLを、全部シノニム名じゃなくテーブル本体名でアクセスするように書き換えますか？
たいていアプリ屋さんに拒否られますよね。

全列全行参照するビューに置き換えれば、一応は対処できます。

頑張って、シノニム全部を読み解いて、ひたすらCREATE VIEW文を書いてください。CREATE SYNONYM文って短くてよかったな、とか思いながら。

シノニムのウラにあるものが、テーブルだったり、ビューだったり、マテリアライズド・ビューだったり。。。

データベースリンクは使えるけど-その１：データベースリンクを実現する機能は２つあるが。。。

Oracle大好きさん、（観測した限り）もれなく、データベースリンクも大好きさん。ただし、「可愛さ余って（多用しすぎて管理できなくなって）憎さ百倍」になっている人も多いです。

PostgreSQLにもデータベースリンク相当の機能はあります。一般的には、dblink（そのまんま）とfdw（Foregin Data Wrapper）という、拡張機能で実現されています。
　
dblinkは、Oracleとはまるで異なる構文でリモートデータベースにアクセスする必要がある。よって、アプリコード内のSQLも、相当な量の書き換えが発生するでしょう。

SELECT * FROM dblink('コネクション名','SELECT * FROM foo') AS t(a int, b text, c text[]);　・・・dblink()関数の中でSELECT文を記述し、さらに、戻りの列定義も記述する必要がある。

とか

SELECT dblink_exec('insert into foo values(21,''z'',''{"a0","b0","c0"}'');’);　・・・更新系はdblink_exec()関数内でSQLを書くことになる。文字列は’を2つ重ねることになる。

とかいった具合です。

fdwは、もう少しOracleのデータベースリンクに近く、普通のSQLと同じ書き方でリモートデータベースにアクセスできます。

ただし、事前に外部テーブル（Foreign table）として、「リモートデータベースにどのユーザーで接続し、どのスキーマの、どのテーブルについて、どんな表定義で」アクセスすることになるのかを定義しておく必要があります。

リモート側のテーブル定義に変更があったら、都度定義しなおさないと、その変更分はローカル側には反映されないです。

また、外部テーブル名は、ローカル側のPostgrSQLの同じスキーマ内でテーブルやビューと名前衝突を起こす。よって、手元のテーブル名と同じテーブル名のリモート表にアクセスしたい場合は、別名で管理しておく必要があります。

外部テーブル同士でも名前重複は許されないので、「リモートDBのAスキーマのX表とBスキーマのX表」を使いたいケースなど、外部表の名前として別名で定義しておかなくてはいけないのです。

Oracleだったら、事前に定義していたTNS接続識別子で「テーブル名＠接続先」と書きわけるだけで識別できていたところですが、PostgreSQLではそうはいかないです。

そんなことを踏まえながら、１つ１つ外部テーブルの定義を作っていくことになります。
（「IMPORT FOREIGN SCHEMA」で、リモートのスキーマ配下のテーブル定義を一発でローカル側の外部表定義として取り込むことができます。名前衝突がなければ、ね。）

それでも、なんとなくfdwのほうが使いやすそうだ、と思った人が多いんじゃないでしょうか。
実際、fdwのほうが高速だとも言われているし。（自分で測ったことはない）

ただし、fdwは、その名前「Data Wrapper」からわかる通り、データアクセスを主眼としています。

よって、リモートサイドのストアドプログラムをコールできない。。。
（テーブル関数使えばできる？やったことないけど。）

その際には、dblinkに頼らざるを得ないです。

結局どっちかだけ、ってわけにはいかないことになります。

データベースリンクは使えるけど-その２：リンクしあってるOracleDBは、同じタイミングでPostgreSQLに移行されるのかい？

OracleからPostgreSQLへの移行計画のなかで、置いてけぼりにしてしまうデータベースはいませんか？

前述のfdwの標準は、PostgreSQL同士のリモートアクセスを実現する「postgres_fdw」でした。

PostgreSQLに移行したデータベースから、置いてけぼりにしてしまったOracleを参照するには、oracle_fdwがあります。希望の光ですね。

長らくAWS RDS/Aurora PostgreSQLでサポートされずツライ思いをしてきたが、先日ようやくサポートされるようになりました。すばらしいですね。

念の為、しっかり検証しながら使ってもらえば良いと思います。

ただし、「逆向き」はどうでしょう？置き去りにされるOracleから、先に移行してしまったPostgreSQLを参照する手段はありますか？

Generic Connectivity？Database Gateway？そんなのもありましたね。

今更、本番稼働中のOracleに新しいの入れたい？しかも、そのDBもいずれ移行するつもりなんでしょ？そこに追加のコストかけられます？

追記：2021/12/16

OracleからPostgreSQLへのDatabase Linkする設定方法の記事がありましたのでリンクしておきます！
Oracle Database から PostgreSQL への接続を試す - Qiita

他にもいろいろあるけれど

とりあえずこれくらいにしておきます。書いていて、つらい思い出がよぎって頭が痛くなってきました。

もっと詳しく、移行の際に検討すべきことを知りたい人には「PostgreSQLエンタープライズコンソーシアム」が公開している成果物の「データベース移行」を読んでみてください。
PostgreSQL エンタープライズ・コンソーシアム : 成果物総索引

この団体の回し者ではないですが、PGEConsのサイトには、このドキュメントに限らずPostgreSQL関連の非常に有用なコンテンツが公開されています。

ここに書いたPostgreSQLの仕様は、それ１つ１つの仕様としては何世代も前から変わらない「基本中の基本」だったりします。*1

普通にPostgreSQLを勉強してたら、「PostgreSQL完全に理解した」宣言するころには必ず通っているはず、というレベルのもので、かなりかわいいレベルの話だと思います。
チョットデキル人だと、何度も見た/聞いた話で、という次元ではないかと。

でも、こんな基本中の基本の話も、「Oracleから移行」という文脈に乗った途端、凶暴な素顔をのぞかせます。

それでも、この難関を乗り越えた後には、素晴らしいPostgreSQLライフが待っているので、頑張ってください！

安心してください。OracleからMySQLに乗り換えるよりは、ぜんぜんラクだから！
（MySQLにはMySQLの良さがあります。タノシイヨMySQL！）

最後に

OracleからPostgreSQLへの移行に成功したプロジェクトに関わった皆さん、マジで素晴らしいです。
その経験値のニーズは非常に高いので、うまくいったところ・うまくいかなかったところ、ぜひ公開してほしいです。

Oracleに残る決断をした皆さん、それも素晴らしいです。なんだかんだで最高峰のDBMSだと思います。
使うと決めたら存分に使い倒しましょう。

いつもブログエントリの最後に「推し本」を紹介してますが、今回は、PostgreSQLを運用する上で絶対に役に立つこちらの本で。

［改訂新版］内部構造から学ぶPostgreSQL 設計・運用計画の鉄則 Software Design plus

*1:基本中の基本、とか言って認識誤ってるところがあったら優しく教えて下さい！