今回は PostgreSQL の運用管理（ユーザー管理、バキューム、インフラ運用など）について解説します。
vacuumdb コマンド
データベースを日々運用していくと、データの更新によって使用されなくなった不要領域が蓄積していきます。
vacuumdb コマンドはその不要領域を回収するコマンドです。
不要な領域が回収されると、その分の領域が使用できるようになります。
■デフォルト（オプションなし）
|
-bash-4.2$ vacuumdb |
- -a ← すべてのデータベースを不要領域の回収対象とします。しかし不要領域は削除しません。
- -f ← 不要領域を OS 上から削除します。
- -z ← 不要領域の回収を行い、且つ統計情報の収集や更新を行います。（不要領域 OK、統計情報 OK）
- -Z ← 不要領域の回収はしません。統計情報の収集や更新のみ行います。（不要領域 NO、統計情報 OK）
■-f オプションの例
|
-bash-4.2$ vacuumdb -f |
■-a オプションの例
|
-bash-4.2$ vacuumdb -a |
-a オプションは、統計情報は収集・更新せずに、すべてのデータベースの不要領域を回収します。
しかし回収した不要領域の削除は行いません。
ユーザー管理
ユーザー管理の方法です。
PostgreSQL のユーザーは、OS のユーザーとは違います。
PostgreSQL のユーザーは、データベースクラスタ単位で作成され、クラスタ全体で利用できます。
ユーザーとロールの違い
- PostgreSQL ユーザー ← 単体
- PostgreSQL ロール ← 複数のユーザーをまとめたグループ（単体としても使用できる）
ロールはグループですがユーザーと同じように利用できます。
CREATE ROLE コマンド
- CREATE ROLE <ROLE名> WITH LOGIN ← データベースへ接続できる権限
- CREATE ROLE <ROLE名> WITH SUPERUSER ← ユーザーユーザー権限
- CREATE ROLE <ROLE名> WITH CREATEDB ← データベース作成権限
- CREATE ROLE <ROLE名> WITH CREATEROLE ← ロール（ユーザー）作成権限
- CREATE ROLE <ROLE名> WITH PASSWORD ‘パスワード’ ← パスワード設定
※CREATE ROLE <ROLE名>で作成されたロールは、デフォルトでは「NOLOGIN」になっているので、データベースへのアクセス権限を与える場合は明示的に「WITH LOGIN」が必要になります。
※CREATE USER <USER名>で作成されたユーザーはデフォルトで「LOGIN」になっているので、「WITH LOGIN」オプションは不要です。
ロール（ユーザー）に対してパスワードを設定する
【例】
|
CREATE ROLE Test01 WITH PASSWORD ‘Password01’; |
CREATE USER コマンド
CREATE USER コマンドで、新規でデータベースロール（データベースユーザー）を作成することができます。
■CREATE ROLE と CREATE USER の違い
- CREATE ROLE ← デフォルトで「NOLOGIN」
- CREATE USER ← デフォルトで「LOGIN」
createuser コマンド
createuser コマンドで PostgreSQL の新規アカウントを作成できます。
■オプション
- -p ← パスワードを設定します。
- -s ← 新規アカウントをユーザーユーザーとして作成します。
- -d ← データベースの作成を許可します。
- -r ← 新規ユーザーの作成を許可します。
- -l ← ログインを許可します。（デフォルト許可）
- -S ← 新規アカウントをスーパーユーザーにしません。（デフォルト禁止）
- -D ← データベースの作成を禁止します。（デフォルト禁止）
- -R ← 新規アカウントの作成を禁止します。（デフォルト禁止）
- -L ← ログインを禁止します。
以下のように覚えると忘れにくいです。
- 小文字系 ← 許可
- 大文字系 ← 禁止
■createuser コマンドを実行できる権限
createuser コマンドは以下の権限を持っているユーザーでのみ実行できます。
- スーパーユーザー
- CREATEROLE 権限を持っているユーザー
dropuser コマンド
PostgreSQL ユーザーアカウントを削除するコマンドです。
dropuser コマンドは「DROP ROLE」コマンドで一緒です。
ただし、データベースオブジェクトを所有するユーザーは削除できません。
■dropuser コマンドのオプション
- -i –interactive ← ユーザーを削除する前に確認のメッセージが表示されます。
REVOKE
REVOKE は GRANT コマンドとは逆に「権限を取り消す」コマンドです。
※revoke の意味は「取消す、廃止する、解約する」という意味です。
■主な取り消しできる権限
- すべての権限を付与（ALL）
- テーブルへのアクセス権限を付与（SELECT）
- データベースへのアクセス権限を付与（CONNECT）
- スキーマへのアクセス権限を付与（CREATE）
- テーブルのデータをファイルにコピーする権限を付与（COPY）
- 外部キーを作成する権限を付与（REFERENCES）
- トリガーの作成を許可する（TRIGGER）
【テーブルへのアクセス権限を取消す】
|
REVOKE <権限> ON <テーブル名> FROM <ユーザー名>; |
【データベースへのアクセス権限を取消す】
|
REVOKE <権限> ON DATABASE <データベース名> FROM <ユーザー名>; |
【スキーマへのアクセス権限を取消す】
|
REVOKE <権限> ON SCHEMA <スキーマ名> FROM <ユーザー名>; |
テーブルの権限を削除する
REVOKE コマンドでテーブルの権限を削除します。
\c で対象のデータベースに接続します。
|
postgres-# \c testdb |
現状の構成を確認します。
|
testdb=# \z |
REVOKE コマンドで DELETE 権限のみ削除します。（全部の権限ではない）
|
testdb=# REVOKE DELETE ON testtable FROM “test03”; |
確認します。
|
testdb=# \z |
実行中の PostgreSQL のプロセスを探す
以下の「SELECT * FROM pg_stat_activity;」コマンドで実行中のプロセス一覧を表示できます。
|
postgres=# SELECT * FROM pg_stat_activity; |
横長ではなく縦長で表示したい
SQL 文の実行結果が表示されたときに横長になると見にくくなります。
そのため「横長」ではなく「縦長」に表示したい場合があります。
|
postgres=# \x auto |
■設定前
|
postgres=# SELECT * FROM pg_stat_activity; |
■設定後
以下のように横長から縦長になり、すっきりとして SQL 文の結果が見やすくなりました。
|
postgres=# SELECT * FROM pg_stat_activity; |
縦長、横長を切り替える
\x コマンドを繰り返すことで「on」「off」を切り替えます。
|
postgres=# \x |
- on ← 縦長で表示されます。
- off ← 横長で表示されます。
PostgreSQL シリーズ
今まで学習した PostgreSQL の技術をシリーズとしてまとめました。
【PostgreSQL】PostgreSQL の特徴と基本【Part.1】
【PostgreSQL】【正規化】リレーショナルデータベース（RDMS）の基本【Part.2】
【PostgreSQL】PostgreSQL のインストールと初期設定【Part.3】
【PostgreSQL】PostgreSQL の標準付属ツールの説明とコマンド手順【Part.4】
【PostgreSQL】PostgreSQL の設定ファイル（postgresql.conf、pg_hba.conf）の解説【Part.5】
【PostgreSQL】PostgreSQL のバックアップ手順とリストア手順【Part.6】
【PostgreSQL】PostgreSQL の運用管理（ユーザー管理、バキューム）【Part.7】
【PostgreSQL】PostgreSQL の基本的な SQL文 とオブジェクトについて【Part.8】
【PostgreSQL】PostgreSQL の組み込み関数、ユーザー定義関数、演算子について【Part.9】
【PostgreSQL】PostgreSQL のトランザクションについて【Part.10】
【PostgreSQL】SQL文【Part.11】
【PostgreSQL】テーブル設計（データ型、制約）【Part.12】
コメント