5 クライアントアプリケーション（解説）

5.1 対話型ターミナル

PostgreSQLのターミナル型フロントエンドpsqlは、対話的にSQL文を入力してサーバで実行し、その結果を確認することができるアプリケーションです。また、psqlはファイルとの間でSQL文やデータの入出力が可能で、各種のメタコマンドをサポートしています。

psqlコマンド

psqlは、OSのシェルから起動します。通常、/usr/binディレクトリにインストールされているため、すべてのユーザが利用できます。起動方法は以下の通りです。

psql [オプション ...] [データベース名 [ユーザ名]]

• データベース名は、接続するデータベースの名前です。省略時は、コマンドを起動しているユーザ名と同じ名前のデータベースになります。
• ユーザ名は、データベースに接続するユーザの名前です。省略時は、コマンドを起動しているユーザ名になります。
• オプションは、データベースへの接続、入出力、その他多岐にわたるpsql起動時のオプションを指定できます。主なものを以下に示します。

例えば、localhostのossdbデータベースにsatoユーザでTCP/IP接続する場合は、以下のように指定します。

psql -h localhost -U sato ossdb

psqlを起動してデータベースへの接続が完了すると、'%/%R%x%#' の形式のプロンプトを表示してコマンドの入力を受け付けます。

• %/は、接続しているデータベース名前です。
• %Rは、コマンドの状態です。先頭行(=)、継続行(-)、その他があります。
• %xは、トランザクションの状態です。トランザクション内(*)、失敗したトランザクション内(!)で、トランザクション外は空文字です。
• %#は、ユーザの状態です。スーパーユーザ(#)、その他のユーザ(>)です。

例えば、'ossdb=*#' は、ossdbにスーパーユーザで接続したトランザクション内で次のコマンドを待っていることを示します。

psqlで使用可能なコマンドには以下の種類があります。

メタコマンド

psqlの主なメタコマンドを以下に示します。

メタコマンドは上記以外にも多数あります。詳細はマニュアルで確認してください。

5.2 ユーザの管理

PostgreSQLは、ロールという概念を使用してユーザのデータベース接続やオブジェクトの所有を管理します。ロールは、その設定方法によって、単一のデータベースユーザ、またはデータベースユーザのグループとなります。このページでは、ロールを扱うクライアントアプリケーションとSQL文について説明します。

createuserコマンド

createuserコマンドは、データベースクラスタに新しいロールを追加します。形式は以下の通りです。

createuser [オプション ...] [ロール名]

• ロール名は、追加するロールの名前です。
• オプションは、サーバへの接続情報と作成するデータベースに関するオプションです。主なものを以下に示すます。

以下は、ログインかつデータベース作成が可能なユーザを追加するcreateuserコマンドの例です。

createuser -d -P sato

上記は、satoユーザをパスワード付きで作成します。

CREATE ROLE文

CREATE ROLE文は、createuserコマンドとほぼ同じ機能を持つSQL文です。形式は以下の通りです。

CREATE ROLE ロール名 [[WITH] オプション [...]]

• ロール名は、追加するロールの名前です。
• オプションは複数指定でき、以下のものがあります。太文字はデフォルトです。
  SUPERUSER | NOSUPERUSER --- スーパーユーザとなるか否か
  LOGIN | NOLOGIN --- ログイン可能か否か
  CREATEROLE | NOCREATEROLE --- 新しいロールを生成できるか否か
  CREATEDB | NOCREATEDB --- 新しいデータベースを作成できるか否か
  [ENCRYPTED] PASSWORD 'password' | NULL --- ログインパスワード(NULLの場合はパスワード認証不可)
  IN ROLE ロール名 [, ...] --- メンバとして追加する既存のロールグループ

CREATE USER文は、CREATE ROLE文と同様の機能を持ちますが、デフォルトでログイン可能になる点が異なります。

CREATE USER ロール名 [[WITH] オプション [...]]

以下は、ログイン可能なユーザを追加するCREATE ROLE文の例です。

CREATE ROLE takahashi WITH LOGIN PASSWORD 'secret';

上記は、ログイン可能なtakahashiユーザをパスワード付きで作成します。

dropuserコマンド

dropuserコマンドは、データベースクラスタのロールを削除します。形式は以下の通りです。

dropuser [オプション ...] [ロール名]

• ロール名は、削除するロールの名前です。
• 接続オプションは、createuserコマンドと同じです。

以下は、ユーザを削除するdropuserコマンドの例です。

dropuser -U postgres sato

上記は、postgresユーザでデータベースに接続し、satoユーザを削除します。

DROP ROLE文

DROP ROLE文は、dropuserコマンドとほぼ同じ機能を持つSQL文です。形式は以下の通りです。

DROP ROLE [IF EXISTS ] ロール名 [, ...]

• IF EXISTS を指定すると、ロールが存在しなくてもエラーになりません。
• ロール名は、削除するロールの名前です。複数指定できます。

DROP USER文は、DROP ROLE文と完全に同じです。

DROP USER [IF EXISTS ] ロール名 [, ...]

以下は、ユーザを削除するDROP ROLE文の例です。

DROP ROLE takahashi;

上記は、takahashiユーザを削除します。

GRANT文

GRANT文には、基本的に2つの種類があります。1つは、表、列、ビューなどのデータベースオブジェクトに対する権限の付与、もう1つはロール内のメンバ資格の付与です。1つ目の形式は以下の通りです。

GRANT 権限 ON オブジェクト TO ロール [WITH GRANT OPTION]

オブジェクトの種類によって付与できる権限は多岐に渡るため、ここでは表とビューに関する権限に絞って説明します。

• 権限は、SELECT, INSERT, UPDATE, DELETE, TRUNCATE, REFERENCES, TRIGGER のいずれかです。カンマで区切って複数指定できます。
  ALL [PRIVILEGES] を指定すると前記の権限すべてを一括で付与します。
• オブジェクトは、[TABLE] 表名 [, ...] で指定します。
  ALL TABLES IN SCHEMA スキーマ名 [, ...] を指定すると、別途説明するスキーマに所属するすべての表に対する権限となります。
• ロールは、ロール名 [, ...] で指定します。
  PUBLICを指定すると、今後作成されるロールを含む全てのロールに権限が付与されます。
• WITH GRANT OPTION を指定すると、権限の受領者は他のユーザにその権限を与えることができます。

GRANT文の2つ目の形式は以下の通りです。

GRANT ロール名1 [, ...] TO ロール名2 [WITH ADMIN OPTION]

• ロール名1は、資格を持っているユーザの名前です。
• ロール名2は、資格が与えられるユーザの名前です。
• WITH ADMIN OPTION を指定すると、資格の受領者は他のユーザにメンバ資格を与えることができます。

GRANT文の例です。

GRANT SELECT ON prod TO sato;

上記は、prod表に対するSELECT権限をsatoユーザに与えています。

GRANT sato TO suzuki;

上記は、satoユーザの資格をsuzukiユーザに与えています。suzukiユーザは、それまで保有している権限に加えてsatoユーザの権限を持つことになります。

REVOKE文

REVOKE文には、GRANT文に対応する2つの種類があります。1つは、オブジェクトに対する権限の取り消し、もう1つはロール内のメンバ資格の取り消しです。1つ目の形式は以下の通りです。

REVOLE [GRANT OPTION FOR] 権限 ON オブジェクト FROM ロール

• 権限、オブジェクト、ロールについては、GRANT文と同じです。取り消すことができるのは、ユーザが直接付与した権限のみです。
• GRANT OPTION FOR を指定すると、権限自体の取り消しではなく、GRANT OPTIONの取り消しです。

REVOKE文の2つ目の形式は以下の通りです。

REVOKE [ADMIN OPTION FOR] ロール名1 [, ...] FROM ロール名2

• ロール名1、ロール名2については、GRANT文と同じです。
• ADMIN OPTION FOR を指定すると、資格自体の取り消しではなく、ADMIN OPTIONの取り消しです。

REVOKE文の例です。

REVOKE sato FROM suzuki;

上記は、suzukiユーザが持っていたsatoユーザの資格を取り消します。

REVOKE SELECT ON prod FROM sato;

上記は、satoユーザが持っていたprod表に対するSELECT権限を取り消します。

SET ROLE文

SET ROLE文は、現在のユーザ識別子を別のロールに変更し、そのロールでログインした状態にします。形式は以下の通りです。

SET ROLE ロール名

• ロール名は、変更後のロールの名前です。

現在のユーザ識別子はCURRENT_USER、セッションのユーザ識別子はSESSION_USERで参照できます。また、RESET ROLE文でCURRENT_USERをSESSION_USERに戻すことができます。

5.3 データベースの作成

PostgreSQLのサーバには、以下の3つのデータベースがインストール時に作成されています。

• postgres --- サーバ接続用のデフォルトのデータベース
• template1 --- 新しいデータベースを作成する際のテンプレートになるカスタマイズが可能なデータベース
• template0 --- template1と同じ。ただし、システム標準の設定のみが含まれており、ユーザによる変更不可

新しいデータベースを作成することにより、複数のデータベースを同時に利用できます。ただし、クライアントはデータベースごとに接続する必要があります。このページでは、データベースの作成・削除を行うクライアントアプリケーションとSQL文について説明します。

createdbコマンド

createdbは、新しいデータベースを作成します。形式は以下の通りです。

creaatedb [オプション] [データベース名]

• データベース名は、作成するデータベースの名前です。省略時は、接続しているユーザ名になります。
• オプションは、サーバへの接続情報と作成するデータベースに関するオプションです。主なものを以下に示すます。

例えば、postgresユーザで接続してnewdbデータベースを作成する場合は、以下のように指定します。

createdb -U postgres -O sato newdb

作成したnewdbデータベースの所有者は、satoになります。

CREATE DATABASE文

CREATE DATABASE文は、createdbコマンドと同じ機能を持つSQL文です。形式は以下の通りです。

CREATE DATABASE データベース名 [WITH オプション [...]]

• データベース名は、作成するデータベースの名前です。
• オプションは、作成するデータベースに関するオプションで、複数指定できます。主なものを以下に示すます。
  OWNER[=] 所有者のユーザ名
  ENCODING[=] 文字コード
  LOCALE[=] ロケール
  TEMPLATE[=] テンプレートデータベース

以下は、newdbデータベースを作成するCREATE DATABASE文です。

CREATE DATABASE newdb OWNER sato;

dropdbコマンド

dropdbは、既存のデータベースを削除します。削除できるのは、データベースの所有者かスーパーユーザです。形式は以下の通りです。

dropdb [オプション] データベース名

• データベース名は、削除するデータベースの名前です。
• オプションは、サーバへの接続情報とそれ以外のオプションです。主なものを以下に示すます。

例えば、newdbデータベースを確認の上削除する場合は、以下のように指定します。

dropdb -U postgres -i newdb

DROP DATABASE文

DROP DATABASE文は、dropdbコマンドと同じ機能を持つSQL文です。形式は以下の通りです。

DROP DATABASE [IF EXISTS] データベース名

• データベース名は、削除するデータベースの名前です。

以下は、newdbデータベースが存在すれば、それを削除するDROP DATABASE文です。

DROP DATABASE IF EXISTS newdb;

5.4 データベースのバックアップ

データベースの運用においては、システムの障害やユーザの誤操作等によってデータが失われないように、普段からバックアップ(ダンプ)を取得しておき、万一問題が生じた際にはリカバリ(リストア)ができるようにしておくことが重要です。PostgreSQLのバックアップ取得方法には、以下の2種類があります。

• 物理バックアップ --- データベースクラスタが配置されているディレクトリ全体をOSのファイルレベルでバックアップします。その方法には、PostgreSQLサーバを停止して行うオフラインバックアップと、サーバを停止させずに行うオンラインバックアップがあります。いずれの場合もリカバリの際にはサーバを一時停止させる必要があります。
• 論理バックアップ --- データベースに格納されているデータを、データベースまたは表単位でバックアップします。バックアップ、リカバリともにPostgreSQLサーバを停止させずにオンラインで行うことができます。また、バックアップの一部を使ったリカバリも可能です。

このページでは、論理バックアップを行うためのクライアントアプリケーションとSQL文について説明します。

pg_dumpコマンド

データベースの内容をスクリプトファイルまたは他のアーカイブファイルへ出力します。形式は以下の通りです。

pg_dump [オプション] [データベース名]

• データベース名は、ダンプするデータベースの名前です。省略時は、接続しているユーザ名になります。
• オプションは、サーバへの接続情報とダンプ方法に関するオプションです。主なものを以下に示すます。

出力形式に平文を選択した場合は、SQL文のスクリプトが出力されるため、psqlコマンドでリストアできます。それ以外の形式を選択した場合は、pg_restoreコマンドでリストアする必要があります。

以下は、ossdbデータベースのダンプをdb.sqlファイルに出力するpg_dumpコマンドです。

pg_dump ossdb > ossdb1.sql

db.sqlファイルは平文形式であるため、リストア時にはpsqlコマンドを使用します。

データベースクラスタ内の全データベースを一括してダンプしたい場合は、pg_dumpallコマンドを使用します。形式は以下の通りです。

pg_dumpall [オプション]

pg_dumpallコマンドの詳細についてはマニュアルを参照してください。

pg_restoreコマンド

pg_restoreは、pg_dumpによって作成されたアーカイブファイルからリストアを行います。形式は以下の通りです。

pg_restore [オプション] [ファイル名]

• ファイル名は、リストアするアーカイブファイルの名前です。省略時は、標準入力になります。
• オプションは、サーバへの接続情報とリストア方法に関するオプションです。主なものを以下に示すます。

ダンプファイルの出力形式が平文の場合は、psqlコマンドを使用してください。

pg_restoreコマンドの例を示します。その前提として、以下のpg_dumpコマンドでossdbデータベースをカスタム形式のダンプファイルにダンプしているものとします。

pg_dump -Fc ossdb > ossdb2.dump

新たにossdb2データベースを作成し、pg_restoreコマンドでダンプファイルからリストアします。

craetedb ossdb2
pg_restore -d ossdb2 ossdb2.dump

ossdb2は、ossdbと同じ内容のデータベースになります。

\copyメタコマンド

psqlの\copyメタコマンドは、ファイルと表の間でデータをコピーします。ファイルへのアクセスは、クライアントユーザの権限で行います。コピーする方向によって、以下の2種類の形式があります。

\copy {表名 [(列名 [, ...])] | (問い合わせ)} to 'ファイル名' [[with] (オプション [, ...])]

\copy 表名 [(列名 [, ...])] from 'ファイル名' [[with] (オプション [, ...])]

前者(\copy to)は表の内容または問合せ結果をファイルに出力し、後者(\copy from)はファイルの内容を表に追加します。

• 表名は、既存の表の名前です。
• 列名は、列の名前です。省略時は、表のすべての列を指定したことになります。
• ファイル名は、コピーするファイルの名前です。
• 問い合わせは、コピーするデータを検索するSELECT文です。
• オプションは、コピー方法のオプションです。主なものを以下に示すます。
  format {text | csv} --- ファイルの形式、省略時はtext
  delimiter '区切り文字' --- データの区切り文字、text形式はタブ文字(\t)、csv形式はカンマ(,)
  header {true | falase} --- csv形式のヘッダの有無、省略時はfalse
  quote '引用符' --- csv形式の文字列の引用符、省略時は二重引用符(")
  encoding '文字コード名' --- ファイルの文字コード、省略時はutf8

例えば、prod表のすべてのデータをdata1.txtに出力する\copy toメタコマンドは、以下のように指定します。

\copy prod to 'data1.txt'

data1.txtファイルは、文字コードがUTF8のテキスト形式のタブ区切りファイルになります。

次は、prod表にdata1.txtファイルのデータを追加する\copy fromメタコマンドです。なお、区切り文字を1文字のためタブ文字はエスケープ文字列定数 E'\t' で表します。

\copy prod from data1.txt with (delimiter E'\t')

data1.txtファイルにタブ区切りで格納されたデータがprod表に追加されます。

COPY文

COPY文は、\copyメタコマンドと同様にファイルと表の間でデータをコピーします。ただし、COPY文でのファイルへのアクセスは、PostgreSQLサーバの権限で行う点が\copyメタコマンドと異なります。コピーする方向によって、以下の2種類の形式があります。

COPY {表名 [(列名 [, ...])] | (問い合わせ)} TO 'ファイル名' [[WITH] (オプション [, ...])]

COPY 表名 [(列名 [, ...])] FROM 'ファイル名' [[WITH] (オプション [, ...])]

前者(COPY TO)は表の内容または問合せ結果をファイルに出力し、後者(COPY FROM)はファイルの内容を表に追加します。

• COPY文のパラメータは、\copyメタコマンドと同一です。
  ただし、ファイル名は絶対パスで指定する必要があります。また、AlmaLinuxの場合はSELinuxの設定によってはファイルにアクセスできない場合があります。

以下は、COPY文の使用例です。

COPY prod TO '/home/postgres/data2.csv' WITH (FORMAT csv, HEADER true);

prod表の内容をヘッダ付きのCSVファイルに出力しています。

5.5 バキューム処理

PostgresSQLは、表のデータを更新したときに元のデータを直接書き換えるのではなく、新たなデータを追加する追記型アーキテクチャを採用しています。そのため、データの更新と削除が繰り返されると表の中の空き領域が増加して性能が低下します。バキューム処理は、この空き領域を回収して再利用可能にします。この処理は自動で行われますが、手動で行うこともできます。

vacuumdbコマンド

vacuumdbは、PostgreSQLデータベースの不要領域のクリーンアップを行うとともに、問い合わせオプティマイザが使用する内部的な統計情報も生成します。形式は以下の通りです。

vacuumdb [オプション] [データベース名]

• データベース名は、バキュームするデータベースの名前です。省略時は、接続しているユーザ名になります。
• オプションは、サーバへの接続情報とバキューム方法に関するオプションです。主なものを以下に示すます。

例えば、ossdbデータベースのバキュームを行う場合は、以下のように指定します。

vacuumdb --analyze ossdb

データベースのバキュームとオプティマイザ用の解析の両方が行われます。

VACUUM文

VACUUM文は、vacuumdbコマンドとほぼ同じ機能を持つSQL文です。形式は以下の通りです。

VACUUM [(オプション [, ...])] [表名 [, ...]]

• 表名は、バキュームする表の名前です。省略時はすべての表がバキュームされます。
• オプションは複数指定でき、以下のものがあります。
  FULL --- 完全なバキュームを実行
  ANALYZE --- オプティマイザが使用する統計情報も生成
  VERBOSE --- 処理中に詳細な情報を表示

以下は、バキュームする表を指定したVACUUM文の例です。

VACUUM (ANALYZE, VERBOSE) prod, orders;

上記は、prod表とorders表をバキュームします。

ANALYZE文

ANALYZE文は、データベース内の表に関する統計情報を収集します。形式は以下の通りです。

ANALYZE [(オプション [, ...])] [表名]

• 表名は、統計情報を収集する表の名前です。省略時はすべての表から収集されます。
• オプションは複数指定でき、以下のものがあります。
  VERBOSE --- 処理中に詳細な情報を表示
  SKIP_LOCKED --- ロックされた表をスキップ