PostgreSQLに限らず、RDBMSにおいて「特定のテーブルの更新差分だけを取得したい」というのは、DBAの夢であると言えます（よね？）。
トランザクションログの中には当然ながら「更新情報」が記録されているわけですが、それを再利用可能な形で取り出す方法が無く、涙で枕を濡らした思い出を持つDBAも多いことでしょう。多分。
もう少し分かりやすく申しますと、PostgreSQLのメジャーバージョンアップの際などにはpg_dumpによるexport/importが必要となるわけですが、データ量が多くなって来ている今、データのexportにも時間がかかりますし、24x365のシステムも増えていますので、移行している間にもデータの更新が発生してしまいます。
なので、こういった作業の合間に発生する更新情報（少ないとは言え）を、保存しておいて、後から再利用できると便利ですよね？ ね？ という話であります。
■tablelogモジュール
というわけで、今回はPostgreSQLにおいて、テーブルの更新差分を取得する方法をご紹介します。
今回はtablelogというモジュールを使います。
PgFoundry: Table Audit: Project Info
http://pgfoundry.org/projects/tablelog/
tablelogモジュールは、テーブルデータに更新が発生した場合に、トリガを使ってその更新データを別のログテーブルに保存するモジュールです。
ですので、特定のテーブルにロギング用のトリガを設定しておくことで、後刻、ログテーブルから更新情報を時系列で取り出すことが可能になる、ということです。
余談ですが、「tablelog」をGoogleで検索すると「次の検索結果を表示しています: tabelog」と言われるのですが、ちょっと違います。惜しいですが。
■tablelogモジュールのインストール
それでは、tablelogモジュールをインストールしてみます。
tar.gzを展開して、環境変数USE_PGXSを1に設定しつつ、make & make install を実行します。
なお、今回はインストールスクリプトやロギングの設定用のSQL関数をいくつか追加していますので、パッチ（table_log-0_4_4_snaga.diff）も適用します。
table_log-0_4_4_snaga.diff
https://gist.github.com/4291369
[snaga@devsv02 pgsql]$ tar zxf table_log-0.4.4.tar.gz [snaga@devsv02 pgsql]$ cd table_log-0.4.4 [snaga@devsv02 table_log-0.4.4]$ ls Makefile table_log.c table_log_init.sql tests README.table_log table_log.h table_log.sql.in [snaga@devsv02 table_log-0.4.4]$ patch -p1 < ../table_log-0_4_4_snaga.diff patching file Makefile patching file table_log.sql.in patching file table_log_uninstall.sql.in [snaga@devsv02 table_log-0.4.4]$ env USE_PGXS=1 PATH=/usr/pgsql-9.1/bin:$PATH make sed 's,MODULE_PATHNAME,$libdir/table_log,g' table_log.sql.in >table_log.sql sed 's,MODULE_PATHNAME,$libdir/table_log_uninstall,g' table_log_uninstall.sql.in >table_log_uninstall.sql gcc -O2 -g -pipe -Wall -Wp,-D_FORTIFY_SOURCE=2 -fexceptions -fstack-protector --param=ssp-buffer-size=4 -m64 -mtune=generic -I/usr/include/et -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Wendif-labels -Wformat-security -fno-strict-aliasing -fwrapv -fpic -I. -I. -I/usr/pgsql-9.1/include/server -I/usr/pgsql-9.1/include/internal -I/usr/include/et -D_GNU_SOURCE -I/usr/include/libxml2 -I/usr/include -c -o table_log.o table_log.c gcc -O2 -g -pipe -Wall -Wp,-D_FORTIFY_SOURCE=2 -fexceptions -fstack-protector --param=ssp-buffer-size=4 -m64 -mtune=generic -I/usr/include/et -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Wendif-labels -Wformat-security -fno-strict-aliasing -fwrapv -fpic -L/usr/pgsql-9.1/lib -L/usr/lib64 -shared -o table_log.so table_log.o rm table_log.o [snaga@devsv02 table_log-0.4.4]$ su Password: [root@devsv02 table_log-0.4.4]# env USE_PGXS=1 PATH=/usr/pgsql-9.1/bin:$PATH make install /bin/mkdir -p '/usr/pgsql-9.1/share/contrib' /bin/mkdir -p '/usr/pgsql-9.1/lib' /bin/mkdir -p '/usr/share/doc/pgsql/contrib' /bin/sh /usr/pgsql-9.1/lib/pgxs/src/makefiles/../../config/install-sh -c -m 644 table_log.sql table_log_uninstall.sql '/usr/pgsql-9.1/share/contrib/' /bin/sh /usr/pgsql-9.1/lib/pgxs/src/makefiles/../../config/install-sh -c -m 755 table_log.so '/usr/pgsql-9.1/lib/' /bin/sh /usr/pgsql-9.1/lib/pgxs/src/makefiles/../../config/install-sh -c -m 644 ./README.table_log '/usr/share/doc/pgsql/contrib/' [root@devsv02 table_log-0.4.4]# exit exit [snaga@devsv02 table_log-0.4.4]$次に、tablelogを使用したいデータベースに対してインストールを行います。
[snaga@devsv02 table_log-0.4.4]$ psql -f /usr/pgsql-9.1/share/contrib/table_log.sql testdb2 SET CREATE FUNCTION CREATE FUNCTION (...snip...) CREATE FUNCTION CREATE FUNCTION [snaga@devsv02 table_log-0.4.4]$これでインストールは完了です。
■ターゲットとなるテーブルにロギングを設定する
まず、ターゲットとなるテーブルのサンプルを作成します。
testdb2=# CREATE table t1 ( uid integer primary key, uname text not null ); NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "t1_pkey" for table "t1" CREATE TABLE testdb2=#次に、上記で作成したテーブル t1 に対してログ取得を設定します。
ログの設定はSQL関数の table_log_init() を使用します。最初の引数はロギングのレベルで、3～5の値を取ります。
ロギングレベルの4はtrigger_idと呼ばれるIDも含めて保存し、ロギングレベル5は更新したユーザ名も含めて保存します（詳細は後述）。
ここではロギングレベルを3にして設定を行います。
testdb2=# SELECT table_log_init(3, 't1');
table_log_init
----------------
(1 row)
testdb2=# \d+
List of relations
Schema | Name | Type | Owner | Size | Description
--------+--------+-------+-------+---------+-------------
public | t1 | table | snaga | 0 bytes |
public | t1_log | table | snaga | 0 bytes |
(2 rows)
testdb2=#
■テーブルを更新してログを取得する
それでは、実際にテーブルにレコードをINSERTしてみましょう。
testdb2=# SELECT * FROM t1; uid | uname -----+------- (0 rows) testdb2=# INSERT INTO t1 VALUES ( 101, 'satoshi nagayasu' ); INSERT 0 1 testdb2=# SELECT * FROM t1; uid | uname -----+------------------ 101 | satoshi nagayasu (1 row) testdb2=# SELECT * FROM t1_log; uid | uname | trigger_mode | trigger_tuple | trigger_changed -----+------------------+--------------+---------------+------------------------------- 101 | satoshi nagayasu | INSERT | new | 2012-11-26 13:34:01.218549+09 (1 row) testdb2=#上記のように、テーブルt1に対して、そのログテーブルであるテーブルt1_logに、カラムのデータに加えて、トリガの種別（INSRET/UPDATE/DELETE）、タプルの種別（更新前のデータoldか、更新後のデータnewか）と、トリガが実行された時刻が保存されています。
次に、データのUPDATEを行ってみます。ここでは、小文字を大文字に変換してみます。
testdb2=# UPDATE t1 SET uname = upper(uname); UPDATE 1 testdb2=# SELECT * FROM t1; uid | uname -----+------------------ 101 | SATOSHI NAGAYASU (1 row) testdb2=# SELECT * FROM t1_log; uid | uname | trigger_mode | trigger_tuple | trigger_changed -----+------------------+--------------+---------------+------------------------------- 101 | satoshi nagayasu | INSERT | new | 2012-11-26 13:34:01.218549+09 101 | satoshi nagayasu | UPDATE | old | 2012-11-26 13:34:38.38462+09 101 | SATOSHI NAGAYASU | UPDATE | new | 2012-11-26 13:34:38.38462+09 (3 rows) testdb2=#今度は、UPDATEのログのため、trigger_modeがUPDATEとなり、更新前のレコード（old）と更新後のレコード（new）の両方が記録されています。
■ロギングの停止
なお、ロギングを停止したい場合には、disable_table_log()関数を使うことで停止することができます。（これはオリジナルには無く、今回適用したパッチで追加されるSQL関数です）
testdb2=# SELECT disable_table_log('t1');
disable_table_log
-------------------
(1 row)
testdb2=# \d t1+
Table "public.t1"
Column | Type | Modifiers
--------+---------+-----------
uid | integer | not null
uname | text | not null
Indexes:
"t1_pkey" PRIMARY KEY, btree (uid)
testdb2=#
■ログレベルの違い
ログレベルを4にするとtrigger_idと呼ばれる値が追加され、ログレベルを5にすると更新を実行したユーザも記録されるようになります。
trigger_idというのは、ロギングの対象テーブルにプライマリキーが存在しない場合に、プライマリキーの代替えとしてレコードを一意に識別するために設定される BIGSERIAL 値です。
以下は、テーブルt1, t2, t3について、それぞれログレベルを変えて変更差分を取得したものです。見ていただくと分かりますが、ログレベルによってログテーブルのカラム数が違っています。
testdb2=# \x Expanded display is on. testdb2=# SELECT * FROM t1_log; -[ RECORD 1 ]---+------------------------------ uid | 101 uname | satoshi nagayasu trigger_mode | INSERT trigger_tuple | new trigger_changed | 2012-11-26 13:48:24.141572+09 testdb2=# SELECT * FROM t2_log; -[ RECORD 1 ]---+------------------------------ uid | 101 uname | satoshi nagayasu trigger_mode | INSERT trigger_tuple | new trigger_changed | 2012-11-26 13:48:24.142149+09 trigger_id | 1 testdb2=# SELECT * FROM t3_log; -[ RECORD 1 ]---+------------------------------ uid | 101 uname | satoshi nagayasu trigger_mode | INSERT trigger_tuple | new trigger_changed | 2012-11-26 13:48:24.142683+09 trigger_id | 1 trigger_user | snaga testdb2=#
■まとめ
というわけで、今回はテーブルの更新情報を取得、保存するtablelogモジュールを御紹介しました。
データベースのデータが大きくなり、かつメンテナンス時間を取りづらい昨今、テーブルの更新情報を取得してうまく使えば、運用管理がもっと容易になるケースが多くあると思います。
ぜひ、こういったモジュールもうまく使いこなしていただければと思います。
ではまた。
0 件のコメント:
コメントを投稿