oss-db

date: 2021-04-02 excerpt: oss-dbについて

oss-dbについて

資格の一種
DB関連の知識を問い、主にpostgresqlに関するものである

postgresqlについて

ライセンス
- 再配布義務なし
- カルフォルニア大学バークレー校発祥のコミュニティドリブン
- PostgreSQL Licenseというライセンス
  - 無保証であることを明記しなくてはいけない　
- MIT -> 似ている
- GLP -> 似ていない
EOF
- 最初のリリースから5年間
バージョン
- マイナーバージョンのリリースは2、3ヶ月に1回程度
- メジャーバージョンアップは1年に1回程度
リリースノート
- 英語以外の言語でも記される事がある
DDL:データ定義言語
- CREATE, DROP, ALTER, TRUNCATE
DML:データ操作言語
- SELECT, INSERT, UPDATE, DELETE
DCL:データ制御言語
- GRANT, REVOKE, BEGIN, COMMIT, ROLLBACK
正規化
- 第一正規化
  - 単純な要素の切り出し
- 第二正規化
  - 従属変数を持つ切り出し
- 第三正規化
  - 従属変数を持つ切り出しで切り出しもとがキーでないもの
initdb
- デフォルトDBを作成するもの
- localhostで実行できる
- rootで実行できない
- -A, --auth==authmethod
- -D, --pgdata=directory, $PGDATA
- -E, --encoding=encoding
- -U, --username=username
- template0, template1, postgresが作成できる
- template0
  - 削除できない、template1をコピー元にしたくないとき
- template1
  - デフォルトのコピー元
  - ユーザーが編集可能
- postgres
  - デフォルトの接続先
ファイル構造
- $PGDATA
  - base
    - データベースの内容
  - global
    - 共有データ
  - log
  - postgresql.conf
  - pg_hba.conf
    - 認証情報
  - postmaster.pid
文字コード
- sjisが設定できない
- eucはできる
pg_controldata
- データベースクラスタの情報を取得できる
psql
- メタコマンド
  - \l -> データベース一覧
  - \d, \z, \dp -> テーブル,ビュー,シーケンス
  - \dt -> テーブル一覧
  - \du -> ユーザ一覧
  - \? -> メタコマンド一覧
  - \h -> SQLヘルプ
  - \! -> OSのコマンド
- コマンドプロンプトからファイルを実行
  - psql --single-step -f 'filename.sql'
- -Uをつけないとき
  - OSのユーザから実行したときとおなじになる
- デフォルトのポート
  - 5432
- -c, --command
  - あとに続くSQLを実行する
- -h
  - hostname
- -d
  - データベース名
- -f
  - バックアップをインプットしてリストア
pg_ctlコマンド
- 概要
  - sudoのようなもの
- stop
  - 実行後はアクセスは禁止される
  - -m smart, -m s
    - グレースフルなstop
  - -m immediate, -m i
    - ローリバックなしの終了
  - -m fast, -m f
    - ロールバックありの終了
  - -mなしの場合
    - デフォルトでは-m fast
  - -W
    - 完了なしにterminalにもどる
  - -t
    - 最大待ち時間
    - デフォルトでは60秒
- kill
  - TERM
    - グレースフルなkill
  - INT
    - ロールバックありの高速kill
  - QUIT
    - ロールバックなしの高速kill
  - HUP
    - 設定ファイルのリロード
- reload
  - 設定ファイルを読み込む
  - ポート番号と最大コネクションサイズについては再起動 or restartでしか反映できない
createuserコマンド
- -s, --superuser
  - スーパーユーザ
- -S, --no-superuser
  - 一般ユーザ
- -d, --createdb
  - データベース作成許可
- -D, --no-createdb
- -r, --createrole
  - ユーザ作成許可
- -R, --no-createrole
  - ユーザ作成不許可
- -P, --pwprompt
  - パスワードの設定
- -l, --login
  - ログイン権限あり
- -L, --no-login
  - ログイン権限なし
dropuserコマンド
- デフォルトでは警告なしで削除される
- スーパーユーザーのみ実行可能
createdbコマンド
- -E, --encoding
- -O, --owner
- -T, --template
pg_hpa.conf書式
- ユーザー名に*を使用できない
- 上から順に判定されて、判定条件に入ったらexitする(最後まで判定されない方式)
- 真偽値
  - 真
    - true, on, yes, 1
- カンマ区切りで複数の接続先データベースを併記できる
postgresql.conf書式
- ミシレイニアス
  - パラメータは小文字大文字を問わない
- log_connections
  - off
- default_transaction_isolation
  - read ccommitted
- listen_address
  - localhost
- port
- max_connection
  - 100
- client_encoding
  - SQL_ASCII
- log_destination
  - stderr
  - options
    - syslog
      - syslogに出力
- logging_collector
  - off
  - 標準エラーをファイルに書き出すかどうか
- log_directory
  - log
- log_min_message
  - WARNING
- log_filename
- log_line_prefix
- log_statement
pg_settingsビュー
- メタテーブル
- サーバのパラメータ値が保存されている
- 参照と更新が可能
ALTER SYSTEM
- 概要
  - サーバのパラメータを変更するクエリ
  - 設定した内容はpostgresql.auto.confに書き込まれる
postgresql.auto.conf
- postgresql.confより優先される
SET LOCAL, SET SESSION
- 実行時パラメータの設定を変更
- 再起動すると値は消える
pg_basebackupコマンド
PITR
- ファイルからバックアップできるようにすること
- WALファイル
  - トランザクションデータのこと
pg_start_backup()、pg_stop_backup()
- バックアップコマンド
pg_dumpコマンド
- -F, --format
  - p: text
  - c: カスタムバイナリ
  - t: tar
- -f, --filename
  - 出力

CLI

パスワードが不明なのでpostgresで最初に入る

$ /usr/bin/sudo -u postgres psql

ユーザの作成

$ /usr/bin/sudo -u postgres createuser ${USER} -S --createdb

データベースの作成

$ /usr/bin/sudo -u postgres createdb -T template0 testdb