37. PostgreSQL スキーマ

運用データベースは必然的に多数のテーブルとビューを持ち、これら全てを一つのスキーマで管理すると、すぐに扱いにくくなります。幸運にも PostgreSQL には "_Schema"の概念があります。

スキーマはフォルダのようなもので、テーブル、ビュー、関数、シーケンスやその他のリレーションを保持できます。全てのデータベースは public という一つのスキーマから始まります。

_images/schemas.jpg

スキーマ内では、PostGIS のデフォルトインストールによって、PostGISが使用する全ての型と関数とともに``geometry_columns``, geography_columns, spatial_ref_sys メタデータリレーションが生成されます。そのため、PostGIS ユーザは常に public スキーマへのアクセスが必要となります。

public スキーマ内では、ワークショップでこれまでに生成してきたテーブルの全てを見ることができます。

37.1. なぜスキーマを使うのか?

スキーマを使用する二つの理由があります:

  • スキーマ内で管理されるデータは一括処理に適用しやすいです。

    • 別のスキーマ内のデータのバックアップの方が簡単ですので、揮発性データと不揮発データとはバックアップスケジュールが異なります。

    • 別のスキーマへのデータの格納は簡単ですので、アプリケーション指向のスキーマは、タイムトラベルとリカバリのための、個別の格納とバックアップが可能です。

    • アプリケーションデータがスキーマ内にある時には、アプリケーションの差異の管理が簡単になるので、新しいバージョンのソフトウェアは、新しいスキーマ内のテーブル構造を処理して、カットオーバーの際はスキーマ名の単純な変更を行います。

  • ユーザ作業を単一スキーマに制限することができ、これによって分析テーブルとテストテーブルを運用テーブルから分離できます。

運用目的では、アプリケーションデータをスキーマで分離するようにすることで、管理が改善します。ユーザ目的では、分離したスキーマのユーザが互いに踏みつけあうことを防ぎます。

37.2. データスキーマの生成

新しいスキーマを生成して、テーブルをそちらに移動させましょう。まず、データベース内に新しいスキーマを生成します:

CREATE SCHEMA census;

次に nyc_census_blocks テーブルを census スキーマに移動させます:

ALTER TABLE nyc_census_blocks SET SCHEMA census;

コマンドラインプログラム psql を使用している場合には、nyc_census_blocks がテーブル一覧から消えたことに気付くでしょう! pgAdmin を使用している場合には、新しいスキーマとその中にあるテーブルを見るためには表示のリフレッシュが必要な場合があります。

スキーマ内のテーブルへのアクセスには二つの方法があります:

  • schema.table 標記を使用した参照によるアクセス

  • スキーマの ``search_path``への追加によるアクセス

明示的な参照は簡単ですが、しばらくすると面倒くさくなります:

SELECT * FROM census.nyc_census_blocks LIMIT 1;

search_path の操作は、多数の追加的なキー入力をせずに複数のスキーマ内にあるテーブルにアクセスするのに良い方法です。

SET コマンドで実行時に search_path の設定ができます:

SET search_path = census, public;

これによって、全てのリレーションや関数委への参照が census と public スキーマの両方で確実に検索されることになります。全ての PostGIS の関数と型が public 内にあり、検索パスから外したくないものであることを忘れないで下さい。

毎回の検索パスの設定も、面倒くさくなりますが、幸運にも、永続的に検索パスを設定することができます:

ALTER USER postgres SET search_path = census, public;

これで postgres ユーザは常に検索パスに census スキーマが入っているようになります。

37.3. ユーザスキーマの生成

ユーザはテーブルの作成を好みますが、PostGIS ユーザは特に好みます。SQL を使った分析演算には、可視化や中間結果のための一時テーブルが必要で、空間 SQL では、通常のデータベース作業より、ユーザが CREATE 権限を持つことが求められる傾向にあります。

デフォルトでは、Oracle の全てのロールに個別のスキーマが与えられています。これは PostgreSQL ユーザにとっても素晴らしい手法であり、使用している PostgreSQL のロール、スキーマ、検索パスの複製が簡単にできます。

新規ユーザをテーブル生成権限を付けて生成し (postgis_writer ロールの詳細については PostgreSQL セキュリティ をご覧下さい)、その後に、そのユーザに権限付与したスキーマを生成します:

CREATE USER myuser WITH ROLE postgis_writer;
CREATE SCHEMA myuser AUTHORIZATION myuser;

そのユーザとしてログインする場合には、PostgreSQL 用のデフォルトの search_path が実際に次の通りであることが分かります:

show search_path;

  search_path
----------------
 "$user",public

The first schema on the search path is the user's named schema! So now the following conditions exist:

  • 空間テーブル生成可能なユーザが存在する。

  • ユーザ名スキーマが存在し、それをユーザ自身が持っている。

  • ユーザの検索パスにユーザスキーマが最初に入っていて、新しいテーブルが自動的にそこに生成され、クエリが自動的にそこで最初に実行される。

これが全てです。これで、ユーザのデフォルト作業領域は、他のスキーマから上手に分割されました。