 |
 |
|
 |
|
|
|---|---|---|
| このサイトは、もともと作者の自分用メモとして書き始めたものです。書いてあることが全て正しいとは限りません。他の文献、オフィシャルなサイトも確認して、自己責任にて利用してください。 | ||
|
|
|
|
|
|
|---|---|---|
| このサイトは、もともと作者の自分用メモとして書き始めたものです。書いてあることが全て正しいとは限りません。他の文献、オフィシャルなサイトも確認して、自己責任にて利用してください。 | ||
PostgreSQL 管理者用ガイド - 定常的なデータベース保守作業 に書いてあるが、VACUUM は、データの更新や削除で不要になったブロックを開放したり、トランザクション ID (XID) の回り込みを防いだりしてくれる。後者は非常に重要で、もしもトランザクション ID の周回が起こると、 PostgreSQL は (非常に簡単に言うと) 過去に行われた更新とこれから行われようとしてる更新との区別がつかなくなり、データが消失してしまう。周回の起こらない限界は 20 億トランザクションだが、安全のためには最低でも 500 万トランザクションに達する前に VACUUM を行わなければならない。
VACUUM の種類には FULL というものもあるが、これはテーブルのデータをまるっきり入れ替えるような更新を行った場合にだけ必要なようで、また、入れ替えの際に TRUNCATE を使った場合には必要ない。 VACUUM ANALYZE はリレーションの分析も行うので、パフォーマンス面でも効果がある。よって、通常の運用データベースには VACUUM ANALYZE を 1 日 1 回程度行い、その他のシステムカタログも含めた全てのリレーションに対してはもう少し緩い頻度で (例えば 1 週間に 1 回) VACUUM を行えばいいだろう。後者は DB スーパーユーザ postgres でなくては遂行できない - いちいち個別のリレーションをループで回すのはあまりにも手間なので、 SQL コマンドの VACUUM よりも PostgreSQL クライアントアプリケーション vacuumdb を使ったほうがいい。例えば cron で定期化するなら、 UNIX ユーザ postgres で `crontab -e' して以下のようなジョブを登録する；
05 4 * * sat vacuumdb -a -q
上記は毎週土曜の早朝 4 時 5 分にシステムカタログも含めた全データベースの全リレーションを VACUUM し、エラー以外のメッセージは出力しない。あるいは、結果を特定のログに必ず記録したい場合には以下のようにする手も考えられる。
保守ログを /var/log/dbdaily に記録したいとしよう。ログは syslog を通すのが安全なので、まず下準備として /etc/syslog.conf にこのような行を追加する；
local3.* /var/log/dbdaily
そして postgres の crontab は下のような塩梅だ。下記のものは、エラーメッセージのみをログしている。 `1>/dev/null' は vacuumdb があまりにも多弁すぎるので入れているが、好みで削って頂いても構わない。 (HTML表示上の都合で 2 行に分けている)
05 4 * * sat vacuumdb -a 2>&1 1>/dev/null | \ /usr/bin/logger -p local3.info -t cron
処理の結果を php でクライアントにダウンロードさせたり、INSERT 処理を一気に行うために中間データを準備する時などに便利。PostgreSQL (7.4.2の時点) では、一時テーブルは他のセッション (たとえ同じユーザで同じデータベースに接続しても) から内容を見たり変更を加えることはできない (つまり常に LOCAL)。セッションが終わればサーバ上から削除される。
CREATE LOCAL TEMP TABLE mytemp1 AS some_query;
※LOCAL は PostgreSQL においては無意味だが、SQL92規格では "GLOBAL" もあるため、他のデータベースとの互換性を考慮し、LOCAL を入れた方がよい、とされている。 (PostgreSQLの仕様も将来変わるかもしれないし)
システムカタログに問い合わせを行えば、一時テーブルも存在だけは確認できる (明示しないテーブル結合を使ってみた)。「一意な名前」には、php から SQL を使う場合、PHPセッションIDを使うと重複が無く、トレースにも役立つ。下記がそのクエリ例。 tmpname は一時テーブルの名前、 foo は一時テーブルを作成したユーザ名：
SELECT relname, usename FROM pg_class, pg_user WHERE relkind = 'r' AND relname LIKE '%tmpname%' AND usename = 'foo';
pg_class システムテーブルの relkind カラムは、オブジェクトの種別を記録している。"r" はリレーション (=通常のテーブル)。ちなみに、
SELECT * FROM pg_temp_x.mytemp1 ; (x はPID、mytemp1 は一時テーブルの名前)
と上位スキーマから指定すれば、参照は試みられる (権限がないといって怒られるが.....)。
※ システムカタログについては PostgreSQL
7.3.x 開発者ガイド
或るカラムの値が入力されていない レコードを検索するには、"=" ではヒットしない。"IS NULL" を使う。
※ postgresql.conf の設定で "=NULL" も使えるようにできるが、正規の記述法ではないのであまりお勧めしない。
例：
SELECT fl_name, fl_kana FROM tfiles
WHERE img_width IS NULL ORDER BY fl_name;
ファイルアップローダ php で使ったクエリ。画像か非画像ファイルかを判定。画像でないファイルの場合は「横幅」の値はカラであることを利用して、カラム数を節約。
serial 型カラム v_id を持つテーブル "sample" があると仮定し、そのシーケンス名は sample_v_id_seq だったとする。 1. と 3. は行わなくても操作は可能だが、その場合には何か他の手段でデータベースの更新を禁止する方策が必要だ。；
UPDATE pg_class SET reltriggers = 0 WHERE relname = 'sample';
SELECT setval ('sample_v_id_seq', 15, true);
同じシーケンスを他のテーブルや他のカラムで使っていないことが確かなら、こういう手もある：
SELECT setval ('sample_v_id_seq', max(v_id), true) FROM sample;
トリッキーな文法だ。噛み砕くと、下記SQLと同義：
SELECT setval ('sample_v_id_seq',
(SELECT max(v_id) FROM sample), true);
シーケンスの最小値は 0 でなく 1 だということに注意。 setval() 関数の3つ目のパラメータは、このシーケンスが次回初めて呼ばれたときに、シーケンスを進めるかどうか。上記の例で、true でセットした場合は次回の生成値は 16、falseでは 15 となる (それ以降の動作には関係ない)。
UPDATE pg_class reltriggers = (
SELECT count(*) FROM pg_trigger, pg_class
WHERE pg_class.oid = tgrelid
) WHERE relname = 'sample';
※ count( ) 関数については 集約関数 を参照のこと。 実地の例を見る手っ取り早い手段は、シリアルから無を持つデータベースのデータのみのダンプ (-a オプション) をプレーンテキストで採って、覗いてみること。
シーケンスオブジェクトは、１行のみから成る一種のテーブルなので
SELECT last_value FROM sample_v_id_seq;
※詳細は PostgreSQL 7.3.x ユーザガイド または リファレンスマニュアル
通常、自動コミットがONになっているので、ひとつの SQL文 ( "；" まで) がひとつのトランザクション単位であり、"；" 毎に変更がコミット (実行) される。いくつかの文から成る処理が途中でキャンセルやエラーで中断されるとデータに矛盾が発生してしまうような場合、それらの処理をひとつのトランザクションにまとめる必要がある。
トランザクションSQLコマンド：
| コマンド | 意味 |
|---|---|
| BEGIN; | トランザクションの開始 |
| ROLLBACK; | トランザクション中での変更をすべて取り消し、トランザクションを完結する |
| ABORT; | 同上。PostgreSQL での”方言”。使うな！ |
| COMMIT; | 変更を適用して、トランザクションを完結する |
| END; | 同上。PostgreSQL での”方言”。使うな！ |
自トランザクション内で命令した変更は、まだコミットされていなくても参照できる。
トランザクションの最中に全く別のトランザクションがデータを変更してしまった場合の振る舞いが異なる。PostgreSQL には２つの隔離レベルがある (しかない)。 PostgreSQL 8.x からは "READ UNCOMMITTED" や "REPEATABLE READ" を指定してもエラーにはならないが、実際には、前者の時は "READ COMMITTED"、後者では "SERIALIZABLE" として動作する。
隔離レベルを変更するにはトランザクションブロックの中で：
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
PostgreSQL のデフォルト隔離レベルは "READ COMMITTED"。上記コマンドは、そのトランザクションが完結すれば無効化しデフォルトに戻る。デフォルトの隔離レベルを変えるには、 postgresql.conf で下記のように宣言：
default_transaction_isolation = 'serializable'
データベースのダンプを書き出すシェルスクリプトを用意し、cron を利用して定期的にそれを実行する。データベースがどの PostgreSQL ユーザの所有であってもダンプできるよう、この cron ジョブは postgres 権限で実行されるようにする。
下記のシェルスクリプト "pgbackup" は、複数のデータベースを順繰りに pg_dump 処理して、ファイル "/home/backup/pgdata/データベース名.dump" にそれぞれ書き出す。その際、ダンプ先と同じディレクトリにある (無ければできる) ログファイル pgbackup.log にログも出力する。その内容は、処理日時と、すべて成功したら「正しくバックアップした」旨、失敗したデータベースがあればそのデータベース名となる。また、リストアの際にデータベースユーザや入れ物となるデータベースを作っておかなくてはならなくて非常に面倒だったので、 pg_dumpall -s で、ユーザ情報と、各データベースのフレームワークを pgframe.dump にダンプするようにした。
なお、書き出し先はシステムユーザ postgres (またはグループ) が書き込みできるパーミションになっていなければならないことに注意。ひとつの方策としては、様々なバックアップを一手に引き受けるシステムユーザをひとつ作り、それをサブグループとして postgres グループにも所属させておく手が考えられる。そして、そのバックアップユーザのホームディレクトリ下をダンプ先にすればいいわけだ。
#!/bin/bash
#
# This sample is a pgdatabase backup script
#
PROG=pgbackup
BASEDIR="/home/backup"
DIST="$BASEDIR/pgdata"
# You can put multiple databases after 'set' here.
# They will be set into positional variables and
# var 'I' in the for loop will be replaced with
# each database name one by one.
set database1 database2 database3
echo -n "#### " >>$DIST/$PROG.log
date >>$DIST/$PROG.log
for I ; do
pg_dump -c $I > $DIST/${I}.dump
if [ "$?" != "0" ]; then
W=1
echo "Could not backup $I" >>$DIST/$PROG.log
fi
done
pg_dumpall -s > $DIST/pgframe.dump
if [ "$?" != "0" ]; then
W=1
echo "Could not backup framework" >>$DIST/$PROG.log
fi
[ "$W" = "1" ] || echo "All DBs backed up properly" >>$DIST/$PROG.log
exit 0
このシェルスクリプトをディレクトリ /etc/cron.pg (名前は任意だが、パーミションは例えば root.root 755 などとし、他者に書き込み権限は与えてはいけない) に、やはり同様のパーミションで置く。加えて /etc/crontab に以下の行を記述する。ここでは、毎日午前 3 時に実行されるようにしている：
00 3 * * * postgres run-parts /etc/cron.pg
postgres エクステンションを組み込んだ php モジュールを Apache にロードして WEB アプリケーションから PostgreSQL サーバを利用する際に、サーバのログにこうしたエラーが記録されることがある。これは、クライアント (フロントエンド) 側のモジュールが PostgreSQL 7.4 以降の新しいもので、サーバの PostgreSQL が 7.3 以前である時に起こる。 7.3.x のフロントエンド/バックエンドプロトコルは Ver. 2.0 だったが、 7.4.0 からは Ver. 3.0 が新たに導入されたので、クライアントのしゃべる新しい言葉をサーバが理解できないという問題が起こっているわけだ。ただし、フロントエンドモジュールがプロトコルバージョンを自動的に引き下げてリトライしている様子なので、動作自体に支障を来さなければ、エラーメッセージは無視して構わないようだ。モジュールに何らかのスイッチが用意されて php.ini や ini_set() でプロトコルバージョンをコントロールできれば良いのだが、 php4 用の pgsql モジュールには今のところ見あたらない。当然 JDBC でも同じような現象が起こるらしい。根本的にはサーバの PostgreSQL をバージョンアップするしかなさそうだ。
ユーザやデータベースの構築中に、にっちもさっちも行かなくなって、クリーンな状態から全てを作り直したくなることがたまにある。PostgreSQL をバージョンアップしたはいいが、「データベースの形式が古い」というエラーを吐いてサービスが立ち上がらない場合もそのひとつだ。そういった場合の手順を簡単に説明する。
バックエンドの障害や停止が起こっていないか監視する手段はいろいろあるだろうが、筆者は MONIT を使うのが好きだ。ただし MONIT には (Ver.4.7 時点) PostgreSQL プロトコルでのポートレスポンステスト機能がなかった。汎用のコネクションテストでもポートが生きているかは一応確認可能だが、ログが `incomplete startup packet' という警告で汚れる。
そこで、独自に PostgreSQL 用プロトコルテストを書いた （MONIT の専用ページにある） が、小生のコードが MONIT 4.8 で正式に搭載されたので、パッチを当てることなく PostgreSQL が監視できるようになった。