postgresのSQLログから、phpが実行したSQLを抜き出す。というツール(バッチ実行)が必要になった。
postgresのSQLログから、phpが実行したSQLを抜き出す。というツール(バッチ実行)が必要になった。Googleったが見つからないので作った。とりあえずうまく動きそう。
phpで『$pid = pg_get_pid($conn);』をデバックログに吐き出して、そのPIDでpostgresのSQLログを抽出しようというアイデアなのだけど、うまくいくかどうかは休み明け。
1.postgresのSQLログの指定したPID番号のみを抽出するツール。sqlが改行している場合でもOK
プログラム名=pstgrSqlPid.php <−chmod 777する。
#!/usr/local/php/bin/php <?php if ($argc != 3 ) { ?> This is a command line PHP script with 2 option. Usage: <?php echo $argv[0]; ?> <option> <option1> postgres sqlLog file name. <option2> pid. <?php exit; } $fp = fopen( $argv[1], "r") or die("$argv[1] couldn't open."); // file open $flg = false; // continue pid data line flg off while( $line = fgets($fp) ) { $str = stristr($line, " PID:$argv[2] " ); // find pid if ($str === false) { // not found $str2 = stristr($line, " PID:" ); // not found pid if ($str2 === false) { // not found if ($flg == true) { // continue pid data line echo $line; } } else { $flg = false; // continue pid data line flg off } } else { echo $line; $flg = true; // continue pid data line flg on } } fclose($fp); ?>
2.パラメータを指定しないで実行すると使用法表示されます。
[xxxxxxx@yyyyyy xxxx]$ ./pstgrSqlPid.php
This is a command line PHP script with 2 option. Usage: ./pstgrSqlPid.php <option1> <option2> <option1> postgres sqlLog file name. <option2> pid.
3.postgreログファイル名と抽出したいPIDを指定して実行した例
[xxxxxxx@yyyyyy xxxx]$ ./pstgrSqlPid.php postgreLog.txt 123
(1)PID=123で抽出した例
[2009-10-09 22:20:00 JST] PID:123 DB:[unknown] USER:[unknown] LOG: connection received: host=[xxxxx] [2009-10-09 22:20:01 JST] PID:123 DB:postgres USER:postgres LOG: duration: 999.999 ms statement: select pg_start_xxxxxx('online_xxxxxx') ; [2009-10-09 22:20:03 JST] PID:123 DB:postgres USER:postgres LOG: duration: 99.999 ms statement: select xxxxx from aaaa where ccc=111 and ddd=111; [2009-10-09 22:20:03 JST] PID:123 DB:postgres USER:postgres LOG: duration: 99.999 ms statement: select xxxxx from aaaa where ccc=222 and ddd=222; [2009-10-09 22:20:03 JST] PID:123 DB:postgres USER:postgres LOG: duration: 99.999 ms statement: select pg_stop_xxxxxx(); [2009-10-09 22:20:03 JST] PID:123 DB:postgres USER:postgres LOG: duration: 99.999 ms statement: select xxxxx from aaaa where ccc=444 and ddd=444;
(2)抽出前のpostgresのSQLログ内容
[2009-10-09 22:20:00 JST] PID:123 DB:[unknown] USER:[unknown] LOG: connection received: host=[xxxxx] [2009-10-09 22:20:00 JST] PID:2568 DB:postgres USER:postgres LOG: connection authorized: user=postgres database=postgres [2009-10-09 22:20:01 JST] PID:123 DB:postgres USER:postgres LOG: duration: 999.999 ms statement: select pg_start_xxxxxx('online_xxxxxx') ; [2009-10-09 22:20:01 JST] PID:1234 DB:postgres USER:postgres LOG: disconnection: session time: 9:9:9.9 user=postgres database=postgres host=[xxxxx] [2009-10-09 22:20:03 JST] PID:123 DB:postgres USER:postgres LOG: duration: 99.999 ms statement: select xxxxx from aaaa where ccc=111 and ddd=111; [2009-10-09 22:20:03 JST] PID:123 DB:postgres USER:postgres LOG: duration: 99.999 ms statement: select xxxxx from aaaa where ccc=222 and ddd=222; [2009-10-09 22:20:03 JST] PID:2568 DB:[unknown] USER:[unknown] LOG: connection received: host=[xxxxx] [2009-10-09 22:20:03 JST] PID:24422 DB:postgres USER:postgres LOG: connection authorized: user=postgres database=postgres [2009-10-09 22:20:03 JST] PID:123 DB:postgres USER:postgres LOG: duration: 99.999 ms statement: select pg_stop_xxxxxx(); [2009-10-09 22:20:03 JST] PID:24422 DB: USER: LOG: archived transaction log file "xxxxx.xxxxxx.xxxxxx" [2009-10-09 22:20:03 JST] PID:24422 DB:postgres USER:postgres LOG: duration: 99.99 ms statement: select xxxxx from aaaa where ccc=333 and ddd=333; [2009-10-09 22:20:03 JST] PID:24422 DB:postgres USER:postgres LOG: disconnection: session time: 9:99:99.99 user=postgres database=postgres host=[xxxxx] [2009-10-09 22:20:03 JST] PID:123 DB:postgres USER:postgres LOG: duration: 99.999 ms statement: select xxxxx from aaaa where ccc=444 and ddd=444;
追加:『#!/usr/local/php/bin/php』はサーバ環境に合わせないといけない。
12:00-19:00