OSS-DB　Silver

OSS-DBは色々なものがありますが、商用データベースとの連携に優れ、エンタープライズシステムでも多くの導入実績があるのが「PostgreSQL」です。
OSS-DB Silverは「PostgreSQL　11」に関する能力を問う資格です。

試験で問われる範囲は以下の通りです。

これらから50題出題され、65%程度の正答率で合格となります。

今回は一般知識と運用管理の中からインストール、ツールの使い方までを説明します。

Sec1 概要

• 1986年にカリフォルニア大学バークレイ校で開発が開始された
• 最初のリリースはPostgreSQL 6.0
• PostgresSQL のライセンスは、BSDライセンスをベースにしている
• 著作権とライセンス条文を表示することで、配布も可能であり、改変した場合もソースコードの公開は不要
• 開発コミュニティは「PostgreSQL Global Development Group」であり、一般でも参加可能

Sec2. DB基礎

• DBMSはアプリーケーションプログラムとDBを仲介し、アプリケーション側からDBに直接アクセスせずにデータを扱えるようにするもの
• 論理データモデル
  • 階層モデル：一つの子レコードに対して、親レコードは一つ
  • ネットワークモデル：一つの子レコードに対して、親レコードは複数可能
  • 関係モデル：リレーションでデータを表現。関連付けは列の値を比較することにより行う
• DBの設計（ER図→関係モデル）
  1. Entityの洗い出し→最低限の属性を明確にしたのちに、関係線を引く
  2. 多対多のリレーションに関しては、連関Entityを追加して、1対多、多対1になるように変更する（関係モデルとして取り扱うことができないので）
  3. 各Entityが保持する詳細な属性、データ型やデータ長を明確にする
  4. 正規化
• 正規化
  • 第一正規化：繰り返し項目の平坦化
  • 第二正規化：非キー属性が、候補キーに対して完全関数従属であること(主キーに複数の属性がある場合などに、部分関数従属を排除するß)
  • 第三正規化：非キー属性が、どの候補キーに対しても推移的に関数従属していないこと
  • ボイスコッド正規系：第二正規化の対象が、「非キー属性」であるため、候補キーに対しては冗長な構造があっても改善されていない。ボイスコッド正規系は、関係Rの全ての候補キーについて、候補キーをXとし、Xに含まれていない関係Rの属性をYとすると、YはXに完全関数従属する。
  • 正規化するデータによっては、ボイスコッド正規化によって、関数従属性を失う場合がある。→第三正規化までで留めておく場合もある。

Sec.3 インストール

環境

• macOS Monterey
• チップ：Apple M1

手順

Homebrewからインストール可能

brew insrall postgresql

起動時のコマンドは以下

brew services start postgresql
pg_ctl -D /opt/homebrew/var/postgres start

終了時は

brew services stop postgresql

実行確認

psql postgres
create database test1 owner=postgres;
psql test1 -U postgres
create table assount(id varchar(8), name varchar(8));
\d
# 上記でテーブルが作成されていることを確認する

概要

• データベースクラスタにはデフォルトで3つが登録されている

  postgres=# select oid, datname FROM pg_database;
  oid  |  datname
  -------+-----------
  14021 | postgres
     1 | template1
  14020 | template0

• template1をベースとしてデータベースが作成される。

• template0は書き込み不可（template1を編集したが、それを使いたくない場合のデフォルトとして使用）

Sec.4 標準付属ツール

pg_ctl

起動・停止・状態確認に使用

• pg_ctlはPostgreSQLが稼働するホスト上のみで実行可能。リモートにあるホストからPostgreSQLを管理することはできない。
  ※基本的にpg_ctl以外のツールはどのホストからも実行可能
• reloadは設定ファイルの再読み込み
  • 再読み込みの対象は、postgresql.conf と pg_hba.conf
  • 再読み込みだけでは反映できないパラメータもある

createuser

データベースユーザの作成に使用

createuser [接続オプション] [オプション] [ユーザ名]

• -s, --superuser
• -S, --no-superuser
• -d, --createdb
• -D, --no-createdb
• -r, --createroll
• -R, --no-createroll

ロール：ユーザ、グループを統合した概念。createrollはユーザ作成権限と同義。

dropuser

データベースユーザの削除に使用

dropuser [接続オプション] [オプション] [ユーザ名]

createdb

データベースを作成する

createdb [接続オプション] [オプション] [データベース名]

• -E, --encoding=エンコーディング名
• -l, --locale=ロケール名
• -O, --owner=ユーザ名
• -T, --template=データベース名

エンコーディングとロケールをテンプレートから変更したい場合は、template0を指定してオプションを記載する必要がある。

dropdb

データベースを削除する

dropdb [接続オプション] [オプション] [データベース名]

psql

データベースの接続・切断

psql [接続オプション] [オプション] [データベース名[ユーザ名]]

• -l, --list : データベースの一覧を表示
• -c --command="コマンド" : 指定したコマンドを実行する。
• -f, --file=ファイル名 : 指定したファイルからコマンドを読み込み実行する
• -d, --dbname=データベース名 : 接続するデータベースの名前を指定する

メタコマンド

• \q : psqlを終了する
• \l : データベース一覧を表示する
• \d パターン : 指定したパターンに一致するテーブル、インデックスの構成情報を表示する
• \d : テーブル・ビュー・シーケンスの一覧を表示する
• \du : データベースユーザの一覧を表示する
• \dt : テーブルの一覧を表示する
• -c(\connect) データベース名 : 既存の接続を切断し、指定したデータベースに接続する