SqlSyncによるデータベースの同期化

  SqlSync では2つのデータベースを比較して、どのタプルが追加され、除かれ、変更されたかを知ることができる。また、一方のデータベースを他方のクローンにし、必要な変更を加えながらその状態を保つことができる。同期化にSqlSyncを用いることには、異種エンジン間(たとえば、MySQLからPostgreSQLへ)の同期化ができるというメリットがある。

 Ubuntu、Fedora、openSUSEの標準リポジトリにはSqlSyncパッケージがない。そこで、本稿ではバージョン1.0.0-rc1を使い、64ビットのFedora 8マシンでソースからビルドすることにした。プロジェクトのfreshmeatページにもホームページにも書かれていないが、SqlSyncはデータベースへのアクセスにODBCを使う。したがって、unixODBC開発パッケージがインストールされていないと、ビルドは成功しない。

 SqlSyncはautotoolsによる自動ビルドを行わず、Makefileを使うので、ビルドに失敗したときは、Makefile.logを見れば失敗理由がわかる。筆者は、-fPIC 位置独立コードオプションなしで共有ライブラリをmakeしようとして、ビルドに失敗したことがある。PICコードの特長はメモリ中のどこにでもロードできることであり、共有ライブラリのコードのコンパイルでこれを使えば、2つのライブラリが同じアドレスを使いたいと主張するとき、一方を別のアドレスに移動できて便利である。また、静的リンクバージョンのビルドでも、やはりリンクに関連して別の問題が発生した。これの解決には、Makefile.defを次のように変更して、makeを実行し、SqlSyncをビルドした。これで、静的にリンクされたsrc/sqlsync_staticツールが得られる。

$ vi Makefile.def
-  ODBC_STATIC_LIB=`odbc_config --static-libs` -L`odbc_config --lib-prefix` -liconv
+  ODBC_STATIC_LIB=`odbc_config --static-libs` -L`odbc_config --lib-prefix` -ldl
$ make  WITH_SHARED=NO WITH_STATIC=YES clean  all
...

 sqlsyncコマンドで指定する主なオプションには、ソースと宛先のODBCデータソース名(DSN)、比較したいテーブルと列の特定、同期化のためのクエリを表示したいのか実行したいのか、などがある。ODBC DSNとは、データベースや使用ドライバ、その他の設定パラメータを特定するための名前である。クエリ表示なのかクエリ実行(宛先データベースの更新)なのかを指定せずにsqlsyncを実行すると、加えられた変更の要約が表示される。

 手始めに、2つのPostgreSQLデータベース、sourcedbとdestinationdbでテストしてみよう。データベースの初期セットアップは下に示すとおりである。sqlsyncを実行する前に、destinationdbにデータベーススキーマがなければならないことに注意。下記の最後のコマンドでは、sourcedbからdestinationdbにスキーマだけがダンプされる。つまり、destinationdbにはテーブルが存在するものの、タプルは存在しない。

$ psql
ben=# create database sourcedb;
CREATE DATABASE
ben=# create database destinationdb;
CREATE DATABASE
ben=# \c sourcedb
sourcedb=# create table customers ( cid serial primary key, name varchar(100) ) ;
sourcedb=# create table info ( id serial primary key, cid int references customers(cid), note text );
sourcedb=# insert into customers values ( default, 'fred' );
sourcedb=# insert into customers values ( default, 'john' );
sourcedb=# insert into customers values ( default, 'peter' );
sourcedb=# insert into info values ( default,
               (select cid from customers where name='fred'), 'fred is home' );
sourcedb=# insert into info values ( default,
               (select cid from customers where name='john'), 'john is with fred' );
sourcedb=# insert into info values ( default,
               (select cid from customers where name='fred'), 'a second note for him' );
sourcedb=# \q

$ pg_dump -c -s  sourcedb  | psql destinationdb

 わかりやすくするために、ODBC DSNをpostgresqlデータベース名と同じにしよう。ODBC DSNのセットアップには、ODBCConfigグラフィカルクライアントを使用できる。以下では、まず、2つのODBC DSNの間にある違いの要約を表示してから、destinationdbをsourcedbと同じ状態にするためのSQLクエリを表示する。クエリの表示には、sqlsync --show-queriesオプションを使う。

$ sqlsync_static --src-dsn sourcedb --dst-dsn destinationdb --show-summary
Connected to dsn_src server sourcedb
Connected to dsn_dst server destinationdb
All available tables will be synchronized
User didn't specify list of tables to synchronize
Checking availability of specified tables
Comparing tables customers
Comparing tables customers completed
[BEGIN] -----------------------------
[SUMMARY] ---------------------------
Table customers
Number of columns 2
Equal rows 0
Different rows 0
Missing rows 3
Additional rows 0
[COLUMNS] ---------------------------
       cid| name|
[DIFF] ------------------------------
[MISSING] ---------------------------
         1| fred|
         2| john|
         3| peter|
[ADDITIONAL] ------------------------
[END] -------------------------------
Comparing tables info
Comparing tables info completed
[BEGIN] -----------------------------
[SUMMARY] ---------------------------
Table info
Number of columns 3
Equal rows 0
Different rows 0
Missing rows 3
Additional rows 0
[COLUMNS] ---------------------------
        id| cid| note|
[DIFF] ------------------------------
[MISSING] ---------------------------
         1|   1| fred is home|
         2|   2| john is with fred|
         3|   1| a second note for him|
[ADDITIONAL] ------------------------
[END] -------------------------------
Comparing tables sql_parts
Error occured while running SELECT query on source table,
 probably generated query is corrupted for sql_parts table
[unixODBC]Error while executing the query (non-fatal);
ERROR:  relation "sql_parts" does not exist
Comparing tables sql_parts completed
[BEGIN] -----------------------------
[SUMMARY] ---------------------------
Table sql_parts
...
[END] -------------------------------

$ sqlsync_static --src-dsn sourcedb --dst-dsn destinationdb --show-queries
Connected to dsn_src server sourcedb
Connected to dsn_dst server destinationdb
...
Creating synchronization queries
INSERT INTO "customers" ("cid", "name") VALUES (1, 'fred');
INSERT INTO "customers" ("cid", "name") VALUES (2, 'john');
INSERT INTO "customers" ("cid", "name") VALUES (3, 'peter');
Creating synchronization queries completed
Comparing tables info
Comparing tables info completed
Creating synchronization queries
INSERT INTO "info" ("id", "cid", "note") VALUES (1, 1, 'fred is home');
INSERT INTO "info" ("id", "cid", "note") VALUES (2, 2, 'john is with fred');
INSERT INTO "info" ("id", "cid", "note") VALUES (3, 1, 'a second note for him');
Creating synchronization queries completed
...

 --show-queriesオプションを--run-queriesに変えるだけで、いま表示したクエリ群を実行し、宛先データベースをソースデータベースと同じ状態に更新できる。この同期化の実行後に、ソースデータベースと宛先データベースに新しいタプルを追加してみよう。最後のコマンドでは、sqlsyncがソースデータベースの新タプルを宛先データベースに追加し、筆者が宛先データベースに追加しておいた新タプルを削除していることがわかる。

$ sqlsync_static --src-dsn sourcedb --dst-dsn destinationdb --run-queries
Connected to dsn_src server sourcedb
Connected to dsn_dst server destinationdb
...
Creating synchronization queries
Running UPDATE synchronization queries
Running DELETE synchronization queries
Running INSERT synchronization queries
Running synchronization query (0/3)
...

$ sqlsync_static --src-dsn sourcedb --dst-dsn destinationdb --show-queries
Connected to dsn_src server sourcedb
Connected to dsn_dst server destinationdb
...
Creating synchronization queries
Creating synchronization queries completed
Comparing tables info
Comparing tables info completed
Creating synchronization queries
Creating synchronization queries completed
...

$ psql sourcedb
sourcedb=# insert into info values ( default, (select cid from customers where name='john'), 'another' );
sourcedb=# \c destinationdb
destinationdb=# insert into info (cid, note) values ( (select cid from customers where name='peter'), 'this will be gone' );
destinationdb=# \q

$ sqlsync_static --src-dsn sourcedb --dst-dsn destinationdb --show-queries
...
Creating synchronization queries
DELETE FROM "info" WHERE "id"=20;
INSERT INTO "info" ("id", "cid", "note") VALUES (4, 2, 'another');
Creating synchronization queries completed

 次に、customersテーブルへの外部キー参照を行う新タプルをinfoテーブルに追加し、それからsqlsyncによる同期化を行ってみよう。

$ psql sourcedb
sourcedb=# insert into customers values ( default, 'luke' );
sourcedb=# insert into info values ( default, (select cid from customers where name='luke'), 'lucky' );
sourcedb=# \q

$ sqlsync_static --src-dsn sourcedb --dst-dsn destinationdb --show-queries
...
INSERT INTO "customers" ("cid", "name") VALUES (4, 'luke');
...
DELETE FROM "info" WHERE "id"=20;
INSERT INTO "info" ("id", "cid", "note") VALUES (4, 2, 'another');
INSERT INTO "info" ("id", "cid", "note") VALUES (5, 4, 'lucky');

$ sqlsync_static --src-dsn sourcedb --dst-dsn destinationdb --run-queries

異種DBMSエンジン間の同期化

 異種データベース間の同期化をテストするため、宛先となるMySQLデータベースを作り、これをdestinationdbと呼ぶことにする。ODBC DSNはmysql-destinationdbである。unixODBCからisqlコマンドを使えば、ODBC経由でデータベースに接続できる。この方法は、ODBCアクセス問題のトラブルシューティングにも便利に使える。MySQLデータベーススキーマには、serialデータ型でなく、単なる整数型を使うことにした。次に示すsqlsyncコマンドでは、userオプションとpasswordオプションを使い、MySQLデータベースDSNに接続している。

$ isql mysql-destinationdb user passwd
SQL> create table customers ( cid int primary key, name varchar(100) ) ;
SQL> create table info ( id int primary key, cid int references customers(cid), note text );
SQL> quit

$ sqlsync_static --src-dsn sourcedb \
 --dst-dsn mysql-destinationdb \
 --dst-user user --dst-password passwd \
 --show-queries
...
Creating synchronization queries
INSERT INTO `customers` (`cid`, `name`) VALUES (1, 'fred');
INSERT INTO `customers` (`cid`, `name`) VALUES (2, 'john');
INSERT INTO `customers` (`cid`, `name`) VALUES (3, 'peter');
INSERT INTO `customers` (`cid`, `name`) VALUES (5, 'luke');
...
INSERT INTO `info` (`id`, `cid`, `note`) VALUES (1, 1, 'fred is home');
INSERT INTO `info` (`id`, `cid`, `note`) VALUES (2, 2, 'john is with fred');
INSERT INTO `info` (`id`, `cid`, `note`) VALUES (3, 1, 'a second note for him');
INSERT INTO `info` (`id`, `cid`, `note`) VALUES (4, 2, 'another');
INSERT INTO `info` (`id`, `cid`, `note`) VALUES (6, 5, 'lucky');

 同期化するテーブルと列は、sqlsyncの--tablesオプションで制限できる。たとえば、「--tables customers[name],info[note]」とすれば、nameフィールドとnoteフィールドだけを同期化できる。これをテストするため、infoテーブルにidフィールドとnoteフィールドしかない新しいデータベースを作り、このデータベースに対して下記の同期化を実行した。

 特定の列だけを選択的に同期化するという機能は便利だが、現在のところ、テーブル名マッピング(たとえば、infoテーブルで宛先データベースのlimitedinfoテーブルを同期化すること)はできない。ただ、将来、そのような機能がSqlSyncに追加されない理由はない。

$ psql
ben# create database partdestdb
ben# \c partdestdb
partdestdb# create table info ( id serial primary key, note text );
partdestdb# \q

$ sqlsync_static --src-dsn sourcedb --dst-dsn partdestdb --tables info[id,note] --show-queries
Parsing tables and columns passed from command line
Parsing tables and columns completed
Connected to dsn_src server sourcedb
Connected to dsn_dst server partdestdb
Checking availability of specified tables
Comparing tables info
Comparing tables info completed
Creating synchronization queries
INSERT INTO "info" ("id", "note") VALUES (1, 'fred is home');
INSERT INTO "info" ("id", "note") VALUES (2, 'john is with fred');
INSERT INTO "info" ("id", "note") VALUES (3, 'a second note for him');
INSERT INTO "info" ("id", "note") VALUES (4, 'another');
INSERT INTO "info" ("id", "note") VALUES (6, 'lucky');
Creating synchronization queries completed

 データベースのバックアップ方法として、SqlSyncは十分に考慮に値する。レプリケーションツールを用意しているデータベースは多いが、SqlSyncでは、別のデータベースサーバを使ってデータベースのバックアップができる。あるデータベースエンジンから別のデータベースエンジンへの移行で、テスト中はそれぞれのデータベースサーバ上にデータベースを置いておきたい場合などに、便利に使用できる。

 SqlSyncは便利だが、完璧ではない。宛先データベースに自動的にスキーマをセットアップしてくれる機能などは、あって決して邪魔にならないと思う。

Ben Martinは、ファイルシステムに携わって10数年。現在、PH.D.も取得し、libferris、各種ファイルシステム、検索ソリューションを中心にコンサルティング事業を展開している。

Linux.com 原文