OracleユーザがPostgreSQLを使用する時に注意する点

はじめまして、サークル「まぐろのみぞおち」のまぐろと申します。

弊サークルでは主にPostgreSQLに関する知識を同人誌にまとめて、より多くの方にPostgreSQLを知ってもらい、ユーザを増やすことでコミュニティに貢献しようと活動しております。

最近はOracle databaseからPostgreSQLに移行を検討する方も多く、ユーザを増やすチャンス！ということで、私が実際にOracleからPostgreSQLに移行する際に困った点や、Oracleユーザからここはどうなっているの？と聞かれた点を中心に、OracleからPostgreSQLに移行する際の注意点を一問一答形式でご紹介します。

Q1. SQLに互換性はありますか？

A. ほとんど問題ありませんが、いくつか修正が必要な点があります。

PostgreSQLはSQL標準にできる限り従うことを念頭に置いて作成されています。そのため、通常使用するSQLに関してはほとんど修正する必要なくそのまま使用できます。

ただし、SQL標準に完全に合致したデータベース製品というのは存在しません。それはPostgreSQLでもOracleでも同様で、Oracleの”方言”をPostgreSQLでも通じるように修正しなければなりません。

いくつか修正点の例をご紹介します。

• dual表はOracleにしか存在しません。
  • 実はSELECT文はFROM句がなくても文法的には問題ありません。PostgreSQLにはdual表は存在しませんので、削除しましょう。

  

• 互換性のない関数+
  • たとえばNVL関数のように、PostgreSQLには存在しない関数は、代替手段を考える必要があります。

  

• (+)でJOINしている
  • Oracleでも古い書き方なので今は珍しいかもしれませんが、当然、PostgreSQLでは動きません。通常の外部結合に書き換えましょう。
• 空文字列の扱い
  • Oracleでは空文字とNULLが等価の扱いをされます。PostgreSQLではもちろん異なる扱いになるため、NULLを条件にしているSQLがあったら修正しましょう。

Q2. データ型は共通していますか？

A. 全く同じではありません。合わせて修正する必要があります。

データ型は共通しているものもありますが、変えなければいけないものも多くあります。代表的なデータ型の置き換えの例をご紹介します。

Q3. PostgreSQLでテーブルを作成したら、テーブル名が小文字になりました

A. PostgreSQLでは、小文字に変換されます。

Oracleではテーブル名やカラム名に英字を指定すると、自動で大文字に変換します。しかし、PostgreSQLでは逆にすべて小文字に変換します。PostgreSQLでどうしてもテーブル名を大文字にしたい場合は、テーブル名をダブルクォーテーションで囲ってCREATEします。

しかし、私はあまりこのように指定するのはおすすめしません。なぜならば、作成時だけではなく、SELECTなど他のSQLでテーブルを指定する際にもダブルクォーテーションで囲まなければならなくなり、かえってOracleから移行するときのSQLの修正量が増えてしまうからです。

通常のSELECT文を書くとき、Oracleではテーブル名を大文字で書いても小文字で書いても特に問題ありません。それは、小文字で書いても実行時に大文字に変換してくれるからです。PostgreSQLでも同様で、ただ大文字で書いても小文字になるだけで、通常使用する分にはテーブル名が大文字でできていても小文字でできていても、意識する必要はありません。見た目が大文字から小文字になるだけで本質的には問題ないのに、わざわざ他のSQLの修正量を増やす必要はないと思います。

Q4. PostgreSQLでもストアド・ファンクションは使用できますか？

A. 使用可能ですが、完全に互換はしていません。

PostgreSQLにも、OracleのPL/SQLに見た目がそっくりな、PL/pgSQLという言語があり、同じようにストアド・ファンクションが書けます。

しかし、PL/SQLが様々な機能を持ち、かなり広範囲で使用されているのに比べ、残念ながらPL/pgSQLは機能的にも性能的にも劣ります。よく似た文法とはいえ修正も大変ですし、無理にストアド・ファンクションやストアド・プロシージャにするよりも、別の言語に書き換えるという方法も検討することをおすすめします。

Q.5 表領域は作成できますか？

A. 同じような機能を持つ「テーブル空間」というものが存在します。

Oracleは表領域というファイルを作成し、その中にテーブルやインデックスを作成します。OracleとはファイルI/Oのアーキテクチャがまったく異なり、PostgreSQLのテーブル空間はディレクトリです。テーブル空間として指定したディレクトリ配下に、テーブルやインデックスがファイルとして存在します。使用する場面としては、異なるディスクにテーブル空間を配置して負荷分散するなど、表領域とそう変わらない使い方ができます。

しかしOracleの表領域が初期サイズや最大サイズを指定でき、ディスク使用量を制御できるのに比べ、テーブル空間はディレクトリなのでサイズの指定はできず、最大サイズはディスクの限界までとなります。

Q.6 REDOログサイズは変更できますか？

A. できないことはありませんが、現実的ではありません。

OracleでいうところのREDOログは、PostgreSQLではWALやWALファイル、トランザクションログと呼ばれます。OracleではREDOログのサイズをインストール後に変更できますが、PostgreSQLのデフォルトではWALは1つ16MBで、インストール後にこれを変更することはできません。変更できるのは、ソースからビルドするときのみ。実際にはほとんど行うことはないと思います。

ただし、1つ16MBのWALをどのくらいの量保持するか、という設定は可能です。デフォルトは1GBですが、これは一般的には小さすぎます。性能にも関わる設定ですので、よく調べて設定してください。

Q.7 サーバでPostgreSQLを起動しましたが、リモートからデータベースに接続できません

A. listen_addressesか、pg_hba.confの設定に誤りがある可能性が高いです。

PostgreSQLは、デフォルトではローカル接続のみを許可しています。リモートからの接続を許可したい場合、まずpostgresql.confのlisten_addressesを「localhost」から「*」に変更します。この設定でローカルだけではなくすべてのIPアドレスから接続を受け付けるようになります。

しかしこれだけではリモートから接続できません。もう一つの設定ファイルであるpg_hba.confを修正し、リモートからのアクセスを許可する必要があります。

デフォルトではこのように、ローカルからのアクセスのみ、しかもパスワード入力無しで受け付ける設定になっています。

すべてのアドレスからの接続を受け付け、かつパスワード認証を必須にするには、次のように修正します。

これでも接続できない場合はファイアウォールの設定を見直してください。なお、Oracleと異なり、PostgreSQLにはリスナーを別途起動する必要はありません。インスタンス起動時に接続待受プロセスが起動します。

Q.8 ユーザを作ったのにスキーマが作成されません

A. PostgreSQLではユーザとスキーマは無関係です。

Oracleではユーザを作成すると、ユーザと同名のスキーマが作成されます。利用者はほとんどその存在を意識することはなく、「ユーザ＝スキーマ」と考えても問題ないくらいです。しかし、PostgreSQLではユーザとスキーマは無関係です。

PostgreSQLではデータベースを作成すると、自動的に「public」という名前のスキーマを作成します。通常はこのスキーマにテーブルなどを作成しますが、その理由は「search_path」という設定にあります。

search_pathはCREATEやSELECTなど、SQLを実行する際に参照するスキーマの優先順位を指定します。デフォルトは「"$user", public」で、ユーザ名と同じスキーマを優先していますが、そのようなスキーマはデフォルトでは存在しないので、publicスキーマが優先されます。

つまり、ユーザ名と同じ名前のスキーマを作成すれば、Oracleと同様にユーザ名と同じスキーマにテーブルなどが作成できます。

Q.9 データベースを複数作りました。一時的にあるデータベースを落とせますか？

A. 特定のデータベースだけシャットダウンすることはできません。

PostgreSQLでは1つのインスタンスの中に複数データベースを作成できます。しかし起動と停止はインスタンス単位で行われ、データベース単位で起動・停止はできません。

どうしてもデータベースに誰もアクセスさせたくない、というときは、データベースからコネクト権限を削除する、という方法があります。

この方法を使えば不要な接続を排除できますが、データベースをメモリ上から完全に排除するのは難しいでしょう。

Q10. サーバの文字コードをShift_JISにできますか？

A. PostgreSQLでは、サーバの文字コードをShift_JISにすることはできません。

PostgreSQLは様々な文字コードに対応していますが、サーバ側の符号化方式としてShift_JISを指定することはできません。日本語を扱う場合はEUC-JPか、UTF-8を使用することになります。

しかし、クライアント側の符号化方式としては、Shift_JISをサポートしています。PostgreSQLは、サーバ側の符号化方式が何であれ、クライアントからの要求に対し結果を返すときは、クライアントの符号化方式に変換して返します。そのため、ユーザがサーバ内の文字コードが何であるかを意識することはあまりありません。

クライアント側で文字化けしてしまうというときは、client_encodingを変えましょう。

最後に

Oracleと異なる点を一問一答形式で10箇所ご紹介しました。基本的な質問だったりちょっと踏み込んだ内容だったりしますが、すべて私が実際に直面したり、Oracleユーザの方に質問されたりしたものです。つまり、OracleユーザがPostgreSQLを使用するときに疑問に思う可能性が高いものであると思います。このブログを読まれた方がPostgreSQLを使用するとき、またはPostgreSQLについて質問されたときの参考にしていただければと思います。

もちろん、ここに挙げた点以外にも共通点・相違点があります。ご質問等ございましたら、私のTwitterにリプライしていただければと思います。それでも解決しない疑問は、日本PostgreSQLユーザ会へ！

============================

サークル「まぐろのみぞおち」代表

まぐろ @tameguro

============================