PostgreSQL Deep Dive: tablelog extension を使ってDB移行に必要なテーブルの更新差分のログを取得する

先日開催されたPostgreSQLアンカンファレンスで tablelog という extension の話をしたのですが、本エントリでは改めてその紹介をさせていただこうと思います。

第10回PostgreSQLアンカンファレンス - Togetter
https://togetter.com/li/1315752

■DB移行やメジャーバージョンアップの時、、、

皆さんは、

システム更改によるDB移行
PostgreSQLのバージョンアップ
特定のテーブルだけ別インスタンスにコピーしたい

といったことをしたい場合に、どのように対処しているでしょうか？

その方式は？
ツールは何を使う？
ダウンタイムは？
DBaaSの場合はどうする？

場合によって変わってくるかと思いますが、皆さんはどのように対処しているでしょうか？

もっともシンプルな方法は Dump & Restore だと思いますが、データベースの規模が大きくなってきた状態だと非常に時間がかかる場合があり、単純な Dump & Restore だと数日間データベースを停止しなければならない、といった見積もりになることもあります。

■更新差分だけを取得・適用して追い付きたい

そういう状況で次に考えるのは、「データベースを一旦コピーしておいて、後から更新差分だけ適用して追い付きたい」という方式です。

以下の図で言うと、

「インスタンスA」から「インスタンスB」に「テーブル1」を移行しようとする場合に、
① 稼働系のデータベースに通常の更新処理が行われている間、
② 更新処理をログとして取得しておき、
③ 更新が行われているかどうかに関わらずデータベースをコピーし、
④ 稼働系のデータベースへの更新を一時的に停止して、
⑤ 蓄積しておいた更新ログを新しいデータベースに適用して最新化し、
⑥ 新しいデータベースを稼働系にする切り替えを行う

という流れです。

この時必要とされる機能は、②の「更新処理をログとして取得しておく」という機能になります。

「更新処理のログ」というのは、要するにINSERT/UPDATE/DELETEに関する情報のことで、

どのようなレコードをINSERTしたのか？
どのレコードをどのようにUPDATEしたのか？
どのレコードをDELETEしたのか？

といった情報を蓄積できれば、後からいろいろ活用できるはず、と言うことになります。

■PostgreSQLで更新処理のログを取得するには

PostgreSQLで更新処理のログを取得するには、現時点では大きく2つの方法があります。「Logical Decoding」を使う方法と「テーブルトリガ」を使う方法です。

Logical Decodingを使う方法は新しくてPostgreSQLらしい洗練された方式ではあるのですが、バージョンや稼働環境を選ぶのと、PostgreSQLエンジニア以外に使ってもらうにはちょっとハードルが高くなるかもしれません。

なお、Logical Decodingについては、以前書いた以下のエントリを参考にしてください。

PostgreSQL Deep Dive: Logical Decodingを使ったCDC（Change Data Capture）の実現方法を考えてみる
http://pgsqldeepdive.blogspot.com/2016/12/logical-decoding-change-data-capture.html

テーブルトリガを使う方法は、昔からある伝統的な方法で特に新しくも無いのですが、誰でも簡単に理解できて（ツールさえあれば）使える方法であると言えるでしょう。

実際、以下のようなエントリを書いたことがありました。

PostgreSQL Deep Dive: tablelogでテーブルの更新差分を取得する
http://pgsqldeepdive.blogspot.com/2012/12/tablelog.html

但し、上記のエントリで使った tablelog は C 言語で実装されていてビルドやサーバへのデプロイが必要なため、今日的な DBaaS 環境では使うことができません。

そのため、改めて（DBaaS環境でも）テーブル更新差分を取得することができる extension を作ることにしました。

■tablelog pl/v8版

さて、先に要件だけ書き出してしまうと、今回は以下のような要件で開発しました。

DBaaS環境で利用できること（PLなんちゃら、で実装されていること）
モダンな言語で実装されていること
extension としてパッケージングされていること

というわけで、とりあえずは RDS for PostgreSQL と Azure Database for PostgreSQL をターゲットとして pl/v8 で実装することにしました。

実は、「extension としてパッケージング」と「DBaaS環境で利用できること」は背反する条件だったりするのですが（extensionはサーバにデプロイが必要なので）、この点については今回は別の方法で逃げることにしました。

pl/v8 版の tablelog extension は以下のレポジトリから取得できます。

snaga/tablelog: A PostgreSQL extension for capturing table modifications with table trigger
https://github.com/snaga/tablelog

なお、pl/pgsqlではなくpl/v8を使ったのは、トリガーの内部で受け取ったレコードのカラム名を取得する時に、連想配列のキーとして取得できるためです。

new_cols = Object.keys(NEW);
old_cols = Object.keys(OLD);

のようなカラム名の取り出し方は pl/pgsql では実現できないのですが、pl/v8（やpl/perl）だとこれが可能なので、トリガー関数を汎用的に作成することができるようになります。

■tablelogのインストール

詳細は README に記載していますが、スタンドアロンのPostgreSQLの場合は create extension コマンドで、

env USE_PGXS=1 make install
psql -c 'create extension plv8' dbname
psql -c 'create extension tablelog' dbname

DBaaS の場合はSQLファイルを編集して（\echo行をコメントアウトして） psql コマンドで流し込んでください。

vi tablelog--X.X.sql
psql -c 'create extension plv8' dbname
psql -f tablelog--X.X.sql dbname

■tablelogの使い方

まず、テーブルを作成して、tablelog_enable_logging() 関数でログ取得を有効化します。

testdb=# create table t (uid integer primary key, uname text);
CREATE TABLE
testdb=# select tablelog_enable_logging('public', 't');
 tablelog_enable_logging
-------------------------
 t
(1 row)

testdb=#

するとテーブルにトリガが設定されます。

testdb=# \d t
                 Table "public.t"
 Column |  Type   | Collation | Nullable | Default
--------+---------+-----------+----------+---------
 uid    | integer |           | not null |
 uname  | text    |           |          |
Indexes:
    "t_pkey" PRIMARY KEY, btree (uid)
Triggers:
    public_t_trigger AFTER INSERT OR DELETE OR UPDATE ON t FOR EACH ROW EXECUTE PROCEDURE tablelog_logging_trigger('uid')

testdb=#

この状態でテーブルのレコードを更新（INSERT/UPDATE/DELETE）すると、 __table_logs__ テーブルにログが記録されます。

testdb=# insert into t values ( 1, 'name 1');
INSERT 0 1
testdb=# select * from __table_logs__ ;
             ts             |  txid  |  dbuser  | schemaname | tablename | event  |  col_names  |  old_vals   |   new_vals   | key_names | key_vals | status
----------------------------+--------+----------+------------+-----------+--------+-------------+-------------+--------------+-----------+----------+--------
 2019-02-05 10:07:45.868346 | 244086 | postgres | public     | t         | INSERT | {uid,uname} |             | {1,"name 1"} | {uid}     | {1}      |      0
(1 row)

testdb=#

UPDATE/DELETEでも同様に記録されます。

testdb=# update t set uname = 'uname 11';
UPDATE 1
testdb=# delete from t;;
DELETE 1
testdb=# select * from __table_logs__ ;
             ts             |  txid  |  dbuser  | schemaname | tablename | event  |  col_names  |    old_vals    |   new_vals   | key_names | key_vals | status
----------------------------+--------+----------+------------+-----------+--------+-------------+----------------+--------------+-----------+----------+--------
 2019-02-05 10:07:45.868346 | 244086 | postgres | public     | t         | INSERT | {uid,uname} |                | {1,"name 1"} | {uid}     | {1}      |      0
 2019-02-05 10:28:54.124243 | 244088 | postgres | public     | t         | UPDATE | {uname}     | {"name 1"}     | {"uname 11"} | {uid}     | {1}      |      0
 2019-02-05 10:29:13.115856 | 244090 | postgres | public     | t         | DELETE | {uid,uname} | {1,"uname 11"} |              | {uid}     | {1}      |      0
(3 rows)

testdb=#

ログテーブルの構造の詳細については README を参照してください。

tablelog/README.md at master · snaga/tablelog
https://github.com/snaga/tablelog/blob/master/README.md

■まとめ

というわけで、本エントリではテーブルの更新差分をトリガーで取得するための汎用的なextension「tablelog」を紹介しました。

データベースで扱うデータ量が膨らむ一方の現在、アプリケーション更改におけるデータベース移行や、データベースのメジャーバージョンアップによるデータベース移行など、さまざまなところで「ダウンタイムを最小化してデータベースを移行したい」というニーズは高まる一方のように思います。

一方で、DBaaS環境に代表されるように、さまざまな制約の中でこういった移行作業を実現しなければならない現実もあります。

ぜひ、本エントリで紹介した tablelog のようなツールを使いこなして、DBaaS環境であっても、安全、確実、かつ最小のダウンタイムで実現していただければと思います。

では。

PostgreSQL Deep Dive

2019年2月5日

tablelog extension を使ってDB移行に必要なテーブルの更新差分のログを取得する