【OSS-DB Silver】PostgreSQLのインストールから使い方

OSS-DB　Silver

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

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

分類 説明
一般知識(16%) OSS-DBの一般的特徴、リレーショナルデータベースに関する一般知識
運用管理(52%) インストール、ツールの使い方、設定ファイル、バックアップ方法、運用管理作業
開発/SQL (32%) SQLコマンド、組み込み関数、トランザクションの概念

これらから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) データベース名 : 既存の接続を切断し、指定したデータベースに接続する

コメント