OSS-DB　Silver

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

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

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

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

今回は運用管理の中から基本的な運用管理について説明します。

Sec 7. 基本的な運用管理

データベースユーザの追加/削除/変更

追加：createuser
削除：dropuser

変更：ALTER USER

ALTER USER ユーザ名 [[WITH]
        SUPERUSER  | NOSUPERUSER
    | CREATEDB   | NOCREATEDB
    | CREATEROLE | NOCREATEROLE
    | LOGIN      | NOLOGIN
    | PASSWORD 'パスワード'
    | VAID UNTIL '日付']
ALTER USER ユーザ名 RENAME TO 新しいユーザ名

ALTER USERはユーザの作成権限を持つユーザで実行する必要がある

VACUUM, ANALYZE

VACUUM
- 前提：PostgreSQLは複数のユーザからのトランザクションを同時に実行するため、MVCC(Multi-Version Concurrency Control)を実装している。PostgreSQLのMVCCでは追記型を採用しており、不要領域の回収が必要
- 補足：追記型は読み込みロックと書き込みロックが同時に発生しない形
- 途中でコミットされた場合に、ロックの獲得が競合しないようになっている反面、追記が行われる前のデータに関しては不要領域となる。
  →これを解放するのがVACUUM
- VACUUM [FULL] [VERBOSE] [テーブル名]
- VACUUMの実行中は、ほとんどのSQLを通常どおりに実行可能
- VACUUM FULLを実行すると、テーブルの内容を新しいファイルに書き換えて、不要領域を物理的にディスクから削除する。
  →実行中は、対象のテーブルを排他ロックするので他のクライアントからの処理はできない。
- VACUUMでテーブルを指定しなかった場合は、データベース内の全てのテーブルに対して実行される。
- VERBOSEを指定すると、実際に削除した領域などの詳細情報を見ることが可能。標準エラー出力に出力される。
ANALYZE
- SQLはデータベースに格納されているデータの統計情報に基づいて実行される。
  この統計情報を更新するのがANALYZE
- ```
ANALYZE [テーブル名]
```
- 特定の列を指定して実行可能。また、対象となるテーブルからランダムにサンプリングして統計情報を更新するため、巨大なテーブルであっても、短時間で解析可能。
VACUUM ANALYZE
- ```
VACUUM ANALYZE [テーブル名]
```
VACUUMとANALYZEを同時に実行する(テーブル名を省略した場合は全てのテーブルが対象となる)。
自動バキューム
- 定期的に行う訳ではなく、不要領域が増えた段階で実行
- postgresql.confのautovacuumで設定する

システム情報取得関数

version() : サーバで稼働しているPostgreSQLのバージョン
current_database() : 現在接続しているデータベース
current_user, user : 現在のユーザ（カッコは不要）

情報スキーマ、システムカタログ

情報スキーマ(information_schema)：SQLの標準規格で規定

例）

SELECT * FROM information_schema.enabled_roles;
         role_name
---------------------------
 hfuku
 pg_database_owner
 pg_read_all_data
 pg_write_all_data
 pg_monitor
 pg_read_all_settings
 pg_read_all_stats
 pg_stat_scan_tables
 pg_read_server_files
 pg_write_server_files
 pg_execute_server_program
 pg_signal_backend
 dbuser
(13 rows)

システムカタログ(pg_catalog)：データベース管理ソフトウェア固有

例)

SELECT * FROM pg_roles WHERE rolcanlogin IS TRUE;
 rolname | rolsuper | rolinherit | rolcreaterole | rolcreatedb | rolcanlogin | rolreplication | rolconnlimit | rolpassword | rolvaliduntil | rolbypassrls | rolconfig |  oid
---------+----------+------------+---------------+-------------+-------------+----------------+--------------+-------------+---------------+--------------+-----------+-------
 user1   | t        | t          | t             | t           | t           | t              |           -1 | ********    |               | t            |           |    10
 dbuser  | f        | t          | t             | t           | t           | f              |           -1 | ********    |               | f            |           | 16384
(2 rows)

PostgreSQLのシステムカタログには、通常先頭に「pg_」がつく

テーブル単位の権限

GRANT : テーブルの権限の設定に使用する

GRANT {{SELECT | INSERT | UPDATE | DELETE | TRUNCATE | REFERENCES | TRIGGER } [, ...] | ALL [PRIVILEGES]} 
ON {[TABLE] テーブル名 [, ...]}
TO {ユーザ名 | PUBLIC } [, ...] [WITH GRANT OPTION]

SELECT権限を付加することで、COPY TO STDOUTを使用できるようになる。

REVOKE : テーブルに設定されたアクセス権限を取り除く

REVOKE {{SELECT | INSERT | UPDATE | DELETE | TRUNCATE | REFERENCES | TRIGGER } [, ...] | ALL [PRIVILEGES]} 
ON {[TABLE] テーブル名 [, ...]}
TO {ユーザ名 | PUBLIC } [, ...]

\dp, \z: テーブルの権限の確認
- どちらのコマンドも同様に各テーブルのアクセス権限を確認可能
- 表示される権限の種類
- r : SELECT
- w : UPDATE
- a : INSERT
- d : DELETE
- D : TRUNCATE(全ての行の削除（テーブルを消せる訳ではない）)
- x : REFERRENCES
- t : TRIGGER
- /yyyy : この権限を設定したユーザ

【OSS-DB Silver】PostgreSQL基本的な運用管理　ユーザやテーブルに権限を指定する方法は？