PostgreSQL  
  HOME Next
オフィシャル日本語ドキュメント
http://www.postgresql.jp/document/

PostgreSQL

表記について

Table of Contents

インストール

rpm でまったく問題なし。

追加手順

rpm インストールならば、システムアカウント "postgres" は既にできている。ログオン可能にするため

roor# passwd postgres

でパスワードを設定。なお、ターミナルプロンプトや cp, mv の上書きプロンプトの出る/出ないといった挙動が他のユーザと違う場合は、postgres のホームディレクトリである /var/lib/pgsql にシェル環境設定ファイル類がコピーされていないのかもしれない。その場合は root になってから /etc/skel ディレクトリ (ユーザ環境設定ファイルのテンプレートディレクトリ) から .bashrc をコピーし、しかるべきパーミションに設定すれば解決するだろう。ただし注意、Fedora Core 3 標準の PostgreSQL 7.4.x では、postgres 専用の .bash_profile が既に存在し、重要な役割を持っているので、skel の .bash_profile で上書きしないように。必要部分だけ書き足すようにしよう。

環境変数に以下の変数を追加 (/etc/profileを編集)

POSTGRES_HOME="/var/lib/pgsql"
PGDATA="/var/lib/pgsql/data"
export POSTGRES_HOME PGDATA

RedHat 系の rpm でインストールを行った場合には、上記作業は rc スクリプト /etc/rc.d/init.d/postgres の中に組み込まれているので必要ない。また、ものの本によると以下も必要とあるが、RedHat では特に必要はなかった。

PG_LIB="/usr/lib/pgsql"
LD_LIBRARY_PATH="$LD_LIBRARY_PATH:$PG_LIB"
export PG_LIB LD_LIBRARY_PATH

データベースの初期化、テンプレートの作成

user$ su - postgres
postgres$ initdb -W -E EUC_JP --no-locale

-E オプションでテンプレートデータベースのエンコードをEUC_JPにする。
日本語環境の場合はデータベースのロケールはかえって有害。 --no-locale でロケールを C のままにする。
本稿ではデータベースのスーパーユーザ postgres にはパスワードを設定したいので -W でパスワード設定プロンプトも出させる。

初期ファイルを作らせるためPostgreSQLサービスをスタート/ストップ

root# service postgresql start
root# service postgresql stop

これで、必要なファイルが一通り、$PGDATA下にコピーされる。

設定

設定でさわるのは $PGDATA ディレクトリ下の３つのファイル。　日本語マニュアル「管理者ガイド」参照。

postgresql.conf グローバルな動作設定
pg_hba.conf クライアントホスト認証の設定
pg_ident.conf pg_hba.conf で ident 認証を使う場合に、ユーザとシステムアカウントとの結びつけを指定する補助的設定ファイル

postgresql.conf 最低限の設定項目

tcpip_socket = true|false TCP/IP 経由接続を受け入れるか。ローカルUNIXソケットのみで済むなら false にすべき。WEBアプリケーションから PostgreSQL に接続する場合でも、WEBサーバと PostgreSQL サーバが1台のホストに同居しているなら要らない
port = 5432 TCP/IP 接続を受け入れる場合の通信ポート。デフォルトでは 5432 (TCP)
max_connections = 32 最大接続数。デフォルトは 32 (7.4.x では 100 ?)
syslog = 2 ログの出力先。 0 は stdout のみ、 1 は stdout と syslog の両方、 2 は syslog のみ
syslog_facility = 'LOCAL0' シスログファシリティ。 PostgreSQL のログをシステムログ /var/log/messagesでないファイルに記録したい場合に指定。呼応して、 /etc/syslog.conf も編集が必要 (※)。
syslog_ident = 'postgres' シスログに渡すときの識別文字。あったほうがログが見分けやすい
log_timestamp = false ログ出力時に発生日時を添えるか。 syslog にのみ渡す場合には syslog が日時を添えるので無用
password_encryption = true 'CREATE USER' や 'ALTER USER' でパスワードを設定する際、パスワードを暗号化して保存するか、暗号化しないかのデフォルトを決定する
LC_MESSAGES = 'C'
LC_MONETARY = 'C'
LC_NUMERIC = 'C'
LC_TIME = 'C'		 データベースのデータ内に日本語を使う場合は、すべてのロケール変数は 'C' に
add_missing_from = false [Ver. 7.4 以降] クエリの中で参照されるテーブルが FROM 句から欠けていた場合に、自動的にそのテーブル名を補うか。 SQL 標準には無い挙動なので、できれば false に
regex_flavor = advanced [Ver. 7.4 以降] 正規表現の拡張レベル。 advanced, extended, basic がある。 extended なら PostgreSQL-7.3.x とほぼ完全互換。 7.4.x から実装された advanced 正規表現 (ARE) は Perl のものに酷似しており、 前方先読参照 "(?= )" や、結果に格納しないグループ参照 "(?:)"、多数のエスケープ特殊文字 \b, \n, \t などが使用できるようになった。詳細は PostgreSQL 7.4.6 (パターンマッチング)
sql_inheritance = true クエリなどの中で、そのテーブルが継承する子テーブルのレコードも参照するか。つまり、false にすると、クエリで常に ONLY キーワードを使っているのと同じ状態となる。 PostgreSQL 7.1 以前から使用しているプログラムを移植して使いたい場合には false にしなければならないケースがあるだろう
transform_null_equals = false NULL値 (データが空) のレコードを検索するのに "=NULL" も使えるようにするか。本来は "IS NULL" であり、こうした挙動は正規ではないので false を推奨するが、 Microsoft Access は "=NULL" を発行する間違った実装をしているので、 Access から PosstgreSQL データベースを参照したい場合には true にする必要がある。 「NULL値を検索でヒットさせるには」も参照のこと
※PostgreSQLのログはすべて/var/log/pgsql 記録したい場合：

/etc/syslog.conf に以下の行を追加。

local0.*    /var/log/pgsql

syslog.conf で、 local0 ファシリティが既に他のサービスに使われていたら、別のファシリティ (local1 - 6) を指定しなくてはならない。 local7 は、RedHat系では通常、ブートログに使用されているので使えない。

また、これだけでは /var/log/messages などのメインログファイルにも PostgreSQL のログが (一部) 行ってしまうので、 syslog.conf に下記のような記述があったら赤字の記述を加える。そうすれば、PostgreSQL のログは目的のファイルにのみ記録されるようになる：

*.info;mail.none;authpriv.none;cron.none;\
 local0.none        /var/log/messages

syslog の詳細は、 man 5 syslog.conf するか、JMプロジェクト syslog を見よ。

pg_hba.conf

設定例 - ローカルソケットのみの場合：

#TYPE     DB      USER        IP        MASK       METHOD
local     all     postgres                       ident sameuser
local     all     all                            md5

ローカルでのUNIXソケット接続のみ使用している場合は、これだけ。

メソッドの意味はこう：

ident sameuser システムにログオンできていれば、同名のDBユーザとしてDBアクセス許可
md5 ユーザ名とパスワードを求める。パスワードのやりとりはMD5暗号化される

まとめると：
スーパーユーザpostgresには、システムに postgres でログオン (su 含む) できていない限り成れない。
その他全ての PostgreSQL ユーザに成るには、当該ユーザのパスワードを入力する必要がある。例えば apache の実行ユーザ "apache" も、正しいユーザ名とパスワードを提示すれば、任意のデータベースにアクセスできる。

難点：
ファイルへダンプしたテーブル情報を、リストアする場合、通常はデータベースの所有者 (実用PostgreSQLユーザ) 権限でリストアを行うが、データベースの構造によっては、システムカタログに関わる部分が含まれるため、リストアの途中で postgres のパスワードを求められることがある。ところが、上記の設定ではシステムユーザ postgres 以外は決して postgres になれないので、そこでエラーになってしまう。少し面倒だが、リストアを行う際のみ、pg_hba.conf の "postgres" に関する行をコメントアウトする必要がある。

別のマシンからも利用する場合には TCP/IP 接続も許可する必要がある。その際の設定例も挙げておく。

設定例 2 - TCP/IP 接続も受け入れる場合：

#TYPE     DB      USER        IP        MASK       METHOD
local     all     postgres                       ident sameuser
local     all     all                            md5
host      all     all   192.168.0.0 255.255.255.0  md5
host      all     all   192.168.1.0/24           md5

IP のフィールドは数字のみ有効で、ホスト名などでは不可。最後の行の "IP/Mask" 表記は PostgreSQL 7.4 以降でのみ有効。7.4 以降では TYPE フィールドに hostssl (SSL over TCP のみが対象), hostnossl (SSL 接続でないホストのみが対象) も指定でき、 host の場合はもちろん両方が対象となる。

pg_ident.conf

pg_hba.conf を前記のように設定した場合はこのファイルは設定不要。
だが、仮に 「hoshu と another はデータベース sample 接続時に foo に成ることができる」 としたい場合：

#MAPNAME    SYSUSER    PGUSER
adminmap    hoshu      foo
adminmap    another    foo

のようにマッピングを設定する。と同時に pg_hba.conf を：

local   all     postgres         ident sameuser
local   sample  all              ident adminmap
local   all     all              md5

とすれば、

hoshu$ psql -U foo sample

がパスワード無しで可能となる。
※PostgreSQL 7.1.x までは、"ident" はTCP/IP経由の接続でしか使えなかった。

ユーザとデータベースの作成

方針：

あとあとの便宜のため、ユーザpostgres の環境を整える

postgres所有の空のDB "postgres" を作る。

user$ su - postgres
postgres$ createdb -E EUC_JP postgres

パスワードは initdb の際に設定してあるはずだが、何かの都合でここでやりたければ、続いて：

postgres$ psql
postgres=# ALTER USER postgres PASSWORD 'password';

実用ユーザの作成

psql で行う場合：

postgres$ psql
postgres=# CREATE USER foo WITH PASSWORD 'password' CREATEDB;

上記は 「ユーザfoo を、パスワードpassword 、データベース作成権限有りで作成せよ」 の意。 postgresql.conf で password_encryption = true にしてあるので、 "ENCRYPTED PASSWORD" と入力しなくてもパスワードは暗号化される。 NOCREATEUSER (ユーザ作成権限を与えない) はデフォルト値なので省略できる。

createuser コマンドで行う場合：

postgres$ createuser -P foo

-P はパスワード設定のためのプロンプトを表示せよの意。 createuser は対話式なので、
「他のユーザを作れる権限を与えるか」 のプロンプトにはNo、 「データベースを作る権限」 には Yes を。

実用ユーザ所有のデータベースを作成

psql で行う場合：

postgres$ psql
postgres=# CREATE DATABASE sample OWNER=foo ENCODING='EUC_JP';

createdb コマンドで行う場合：

postgres$ createdb -E EUC_JP -O 'foo' sample

※PostgreSQL 7.1.x までは、テーブルの所有者を指定するオプションは -U だった。

手続き言語の組み込み

手続き言語 (Procedural Language) により、SQLコマンドのみではクエリを何度もしなければならなかったり、そもそもSQLコマンドでは実現できないようなカスタム関数を定義できる。クライアントからSQLコマンドで渡された値を、変換／計算／整形してからSQLサーバに渡すことが可能となる。
PostgreSQL には、手続き言語 PL/pgSQL が付属しているが、使用するには、データベース毎に「組み込み」を行う必要がある。

※ テンプレートデータベース "template1" に組み込んでおけば、以後新たに作成するすべてのデータベースは組み込み済みで作られるが、あまりおすすめしない。PL を使いもしないデータベースにまで組み込むことになりメモリの無駄だ。

組み込みには、postgres 権限が必要。createlang コマンドは内部で psql コマンドを数回発行するので、パスワードプロンプトも数回出る。すべて、postgres のパスワードを入力する。

user$ su - postgres
postgres$ createlang plpgsql sample   <--sample は対象とするデータベース名
postgres$ createlang -l sample        <-- 正常に組み込まれたかの確認

または、postgres に su せずに

user$ createlang -U postgres plpgsql sample
user$ createlang -U postgres -l sample      <--確認。確認は一般権限でもできる

ちなみに削除は：

user$ su - postgres
postgres$ droplang plpgsql sample    <--sampleは対象とするデータベース名
postgres$ createlang -l sample       <-- 正常に削除されたかの確認

psqlターミナルの基本

ユーザfoo としてデータベース sample に接続

user$ psql -U foo sample

データベース名を省略すると、ユーザと同じ名称のデータベースへの接続が試みられる。

メタコマンド (抜粋)

SQLクエリではコマンドに大文字小文字の区別は無い (変数は別) が、メタコマンド (\で始まる) では区別がある。

\cd dir (実ファイルシステム上の) dir に移動
\!pwd カレントディレクトリを表示。実は、\! はコマンドをUNIXシェルに渡す意味があり、pwd コマンドがシェルで実行されているだけ
\c(onnect) db user 現在の接続を中止して、データベースdb に接続し直す。user を省略すると、現在のユーザで接続する
\d pattern pattern に一致する名前のテーブルの概要を表示。pattern の表記法はUNIXシェルの正規表現と同じ。POSIXやPCREとの大きな違いは、任意の1文字がドットでなく"?"、 * は「0回以上の繰り返し」でなく「任意の文字の連続」、ドットはスキーマとテーブルの区切り文字であること。pattern を省略すると、現在接続しているデータベース内に存在するテーブルをリストアップする
\dd pattern 可視なオブジェクトをすべてリストアップ。pattern を指定すると一致するオブジェクトの説明を表示
\dp pattern \d と同様だが、各テーブルのアクセス権も表示される
\du pattern すべての、またはpattern に一致するデータベースユーザをリストアップ
\i filename filename ファイルの内容を読み取り、SQLコマンドとして実行。pg_dump の結果からリストアを行うときに便利
\l(+) サーバ上のすべてのデータベースをリストアップ。+ を付けると説明も表示
\z \dp のエイリアス
\set var value psqlターミナル内の環境変数 var を value に設定／変更する。シェルの set と同じく、引数を省略すると、セットされている変数をリストアップする
\q psqlターミナルの終了
\h SQLcommand 使用できるSQLコマンドをリストアップ。コマンドを指定するとその説明
\? メタコマンドのヘルプ
  TOP Next
  HOME データベースのダンプ