オープンソースの関係データベース管理システム(RDBMS)

目次

ダウンロード

  1. Webブラウザで日本PostgreSQLユーザ会のWebサイトを開く。
    https://www.postgresql.jp/
  2. ダウンロードページから任意のプラットフォーム用のインストーラをダウンロードする。

インストール

エクスプローラからMicrosoft Windows用PostgreSQLのインストーラを右クリックしてコンテキストメニューを表示させる。メニューから「管理者として実行」をクリックする。

ユーザーアカウント制御のダイアログが表示されて「次のプログラムにこのコンピュータへの変更を許可しますか?」と聞かれるので、プログラム名がPostgreSQLのインストーラであることを確認して、「はい(Y)」ボタンをクリックする。

Windows用PostgreSQLはMicrosoft Visual C++ Redistributableが必要である。まだインストールされていなければ自動的にインストールされる。既にインストールされていれば、Microsoft Visual C++ Redistributableのインストールはスキップされる。

セットアップウィザードが表示されるので、「Next >」ボタンをクリックする。

インストールディレクトリを聞かれるので、任意のディレクトリを指定する。基本的にデフォルトのディレクトリでよい。ディレクトリを指定したら「Next >」ボタンをクリックする。

インストールするコンポーネントを聞かれるので、インストールするコンポーネントにチェックを入れる。基本的にデフォルトの状態のままでよい。コンポーネントを選択したら「Next >」ボタンをクリックする。

データベースのデータを保存するディレクトリを聞かれるので、任意のディレクトリを指定する。インストールディレクトリにProgram Files下のフォルダを指定した場合、データディレクトリのデフォルトもProgram Files下のフォルダになるが、ここはユーザのアクセスが制限される場所なので、Program Files下以外に変更した方がよい。

データディレクトリを指定したら「Next >」ボタンをクリックする。

データベース管理者とサービスのアカウント (postgres) のパスワードを聞かれるので、任意のパスワードを入力する。パスワードを入力したら「Next >」ボタンをクリックする。

PostgreSQLサーバが接続を受け付けるポート番号を聞かれるので、任意のポート番号を入力する。基本的にデフォルトの状態のままでよい。ポート番号を入力したら「Next >」ボタンをクリックする。

作成するデータベース・クラスタのロケールを聞かれるので、「C」を選択する。

ロケールを選択したら「Next >」ボタンをクリックする。

インストール設定が表示されるので、「Next >」ボタンをクリックする。

インストールの準備が整った旨が表示されるので、「Next >」ボタンをクリックする。

インストールが実行される。

インストールが完了した旨が表示される。PostgreSQLの追加ソフトウェアをインストールするためのツールである「スタックビルダ」を起動するかどうかのチェックがあるので、インストーラを終了させた後に起動させたければチェックを入れる。追加したいソフトウェアが無ければチェックを外してよい。

「Finish」ボタンをクリックすると、PostgreSQLのインストーラが終了する。

PostgreSQLの起動と停止

PostgreSQLのサービスを起動

# /etc/init.d/postgresql start

PostgreSQLの起動状態を確認

# /etc/init.d/postgresql status
Running clusters: 9.1/main

PostgreSQLのサービスを停止

# /etc/init.d/postgresql stop

PostgreSQLのコマンド

PostgreSQLのコマンド
コマンド 説明
clusterdb PostgreSQLデータベースをクラスタ化する。
createdb データベースを作成する。
createlang PostgreSQL手続き言ををインストールする。
createuser ユーザを作成する。
dropdb PostgreSQLデータベースを削除する。
droplang PostgreSQL手続き言語を削除する。
dropuser ユーザを削除する。
ecpg C言語プログラム用の埋め込みSQLプリプロセッサ
initdb PostgreSQLのデータベースクラスタを新たに作成する。
oid2name PostgreSQLで使用されるファイル構造を確認する。
pg_archivecleanup PostgreSQL WALアーカイブファイルを消去する。
pg_basebackup PostgreSQLクラスタのベースバックアップを取得する。
pg_config インストールしたPostgreSQLバージョン情報を表示する。
pg_controldata PostgreSQLデータベースクラスタの制御情報を表示する。
pg_ctl バックエンドサーバ(postmaster)を操作する。
pg_ctlcluster PostgreSQLクラスタの管理
pg_dump PostgreSQLデータベースをスクリプトファイル又は他のアーカイブファイルへ抽出する。
pg_dumpall PostgreSQLデータベースクラスタをスクリプトファイルへ抽出する。
pg_isready PostgreSQLサーバの接続情報を検査する。
pg_lsclusters PostgreSQLクラスタの情報表示
pg_receivexlog PostgreSQLクラスタからトランザクションログをストリームする。
pg_resetxlog PostgreSQLデータベースの先行書き込みログやその他の制御情報を初期化する。
pg_restore pg_dumpによって作成されたアーカイブファイルからPostgreSQLデータベースをリストアする。
pg_standby ウォームスタンバイデータベースサーバを作成する。
pg_test_fsync PostgreSQLの最も高速なwal_sync_methodを決定する。
pg_test_timing 時間計測のオーバヘッドを測定する。
pg_upgrade PostgreSQLサーバインスタンスを更新する。
pg_xlogdump PostgreSQLデータベースクラスタの先行書き込みログを表示する。
pgAdmin3 PostgreSQLを管理するGUIアプリケーション
pgbench PostgreSQLに対してベンチマーク試験を行う。
psql 対話形式でデータベースを操作する。
reindexdb PostgreSQLデータベースのインデックスを再作成する。
vacuumdb PostgreSQLデータベースの不要領域の回収と解析を行う。
vacuumlo PostgreSQLデータベースから不要となったラージオブジェクトを削除する。

createdb

データベースを作成する。

createdb [databasename]

databasename には、データベース名を指定する。 databasename を省略した場合は、OSのログインユーザ名でデータベースを作成する。

createuser

ユーザを作成する。

createuser [options] [username]

username にはユーザ名を指定する。 username を省略した場合は、OSのログインユーザ名でユーザを作成する。

-d
--createdb
作成するユーザにデータベース作成を許可する。
-P
--pwprompt
作成するユーザのパスワードを入力するプロンプトを表示する。

dropdb

既存のデータベースを削除する。

dropdb [options] dbname
-h host
--host host
ホスト名
-p port
--port port
ポート番号
-U username
--username username
接続に使用するユーザ名

dropuser

既存のユーザを削除する。

dropuser [options] [username]
-h host
--host host
ホスト名

pg_ctl

バックエンドサーバ(postmaster)を操作する。

pg_ctl	subcommand [subcommand-options]
pg_ctl start [-w] [-s] [-D datadir] [-l filename] [-o options] [-p	path]
pg_ctl stop [-W] [-s] [-D datadir] [-m s[mart] | f[ast] | i[mmediate] ]
pg_ctl restart [-w] [-s] [-D datadir] [-m s[mart] | f[ast] | i[mmediate] ] [-o options]
pg_ctl reload [-s] [-D datadir]
pg_ctl status [-D datadir]
pg_ctlのサブコマンド
コマンド 説明
start postmasterを起動する。
stop postmasterを停止する。
restart postmasterを再起動する。
reload postmasterに設定ファイルを再度読み込ませる。
status postmasterの状態を表示する。
-D datadir
データベースファイルのディレクトリを指定する。省略した場合、環境変数 PGDATA が使われる。
-m mode
停止モード
-w
起動又は停止処理が終了するのを待つ。
-W
起動又は停止処理が終了するのを待たない。

pg_ctlcluster

pg_ctlclusterは、PostgreSQLクラスタの開始、終了、再開又はリロードを行うコマンドである。pg_ctlclusterコマンドを使用するには、データベースクラスタの所有者又はスーパーユーザである必要がある。

pg_ctlcluster [options] cluster-version	cluster-name	action
		

action には以下のいずれかを指定する。

アクション
start
stop
restart
reload
autovac-start
autovac-stop
autovac-restart
-o option
option で指定したオプションをpostmasterのコマンド行オプションとしてそのまま渡す。
$ pg_ctlcluster 9.1 main

pg_lsclusters

pg_lsclustersは、PostgreSQLのクラスタに関する情報を表示するコマンドである。

pg_lsclusters [options]

設定ファイル

設定ファイル 説明
pg_hba.conf クライアントの認証に関する設定ファイル
postgresql.conf PostgreSQLの基本的な設定ファイル

pg_hba.conf

pg_hba.confはクライアントの認証に関する設定ファイルである。

pg_hba.confファイルの例を次に示す。

host studentdb horiuchi 127.0.0.1/32 password
host studentdb horiuchi 127.0.0.1/32 md5

ディレクトリ

pg_xlogディレクトリ

PostgreSQLはデータディレクトリ下のpg_xlogディレクトリ内で先行書き込みログ(Write Ahead Logging: WAL)を管理している。このログはデータベースのデータファイルに行われた変更を記録している。

アーカイブディレクトリ

アーカイブログを格納するディレクトリで、postgresql.confに設定する。

archive_command = 'cp "%p" /var/lib/postgresql/9.1/main/pg_archlog/"%f"'
archive_mode = on

データディレクトリ

データベースのデータを格納するディレクトリで、環境変数PGDATAに設定する。

export PGDATA=/var/lib/postgresql/9.1/main

SQL

PostgreSQLには次の表に示すSQL文がある。

PostgreSQLのSQL一覧
SQL 説明
CREATE ROLE ロール(ユーザ)を作成する。
DROP ROLE ロール(ユーザ)を削除する。
GRANT アクセス権限を付与する。
REVOKE アクセス権限を剥奪する。
SELECT データを検索する。
SET 実行時パラメータを変更する。
SHOW 実行時パラメータの値を表示する。
UPDATE レコードを更新する。

CREATE ROLE

CREATE ROLEはロールを作成するPostgreSQL SQL文である。作成したロールはDROP ROLE文で削除できる。

PostgreSQLでは、いわゆるユーザのことを「ロール」と呼ぶ。Oracleデータベースではユーザとロールは別のものであるが、PostgreSQLではそのような区別はない。

CREATE ROLE name WITH LOGIN PASSWORD 'password'

DROP ROLE

DROP ROLEはCREATE ROLE文で作成したロールを削除するPostgreSQL SQL文である。

PostgreSQLでは、いわゆるユーザのことを「ロール」と呼ぶ。Oracleデータベースではユーザとロールは別のものであるが、PostgreSQLではそのような区別はない。

ロールを削除する。指定したロールが存在しない場合はエラーになる。

DROP ROLE name

ロールを削除する。指定したロールが存在しない場合でもエラーにならない。

DROP ROLE IF EXISTS name

GRANT

GRANTはアクセス権限を付与するPostgreSQL SQL文である。付与したアクセス権限はREVOKE文で剥奪できる。

テーブル、ビューおよびシーケンスに対するSELECTを許可する。SELECT FOR UPDATE文を実行するには、SELECT権限に加えてUPDATE権限も必要である。

GRANT SELECT ON object TO user

テーブルへのINSERTを許可する。

GRANT INSERT ON table TO user

テーブルに対するUPDATEを許可する。SELECT FOR UPDATE文を実行するには、SELECT権限に加えてUPDATE権限も必要である。

GRANT UPDATE ON table TO user

テーブルから行のDELETEを許可する。

GRANT DELETE ON table TO user

テーブル上のトリガの作成を許可する。

GRANT TRIGGER ON table TO user

外部キー制約を作成する際、キーを参照することを許可する。外部キー制約を持つテーブルを作成するためには、被参照キーを持つテーブルに対してこの権限が必要である。

GRANT REFERENCES ON table TO user

テーブルに対するすべての権限を許可する。

GRANT ALL PRIVILEGES ON table TO user

REVOKE

REVOKEはGRANT文によって付与されたアクセス権限を剥奪するSQL文である。

テーブル、ビューおよびシーケンスに対するSELECTを禁止する。

REVOKE SELECT ON object FROM user

テーブルへのINSERTを禁止する。

REVOKE INSERT ON table FROM user

テーブルに対するUPDATEを禁止する。

REVOKE UPDATE ON table FROM user

テーブルから行のDELETEを禁止する。

REVOKE DELETE ON table FROM user

テーブル上のトリガの作成を禁止する。

REVOKE TRIGGER ON table FROM user

外部キー制約を作成する際、キーを参照することを禁止する。

REVOKE REFERENCES ON table FROM user

テーブルに対するすべての権限を剥奪する。

REVOKE ALL PRIVILEGES ON table FROM user

SELECT

SELECTはデータベースに照会して、指定した条件に一致するデータを検索するSQL文である。

SELECT select_list FROM table_expression WHERE where_clause [LIMIT {number|ALL}] [OFFSET number]
[ORDER BY sort_expression [ASC|DESC] [NULLS {FIRST|LAST}]]
LIMIT

返す結果の最大行数をnumberで指定した行数に制限する。LIMIT ALLを指定した場合は制限しない。

OFFSET

返す結果のオフセットを指定する。

ASC

結果を昇順に並び替える。

DESC

結果を降順に並び替える。

NULLS FIRST

NULLを先頭にして並び替える。

NULLS LAST

NULLを末尾にして並び替える。

SET

SETは実行時パラメータを変更するSQL文である。

SET parameter TO value

search_pathには、スキーマ名を省略したときに検索するスキーマを指定する。スキーマ名はカンマで区切って複数指定できる。search_pathの設定は、同一セッション内でのみ有効である。

SET search_path TO schemas

SHOW

SHOWは実行時パラメータの値を表示するSQL文である。

SHOW parameter

search_pathは、スキーマ名を省略したときに検索するスキーマである。

SHOW search_path

UPDATE

UPDATEはレコードを更新するSQL文である。

UPDATE [ONLY] table SET column = {expression|DEFAULT} [, ...] [FROM fromlist] [WHERE condition]
ONLY

指定したテーブルのみを更新する。ONLYの指定を省略した場合、指定したテーブルとそのサブテーブルを更新する。

fromlist

WHERE句やSET句の式に他のテーブル上の列を指定できるようにするテーブル式の集合を指定する。

OracleデータベースのUPDATE文とは異なり、PostgreSQLのUPDATE文ではFROM句を指定できる。これにより、UPDATE文が簡潔に記述できる。

データ型

PostgreSQLにはSQLで使えるさまざまなデータ型が用意されている。PostgreSQLのデータ型を次の表に示す。

PostgreSQLのデータ型
分類 データ型 説明
数値 smallint 2バイトの整数
integer 4バイトの整数
bigint 8バイトの整数
decimal 数値
numeric
文字列 character varying(n) 可変長の文字列
varchar(n)
character(n) 固定長の文字列
char(n)

配列

PostgreSQLでは配列が使える。ただし、Oracleでは配列は使えないので、SQLに互換性は無くなる。

配列を使用したテーブルの作成

生徒ごとに1学期、2学期、3学期の試験の点数を格納するテーブルを作成する例を示す。

CREATE TABLE exam_score (student_no INTEGER, score INTEGER[3])

配列を条件にした検索

2学期の試験の点数が100で1ある生徒を検索する例を示す。

SELECT * from exam_score WHERE score[2] = 100

1学期から3学期までに試験の点数がひとつでも100である生徒を検索する例を示す。

SELECT * from exam_score WHERE score[1] = 100 OR score[2] = 100 OR socre[3] = 100

または次のようなSQLにすることもできる。

SELECT * from exam_score WHERE 100 = ANY (score)

ANYをSOMEにしてもよい。

SELECT * from exam_score WHERE 100 = SOME (score)

1学期から3学期までの試験の点数がすべて100である生徒を検索する例を示す。

SELECT * from exam_score WHERE score[1] = 100 AND score[2] = 100 AND socre[3] = 100

または次のようなSQLにすることもできる。

SELECT * from exam_score WHERE 100 = ALL (score)

配列の更新

配列のすべての要素を更新する例を示す。

UPDATE exam_score SET score = '{0,50,100}'

または次のようなSQLにすることもできる。

UPDATE exam_score SET score = ARRAY[0,50,100]

配列のうちひとつだけ更新することもできる。

UPDATE exam_score SET score[2] = 50

配列のうち一部だけ更新することもできる。

UPDATE exam_score SET score[1:2] = '{0,50}'

演算子

PostgreSQLにはSQLで使えるさまざまな演算子が用意されている。PostgreSQLの演算子を次の表に示す。

PostgreSQLの演算子一覧
分類 演算子 説明
論理演算子 AND 論理積
OR 論理和
NOT 否定
比較演算子 = 等しい
<> 等しくない
!=
< 小さい
<= 小さいか等しい
> 大きい
>= 大きいか等しい
@> 包含する
<@ 包含される
LIKE 文字列が指定したパターンに一致するかどうか。
SIMILAR TO 文字列が指定したパターンに一致するかどうか。正規表現も使用可能。
算術演算子 + 加算
- 減算
* 乗算
/ 除算(余りは切り捨て)
% 剰余(余り)
文字列演算子 || 文字列連結

@>

@>は包含するかどうかを返す演算子である。

array @> sub

arraysubが含まれていれば真、含まれていなければ偽と評価される。

ARRAY[1,2,3] @> ARRAY[1,2]

<@

<@は包含されるかどうかを返す演算子である。

sub <@ array

arraysubが含まれていれば真、含まれていなければ偽と評価される。

ARRAY[2,3] <@ ARRAY[1,2,3]

LIKE

LIKEは、文字列が指定したパターンと一致するかどうかを返す演算子である。OraleのLIKE演算子と同じ。

expr1 [NOT] LIKE expr2 [ESCAPE character]
NOT

文字列が指定したパターンに一致しなかった場合にtrueと評価される。

ESCAPE

エスケープ文字を指定する。

パターンには特殊な意味を持つ「メタ文字」を含めることができる。

メタ文字 説明
_ 任意の一文字
% 任意の文字の0回以上の繰り返し
SELECT * FROM emp WHERE ename LIKE 'Horiuchi%'

SIMILAR TO

SIMILAR TOは、文字列が指定したパターンと一致するかどうかを返す演算子である。正規表現も一部使用できる。ただし、PostgreSQLにおいてはピリオド(.)はメタ文字ではない。

expr1 [NOT] SIMILAR TO expr2 [ESCAPE character]
NOT

文字列が指定したパターンに一致しなかった場合にtrueと評価される。

ESCAPE

エスケープ文字を指定する。

パターンには特殊な意味を持つ「メタ文字」を含めることができる。

メタ文字 説明
| 論理和(OR)
* 直前の0回以上の繰り返し
+ 直前の1回以上の繰り返し
? 直前の0回または1回の繰り返し
{m} 直前のm回の繰り返し
{m,} 直前のm回以上の繰り返し
{m,n} 直前のm回以上かつm回以下の繰り返し
() グループ化
[...] POSIX正規表現と同様な文字クラス

関数

PostgreSQLにはSQLで使えるさまざまな関数が用意されている。PostgreSQLの関数を次の表に示す。

PostgreSQLの関数一覧
関数 説明
CURRENT_TIMESTAMP 現在の日付と時刻を取得する。
NOW 現在の日付と時刻を取得する。トランザクション内では常に同じ値を返す。

CURRENT_TIMESTAMP

CURRENT_TIMESTAMPは現在の日付と時刻を取得する関数である。OracleSYSDATEに相当する。なお、PostgreSQLにSYSDATEという関数は無い。

SELECT CURRENT_TIMESTAMP()

NOW

NOWは現在の日付と時刻を取得する関数である。ただし、トランザクション内では常に同じ値を返す。トランザクション内で同じ日付と時刻を使いたい場合に便利である。

SELECT NOW()

カタログとビュー

カタログ
カタログ 説明
pg_shadow データベースユーザに関するカタログ
ビュー
ビュー 説明
pg_user データベースユーザに関するビュー

pg_user

pg_userはデータベースユーザに関するビューであり、元表のpg_shadowからパスワードに関する情報を取り除いたものである。

関連記事

SEO [PR] 爆速!無料ブログ 無料ホームページ開設 無料ライブ放送