サーバログをSQLで覗いてみる

PostgreSQL 9.1Alpha4 がリリースされて、SQL/MED によるクエリが実行できるようになりました。CSV 形式のサーバログと file_fdw を使うことで、最新のサーバログも SQL で参照できそうだなぁという思いつきをメモ。

サーバログ格納テーブルの作成

この部分は旧バージョンでも可能だった部分です。CSV書式のログ出力の利用 の手順に従って、以下の設定をしておきます。

• CSV 形式のログファイルを出力する
• ログローテーションのタイミングを制御する
• サーバログを格納するためのテーブルを作成する

なお、postgres_log という名前は別のテーブルに使うので、ここではテーブル名を postgres_log_archived にしておきます。

ここまでの手順で、ログローテーションした文については、SQL 文でサーバログを参照できるようになりました。

file_fdw のインストール

ここからが 9.1 ならではの部分です。

file_fdw を使うには、事前にビルド＋インストールを済ませる必要があります。

$ cd postgresql-9.1alpha4/contrib/file_fdw
$ make; make install

9.1から、contrib モジュールをデータベースにインストールする際には CREATE EXTENSION コマンドを使うようになっています。このコマンドで自動的に外部データラッパの file_fdw が作成されますので、それを使って外部サーバの localfs を作成します。USER MAPPING は必須ではないので、今回は省略します。

CREATE EXTENSION file_fdw;
CREATE SERVER localfs FOREIGN DATA WRAPPER file_fdw;

最新ログ用外部テーブルの作成

まだ postgres_log_archived に格納していない最新のサーバログファイルの内容を参照するために、file_fdw を使う外部テーブルである postgres_log_current を定義します。

CREATE FOREIGN TABLE postgres_log_current
(
  log_time timestamp(3) with time zone,
  user_name text,
  database_name text,
  process_id integer,
  connection_from text,
  session_id text,
  session_line_num bigint,
  command_tag text,
  session_start_time timestamp with time zone,
  virtual_transaction_id text,
  transaction_id bigint,
  error_severity text,
  sql_state_code text,
  message text,
  detail text,
  hint text,
  internal_query text,
  internal_query_pos integer,
  context text,
  query text,
  query_pos integer,
  location text,
  application_name text
)
  SERVER localfs
  OPTIONS (format 'csv', filename '/path/to/csv');

参照用ビューの作成

このままだと個別の検索しかできないので、ビューで統合します。

CREATE VIEW postgres_log AS SELECT * FROM postgres_log_archived
  UNION ALL SELECT * FROM postgres_log_current;

この postgres_log ビューを検索すれば、過去分から現在までのログに SQL でアクセスできます。もちろん、WHERE 句での絞り込みや ORDER BY 句でのソートも可能です。ただし、サーバログを書き込んでいる最中に参照すると、CSV パースエラーになる可能せいがあります。

過去ログテーブルのパーティション化

データが大量にある場合、postgres_log_archived をパーティション化したいですよね。外部テーブルで継承とチェック制約がサポートされれば、サーバログを実テーブルに取り込むことなく、親テーブル＋外部テーブルで時系列パーティションにすぐ対応できるんですが…現状だと、サーバログファイルごとに postgres_log_archived テーブルを log_time をキーに分割するくらいでしょうか。

また、データベース名など log_time 以外の項目をパーティションキーとする場合は、取りこみ対象ファイルを読み込む外部テーブルを定義しておいて INSERT 〜 SELECT でデータベースごとのテーブルにロードする、なんていう使い方もできます。

CREATE FOREIGN TABLE postgres_log_20110315 (...) SERVER localfs OPTIONS (...);
INSERT INTO postgres_log_archived_foo
  SELECT * FROM postgres_log_20110315 WHERE database_name = 'foo';
INSERT INTO postgres_log_archived_bar
  SELECT * FROM postgres_log_20110315 WHERE database_name = 'bar';
DROP FOREIGN TABLE postgres_log_20110315;

CREATE TEMP FOREIGN TABLE ができると、最後の DROP が不要になるかもしれません。