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 で上書きしないように。必要部分だけ書き足すようにしよう。
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 でパスワード設定プロンプトも出させる。
root# service postgresql start root# service postgresql stop
これで、必要なファイルが一通り、$PGDATA下にコピーされる。
設定でさわるのは $PGDATA ディレクトリ下の3つのファイル。 日本語マニュアル「管理者ガイド」参照。
postgresql.conf | グローバルな動作設定 |
pg_hba.conf | クライアントホスト認証の設定 |
pg_ident.conf | pg_hba.conf で ident 認証を使う場合に、ユーザとシステムアカウントとの結びつけを指定する補助的設定ファイル |
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値を検索でヒットさせるには」も参照のこと |
/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 を見よ。
設定例 - ローカルソケットのみの場合:
#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_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所有の空のDB "postgres" を作る。
user$ su - postgres postgres$ createdb -E EUC_JP postgres
パスワードは initdb の際に設定してあるはずだが、何かの都合でここでやりたければ、続いて:
postgres$ psql postgres=# ALTER USER postgres PASSWORD 'password';
postgres$ psql postgres=# CREATE USER foo WITH PASSWORD 'password' CREATEDB;
上記は 「ユーザfoo を、パスワードpassword 、データベース作成権限有りで作成せよ」 の意。 postgresql.conf で password_encryption = true にしてあるので、 "ENCRYPTED PASSWORD" と入力しなくてもパスワードは暗号化される。 NOCREATEUSER (ユーザ作成権限を与えない) はデフォルト値なので省略できる。
postgres$ createuser -P foo
-P はパスワード設定のためのプロンプトを表示せよの意。 createuser
は対話式なので、
「他のユーザを作れる権限を与えるか」 のプロンプトにはNo、 「データベースを作る権限」 には Yes
を。
postgres$ psql postgres=# CREATE DATABASE sample OWNER=foo ENCODING='EUC_JP';
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 <-- 正常に削除されたかの確認
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コマンドをリストアップ。コマンドを指定するとその説明 |
\? | メタコマンドのヘルプ |