goodbyegangsterのブログ

備忘録的な

PostgreSQLでDBを作成する

テーブル空間を作って、そこに新規データベースを作成する手順です。

環境情報

テーブル空間の作成

そもそもテーブル空間とは、

PostgreSQLのテーブル空間により、データベース管理者はデータベースオブジェクトを表すファイルを格納できるファイルシステム上の場所を定義することができます。

PostgreSQL - テーブル空間

Oracle DBで言うところの、 表領域 て感じでしょうか。 create tablespace 文で作成できます。

create tablespace

サンプルはこちら。SuperUserで実行する必要があります。

postgres=# CREATE TABLESPACE sample_tablespace OWNER sample_user LOCATION '/data/sample';

オプションの説明。

  • OWNER
    • テーブル空間の所有者を指定可能です。指定ない場合、コマンド実行ユーザ(通常はSuperUser)が適用されます。
  • LOCATION
    • テーブル空間を作成するOS上Pathを指定できます。指定Pathは、Postgresプロセス実行ユーザ(通常はpostgres)からアクセス可能である必要があります。

また、その他オプションとして、実行計画作成時のコストを、ここで制御することもできます。こんな事できるのか、と興味深かかったのでメモっておきます。

特定のテーブル空間について、そのいずれかの値(seq_page_cost、random_page_cost、effective_io_concurrenc)を設定すると、そのテーブル空間内のテーブルからページを読み込むコストの推定値について、プランナが通常参照する同じ名前の設定パラメータ(seq_page_cost、random_page_cost、effective_io_concurrencyを参照)よりも優先します。

テーブル空間(つまり利用するディスク)に対して、シーケンシャルリードやランダムアクセスのコスト配分を制御できる訳ですね。シーケンシャルリード時のデフォルト・コスト配分は下記。

seq_page_cost。シーケンシャルな一連の取り出しの一部となる、ディスクページ取り出しに関する、プランナの推定コストを設定します。 デフォルトは1.0です。

ランダムアクセスはこちら。

random_page_cost。非シーケンシャル的に取り出されるディスクページのコストに対するプランナの推測を設定します。 デフォルトは4です。この値をseq_page_costと比較して小さくすると、システムはなるべくインデックススキャンを使用するようになります。 大きくすると、インデックススキャンが相対的に高価になります。 両方の値を増減させることで、CPUコストに対するディスクI/Oコストの重要性を変更させることができます。

PostgreSQL - プランなコスト定数

作ったテーブル空間の、確認コマンドはこちら。

postgres=# \db
                テーブル空間一覧
       名前        |   所有者    |     場所
-------------------+-------------+--------------
 pg_default        | postgres    |
 pg_global         | postgres    |
 sample_tablespace | sample_user | /data/sample
(3 行)

デフォルトで、 pg_defaultpg_global のテーブル空間が用意されています。それらのOS上Pathは、データベースクラスタ配下の baseディレクトリ(通常は /var/lib/pgsql/10/data/base/ )となっています。

独自に作成したテーブル空間は、データベースクラスタ配下の pg_tblspc ディレクトリ(通常は /var/lib/pgsql/10/data/pg_tblspc/ )にて、シンボリックを作成されて管理されています。

$ ls -l /var/lib/pgsql/10/data/pg_tblspc/
total 0
lrwxrwxrwx. 1 postgres postgres 12  430 23:04 16385 -> /data/sample

上の 16385 とは、該当テーブル空間のoidとなります。PostgreSQLでは、DBMS内の各オブジェクトに、oidというメタidが付与されており、その番号により管理されているらしいです。テーブル空間ごとのoid確認は、下記のselect文より確認できます。

postgres=# select oid, * from pg_tablespace;
  oid  |      spcname      | spcowner | spcacl | spcoptions
-------+-------------------+----------+--------+------------
  1663 | pg_default        |       10 |        |
  1664 | pg_global         |       10 |        |
 16385 | sample_tablespace |    16384 |        |
(3 行)

DBの作成

上記で作成したテーブル空間上に、新しいデータベースを作成します。 create database 文にて作成できます。

create database

サンプルはこちら。SuperUser、またはcreatedb権限を持ったユーザで実行する必要があります。

postgres=# CREATE DATABASE sample OWNER sample_user TABLESPACE sample_tablespace;

各オプションについて。

  • OWNER
    • データベース所有ユーザを指定。デフォルトでは、SQL実行ユーザとなります。
  • TABLESPACE
    • 作成データベースのデフォルトとなるテーブル空間を指定。デフォルトでは、テンプレートデータベースと同じ(つまり pg_default )になります。
  • template
    • 作成データベースの複製元となるテンプレートのデータベースを指定。デフォルトでは、 template1 となる。

その他に、データベースで利用するencordingや照合順序をオプションで指定できます。

データベースの一覧を確認。

postgres=# \l
                                          データベース一覧
   名前    |   所有者    | エンコーディング |  照合順序  | Ctype(変換演算子) |     アクセス権限
-----------+-------------+------------------+------------+-------------------+-----------------------
 postgres  | postgres    | UTF8             | ja_JP.utf8 | ja_JP.utf8        |
 sample    | sample_user | UTF8             | ja_JP.utf8 | ja_JP.utf8        |
 template0 | postgres    | UTF8             | ja_JP.utf8 | ja_JP.utf8        | =c/postgres          +
           |             |                  |            |                   | postgres=CTc/postgres
 template1 | postgres    | UTF8             | ja_JP.utf8 | ja_JP.utf8        | =c/postgres          +
           |             |                  |            |                   | postgres=CTc/postgres
(4 行)

利用されるテーブル空間を確認したい場合は、以下のselect文。システムカタログ表の pg_databasepg_tablespace を結合しています。

postgres=# SELECT A.datname, B.spcname FROM pg_database A JOIN pg_tablespace B ON A.dattablespace = B.oid;
  datname  |      spcname
-----------+-------------------
 postgres  | pg_default
 sample    | sample_tablespace
 template1 | pg_default
 template0 | pg_default