OSS-DB Silverチートシート

概要

OSS-DB Silverに合格するためのチートシート

試験概要

• バージョン：3.0
• 問題数：50問
• 試験時間：90分
• 合格点：64点（100点満点）

出題分野（数字は重要度を示す）

• S1 一般知識（16％）
  • S1.1 OSS-DBの一般的特徴　【重要度：4】
  • S1.2 リレーショナルデータベースに関する一般知識　【重要度：4】
• S2 運用管理（52％）
  • S2.1 インストール方法　【重要度：2】
  • S2.2 標準付属ツールの使い方 【重要度：5】
  • S2.3 設定ファイル 【重要度：5】
  • S2.4 バックアップ方法 【重要度：7】
  • S2.5 基本的な運用管理作業 【重要度：7】
• S3 開発/SQL（32％）
  • S3.1 SQL コマンド 【重要度： 13】
  • S3.2 組み込み関数 【重要度：2】
  • S3.3 トランザクションの概念 【重要度：1】

S1 一般知識（16％）

S1.1 OSS-DBの一般的特徴　【重要度：4】

用語集

• カリフォルニア大学のバークレイ校で開発されたPOSTGRES(1986年)が起源
• PGDG(PostgreSQL Global Development Group)：開発コミュニティ
  • メーリングリストの運営
  • 世界各地でイベント開催
  • バグの修正に対して義務は負わない
• BSDライセンス：PostgreSQLのベースとなるライセンス
  • 無償利用，再配布，ソースコードの改変可能
  • ソースコードの公開義務なし
  • 再配布はの際は，著作権とライセンス条文，無保証であることをドキュメントに記載する

PostgresSQLの特徴

• オープンソース
• 多言語対応
• マルチプラットフォーム
• レプリケーション機能

バージョンについて

• 「x.y」のように2要素で表記
  • メジャーバージョン(x)：機能の追加や仕様の変更
    • データベースを構成するファイルに互換性がなくなる場合あり
    • 1年に1回程度実施
    • 5年間のサポート
  • マイナーバージョン(y)：不具合やセキュリティ対策
    • 2,3ヶ月に1回程度実施

S1.2 リレーショナルデータベースに関する一般知識　【重要度：4】

DBMS（デーがベース管理システム）の機能

• データ管理
• 整合性の維持
• トランザクション管理
• 同時実行制御
• セキュリティ機能
• 障害回復機能

データベースの種類

• リレーショナルモデル（関係モデル）
  • 表構造で表現，集合論をベース，分離性が高くプログラムへの影響が少ない
  • 「1対多」「多対1」の関係
• 階層モデル
  • 親データと子データが「1対多」になる
• ネットワークモデル
  • 親データと子データが「多対多」になる

SQLの分類

• DDL（データ定義言語）
  • CREATE，DROP，ALTER，TRUNCATE
• DML（データ操作言語）
  • SLECT，INSERT，UPDATE，DELETE
• DCL（データ制御言語）
  • GRANT，REVOKE，BEGIN，COMMIT，ROLLBACK

正規化

• 第1正規形
  • 主キーを設定
    • ユニーク制約とNOT NULL制約がある
  • 繰り返しがない
  • 導出項目(他の属性から算出できる項目)がない
• 第2正規形
  • 部分関数従属性がない（YがAに部分関数従属する＝「{A, B} → Y」かつ「A → Y」）
• 第3正規形
  • 推移関数従属性がない（ZがXに推移関数従属する＝「Y → X」は不成立で「X → Y」かつ「Y → Z」）

S2 運用管理（52％）

S2.1 インストール方法　【重要度：2】

用語

• データベースクラスタ：データベースのファイルを格納する領域
  • ファイルシステム上の1つのディレクトリ
  • PostgreSQLサーバと1対1の関係になる
  • initdbコマンドで作成する
  • root以外のOSの一般ユーザで作成する（OSのユーザ＝DBの管理者ユーザになる）
  • デフォルトで3つのデータベースが作成される
    • template0：新規のデータベース作成時のテンプレートに指定できる（変更不可）
    • template1：新規のデータベース作成時のテンプレートになる（変更可）
    • postgres：通常のデータベース同様に使える
• エンコーディング：UTF8, EUC_JP, SJISが日本語のエンコーディング（SJISはデータベース側で使用できない）

initdbコマンド：データベースクラスタを作成するコマンド``

• -D, --pddata=ディレクトリ名：ディレクトリを指定（指定しない場合は環境変数$PGDATAになる）
• -E, --encoding=エンコーディング：エンコーディングを指定（未指定の場合はOSのロケールから自動的に設定）
• --locale=ロケール：ロケールを指定する（未指定の場合はOSのロケールを使用）
• --no-locale：ロケールを無効（--locale=Cでも可）
• -U, --username=ユーザ名：管理者ユーザ名を指定（未指定の場合OSユーザ名）
• -k, --data-checksums：データベースのチェックサム（データ破損を検出する仕組み）を有効
• -X, --waldir=ディレクトリ名：WAL（データベースへの変更ログ）を格納するディレクトリを指定

S2.2 標準付属ツールの使い方 【重要度：5】

pg_ctlコマンド：データベースクラスタの管理コマンド（管理者ユーザのみがローカルホストから実行できる）

• pg_ctl initdb(init)：新規データベースクラスタを作成する
  • -D ディレクトリ名 | --pgdata=ディレクトリ名：格納先ディレクトリを指定．未指定は$PGDATA
• pg_ctl start：バックグラウンドでPostgreSQLサーバを起動する
  • -D データベースクラスタ | --pgdata=データベースクラスタ：対象のクラスタを指定．未指定は$PGDATA
  • -W：起動の完了を待たずにコマンド発行元に制御を戻す（デフォルトは最大60秒待ち起動完了メッセージを表示）
  • -t 最大待ち時間：処理が完了するまでの待ち時間を指定（未指定は60秒）
• pg_ctl stop：シャットダウンする
  • -m シャットダウンモード：3つの異なるシャットダウン方式を指定
    • f | fast：高速シャットダウン．実行中の処理を中断しクライアント接続を強制的に切断．トランザクションはロールバック（デフォルト）
    • i | immediate：即時シャットダウン．実行中の処理を強制終了しクライアント接続を強制的に切断．次回起動時に回復処理を実行
    • s | smart：スマートシャットダウン．全てのクライアント接続が切断されてからシャットダウン．実行中の処理はシャットダウンの前に正常に完了される
• pg_ctl restart：再起動する
• pg_ctl reload：設定ファイルを再読み込みする
• pg_ctl status：PostgreSQLサーバの稼働状態を確認数する
• pg_ctl kill：プロセスにシグナルを送信する
  • INT：高速シャットダウン
  • QUIT：即時シャットダウン
  • TERM：スマートシャットダウン
  • HUP：設定ファイル再読み込み（reloadと同じ）

psqlコマンド：PostgreSQLに接続するコマンド（ex: psql -h 192.168.0.10 -p 9999 -U user1）

• -h, --host=ホスト名：ホスト名を指定（デフォルトは$PGHOST）
• -p, --port=ポート番号：ポート番号を指定（デフォルトは5432）
• -U, --username=ユーザ名：ユーザを指定（デフォルトは$PGUSER）
• 他オプション
  • -l | --list：すべてのデータベースのリスト
  • -c | --command：指定したコマンドの実行結果を表示
  • -f | --file：指定したファイルを読み込み実行結果を表示
  • -s | --single-step：各コマンド毎に実行するかを確認
  • -1 | --single-transaction：複数のコマンドを1つのトランザクションで実行

ユーザ管理コマンド

• createuser：ユーザを作成する
  • -p | --pwprompt：パスワードを設定
  • -s | --superuser：新しいユーザをすーぱユーザとして作成
  • -d | --createdb：データベースの作成を許可
  • -r | --createrole：新しいユーザの作成を許可
  • -l | --login：ログインを許可（デフォルト）
  • -S | --no-superuser：新しいユーザをスーパーユーザにしない（デフォルト）
  • -D | --no-createdb：データベースの作成を禁止（デフォルト）
  • -R | --no-createrole：新しいユーザの作成を禁止（デフォルト）
  • -L | --no-login：ログインを禁止
• dropuser：ユーザを削除する
  • -i | --interactive：削除前に確認

データベース管理コマンド

• createdb：データベースを作成する
  • -E | --endording：エンコーディングを指定
  • -O | --owner：所有ユーザを指定
  • -l | --locale：ロケールを指定
  • -T | --template：データベーステンプレートを指定
• dropdb：データベースを削除する
  • -i | --interactive：削除前に確認

その他コマンド

• pg_config：PostgreSQLの設定情報を表示する
• pg_controldata：制御情報を表示する
• pg_isready：接続状態を確認する
• pg_resetwal：WALを消去して制御情報を初期化する
  • -D | --pgdata：対象のデータベースクラスタを指定
  • -n | --dry-run：実際に処理は行わず変更内容の出力のみ事項
  • -f | --force：制御情報が読み取れない場合でも強制的に実行

メタコマンド：データベース内の情報を取得するコマンド

• \l：データベースの一覧
• \d：テーブル，ビュー，シーケンス一覧
• \dt：テーブル一覧
• \du：ユーザ一覧
• \?：メタコマンド一覧

S2.3 設定ファイル 【重要度：5】

パラメータ設定に関するコマンド

• SHOW パラメータ名;：パラーメータの値を表示（ALLを指定するとすべて表示）
• SET パラメータ名 TO {設定値 | DEFAULT};：パラメータの設定を変更
• SET パラメータ名 = {設定値 | DEFAULT};：上に同じ
  • SETの後に、SESSION(現在のセッションのみ有効，デフォルト)とLOCAL(現在のトランザクションのみ有効)を指定できる

postgresql.conf：PostgreSQL全体の動作を制御する設定ファイル

• パラメータは1行につき1つ
• 大文字，小文字の区別はない
• #から行末まではコメント
• パラメータの値は、boolean型、整数型、浮動小数点、文字列の4つ
• boolean値はon, off, true, false, yes, no, 1, 0（t や f などの省略形も可）
• メモリの単位：B(バイト)～TB(テラバイト)
• 時間の単位：us(マイクロ秒)～d(日)
• 以下主なパラメータ

#------------------------------------------------------------------------------
# CONNECTIONS AND AUTHENTICATION
#------------------------------------------------------------------------------
listen_addresses = '*'    # 接続要求を受け付けるPostgreSQLサーバ自身のIPアドレス
#port = 5432              # 接続を待ち受けるポート番号
max_connections = 100     # PostgreSQLに同時に接続可能な最大数

#------------------------------------------------------------------------------
# CLIENT CONNECTION DEFAULTS
#------------------------------------------------------------------------------
#search_path = '"$user", public'        # デフォルトで使用するスキーマ名
#default_transaction_isolation = 'read committed'  # 新しいトランザクションのデフォルトの分離レベル
#client_encoding = sql_ascii            # クライアントのエンコーディング

#------------------------------------------------------------------------------
# REPORTING AND LOGGING
#------------------------------------------------------------------------------
#log_destination = 'stderr'             # ログの出力先                                   
#logging_collector = off                # 標準エラーをファイルに出力するか
#log_directory = 'log'                  # ログファイルを格納するディレクトリ
#log_filename = '%Y-%m-%d_%H%M%S.log'   # ログを書き込むファイルのファイル名
#log_min_messages = warning             # ログを書き込むログのレベル
#log_connections = off                  # クライアント情報をサーバログに出力するか
#log_line_prefix = '%m [%p] '           # サーバログメッセージの行頭の書式文字列
#log_statement = 'none'                 # サーバログに書き込むSQL分の種類

pg_hba.conf：クライアント認証を設定するファイル

• 認証方式：trust(常に許可), reject(常に拒否), md5(暗号化パスワード), password(平文パスワード)

local データベース名 ユーザ名 認証方式
host データベース名 ユーザ名 IPアドレス サブネットマスク 認証方式
host データベース名 ユーザ名 CIDRアドレス 認証方式

S2.4 バックアップ方法 【重要度：7】

dumpコマンド

• pg_dump：データベースをバックアップする（ex: pg_dump -Fc examdb -f examdb.dump）
  • -Fp：平文(plain)で出力する（未指定の場合のデフォルト）
  • -Fc：カスタム形式(custom)で出力する
  • -Ft：tar形式で出力する
  • -f：バック先のファイル名を指定（省略した場合標準出力）
• pg_dumpall：データベースクラスタ全体をバックアップ（ex: pg_dumpall -f db.sql）
• pg_restore：平文以外のバックアップからリストアする（ex: pg_restore -d examdb examdb.dump）
  • -d：リストア先のデータベース名を指定
  • -f：リストアに必要なSQLコマンドを出力
• psql：平文形式のバックアップからリストアする（ex: psql -f examdb.sql examdb）

copyコマンド

• COPY：サーバ側のファイルとテーブル間のデータをコピーするSQLコマンド
  • COPY テーブル名 TO 絶対パスのファイル名;：テーブルのデータをファイルに出力
  • COPY テーブル名 FROM 絶対パスのファイル名;：ファイルのデータをテーブルに挿入
• \copy：クライアント側のファイルとテーブル間のデータをコピーする
  • \copy テーブル名 to ファイル名：テーブルのデータをファイルに出力
  • \copy テーブル名 from ファイル名：ファイルのデータをテーブルに挿入

PITR(Point In Time Recovery)：データベース全体のバックアップとWALファイルを使用して、データベースの復旧を行う

S2.5 基本的な運用管理作業 【重要度：7】

データベースロールを管理するSQLコマンド

• CREATE ROLE/USER ロール名 [WITH] [属性];：データベースロールを作成
• ALTER ROLE/USER ロール名 [WITH] [属性];：属性を設定
• ALTER ROLE/USER 現在のロール名 RENAME TO 新しいロール名;：ロール名を変更する
• DROP ROLE/USER ロール名;：データベースロールを削除

属性一覧

• SUPERUSER/NOSUPERUSER：スーパーユーザ権限
• CREATEDB/NOCREATEDB：データベース作成権限
• CREATEROLE/NOCREATEROLE：レプリケーション作成権限
• LOGIN/NOLOGIN：ログイン権限
• REPLICATION/NOREPLICATION：レプリケーション権限
• PASSWORD 'パスワード'：パスワードを設定または変更
• VALID UNTIL '日付'：パスワードの有効期限

不要領域や統計情報を整備するSQLコマンド

• VACUUM：不要領域を回収する
• ANALYZE：統計情報を収集して更新する

権限管理コマンド

• GRANT：権限を付与
• REVOKE：権限を削除

書式
・GRANT 権限 ON テーブル名 TO {ユーザ名 | PUBLIC};
・GRANT 権限 ON DATABASE データベース名 TO {ユーザ名 | PUBLIC};
・GRANT 権限 ON SCHEMA スキーマ名 TO {ユーザ名 | PUBLIC};

権限一覧
・SELECT, INSERT, UPDATE, DELETE, TRUNCATE, REFERENCES, TRIGGER, CONNECT, CREATE, ALL

システム情報関数

• version()：PostgreSQLのバージョン
• current_database()：データベース情報
• current_user | user：ユーザ情報（()が付かない点に注意）

S3 開発/SQL（32％）

S3.1 SQL コマンド 【重要度： 13】

データ取得SQL

• SELECT：データ取得
• ORDER BY ソート対象 [ASC | DESC]：ソート
• LIMIT 件数 OFFSET 位置：取得件数と先頭からスキップする位置を指定
• DISTINCT [ON 重複除去対象列名] 列名：重複を除去する
• GROUP BY 列名 HAVING 条件：指定した列でグループ化して条件で絞り込む
• BETWEEN A and B：AからBの範囲を取得
• EXISTS / NOT EXISTS：副問合せの結果を条件に取得
• UNION [ALL]：重複を除外してテーブルを縦に結合（ALLを指定した場合重複も含める）
• EXCEPT [ALL]：除外したい行を右側に指定
• INTERSECT：取得したい行を右側に指定

テーブル結合SQL

• INNER JOIN（内部結合）：結合キーの値が等しいデータのみを取得
• OUTER JOIN（外部結合）：結合キーの値が等しいデータに加えて、条件に一致しなかったデータも取得
• CROSS JOIN (交差結合)：対象テーブルの全データの組み合わせを取得

データ型

• 数値型
• 文字列型
• バイナリ列データ型
• 論理値型
• 日付・時刻型
• 連番型
• OID型
• 配列型
• JSON型

制約

• 主キー制約：重複，NULLの格納を禁止．1つのテーブルに1つだけ作成可能
• ユニーク制約：重複地の格納を禁止
• NOT NULL制約：NULLの格納を禁止
• 外部キー制約：参照先の列に無い値の格納を禁止
• チェック制約：ユーザが定義した値以外の格納を禁止
• ドメイン製薬：ユーザが定義した値以外の格納を禁止
• 識別子制約：自動で払い出した連番を列に設定

用語

• パーティション：テーブルをある範囲に区切ったもの
• シーケンス：自動で連番を払い出すオブジェクト
• ビュー：SELECT文の結果を定義したもの
• インデックス：辞書の索引の役割を果たすもの
• トリガ：特定のテーブルに対して操作が行われた場合に，既定のユーザ定義関数を発動させる機能
• スキーマ：テーブルや関数などのオブジェクトをまとめる単位
• レプリケーション：データを複製し高可用性や負荷分散を実現すること

S3.2 組み込み関数 【重要度：2】

組み込み関数一覧

• 集約関数：複数の数値データに対して処理を行った結果を返す
  • count()：NULL以外の件数
  • sum()：NULL以外の合計値
• 文字列関数：文字列に対して処理を行った結果を返す
  • trim()：指定した文字列から文字列を除去する
  • lpad()：指定した文字数になるように文字を左側に埋め込む
• 算術関数：数値に対して処理を行った結果を返す
  • floor()：指定した数値より小さい最大の整数を取得
  • ceil()：指定した数値より大きい最小の整数を取得
  • random()：0以上1未満のランダムな値を返す

S3.3 トランザクションの概念 【重要度：1】

ACID特性

• Atomicity(原子性)：一連の処理が完全な状態でのみ実行される
• Consistency(整合性)：実行前後でデータの整合性が保たれる
• Isolation(分離性)：他のトランザクション処理に影響を与えたり受けたりしない
• Durability(持続性)：コミットにより変更された内容が確実に保存される

コマンド

• BEGIN / START TRANSACTION：トランザクション開始
• COMMIT / END：トランザクションを終了し処理を確定
• ROLLBACK / ABORT：トランザクションを停止し処理を取り消す
• SAVEPOINT セーブポイント名：セーブポイントを設定
• ROLLBACK TO セーブポイント名：セーブポイントまで処理を戻す
• RELEASE SAVEPOINT セーブポイント名：セーブポイントを削除