技術的雑談-PostgreSQLの自動運転

とりあえずWindows2000で動作確認。
PostgreSQL8.0.1

目的

TOP ↑ ↓

PostgreSQLに対して、バッチでSQLの発行をしたい。

方法

TOP ↑ ↓

PostgreSQL付属のコマンドライン用コマンドのpsqlを使います。

psqlって何？

TOP ↑ ↓

psql(.exe)はPostgreSQL標準添付のコマンドラインコマンドです。
PostgreSQL DBに接続してSQLやその他の制御コマンドを投入できます。

psqlでのDatabase自動ログイン方法

TOP ↑ ↓

まず、psqlをバッチ上で自動ログイン可能な状態にしないとなりません。

psqlを使おうとしているUnix(Windows)ユーザのホームディレクトリ(Windowsの場合は、そのユーザの${APPDATA}ディレクトリ。普通はC:\Documents and Settings\ユーザ名\Application Data\)に.pgpass(Windowsの時はpgpass.conf)というファイルを置き、

DBホスト名:ポート番号:DB名:DBのユーザ名:パスワード

(*とかのワイルドカードも指定可能です)

例)
localhost:5432:template1:postgres:password

という行を書きます。

次に、psqlにパスを通して、

psql.exe -U postgres -h localhost -q -t -d template1 -c'select now();'

で、日付が出力されるのを確認します。

例)
C:\Program Files\PostgreSQL\8.0\bin>psql.exe -U postgres -d template1 -h localhost -q -t -c"select now();"
 2005-05-25 14:50:16.166+09

psqlで指定するパラメータは先ほどの.pgpassファイルの内容とあわせてください。
ホスト名はIPアドレスが同一なのではなく、文字列として同一でないとダメです。

-U -- ユーザ名
-d -- DB名
-h -- DBサーバ名
-q -- 余計なものを出力しない
-t -- SELECT文で結果だけを出力する。(列名などを表示しない)

.pgpassでの設定内容と、psqlでのパラメータが完全に一致しないと対話形式でパスワードなどを聞いてきてしまいます。
psqlのオプションでパスワードを指定する方法は「ない」のでご注意ください。これは仕様です。

死活監視

TOP ↑ ↓

これの具体的な活用方法として、DBエンジンの死活監視などに使えます。
バッチで↓のようなコマンドを叩きます。

psql.exe -U postgres -d localhost -q -t -d template1 < test1.txt > result.txt

text1.txtにはpsqlに与えるSQLをセットしておきます。

select now();
\q　　　　　　　　←psqlを終わらせる為に必要です。

で、result.txtの内容を評価します。

DBが死んでいる時はpsqlが標準エラーに文句を言いますので、標準出力には何も出て来ません。(＝ファイルサイズが0)
DBが生きている時にはresult.txtに現在の日付が入っています。

評価方法は……grepとかperlとかで日付が入っていることを確認しましょう。(すいません、DOSバッチではこれ以上はちょっと面倒なので、テキストエディターで人力判断しました。)

恐らくUnixでもほぼ同じ方法で使えます。shell scriptのほうが強力なので多分大丈夫でしょう。

死活監視以外に、定期的なレコード削除(特定の条件のレコードを削除するSQLを実行)とか、日付の更新(監視テーブルなどに更新をかける)などに使えるかと思います。

おまけ

TOP ↑ ↓

psqlのHELPを引用しておきます。

これは psql 8.0.3 と呼ばれる PostgreSQL の会話型ターミナルです

使用法:
  psql [オプション]... [DBNAME [USERNAME]]

一般的なオプション:
  -d DBNAME       接続するデータベース名を指定する(デフォルト: "sakamoto")
  -c COMMAND      1つの(SQLまたは内部)コマンドを実行して終了
  -f FILENAME     ファイルからのコマンドを実行して終了
  -l              使用できるデータベース一覧を表示して終了
  -v NAME=VALUE   psql 変数 'NAME' を 'VALUE' にセット
  -X              初期化ファイル (~/.psqlrc) を読みこまない
  --help          このヘルプを表示して終了
  --version       バージョン情報を表示して終了

入出力オプション:
  -a              スクリプトからのすべての入力を表示する
  -e              サーバへ送信したコマンドを表示する
  -E              内部コマンドが生成したクエリーを表示する
  -q              静かに実行(メッセージなしで、クエリー出力のみ)
  -o FILENAME     クエリーの結果をファイル(または |パイプ)に送る
  -n              拡張コマンドライン編集機能(readline)を無効にする
  -s              シングルステプモード(各クエリーごとに確認)
  -S              単一行モード(行末を SQL コマンドの終了とする

出力フォーマット・オプション:
  -A              桁揃えなしのテーブル出力モード (-P format=unaligned)
  -H              HTML テーブル出力モード (-P format=html)
  -t              行のみを表示 (-P tuples_only)
  -T TEXT         HTMLテーブルのタグ属性をセット(幅, 境界線) (-P tableattr=)
  -x              拡張テーブル出力を有効にする (-P expanded)
  -P VAR[=ARG]    表示オプション 'VAR' を 'ARG' にする (see \pset command)
  -F STRING       フィールド区切り文字(デフォルト: "|") (-P fieldsep=)
  -R STRING       レコード区切り文字(デフォルト：newline) (-P recordsep=)

接続オプション:
  -h HOSTNAME     データベースサーバのホストまたはソケット (default: "ローカルソケット")
  -p PORT         データベースサーバのポート番号(デフォルト: "5432")
  -U NAME         データベースのユーザ名を指定(デフォルト: "sakamoto")
  -W              パスワード入力を促す(自動的に行われるべき)

詳細は psql の中で "\?" (内部コマンドの場合) または "\help"
(SQL コマンドの場合) をタイプするか、PostgreSQL ドキュメントの psql の
セクションを参照のこと。

バグレポートは <pgsql-bugs@postgresql.org>まで。
<Enter>